YearFrac

[delainasmith321]

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 liek to understand how this is being c

[#2]

Hello @delainasmith321,

The following describes the syntax for YEARFRAC() and provides an example:

https://support.office.com/en-us/article/yearfrac-function-3844141e-c76d-4143-82b6-208454ddc6a8

For basis 1, the calculation is actual number of days in the month divided by actual number of days in the year.

[#3]

@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

[#4]

@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.

[#5]

Many thanks for that Joe.
Great explanation and I can see how the excel yearfrac function works.
I agree, I prefer my method.