https://bugs.freedesktop.org/show_bug.cgi?id=69569
Priority: medium Bug ID: 69569 Assignee: libreoffice-bugs@lists.freedesktop.org Summary: Implementation of YEARFRAC function inconsistent with Excel Severity: normal Classification: Unclassified OS: All Reporter: em...@christian-fries.de Hardware: Other Status: UNCONFIRMED Version: unspecified Component: Spreadsheet Product: LibreOffice Created attachment 86136 --> https://bugs.freedesktop.org/attachment.cgi?id=86136&action=edit Attachment: Spreadsheet to verify implementation (requires LibreOffice Add-In Obba.oxt) 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 OpenOffice implementation (which doesn't agree with Excel either). Remark: b) might have got fixed via bug 40100, but since the OASIS Documentation is not compliant with Excel a) remains (and it is likely that future "bug" reports will pop up) - see below. Remark: Don't care about c), the implementation is even worse. A re-implementaiton 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 the assignee for the bug.
_______________________________________________ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs