Share via

Why yearfrac function on an actual basis calculates wrong value for leap years.

Anonymous
2024-09-22T14:53:58+00:00

Hello,

I attempted to calculate the monthly fractions for CY2028 using the yearfrac function on an actual basis. However, for some reason, the December percentage does not match the prior months having similar number of days, and the total sum of the monthly fractions is 1.0002 instead of 1.0. Strangely, if I manually calculate based on a 366-day basis, then it adds up correctly. This issue only occurs for non-leap years.

Please find the below working for reference,

1/1/28 2/1/28 3/1/28 4/1/28 5/1/28 6/1/28 7/1/28 8/1/28 9/1/28 10/1/28 11/1/28 12/1/28
1/31/28 2/29/28 3/31/28 4/30/28 5/31/28 6/30/28 7/31/28 8/31/28 9/30/28 10/31/28 11/30/28 12/31/28
1. In this first section, I manually calculated month days/total days to arrive at monthly percentages.
Days 31.00 29.00 31.00 30.00 31.00 30.00 31.00 31.00 30.00 31.00 30.00 31.00 366.00
Average 30.50
366 0.0847 0.0792 0.0847 0.0820 0.0847 0.0820 0.0847 0.0847 0.0820 0.0847 0.0820 0.0847 1.0000
Average 0.0833
2. In this section, I have used yearfrac function: YEARFRAC(Start Date,End Date+1,1), to calculate monthly weights/percentages.
Days 31.00 29.00 31.00 30.00 31.00 30.00 31.00 31.00 30.00 31.00 30.00 31.00 366.00
Yearfrac (Actual Basis) 0.0847 0.0792 0.0847 0.0820 0.0847 0.0820 0.0847 0.0847 0.0820 0.0847 0.0820 0.0849 1.0002
Average 0.0834
Check 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 -0.0002

Hence, I would like to know the reason for the same, is it a bug or glitch, or I am missing something here?

Thanks in advance.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2024-09-23T03:52:39+00:00
    1. It appears that for a leap year, the function is incorrectly calculating December's fraction as 31/365 days = 0.0849, instead of 31/366 days = 0.0847, which is incorrect.

    It may seem incorrect to you because you are comparing it to a calculation method you have invented.

    YEARFRAC obviously uses a different method to do the calculations.

    The point is: It doesn't calculate the way you think.

    Calculations in Excel involving a date and/or time are always difficult to understand, because the fact is that there is no date (or time) in Excel. A date is a sequential number, not what you see on the screen.

    In particular, leap years don't matter here, if you apply your wrong YEARFRAC formulas in January, for example, you will get an error even in some non-leap years.

    If you want to report this as a bug, please use the feedback function in Excel.

    How do I give feedback on Microsoft Office? - Office Support

    Andreas.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2024-09-22T16:11:07+00:00

    I have used yearfrac function: YEARFRAC(Start Date,End Date+1,1), to calculate monthly weights/percentages.

    In December, the last day is 12/31/2028, but you calculate + 1, therefore you ask YEARFRAC to calculate the difference from 12/1/2028 till 1/1/2029!

    That's the reason why you get a different result in this month. Change the formula for December only to
    =YEARFRAC(Start Date - 1,End Date,1)

    and you get the result you expect.

    Andreas.

    Hello Andreas,

    Thank you for the reply.

    Please note that after making the edit to the December formula, it is now coming to 0.0847 and total sum of the monthly percentages is also matching. However, I have some follow-up questions;

    1. By adjusting the formula for December, to =YEARFRAC(Start Date - 1,End Date,1), are we not considering 11/30/2028, in our calculation twice, for November we are including 11/30/2028 but december we are going back and considering 11/30/2028 again.
    2. When I am using the same function to calculate the percentage for the whole year (CY2028), it is summing up to 1.0 = Yearfrac (12/1/28,12/31/28+1,1) = 1.0
    3. Again, when I am using the same formula as I did previously, in year 2027 (non-leap year) it is calculating correctly and summing up to 1.0 without making any adjustment to the last months formula.
    1/1/27 2/1/27 3/1/27 4/1/27 5/1/27 6/1/27 7/1/27 8/1/27 9/1/27 10/1/27 11/1/27 12/1/27
    1/31/27 2/28/27 3/31/27 4/30/27 5/31/27 6/30/27 7/31/27 8/31/27 9/30/27 10/31/27 11/30/27 12/31/27
    1. In this first section, I manually calculated month days/total days to arrive at monthly percentages.
    Days 31.00 28.00 31.00 30.00 31.00 30.00 31.00 31.00 30.00 31.00 30.00 31.00 365.00
    Average 30.42
    365 0.0849 0.0767 0.0849 0.0822 0.0849 0.0822 0.0849 0.0849 0.0822 0.0849 0.0822 0.0849 1.0000
    Average 0.0833
    2. In this section, I have used yearfrac function: YEARFRAC(Start Date,End Date+1,1), to calculate monthly weights/percentages.
    Days 31.00 28.00 31.00 30.00 31.00 30.00 31.00 31.00 30.00 31.00 30.00 31.00 365.00
    Yearfrac (Actual Basis) 0.0849 0.0767 0.0849 0.0822 0.0849 0.0822 0.0849 0.0849 0.0822 0.0849 0.0822 0.0849 1.0000
    Average 0.0833
    Check 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 
      4. It appears that for a leap year, the function is incorrectly calculating December's fraction as 31/365 days = 0.0849, instead of 31/366 days = 0.0847, which is incorrect.

    Thanks.

    Was this answer helpful?

    0 comments
  3. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2024-09-22T15:21:37+00:00

    I have used yearfrac function: YEARFRAC(Start Date,End Date+1,1), to calculate monthly weights/percentages.

    In December, the last day is 12/31/2028, but you calculate + 1, therefore you ask YEARFRAC to calculate the difference from 12/1/2028 till 1/1/2029!

    That's the reason why you get a different result in this month. Change the formula for December only to
    =YEARFRAC(Start Date - 1,End Date,1)

    and you get the result you expect.

    Andreas.

    Was this answer helpful?

    0 comments