Accrued Interest Calculations and Spreadsheet Convention Differences

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


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

## Answer by Phil H (score 4, accepted)

https://quant.stackexchange.com/a/7064

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

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

## Answer by Matt Wolf (score 2)

https://quant.stackexchange.com/a/7055

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

## Answer by Christian Fries (score 1)

https://quant.stackexchange.com/a/7042

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

According to this page it is

$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

## Answer by anonymous (score 1)

https://quant.stackexchange.com/a/15746

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.