https://issues.apache.org/ooo/show_bug.cgi?id=123294
Bug ID: 123294 Issue Type: DEFECT Summary: Implementation of YEARFRAC function inconsistent with Excel Product: Calc Version: 4.0.0 Hardware: All OS: All Status: UNCONFIRMED Severity: normal Priority: P3 Component: code Assignee: issues@openoffice.apache.org Reporter: em...@christian-fries.de CC: issues@openoffice.apache.org 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. -- You are receiving this mail because: You are on the CC list for the bug. You are the assignee for the bug. You are watching all bug changes.