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


1 Answer 1


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.

  • $\begingroup$ 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). $\endgroup$ Mar 10, 2015 at 8:33
  • $\begingroup$ @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. $\endgroup$ Mar 10, 2015 at 8:38
  • $\begingroup$ I agree. Thank you. $\endgroup$ Mar 10, 2015 at 9:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.