Yearfrac error?

[mthomas4]

I have been using the yearfrac calc but have notised the following when you
use periods across a leap year:

Date Days Yearfrac Yearfrac from start date
per period
30-Jun-93
30-Jun-94 365 1.0000 1.0000
30-Jun-95 365 1.0000 2.0000
30-Jun-96 366 1.0000 3.0007

Does this indicate an error when using this function?

 

[bj]

year frac depending on the basis, uses the 360, 365, or the number of days
in the first year for the denominator and either the actual number of days or
a monthly multiple of 30 +day difference as the numerator.

this is one of those functions which looks good, and works well enough for
most situations, but many people need to write their own function to meet
their needs. In other words, how do you!! need year to be defined?

 

[Ron Rosenfeld]

I have been using the yearfrac calc but have notised the following when you
use periods across a leap year:

Date Days Yearfrac Yearfrac from start date
per period
30-Jun-93
30-Jun-94 365 1.0000 1.0000
30-Jun-95 365 1.0000 2.0000
30-Jun-96 366 1.0000 3.0007

Does this indicate an error when using this function?

Chip Pearson wrote this to me in 2001 when I had a similar question regarding
YEARFRAC("1/1/00","1/1/02",1) equals 2.000912409

===========================================
I suspect it is a bug within the YEARFRAC function. MS hasn't published
(that I know of) how it actually calculates the YEARFRAC value. There are
366 days between 1-Jan-2000 and 1-Jan-2001, 365 days between 1-Jan-2001 and
1-Jan-2002, and 731 days between 1-Jan-2000 and 1-Jan-2002.


I can't find any combinations of these values which come up with
2.000912409. Even using the different Basis values for either the first or
the second year, I can't get the same answer MS does.


The result *should* be exactly 2 (731/731) if it is using actual day counts
of both years, or 2.00274 (731/365) if it is using a 365 day year, or
1.99727 if it is using a 366 day year. Note that in these cases, the
fractional part of the number represent either 1/365 or 1/366, or 1 day.
This is to be expected.


But the fraction part of the MS answer (0.000912409) represents about 1
minute, 20 seconds, which makes no sense at all.


For what it is worth, I've always thought the YEARFRAC function was rather
useless. A "fraction of a year" strikes me as something left over from the
dark ages of finance before computers were used.


This may be one of those cases in which MS knows the answer is incorrect,
but doesn't fix it because "those who use it have already worked around the
problem, and fixing it would break the work-arounds".
========================================
--ron

 

[Ron Rosenfeld]

Chip Pearson wrote this to me in 2001 when I had a similar question regarding
YEARFRAC("1/1/00","1/1/02",1) equals 2.000912409

===========================================
I suspect it is a bug within the YEARFRAC function. MS hasn't published
(that I know of) how it actually calculates the YEARFRAC value. There are
366 days between 1-Jan-2000 and 1-Jan-2001, 365 days between 1-Jan-2001 and
1-Jan-2002, and 731 days between 1-Jan-2000 and 1-Jan-2002.


I can't find any combinations of these values which come up with
2.000912409. Even using the different Basis values for either the first or
the second year, I can't get the same answer MS does.


The result *should* be exactly 2 (731/731) if it is using actual day counts
of both years, or 2.00274 (731/365) if it is using a 365 day year, or
1.99727 if it is using a 366 day year. Note that in these cases, the
fractional part of the number represent either 1/365 or 1/366, or 1 day.
This is to be expected.


But the fraction part of the MS answer (0.000912409) represents about 1
minute, 20 seconds, which makes no sense at all.


For what it is worth, I've always thought the YEARFRAC function was rather
useless. A "fraction of a year" strikes me as something left over from the
dark ages of finance before computers were used.


This may be one of those cases in which MS knows the answer is incorrect,
but doesn't fix it because "those who use it have already worked around the
problem, and fixing it would break the work-arounds".
========================================
--ron

A little investigation reveals that, at least for your example, when the leap
year is involved and actual/actual basis is being used, that Excel is using
365.25 for the actual year length.

I believe this is wrong since YEARFRAC is designed to help compute interest
accruals.

From another posting on a similar topic having to do with MathWorks:

http://www.mathworks.com/support/solutions/data/1-3MPMX0.html?product=FI&solution=1-3MPMX0
============================
The reference book "Money Market and Bond Calculations" by Stigum and Robinson
states that for the actual/actual convention, “if interest is paid annually,
then the denominator of the day-count fraction equals the actual number of
days, 365 or 366, in the relevant 1-year period.”

The "relevant 1-year period" means "the year in which the coupon starts." The
YEARFRAC function is designed to work directly with our interest accrual
functions and the dates are supposed to be aligned to coupon dates. If the
coupon starts in a non-leap year like 2006, then the denominator for the
actual/actual basis is 365. If the coupon starts in a leap year like 2008, then
the denominator for the actual/actual basis is 366.
===============================
--ron