GNOME Bugzilla – Bug 611436
ACCRINT doesn't work as advertised
Last modified: 2010-06-10 04:46:07 UTC
For all I can tell, Gnumeric seems to completely ignore the "first_interest" and "frequency" parameters to the ACCRINT function and always calculates the interest earned between "issue" and "settlement". It may be that I'm missing something, but then at least the documentation is buggy.
20:32 <@gm_lap_> mgoetze: the financial functions from the XL era behave weirdly at the best of times. 20:32 <@gm_lap_> We have, I believe, some approximation of what XL does, but in general the algorithms and bugs are insufficiently documented. 20:34 <@gm_lap_> mgoetze: the code uses that argument for an error check only.
Btw, if you happen to have information about what the functions should do, we'd like to know. We should match Excel, unless there is a very good reason not to.
I don't know what Excel does, but here's what I think the function should do: ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis) We want to calculate the interest from the beginning of the current interest period, let's call it "interest_from", to the settlement date. When did the current interest period start? Here's an algorithm: if (settlement < first_interest) then interest_from := issue else if (frequency == 1) then increment := 1 year if (frequency == 2) then increment := 6 months if (frequency == 4) then increment := 3 months interest_from := first_interest while (first_interest <= settlement) first_interest += increment first_interest -= increment Note that adding 3 months here means increasing the number of the month by 3, and not something like adding 90 days. Then we just need to convert "settlement - first_interest" into a percentage (the method for this is determined by "basis") and multiply that by rate and par.
Sorry, the last three lines should of course have interest_from rather than first_interest.
"Note that adding 3 months here means increasing the number of the month by 3, and not something like adding 90 days." This surely should depend on the value of 'basis'. While the specification of ACCRINT in OpenFormula at this time is badly incomplete, it includes the constraint: issue < first_interest < settlement. Moreover ACCRINT should calculate the "accrued interest for securities with periodic interest payments". I don't think that is the same as "the interest from the beginning of the current interest period". Note that there is a formula given for ACCRINT at http://office.microsoft.com/en-us/excel/HP052089791033.aspx which is probably what we really should implement. (Of course this depends on somebody understanding that formula.) Also note that GNumeric's calculation currently matches the examples on that page.
I probably should have referred to http://openiso.org/Ecma/376/Part4/3.17.7 which contains the same info without the examples.
(In reply to comment #5) > "Note that adding 3 months here means increasing the number of the month by 3, > and not something like adding 90 days." This surely should depend on the value > of 'basis'. How so? > While the specification of ACCRINT in OpenFormula at this time is badly > incomplete, it includes the constraint: > issue < first_interest < settlement. With that constraint, one of the parameters "issue" or "first_interest" is redundant. However, in the real world, it makes sense to have both parameters, as there actually are securities with a first interest period of a different length than all the other interest periods (if you google "long coupon" you will see this is a well-known phenomenon). > Moreover ACCRINT should calculate the "accrued interest for securities with > periodic interest payments". I don't think that is the same as "the interest > from the beginning of the current interest period". As far as I am concerned, "the interest from the beginning of the current interest period" is an explanation of "accrued". Quoting http://en.wikipedia.org/wiki/Accrued_interest , "In finance, accrued interest is the interest that has accumulated since the principal investment, or since the previous interest payment if there has been one already." > Note that there is a formula given for ACCRINT at > http://office.microsoft.com/en-us/excel/HP052089791033.aspx which is probably > what we really should implement. (Of course this depends on somebody > understanding that formula.) > > Also note that GNumeric's calculation currently matches the examples on that > page. Also note that said example fails to satisfy your proposed constraint of first_interest < settlement. The formula looks like it's trying to calculate the total cumulative earned interest since the security was issued, which would be unfortunate, since that is not what anyone actually dealing with these securities would refer to as "accrued interest". The example unfortunately only deals with the case where the total cumulative earned interest happens to coincide with the accrued interest. It would be great if someone with a recent version of Excel could check what it actually does in the other cases.
Depending on the "basis" value a year is interpreted to be anything between 360 and 366 days. Length of months also varies. For some bases, each month is considered 30 days, in others one looks at the true number of days. I doubt that all of this should have no effect on the return value. Not ethat wikipedia is not a normative reference but is definitions can change. Please also not e that I did not "propose" a constraint with respect to issue, settlement etc. but only quoted a constraint currently in the OpenFormula draft. I have no idea what is the correct formula or constraints.
Of course "basis" should have an effect on the return value. But not by changing the interest dates. I have never seen a security which does not (nominally) pay interest on the same day of the month everytime. Rather, "basis" determines what percentage of the current coupon period is deemed to have elapsed. My knowledge of what "accrued" means does not come from Wikipedia, but rather from actually dealing with bonds. I provided the Wikipedia quote as a courtesy for those to whom it is not so obvious. Feel free to do further research if you don't want to take my word for it.
Michael, I have no doubt that you have excellent knowledge of the bond market, but ACCRINT is supposed to calculate whatever Excel does; this may not be the most useful in the bond market, (If the latter is the case that's an argument of adding another useful function, not to change ACCRINT.) With respect to the basis issue: If a first-interest date of xxxx/11/30 at a frequency of 4 is not automatically return an error, then we can't just add 3 months since xxxx+1/2/30 does not exist. Now in real live there may not be a bond that has such an interest day but the function needs to be defined in such a way that this makes sense nonetheless.
OK, I've just borrowed someone's Excel and it seems that Excel does, indeed calculate the cumulative earned interest rather than the accrued interest. Poor Excel users. So, please consider this a feature request for a function which actually does calculate the accrued interest. Call it something else, that's fine with me. I've never seen a bond with the problem you mentioned but I expect that the interest dates would be xxxx/11/30, xxxx+1/2/28, xxxx+1/5/30 and so on. (This could get tricky with leap years, of course...)
Excel 2007's ACCRINT has another argument calc_method that determines whether to calculate the interest from issue date (the default) or from the last coupon payment date.
well, my comment #12 reflected what Excel's function wizard says. That is not correct though. Its help browser says that calc_method == false calculates the interest from first_interest to settlement date (calc_method == true from issue date to settlement date). That is not correct either.
Okay, apparently: if calc_method == true, ACCRINT returns the sum of the interest paid or accrued from issue date to settlement. if calc_method == false, ACCRINT returns the sum of the interest paid or accrued on or after the first interest date to up to the settlement date, i.e. it ignores the interest prior to the coupon period ending on first_interest date.
18.17.7.2 of ISO/IEC 29500-1:2008(E) does not mention a calc_method. It does give a mathematical formula but unfortunately it uses values (A_i and NL_i) that are insufficiently described.
It turns out that the examples in 18.17.7.2 of ISO/IEC 29500-1:2008(E) are useless in trying to figure out what A_i and NL_i are. WE are currently calculating the same values as those examples (but are really just using ACCRINTM).
I have tried to clarified the description and added the calc_method argument used by Excel 2007 and later. (Note that Excel's calculation changed to Excel 2003 and again to Excel 2007.) We seem to be matching what Excel 2007/2010 is doing. The Openformula draft make seven less sense and would have us primarily return an error value. Note that this is most likely not what you want to calculate. This problem has been fixed in the development version. The fix will be available in the next major software release. Thank you for your bug report.