2003.10.22 03:43 PM

Excel 97/2002 YEARFRAC Difference

It seems the Excel YEARFRAC function, which is part of the Analysis Toolpak add-in, returns different results in Excel 97 and 2002 when used with the US (NASD) 30/360 basis option over certain dates.

Using this formula:

```=YEARFRAC(DATE(2002, 2, 28),DATE(2002, 12, 31),0)
```

In Excel 97 SR-2 (I) we get this:

```0.833333333
```

But in Excel 2002 SP-2, we get this:

```0.836111111
```

That's a difference of about a day, which is pretty serious in the context in which we were using it.

A quick search of the MS Knowledge Base found no relevant articles, and I can't find anyone reporting this particular YEARFRAC problem on the newsgroups via Google, though there have been numerous problems with YEARFRAC and other Excel date/time functions as a result of Y2K and leap years. I posted this as a question to the microsoft.public.excel.worksheet.functions newsgroup, but haven't yet received a reply.

Here's the only real definition of the US (NASD) 30/360 basis method I've been able to find (you'll have to navigate yourself to the Date & Time > YEARFRAC entry, as the frame is redirecting deep links to its home page).

By the way, in case you didn't know, there's also a (mostly) undocumented Excel function named DATEDIF (just one "F") available in all versions of Excel that we've used with some success to produce custom YEARFRAC-like functionality. The folks at Pearson Software Consulting, LLC have written the best plain-spoken explanation of DATEDIF I've ever found. It includes this beauty:

"DATEDIF has, for whatever reason, been treated as one of the drunk cousins of the Function Family."

Indeed.

yearfrac sucks! i was using it 4 a simple calculation 4 only 1 month in 2008, a leap year, & it gave me a result that is just wrong! i wasn't even going anywhere near february. something as simple as =YEARFRAC(DATE(YEAR(B4),1,0),B4), where B4=1/29/2008, gave me 0.080556! that's wrong! =29/366 equals 0.079235. do u have any idea what kind of rounding error this thing creates!? So i had 2 create this function instead: =DATEDIF(DATE(YEAR(B4),1,0),B4,"d")/DATEDIF(DATE(YEAR(B4),1,0),DATE(YEAR(B4),12,31),"d"). yearfrac is teh suck.

z | 2007.02.21 08:44 AM

Take Care
With Regards,
Asaikarthik

Asaikarthik | 2007.05.29 11:17 PM