Excel YEARFRAC ACT/ACT Behavior and Day-Count Caveats
Summary
The document asks which convention Excel uses for YEARFRAC with the ACT/ACT basis. It argues that Excel’s behavior does not match ACT/ACT ISDA or ACT/ACT AFB, and suggests it may be a Microsoft-specific implementation. The response questions whether Excel’s calculation is suitable for financial day-count work.
Several date examples illustrate the reported output, including a year spanning a leap day and a two-day interval at the end of February in a leap year. The response also cautions against assuming all day-count conventions are additive, noting that methods which switch between 365 and 366 as denominators can violate additivity. These examples are observations from the discussion, not a formal specification of Excel’s algorithm; the document leaves the convention’s precise name unresolved.
Key ideas
- Excel’s YEARFRAC ACT/ACT behavior is reported to differ from ACT/ACT ISDA and ACT/ACT AFB.
- The discussion does not establish a standard name for Excel’s specific calculation.
- Reported date examples show that leap-year treatment can affect the calculated fraction.
- Day-count conventions that change denominators may not be additive across adjacent intervals.
Tags
Full text
# Which method is implemented by Excel's YEARFRAC for ACT/ACT? # 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". ## Answer by AD - Stop Putin - (score 2) https://quant.stackexchange.com/a/16912 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.
Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.