# Which method is implemented by Excel's YEARFRAC for ACT/ACT?

I know the algorithm used by Excel to calculate the YEARFRAC(startDate, endDate, basis) for basis=1. Excel calls the method "act/act". A Java re-implentation of Excel's algorithm can be found at finmath.net together with a spreadsheet comparing several day count conventions.

It appears as if Excel does not implement "ACT/ACT AFB" and definitely the method is not "ACT/ACT ISDA" (which is the most common one I assume).

I know all the algorithms (see also Implementation of the YEARFRAC). I just have the simple question:

What is the name of Excel's algorithm?

Or is it just "Microsoft's proprietary ACT/ACT".

This is rather a long comment to your post.

I think YEARFRAC version Act/Act has nothing to do with finance at all, probably it is a big bug. Also, I checked the link "Implementation of the YEARFRAC", the author of that page is not fully right either - "Property 1: Additivity" - day conventions need not be additive.
Act/365L (and Act/Act-AFB) we sometimes divide by 365 other times we divide by 366 - stepping over a leap day can not be additive.
30A/360 avoid the 31 rule if the start date is not "the end of a month date".

Test 1
Start Date: 2015-01-01
End Date: 2016-01-01
YEARFRAC(X;Y;1) = 365/365 Fine..

Test 2
Start Date: 2015-01-01
End Date: 2016-01-02
YEARFRAC(X;Y;1) = 1.001367989056 = 366 / 365,5

Test 3
Start Date: 2016-02-27 End Date: 2016-02-29 YEARFRAC(X;Y;1) = 0.005464481 = 2 / 366

I myself believe that daycount conventions, might be the dumbest invention made in economy. If I could recommend any day convention it would be Act/365F -- that is Actual days divided by 365. However, it is hard to have any impact on large institutions that gain money on things like this.

• I agree in one point: I don't like ACT/ACT and I prefer ACT/365 too. Also, I do not claim that daycounting has to be additive, but it is a desirable feature and ACT/ACT ISDA is addtive but Excel ACT/ACT is not. Apart from this, you do not answer the question in any aspect: Which method is implemented by Excel? - It appears that Excel is not implementing any known method at all (and there are "some" well definied method under the name ACT/ACT). Mar 10, 2015 at 8:33
• @ChristianFries In a better world the dayfraction should of course be additive, it does not make sense that certain dates leads to higher discounts. Also, I said in the beginning that this was nothing but a expanded comment. However, the examples provided shows that YEARFRAC is pretty useless. Mar 10, 2015 at 8:38
• I agree. Thank you. Mar 10, 2015 at 9:14