Issue 93527 - ODFF: INTRATE and RECEIVED functions different to Excel
Summary: ODFF: INTRATE and RECEIVED functions different to Excel
Status: CONFIRMED
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: OOo 2.4.1
Hardware: All All
: P4 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact: Rob Weir
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2008-09-05 17:40 UTC by drking
Modified: 2014-01-31 22:23 UTC (History)
4 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: 4.1.0-dev
Developer Difficulty: ---

Attachments
comparison spreadsheet (9.70 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-01-02 17:34 UTC, Edwin Sharp 		no flags Details
Enhanced Sample Document based on Edwin's (11.02 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2014-01-31 17:15 UTC, Rainer Bielefeld 		no flags Details
Show Obsolete (1) Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
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.