4
$\begingroup$

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

Improve this question
$\endgroup$

4 Answers 4

Reset to default
3
$\begingroup$

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

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

Improve this answer
$\endgroup$
2
  • $\begingroup$ 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! $\endgroup$
    – ismael
    Jan 22, 2013 at 13:48
  • $\begingroup$ Cheers, I remembered it from something I read and remember being alarmed that it favoured Excel's methods even when they were wrong. $\endgroup$
    – Phil H
    Jan 23, 2013 at 17:21
2
$\begingroup$

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

Improve this answer
$\endgroup$
18
  • $\begingroup$ 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). $\endgroup$
    – ismael
    Jan 22, 2013 at 2:22
  • $\begingroup$ 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. $\endgroup$
    – Matt Wolf
    Jan 22, 2013 at 2:30
  • $\begingroup$ It does not, because Excel does not either. Holiday schedules with Excel have to be explicitly specified, as does NETWORKDAYS for example. $\endgroup$
    – ismael
    Jan 22, 2013 at 2:33
  • 1
    $\begingroup$ Upvoting stuff you like is also a way to show appreciation for the time spent ;-) but thanks for saying thanks, glad it helped. $\endgroup$
    – Matt Wolf
    Jan 22, 2013 at 4:14
  • 1
    $\begingroup$ @Matt Wolf: Why would you consider holidays in the accrued calculation? IMHO interest is paid also for holidays/weekends $\endgroup$
    – richman
    May 4, 2017 at 9:47
1
$\begingroup$

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

Improve this answer
$\endgroup$
4
  • $\begingroup$ 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. $\endgroup$
    – ismael
    Jan 21, 2013 at 15:06
  • $\begingroup$ 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. $\endgroup$
    – Christian Fries
    Jan 21, 2013 at 19:34
  • $\begingroup$ 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 $\endgroup$
    – ismael
    Jan 21, 2013 at 19:37
  • $\begingroup$ 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). $\endgroup$
    – Christian Fries
    Sep 19, 2013 at 19:29
1
$\begingroup$

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.

Improve this answer
$\endgroup$
2
  • $\begingroup$ Very interesting. Would you mind sharing with us where you learnt all this? Is there any documentation available? $\endgroup$
    – SRKX
    Dec 4, 2014 at 4:07
  • $\begingroup$ The TIPS calculator mentioned in this answer was difficult to find, so here is the link: tipsinc.com/ficalc/calc.tips $\endgroup$
    – Tim Mayes
    Feb 28, 2018 at 20:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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