Re: [office-formula] YEARFRAC differences

["David A. Wheeler" <dwheeler@dwheeler.com>]

Rob Weir says:
> For what it is worth, 1-2-3 agrees with Excel in your example: 
> 
> This is all part of an unholy mess called "day count conventions". Not 
> only are there four different conventions (actual/360, 30/360, actual/365 
> and actual/actual) there are actually three different ways of doing the 
> actual/actual calculations, which vary based on the treatment of leap 
> years:  the ISDA method, the ISMA method and AFB method.
> 
> More than you want to know, but less than we need to know is here:
> http://www.isda.org/c_and_a/pdf/mktc1198.pdf

Actually, that's a really good reference; it gives the rules, WITH EXAMPLES -
perfect for our test cases.

Excel and 1-2-3 appear to implement AFB, aka actual/actual (Euro).
I don't know what system OOo uses; it doesn't seem to match those 3
when basis=1.  We have two different applications (Excel and 1-2-3)
agreeing on AFB, so for the moment I think we should spec AFB for basis=1.

Which means there are two other actual/actual systems that are not supported
at all:
* actual/actual (Historical) - the old ISDA way
* actual/actual (Bond), ISMA's approach.
We probably should define basis numbers for them, and encourage
everyone to agree to those allocations.  If not in this version, note it
as a TODO for a next version.  I wish Microsoft would cooperate more
with others for interoperability, but here's a case where perhaps they could
be also convinced.

> Oh,and just to complicate things, Excel had a Y2K bug in their 
> implementation of YEARFRAC(), so it works differently in Excel 97 compared 
> to Excel 2002.   See: 
> http://ewbi.blogs.com/develops/2003/10/excel_yearfrac_.html

Interesting.  Good test case, added.

--- David A. Wheeler