https://bugs.freedesktop.org/show_bug.cgi?id=69569
Priority: medium
Bug ID: 69569
Assignee: [email protected]
Summary: Implementation of YEARFRAC function inconsistent with
Excel
Severity: normal
Classification: Unclassified
OS: All
Reporter: [email protected]
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
[email protected]
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
