Re: [office-formula] YEARFRAC differences

[robert_weir@us.ibm.com]

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

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

I checked the OOXML spec (1.3 draft)
and it does define YEARFRAC(), but is silent on the issue of leap years.
Nine example calculations are given, but the dates are all in 2006. so
useless for our purposes.

So, this one will be a tricky one to
define.    Anyone know a professor of accounting?  Or someone
on an accounting standards board?  We may need such expert opinion
to resolve this.

-Rob

"David A. Wheeler" <dwheeler@dwheeler.com>
wrote on 08/09/2006 07:54:52 PM:

> YEARFRAC has a minor variance between Excel and at least OOo -
> anybody know more about the unwritten semantics of this function?
> 
> Background: I love the fact that we're creating all these test cases.
> It helps us be clear on the spec, and it also helps us discover
> stuff that we _thought_ everyone agreed on... but maybe not.
> 
> Here's one such case: YEARFRAC.  OpenOffice.org and Excel agree
> on results generally, including lots of complicated cases.
> But sometimes when basis=1 (real year, real month!),
> the results differ by a very small amount.
> 
> Given:
>  =YEARFRAC("2000-12-26";"2001-02-11";1)
> Excel produces: 0.128767123
> OOo produces:  0.128415300546448
> 
> These days are 47 days apart, and these answers
> are consistent with the programs deciding on a
> different number of days in the year:
> 0.128767123287671 ... a 365-day year, Excel's answer.
> 0.128415300546448 ... a 366-day year, OOo's answer.
> 
> Since 2000 was a leap year, and 2001 wasn't, that makes sense.
> 
> Anyone know more about the unwritten rules for this function?
> Any comments?
> 
> For the moment, I'm going to embed a pile of test cases
> for this function, so that we can reveal "hidden" semantics,
> and see if anyone else already knows something I don't.
> 
> --- David A. Wheeler