YEARFRAC function in Excel

Question

When I create a formula =YEARFRAC("1/1/2017","12/31/2017") it returns 1. One year between 1/1/17 & 12/31/17. However when I create =YEARFRAC("1/1/2017","06/30/2017") I was expecting 0.5 i.e. six months from 1/1/17 thru 6/30/17, but it returns 0.4972

However when one more day is added to it i.e. =YEARFRAC("1/1/2017","07/01/2017") it now returns 0.5.

I am not very clear how this function really works. Which is the most correct option for the [basis] parameter i.e. third optional parameter in YEARFRAC?

Thanks.

Answer 1

Pretty good question, and surprisingly there are not many resources on that.

Office help on YEARFRAC() gives some information, but not many details on the calculation itself.

The system behind the calculation is called "Day count convention" and it is used mainly in finance calculating interest rates and payments.

It has slightly different variations, some of them are covered by the parameters of YEARFRAC().

Not having experience with it I can't explain the system for you, however just created a small table showing function's output for beginning and end of each month in a year, it might help you choosing the one best fit your need.

The formula used for the calculation is
=ROUND(YEARFRAC($B$3,$B3,D$2),3)

Answer 2

You are mistaken with the output of the function. Here is the description for the function :

Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term.

Can you observe the line that says : number of whole days between two dates (the start_date and the end_date) , i.e. when you are using 30/06/2017 , you are not considering that date for inclusion which leads to different value than expected.