Issue 93527

Summary: ODFF: INTRATE and RECEIVED functions different to Excel
Product: Calc Reporter: drking <openoffice>
Component: programmingAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact: Rob Weir <robweir>
Severity: Trivial    
Priority: P4 CC: elish, issues, rainerbielefeld_ooo_qa, rb.henschel
Version: OOo 2.4.1   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: DEFECT Latest Confirmation in: 4.1.0-dev
Developer Difficulty: ---
Attachments:
Description Flags
comparison spreadsheet
none
Enhanced Sample Document based on Edwin's none

Description drking 2008-09-05 17:40:03 UTC
Both these functions involve deciding how many days are in a year (and more 
particularly, *which* year); Calc and Excel do it slightly differently in basis 
1.

Calc uses the year containing the settlement date.
Excel is more subtle - I think it is documented in OOXML, but I'm not sure if 
Excel actually follows that exactly. David Wheeler has done some work on this 
for ODFF. It's something like "366 only if Feb29 is included, otherwise 365".

Examples:
INTRATE("2008-09-02"; "2009-03-02"; 1000; 1080;1) 
RECEIVED("2008-09-01"; "2009-03-31"; 10000; 5%; 1)

This is not of great importance - but worth making a note of I hope.
Comment 1 Edwin Sharp 2014-01-02 17:34:54 UTC
Created attachment 82202 [details]
comparison spreadsheet
Comment 2 Edwin Sharp 2014-01-02 17:36:23 UTC
Confirmed with
AOO410m1(Build:9750)  -  Rev. 1552994
Rev.1552994
Excel 2010
Comment 3 Rainer Bielefeld 2014-01-31 17:14:52 UTC
I also can confirm the difference, but who can know who is right? I also tested Gnumeric and SoftMake FreeOffice (with an .xls created from Edwins document): Both agree with AOO.

So I am not sure whether this one really simply needs a fix, may be some more discussion will be required.

I will attach a new sample document where I added SoftMake FreeOffice and Gnumeric results.

Some more info INTRATE:
<https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_INTRATE_function>
" ...  It is not clear which is theoretically correct", mentions this Bug here

Some more info RECEIVED:
<https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_RECEIVED_function>
" ...  It is not clear which is theoretically correct", mentions this Bug here

@drking
I see you also were active there in the wiki, if you have new information underpinning that Excel does it right, can you please add some info in the Wiki?
Comment 4 Rainer Bielefeld 2014-01-31 17:15:47 UTC
Created attachment 82455 [details]
Enhanced Sample Document based on Edwin's
Comment 5 Rob Weir 2014-01-31 19:42:04 UTC
AOO follows the ODF 1.2 standard.  The financial functions in ODF 1.2 were defined to match what Excel does, since that is the de facto standard.  That was the explicit goal in developing the formula definitions in ODF 1.2.

So, if AOO differs from Excel is likely a bug in AOO.
Comment 6 Rainer Bielefeld 2014-01-31 22:23:46 UTC
Thx for explication, I will adapt Wiki tomorrow.