How to implement Excel ACCRINT

Question

As part of the Formula.js project, I am trying to remake Excel ACCRINT (in JavaScript, but the language should not matter). I tried to find the correct description of how it should work (especially with respect to the first_interest parameter), but did not find anything.

Interestingly, Excel, Google Spreadsheets, Apple Numbers, Gnumeric, and OpenOffice disagree on how to implement it, although all three major versions of Excel (Win, Mac, Web) seem to be consistent with each other. You can find several more contexts on this blog .

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

Any help would be greatly appreciated!

UPDATE: to be clear, the problem is not related to the daily billing counter , which we implemented using the David Wheeler pseudo-code for YEARFRAC , which itself was confirmed by more than 32 million tests covering all five basic variants. The problem comes from the first_interest parameter, which no one seems to understand. As far as we can tell, this parameter is simply ignored by many alternative spreadsheets, including OpenOffice (it is commented out in the source code). And this parameter really behaves in a strange way. If you use Excel and you change its value, you will see that it will change the results obtained by the ACCRINT function, but as if they are chaotic. Try changing the first_interest date to a full century, and you will see that the accrued interest changes, but not much. I really can't understand. If anyone can, I'm all ears ...

Answer 1

Phil H from Quantitative Finance found this one . A NET library that provides a cleanroom implementation for all financial functions, and all but two seem to pass the test (in fact, 201,349 test cases). These include ACCRINT. Code licensed under the Creative Commons Attribution. It is written in F #, but it is pretty clear. The code for ACCRINT is in the bonds.fs file. We will try to port it to JavaScript and see if we get the same results as Excel. More on this soon ...

Answer 2

This is in php (I think): from this page

  /** * ACCRINT * * Returns the discount rate for a security. * * @param mixed issue The security issue date. * @param mixed firstinter The security first interest date. * @param mixed settlement The security settlement date. * @param float rate The security annual coupon rate. * @param float par The security par value. * @param int basis The type of day count to use. * 0 or omitted US (NASD) 30/360 * 1 Actual/actual * 2 Actual/360 * 3 Actual/365 * 4 European 30/360 * @return float */ public static function ACCRINT($issue, $firstinter, $settlement, $rate, $par=1000, $frequency=1, $basis=0) { $issue = self::flattenSingleValue($issue); $firstinter = self::flattenSingleValue($firstinter); $settlement = self::flattenSingleValue($settlement); $rate = (float) self::flattenSingleValue($rate); $par = (is_null($par)) ? 1000 : (float) self::flattenSingleValue($par); $frequency = (is_null($frequency)) ? 1 : (int) self::flattenSingleValue($frequency); $basis = (is_null($basis)) ? 0 : (int) self::flattenSingleValue($basis); // Validate if ((is_numeric($rate)) && (is_numeric($par))) { if (($rate <= 0) || ($par <= 0)) { return self::$_errorCodes['num']; } $daysBetweenIssueAndSettlement = self::YEARFRAC($issue, $settlement, $basis); if (!is_numeric($daysBetweenIssueAndSettlement)) { return $daysBetweenIssueAndSettlement; } $daysPerYear = self::_daysPerYear(self::YEAR($issue),$basis); if (!is_numeric($daysPerYear)) { return $daysPerYear; } $daysBetweenIssueAndSettlement *= $daysPerYear; return $par * $rate * ($daysBetweenIssueAndSettlement / $daysPerYear); } return self::$_errorCodes['value']; } // function ACCRINT()