Apache OpenOffice (AOO) Bugzilla – Issue 123294

Implementation of YEARFRAC function inconsistent with Excel

Last modified: 2017-05-20 09:57:32 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.

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 ).

Created attachment 81579 [details] Excel 2010 vs. Calc 4

I've installed Obba for Excel and Calc. The spreadsheets at comment 1 look like in attachment 81579 [details]. Please advise.

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.

No info from author.

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

(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).