Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yearfrac error?
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yearfrac error?
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? "mthomas4" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yearfrac error?
On Wed, 5 Sep 2007 03:12:02 -0700, mthomas4
wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yearfrac error?
On Wed, 05 Sep 2007 08:25:27 -0400, Ron Rosenfeld
wrote: On Wed, 5 Sep 2007 03:12:02 -0700, mthomas4 wrote: 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 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/sol...ution=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
yearfrac | Excel Worksheet Functions | |||
Yearfrac problems | Excel Discussion (Misc queries) | |||
Yearfrac problem | New Users to Excel | |||
yearfrac problem | Excel Worksheet Functions | |||
yearfrac problem | Setting up and Configuration of Excel |