# What's the algorithm behind Excel's ACCRINT?

This question was originally posted on Stackoverflow:

As part of the Formula.js project, I'm trying to re-implement Excel's ACCRINT function (in JavaScript, but the language should not matter). I've been trying to find a proper description for how it's supposed to work (especially with respect to the first_interest parameter), but could not find anything.

Interestingly enough, Excel, Google Spreadsheets, Apple Numbers, Gnumeric, and OpenOffice all disagree on the way to implement it, even though all three major versions of Excel (Win, Mac, Web) seem to agree with each other. Some more context can be found on this blog post.

Dozens of tests cases and my current (flawed) implementation can be found here.

Any help (code, pseudocode, or description) would be greatly appreciated!

UPDATE: to be clear, the issue is not related to the day count convention, which we implemented using David Wheeler's pseudocode for YEARFRAC, which itself was validated by over 32 million tests, covering all five basis options. The issue comes from the first_interest parameter, which nobody seems to really understand. As far as we can tell, this parameter is simply ignored by many alternative spreadsheets, including OpenOffice (it's commented out in the source code). And this parameter really behaves in strange ways. If you use Excel and you change its value, you will see that it will change the results given by the ACCRINT function, but in ways that seem chaotic. Try changing the first_interest date by a full century, and you'll see the accrued interest changing, but not by much. I really can't make sense of that. If anyone can, I'm all ears, because ACCRINT must be used for calculating interest on billions or trillions of dollars every year, and I find it fascinating that nobody outside of a few people at Microsoft seem to know how it's really supposed to work...

-

Take a look at Excel Financial Functions for .Net, it looks like there is source code available:

http://archive.msdn.microsoft.com/FinancialFunctions

-
Awesome find! I think this will help a lot, and the license is compatible with what I'm doing (Creative Commons Attribution). Thank you very much! – ismael Jan 22 '13 at 13:48
Cheers, I remembered it from something I read and remember being alarmed that it favoured Excel's methods even when they were wrong. – Phil H Jan 23 '13 at 17:21

The formula to calculate accrued interest used in Excel is 100% correct (but the day count convention is not). What is not correct and not sufficiently accounted for is the exact day count convention that must be used for the specific asset you try to value its accrued interest on.

Here couple points that may cause your errors:

• I highly recommend you to do in any language, whether compiled or scripted, is to implement Enums for your switch statement for the day count convention in order to avoid ambiguity or errors by your future users; Do not use integers or strings because its a great source for errors.

• In your link, how did you derive the "required" values against which you measure whether your computation was correct or incorrect?

• Did you implement bank holiday schedules? Did you account that such bank holidays are potentially different for every market, even sometimes different for different exchanges in the same market. I do not see any accounting of bank holidays in your source code at all. Is that the issue? Why this is important to pay attention to becomes evident when you check out the following link:

• http://en.wikipedia.org/wiki/Date_rolling

-
The problem is not with the day count convention. Excel's implementation of ACCRINT is based on its implementation of YEARFRAC. We implemented YEARFRAC from David Wheeler's pseudocode, which was validated with over 32 millions tests. We're pretty confident about this one. The issue is that nobody seems to really understand what the first_interest parameter is supposed to do. As far as the required values come from, they're extracted from Excel (Win, Mac, and Web versions are all consistent with each other). – ismael Jan 22 '13 at 2:22
I still ask you the same question. How does your function know about holiday schedules when performing date rolling? You nowhere accounted for that in your source code. – Matt Wolf Jan 22 '13 at 2:30
It does not, because Excel does not either. Holiday schedules with Excel have to be explicitly specified, as does NETWORKDAYS for example. – ismael Jan 22 '13 at 2:33
And you're right, Excel is 100% correct, because Excel defines the absolute truth as far as re-implementing Excel's function goes. It does not matter so much what Excel attempted to do in the first place. Since it's used by the majority of users for doing financial computations, as far as we're concerned, it's the truth, whether it makes sense or not... – ismael Jan 22 '13 at 2:34
Upvoting stuff you like is also a way to show appreciation for the time spent ;-) but thanks for saying thanks, glad it helped. – Matt Wolf Jan 22 '13 at 4:14

