YearFrac

Hi. I would like to understand how the YeaFrac calculation (basis 1) is being computed. When I do YearFrac(StartDate, EndDate, 1) manually I am not coming up the same results and therefore would li...

excel

Formulas and Functions

wombles

Copper Contributor

Aug 31, 2022

PReagan

Hi,

I have tried to calculate the yearfrac using basis=1 (ACTACT) for the start and end dates of 05/10/2022 and 05/04/2027 respectively.

The excel YEARFRAC function calculates a value of 4.49931538110452 whilst calculating the value in the manner you suggest (actual number of days in the month divided by actual number of days in the year) calculates a value of 4.4986301369863.Note I have made the leap year adjustment for the months in year 2024 (366 days)

Please, what am I missing?

Thanks

JoeUser2004

Bronze Contributor

Sep 01, 2022

wombles

When you post dates, it would be helpful if explain what form you use. Apparently, it is DMY. So 5/10/2022 is Oct 5 2022 or DATE(2022,10,5), and 5/4/2027 is Apr 5 2027 or DATE(2027,4,5).

I will use the form MDY. For example, 10/5/2022 and 4/5/2027.

I do not know how YEARFRAC -- and you -- calculate your respective numbers. But the following demonstrates one way that they might differ.

Formulas:

C2: =B2-A2

D2: =C2/E2

E2: =YEARFRAC(A2,B2,1)

C5: =B5-A5

E5: =C5/D5 or =YEARFRAC(A5,B5,1)

C11: =SUM(C5:C10)

D12: =D11/6

E12: =C11/E11

D13: =C11 / (D11/6)

In other words, YEARFRAC might divide the sum of the days in each year by the average days per year, whereas you might sum the YEARFRAC for each year.

IMHO, your calculation is more correct.

Algebraically, we can see why the two results are different.

YEARFRAC calculates (d1+...+d6) / ( (y1+...+y6)/6 ),

which is the same as (6*d1 +...+ 6*d6) / (y1+...+y6).

In contrast, you calculate d1/y1 +...+ d6/y6.

wombles
Copper Contributor
Sep 01, 2022
Many thanks for that Joe.
Great explanation and I can see how the excel yearfrac function works.
I agree, I prefer my method.

Forum Discussion

YearFrac