Issue 123294 - Implementation of YEARFRAC function inconsistent with Excel
Summary: Implementation of YEARFRAC function inconsistent with Excel
Status: CLOSED IRREPRODUCIBLE
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: 4.0.0
Hardware: All All
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords: needmoreinfo
Depends on:
Blocks:
 
Reported: 2013-09-19 11:34 UTC by cfries
Modified: 2017-05-20 09:57 UTC (History)
3 users (show)

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


Attachments
Excel 2010 vs. Calc 4 (497.64 KB, image/jpeg)
2013-09-19 15:31 UTC, Edwin Sharp
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description cfries 2013-09-19 11:34:04 UTC
The implementation of YEARFRAC(start, end, basis) for basis = 1 does not agree with
a) the Excel implementation and
b) with the OASIS Documentation (remark: in addition it appears as if the OASIS Documentation has a typo) and
c) with the LibreOffice implementation (which doesn't agree with Excel either).

A re-implementaton of the Excel 2013 YEARFRAC(start, end, basis) can be found here:
http://svn.finmath.net/finmath%20lib/trunk/src/main/java/net/finmath/time/daycount/DayCountConvention_ACT_ACT_YEARFRAC.java

A spreadsheet to test the day count methods can be found at
http://finmath.net/spreadsheets/Day%20%Count%Fractions.zip


** On the OASIS Documentation **

The algorithm documented in https://www.oasis-open.org/committees/document.php?document_id=39507 appears to be not compliant with Excel's implementation. For Procedure E line 65 states "if A and is-leap-year(year(date1)) then return 366". However, condition A is "year1 != year2". It appears as if this would imply the rule "if is-leap-year(year(date1)) and is-leap-year(year(date2)) then return 365" (which is not what OpenOffice is doing, neither LibreOffice, nor Excel - and which does not make sense).

For the implementation of Excel line 65 should read

8. Otherwise, if is-leap-year(year(date1)) and is-leap-year(year(date2)) return 366.

LibreOffice is a bit closer to Excel than OpenOffice is, but both are wrong. LibreOffice 4.1 implements in the rule 8. as "is-leap-year(year(date1)) OR is-leap-year(year(date2))"


** On the Excel Implementation **

In another comment it was claimed, that Excel implements ACT/ACT AFB. I do find a proof for this claim. In fact, I believe that ACT/ACT AFB is slightly different.
That said, I would like to remark, that in many financial applications act/act day count fraction are calculated using ACT/ACT ISDA. This method has some advantages and the algorithm is much simpler. An implementation of ACT/ACT ISDA can be found at http://svn.finmath.net/finmath%20lib/trunk/src/main/java/net/finmath/time/daycount/DayCountConvention_ACT_ACT_ISDA.java

See also http://svn.finmath.net/finmath%20lib/trunk/src/main/java/net/finmath/time/daycount/ and http://finmath.net/topics/daycountingandschedules


** Test Cases **

YEARFRAC(30.08.1984, 06.07.1990, 1)
OpenOffice  4.0:	5,850...	(NOT OK)
LibreOffice 4.1:	5,847...	(OK)
Excel 2013.....:	5,847...

YEARFRAC(30.12.1999, 04.01.2000, 1)
OpenOffice  4.0:	5/365		(OK)
LibreOffice 4.1:	5/366		(NOT OK)
Excel 2013.....:	5/365

YEARFRAC(30.12.2000, 04.01.2001, 1)
OpenOffice  4.0:	5/366		(NOT OK)
LibreOffice 4.1:	5/366		(NOT OK)
Excel 2013.....:	5/365


** Suggested Fixes **

- Make the implementation compliant with Excel's implementation (both do not implement a standard, so I would call Excel's implementation a reference).
- Make the documentation compliant with Excel's implementation.
- Consider adding ACT/ACT ISDA.
Comment 1 cfries 2013-09-19 11:40:05 UTC
Note: There was a typo in the url to the test spreadsheets. The url is http://finmath.net/spreadsheets/Day%20Count%20Fractions.zip

(I could not attache that file, due to file size limitations. The spreadsheet requires the OpenOffice Add-In Obba.oxt from http://www.obba.info ).
Comment 2 Edwin Sharp 2013-09-19 15:31:31 UTC
Created attachment 81579 [details]
Excel 2010 vs. Calc 4
Comment 3 Edwin Sharp 2013-09-19 15:33:32 UTC
I've installed Obba for Excel and Calc.
The spreadsheets at comment 1 look like in attachment 81579 [details].

Please advise.
Comment 4 Regina Henschel 2013-09-19 16:00:46 UTC
Rob Weir has pointed to his test document at http://www.robweir.com/basis-test.xls in dev@openoffice.apache.org (http://www.mail-archive.com/dev@openoffice.apache.org/msg11342.html), which he had produced to cover all cases which are possible in ODF. Please use that document, to test whether AOO calculates correctly.

If Excel has results, that are different from ODF, then AOO would need an additional function YEARFRAC_ADD, which mimics Excels calculation. Functions defined in ODF should always calculate as specified.
Comment 5 Edwin Sharp 2013-12-14 17:26:19 UTC
No info from author.
Comment 6 cfries 2013-12-14 22:35:09 UTC
Hi Edwin.

Sorry, I did not see that you had a problem to run the spreadsheet. It works for me. I will check that. Can you try again? What error message do you see?

However, why then is this report closed. Even if my demo sheet does not work, I gave test cases. I can give more, if needed (should I then reopen this report)?

Note: The implementation of YEARFRAC has from OO 3.x to 4.x - so maybe I have to update some of my test.

Best
Christian
Comment 7 cfries 2013-12-15 07:24:34 UTC
(In reply to Regina Henschel from comment #4)
> 
> If Excel has results, that are different from ODF, then AOO would need an
> additional function YEARFRAC_ADD, which mimics Excels calculation. Functions
> defined in ODF should always calculate as specified.

The problem is that if AOO opens an XLS file (claiming XLS compatibility) then the Excel YEARFRAC function is mapped to the AOO YEARFRAC function. I assume that all users expect the sheet to work "as in Excel". Hence: if you use the same name as Excel function THAT function should mimic Excels calculation.

PS: My bug report was that a) The AOO Spec does not agree with Excel and b) The AOO implementation did not agree with the spec (that might have changed with AOO 4 - I will check).