The formula implemented by ACCRINT is documented on office.microsoft.com

$ACCRINT = par \times \frac{rate}{frequency} \times \sum_{j=1}^{NC} \frac{A_j}{NL_j}$

where

$A_j$ = number of accrued days for the ith quasi-coupon period within odd period.

$NC$ = number of quasi-coupon periods that fit in odd period. If this number contains a fraction, raise it to the next whole number.

$NL_i$ = normal length in days of the ith quasi-coupon period within odd period.

I assume the biggest part of "logic" in the function is the implementation of the various daycounting conventions.

I have implemented a bunch of standard day count conventions (as well as the non-standard Excel YEARFRAC for act/act) at http://finmath.net/topics/daycountingandschedules/ - see also http://www.christian-fries.de/blog/files/2013-yearfrac.html

-
Yes, indeed, but nobody seems to understand how to implement it, and the formula does not take into account a lot of corner cases. Implementing the day counting convention in the context of the calculation of the accrued interest (not just calculating a YEARFRAC) seems to be the challenge. – ismael Jan 21 '13 at 15:06
I have a simplified implementation of the Excel PRICE function in Java here: obba.info/tutorial/priceandyield - here simplified means that I did not implement exact daycounting. Don't know if that helps. – Christian Fries Jan 21 '13 at 19:34
Thanks! I'll take a look. Regarding proper day counting, I implemented David Wheeler's algorithm, which seems to work: stoic.com/formula/function.html?name=YEARFRAC – ismael Jan 21 '13 at 19:37
With respect to YEARFRAC: I haven a re-implementation of Excel's algorithm and many more at finmath.net svn.finmath.net/finmath%20lib/trunk/src/main/java/net/finmath/… see also christian-fries.de/blog/files/2013-yearfrac.html - the problem is that YEARFRAC is not a standard act/act method. Sure, many may use it for calculating stuff, but that does not mean that is is correct. When it comes to payoff by the financial institution they will use a different (standardized) rule (like ACT/ACT ISDA). – Christian Fries Sep 19 '13 at 19:29

The standard for the bond functions (like ACCRINT) is SIFMA (Securities Information and Financial Markets Association). They put out a pair of books (Standard Securities Calculation Methods in 2 volumes--recently updated). This is the source of the formulas that Microsoft documents in the help for bond functions in some versions of Excel. The software recommended by SIFMA is put out by a company called TIPS (long time president is the author of the above books). There is an on-line calculator for calculating nearly all the bond function (more than Microsoft supports)--search for "TIPS calculator".

In most of the bond functions, Microsoft uses the 6 Coupon functions for days (they all begin with COUP...). Unfortunately, the version used in the bond functions does not always match the stand-alone version--so building the function (using the SIFMA formula) in an Excel spreadsheet for PRICE or YIELD will not always match the Excel result for this function. Further, ACCRINT does not use the same definition as the TIPS calculator (or the books)--TIPS calculates the accrued interest only within the coupon period that the settlement date is in--not the same as either of the values Microsoft gives. Further, even when in the first coupon period, the answer differs between Excel and the calculator in many cases. Part of this is that Excel does not follow the standard for determining coupon dates (see Vol II, p. 7, 2013 edition)--it requires the maturity date or last regular coupon date, which is not an argument to ACCRINT.

Early versions of Excel's bond functions (in late 1990's) had lots of errors. Microsoft has generally been getting them out, so that there are a lot fewer, BUT ACCRINT and ODDFPRICE still have major problems. These conclusions are based on over 100 million tests run on code in a MS clone using the TIPS calculator to test a sampling of the differences.

-
Very interesting. Would you mind sharing with us where you learnt all this? Is there any documentation available? – SRKX Dec 4 '14 at 4:07