[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]
Subject: YEARFRAC: Why both Microsoft XML definitions are wrong, and nextsteps
Andreas J Guelzow: > This exactly is the crux of the matter. Anybody who wants to implement > YEARFRAC has to know "what the rule really is". That's all I am really > asking for! And we should try to determine them. We are going to have to reverse-engineer them, because Microsoft failed to define them properly in the Microsoft XML specification. If anyone could help in doing so, please let me know ASAP. It's easily parallelizable. I have started to do Yearfrac's basis=1, using a very large set of test cases. Help would be VERY APPRECIATED. Aside: This is, of course, ridiculous. ISO's failure to require Microsoft to fully and correctly document these rules, and a long list of other errors and omissions, is (in my viewpoint) a shameful failure by ISO. I don't care WHO the submitter is, a specification that is as full of errors as Microsoft XML should NEVER be fast-tracked; it needs to go through the normal standards review process to get the review it clearly needs. The "last minute repairs" were, unsurprisingly, a complete failure at getting the necessary quality. It appears that we are holding ourselves to a MUCH higher level-of-quality requirement than ISO actually requires. Rob Weir says that Microsoft claims this is their rule for basis=1 (actual/actual): > The fractional number of years represented by the number of whole days > between two dates, start-date and end-date., according to basis. If the > Actual/actual basis is used, the year length used is the average length > of the years that the range crosses, regardless of where start-date and > end-date fall in their respective years. However, if the value of > basis is out of range, #NUM! is returned. This statement is not true (all dates below are in U.S. MM/DD/YYYY format): * Given dates 01/01/2000 and 01/01/2001, YEARFRAC returns 1.0000000000, which is 366/366 - and NOT 366/((366+365)/2). Note that only the FIRST year counts. * It is NOT true that the Jan 1. as an end-date means that that year is ignored. Given dates 01/01/2000 and 01/01/2002, it returns 2.0009124090, which is =(731)/((366+365+365)/3). If Jan 1. were ignored as an end-date, it would be (731)/((366+365)/2) = 2 exactly. Their older posted rules for basis=1, Actual/actual, are: > The actual number of days between the two dates are counted. If the date > range includes the date 29 February, the year is 366 days; otherwise it > is 365 days. This statement is also untrue. * Given dates 01/01/2000 and 01/02/2000, the Excel result is 0.0027322400, which is 1/366, and NOT 1/365. * Given dates 01/01/2000 and 01/31/2004, Excel produces 4.080459770, which is =(diff)/((366+365+365+365+366)/5)... Note that there are TWO, not ONE, 366's in there. Replacing a 366 with 365 (as that rule would suggest) gives the wrong answer. So different rules are needed. Starting with the large set of test cases, I am working backward to determine how each test case is calculated, and from that, to determine the REAL ruleset. Then I write code to implement the ruleset, and test it against an extremely large set of test cases; once I have accounted for all the test data, I have the correct ruleset. Here's an extract of what I have so far for basis=1, so you can see what I'm doing: Date1 Date2 Official Answer Calculated Answer OK? Formula Comments 2000-01-01 2001-01-01 1.0000000000 1.0000000000 OK =366/366 Doesn’t average 2000-01-01 2001-01-02 1.0041039670 1.0041039672 OK =(B13-A13)/((366+365)/2) Averages here 2000-01-01 2001-01-30 1.0807113540 1.0807113543 OK =(B14-A14)/((366+365)/2) 2000-01-01 2002-01-01 2.0009124090 2.0009124088 OK =(B15-A15)/((366+365+365)/3) DOES average 2000-01-01 2000-01-02 0.0027322400 0.0027322404 OK =1/366 Crossing Feb 29 NOT required 2000-01-01 2001-02-02 1.0889192890 1.0889192886 OK =(B17-A17)/((366+365)/2) 2000-01-01 2001-03-01 1.1627906980 1.1627906977 OK =(B18-A18)/((366+365)/2) 2003-01-01 2004-02-01 1.0834473320 1.0834473324 OK =(B19-A19)/((365+366)/2) 2003-01-01 2004-02-28 1.1573187410 1.1573187415 OK =(B20-A20)/((365+366)/2) 2003-01-01 2004-03-01 1.1627906980 1.1627906977 OK =(B21-A21)/((365+366)/2) 2000-01-01 2004-01-31 4.0804597700 4.0804597701 OK =(B22-A22)/((366+365+365+365+366)/5) Crossing Feb 29 NOT required 2000-01-01 2004-02-28 4.1570881230 4.1570881226 OK =(B23-A23)/((366+365+365+365+366)/5) 2000-12-31 2001-01-01 0.0027397260 0.0027397260 OK =(B24-A24)/365 WHOA. Day in leapyear, yet 365. 2000-12-31 2001-01-02 0.0054794520 0.0054794521 OK =(B25-A25)/365 WHOA. Day in leapyear, yet 365. Currently, I suspect that it computes the "daydifference" or some related "size" value, and then uses DIFFERENT rules depending on that value. (Something like "if daydiff <=366, ruleset1, else ruleset2"). I will post more once I have a better handle on what I believe the ACTUAL ruleset to be. What's even MORE remarkable is that according to ISMA there are 3 different actual/actual computations required by different financial institutions: ISMA actual/actual (also US treasury convention), AFB, and the ISDA method. NONE of them appear to be supported by Excel!! My suspicion is that financial organizations specify "actual/actual", and that all of them THINK that they are referring to some official standard. But instead, they are depending on undocumented functionality of Excel. It would be great to define basis values for the other actual/actual conventions, and agree on them. But that's for another day; let's start by determining what we need today, to share what we already have. --- David A. Wheeler
[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]