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.
