Login     Register

        Contact Us     Search

Menu

Should you be using the Excel ACCRINT function?

Jun 9

Written by: Charles Flock
6/9/2014 1:54 PM  RssIcon

In this article we look at the Excel ACCRINT function, exploring the results returned by the function when it is used as documented, as well as looking at some ways of manipulating the input data so that you can get the result that you want, or at least a result that you can understand, and why you should be afraid of that data manipulation.
The Excel documentation for its ACCRINT function describes it as returning the accrued interest for a security that pays periodic interest. It then goes further to provide the following equation:

formula for Excel ACCRINT function
Where
Ai = the number of accrued days for the ith quasi-coupon period with 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.
NLi = normal length in days of the quasi-coupon period within odd period.
The inputs to the function are the issue date, first interest date, settlement date, rate, par, frequency, and basis. There is also something called a calc_method which carries the following explanation:
A logical value that specifies the way to calculate the total accrued interest when the date of settlement is later than the first interest date. A value of TRUE returns the total accrued interest from issue to settlement. A value of FALSE returns the accrued interest from first interest to settlement.
There's an awful lot of jargon in the Excel documentation and very little clarity in terms of what the function is actually calculating.
In this article, I am going to look at what the function does and see if it produces results reliably and further see if those results are consistent with the documentation. Normally, when dealing with bond figuration, the first interest date is important only if a bond has an odd first coupon. In fact, in the Excel PRICE and YIELD function, there is no parameter for either the issue date or the first coupon date as they are not needed to determine the accrued interest. The accrued interest is determined from the previous coupon which can be calculated directly from the maturity date and the settlement date passed into the function, because the bond pays regular periodic interest. However, the Excel documentation uses the term periodic interest, omitting the word regular, and this function clearly seems to be trying to address the possibility of calculating interest in an odd coupon period.
You may wonder why the PRICE and YIELD functions have anything to do with ACCRINT. The price of a bond is the discounted value of all the coupons plus the discounted redemption value minus the accrued interest. This is actually called the 'clean' price and is the value returned by the PRICE function and the value that is supplied to the YIELD function. However, the amount that is actually settled when buying or selling a bond is the 'clean' price plus the accrued interest; also known as the 'dirty' price.  
However, the ODDFPRICE function and ODDFYIELD function do have input parameters for the issue date and the first coupon date and they are specifically designed for bonds with an odd first coupon period, which simply means that the first coupon period is either longer or shorter than all the other coupon periods. Let's start, then, with a bond with odd first period and we can build on that example.
Generally, in calculating the interest for an odd first coupon period, you construct a series of quasi-coupon dates until you arrive at a coupon date that is less than or equal to the issue date. The quasi-coupon dates are simply the convention that is used for calculating the accrued interest making the calculations consistent with the calculations for regular periodic interest.
The quasi-coupon dates are generally constructed backwards using the maturity date and the coupon frequency. For example, if the maturity date of the bond is on the 15th of the month than we would move backwards in 6-month increments until we had come up with a quasi-coupon date that is less than (or equal to) the issue date. Generally, and certainly in the other Excel functions mentioned here, if the maturity date is the last day of the month then all the coupon and quasi-coupon dates are assumed to be on the last day of the month.
Since we don't have the maturity date, we will have to assume that the ACCRINT function uses the first coupon date to do this calculation. So let's start with a simple example. In this example, the bond has an issue date of 2012-08-01, and settlement date of 2012-08-05, a coupon rate of 5%, par value of 100, frequency of 2 and an interest basis of 1. We will create a series of first interest dates and call the ACCRINT function. We end up with a spreadsheet that has the following dates and formulae:

41244
=ACCRINT(DATE(2012,8,1),A2,DATE(2012,8,15),0.05,100,2,1)
41275
=ACCRINT(DATE(2012,8,1),A3,DATE(2012,8,15),0.05,100,2,1)
41306
=ACCRINT(DATE(2012,8,1),A4,DATE(2012,8,15),0.05,100,2,1)
41334
=ACCRINT(DATE(2012,8,1),A5,DATE(2012,8,15),0.05,100,2,1)
41365
=ACCRINT(DATE(2012,8,1),A6,DATE(2012,8,15),0.05,100,2,1)
41395
=ACCRINT(DATE(2012,8,1),A7,DATE(2012,8,15),0.05,100,2,1)
41426
=ACCRINT(DATE(2012,8,1),A8,DATE(2012,8,15),0.05,100,2,1)

This should give you a spreadsheet that looks like this.

firstinterest
ACCRINT
12/1/2012
0.191256831
1/1/2013
0.190217391
2/1/2013
0.190217391
3/1/2013
0.186389022
4/1/2013
0.187728938
5/1/2013
0.172651934
6/1/2013
0.183150183

Remember, that in each of these cases we are calculating the interest from 2012-08-01 to 2012-08-15, or 14 days worth of interest. If you refer to the formula at the beginning of this article, this means that the value for Ai is always 14. We have been careful to make sure that the days from issue to settlement (14) do not cross a quasi-coupon period and keeping everything else constant making it pretty straightforward to figure out what the value for NLi is. We can just add the following formula to column C.

firstinterest
ACCRINT
NL
41244
=ACCRINT(DATE(2012,8,1),A2,DATE(2012,8,15),0.05,100,2,1)
=14/(B2/100*2/0.05)
41275
=ACCRINT(DATE(2012,8,1),A3,DATE(2012,8,15),0.05,100,2,1)
=14/(B3/100*2/0.05)
41306
=ACCRINT(DATE(2012,8,1),A4,DATE(2012,8,15),0.05,100,2,1)
=14/(B4/100*2/0.05)
41334
=ACCRINT(DATE(2012,8,1),A5,DATE(2012,8,15),0.05,100,2,1)
=14/(B5/100*2/0.05)
41365
=ACCRINT(DATE(2012,8,1),A6,DATE(2012,8,15),0.05,100,2,1)
=14/(B6/100*2/0.05)
41395
=ACCRINT(DATE(2012,8,1),A7,DATE(2012,8,15),0.05,100,2,1)
=14/(B7/100*2/0.05)
41426
=ACCRINT(DATE(2012,8,1),A8,DATE(2012,8,15),0.05,100,2,1)
=14/(B8/100*2/0.05)

This should give you a spreadsheet that looks like this.

firstinterest
ACCRINT
NL
12/1/2012
0.191256831
183
1/1/2013
0.190217391
184
2/1/2013
0.190217391
184
3/1/2013
0.186389022
187.7792992
4/1/2013
0.187728938
186.4390244
5/1/2013
0.172651934
202.72
6/1/2013
0.183150183
191.1

Remember that we are using the interest basis of 1 in this example which is the Actual/Actual interest method. In this method, the numerator is the actual number of days from the previous coupon date to the settlement date and the denominator is the actual number of days from the previous coupon date to the next coupon date. It doesn't take a lot of analysis to realize that when there is a semi-annual coupon payment, the only possible values for the denominator are 181, 182, 183, or 184. Obviously, all the values after the first interest date of 2013-02-01 are wrong. In the following table, we demonstrate what they should be.

firstinterest
ACCRINT
NL
Days
PCD
NCD
12/1/2012
0.191256831
183
183
6/1/2012
12/1/2012
1/1/2013
0.190217391
184
184
7/1/2012
1/1/2013
2/1/2013
0.190217391
184
184
8/1/2012
2/1/2013
3/1/2013
0.186389022
187.7792992
184
3/1/2012
9/1/2012
4/1/2013
0.187728938
186.4390244
183
4/1/2012
10/1/2012
5/1/2013
0.172651934
202.72
184
5/1/2012
11/1/2012
6/1/2013
0.183150183
191.1
183
6/1/2012
12/1/2012

PCD is the previous coupon date with respect to the settlement date. NCD is the next coupon date with respect to the settlement date. Days is the number of days from PDC to NCD, calculated as NCD - PCD.
Up to this point, we have ignored the calc_method setting and we didn't use it in our formula. As the following table demonstrates in this case, where the settlement date is less than the first interest date, it has no impact.

firstinterest
Calc_method = TRUE
Calc_method = FALSE
12/1/2012
0.191256831
0.191256831
1/1/2013
0.190217391
0.190217391
2/1/2013
0.190217391
0.190217391
3/1/2013
0.186389022
0.186389022
4/1/2013
0.187728938
0.187728938
5/1/2013
0.172651934
0.172651934
6/1/2013
0.183150183
0.183150183

Let's take a different look at the calculation by holding everything constant except the settlement date. Since the calculation for the 'short' interest periods seems to be OK, let's take the case where the first interest date is 2013-03-01 and advance the settlement date from the issue date + 1 to the quasi-coupon date. Then we will infer what the values are for NL1 and NL2. We don't actually have to calculate NL2 until we get into the second quasi-coupon period which commences on 2013-09-01. You can set this up with the following spreadsheet.

settlement
ACCRINT
NLF1
NLF2
41123
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A2,0.05,100,2,1)
=(A2-DATE(2012,8,1))/(B2/100*2/0.05)
 
=A2+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A3,0.05,100,2,1)
=(A3-DATE(2012,8,1))/(B3/100*2/0.05)
 
=A3+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A4,0.05,100,2,1)
=(A4-DATE(2012,8,1))/(B4/100*2/0.05)
 
=A4+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A5,0.05,100,2,1)
=(A5-DATE(2012,8,1))/(B5/100*2/0.05)
 
=A5+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A6,0.05,100,2,1)
=(A6-DATE(2012,8,1))/(B6/100*2/0.05)
 
=A6+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A7,0.05,100,2,1)
=(A7-DATE(2012,8,1))/(B7/100*2/0.05)
 
=A7+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A8,0.05,100,2,1)
=(A8-DATE(2012,8,1))/(B8/100*2/0.05)
 
=A8+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A9,0.05,100,2,1)
=(A9-DATE(2012,8,1))/(B9/100*2/0.05)
 
=A9+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A10,0.05,100,2,1)
=(A10-DATE(2012,8,1))/(B10/100*2/0.05)
 
=A10+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A11,0.05,100,2,1)
=(A11-DATE(2012,8,1))/(B11/100*2/0.05)
 
=A11+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A12,0.05,100,2,1)
=(A12-DATE(2012,8,1))/(B12/100*2/0.05)
 
=A12+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A13,0.05,100,2,1)
=(A13-DATE(2012,8,1))/(B13/100*2/0.05)
 
=A13+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A14,0.05,100,2,1)
=(A14-DATE(2012,8,1))/(B14/100*2/0.05)
 
=A14+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A15,0.05,100,2,1)
=(A15-DATE(2012,8,1))/(B15/100*2/0.05)
 
=A15+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A16,0.05,100,2,1)
=(A16-DATE(2012,8,1))/(B16/100*2/0.05)
 
=A16+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A17,0.05,100,2,1)
=(A17-DATE(2012,8,1))/(B17/100*2/0.05)
 
=A17+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A18,0.05,100,2,1)
=(A18-DATE(2012,8,1))/(B18/100*2/0.05)
 
=A18+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A19,0.05,100,2,1)
=(A19-DATE(2012,8,1))/(B19/100*2/0.05)
 
=A19+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A20,0.05,100,2,1)
=(A20-DATE(2012,8,1))/(B20/100*2/0.05)
 
=A20+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A21,0.05,100,2,1)
=(A21-DATE(2012,8,1))/(B21/100*2/0.05)
 
=A21+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A22,0.05,100,2,1)
=(A22-DATE(2012,8,1))/(B22/100*2/0.05)
 
=A22+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A23,0.05,100,2,1)
=(A23-DATE(2012,8,1))/(B23/100*2/0.05)
 
=A23+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A24,0.05,100,2,1)
=(A24-DATE(2012,8,1))/(B24/100*2/0.05)
 
=A24+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A25,0.05,100,2,1)
=(A25-DATE(2012,8,1))/(B25/100*2/0.05)
 
=A25+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A26,0.05,100,2,1)
=(A26-DATE(2012,8,1))/(B26/100*2/0.05)
 
=A26+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A27,0.05,100,2,1)
=(A27-DATE(2012,8,1))/(B27/100*2/0.05)
 
=A27+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A28,0.05,100,2,1)
=(A28-DATE(2012,8,1))/(B28/100*2/0.05)
 
=A28+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A29,0.05,100,2,1)
=(A29-DATE(2012,8,1))/(B29/100*2/0.05)
 
=A29+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A30,0.05,100,2,1)
=(A30-DATE(2012,8,1))/(B30/100*2/0.05)
 
=A30+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A31,0.05,100,2,1)
=(A31-DATE(2012,8,1))/(B31/100*2/0.05)
 
=A31+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A32,0.05,100,2,1)
=(A32-DATE(2012,8,1))/(B32/100*2/0.05)
 
=A32+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A33,0.05,100,2,1)
=C32
=(A33-$A$32)/((B33-$B$32)/100*2/0.05)
=A33+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A34,0.05,100,2,1)
=C33
=(A34-$A$32)/((B34-$B$32)/100*2/0.05)
=A34+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A35,0.05,100,2,1)
=C34
=(A35-$A$32)/((B35-$B$32)/100*2/0.05)
=A35+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A36,0.05,100,2,1)
=C35
=(A36-$A$32)/((B36-$B$32)/100*2/0.05)
=A36+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A37,0.05,100,2,1)
=C36
=(A37-$A$32)/((B37-$B$32)/100*2/0.05)

This gives us the following result.

settlement
ACCRINT
NL1
NL2
8/2/2012
0.006831011
365.978022
 
8/3/2012
0.020643166
242.2109091
 
8/4/2012
0.034455321
217.6732026
 
8/5/2012
0.048267475
207.1788491
 
8/6/2012
0.06207963
201.3542926
 
8/7/2012
0.075891785
197.6498516
 
8/8/2012
0.089703939
195.0861925
 
8/9/2012
0.103516094
193.2066715
 
8/10/2012
0.117328249
191.7696737
 
8/11/2012
0.131140404
190.6353749
 
8/12/2012
0.144952558
189.717245
 
8/13/2012
0.158764713
188.9588652
 
8/14/2012
0.172576868
188.3218791
 
8/15/2012
0.186389022
187.7792992
 
8/16/2012
0.200201177
187.3115861
 
8/17/2012
0.214013332
186.9042441
 
8/18/2012
0.227825486
186.5462932
 
8/19/2012
0.241637641
186.2292637
 
8/20/2012
0.255449796
185.9465178
 
8/21/2012
0.269261951
185.6927795
 
8/22/2012
0.283074105
185.4638027
 
8/23/2012
0.29688626
185.2561315
 
8/24/2012
0.310698415
185.0669244
 
8/25/2012
0.324510569
184.8938237
 
8/26/2012
0.338322724
184.7348569
 
8/27/2012
0.352134879
184.5883607
 
8/28/2012
0.365947033
184.4529231
 
8/29/2012
0.379759188
184.3273374
 
8/30/2012
0.393571343
184.2105665
 
8/31/2012
0.407383497
184.1017137
 
9/1/2012
0.421195652
184
 
9/2/2012
0.435007807
184
181
9/3/2012
0.448819962
184
181
9/4/2012
0.462632116
184
181
9/5/2012
0.476444271
184
181
9/6/2012
0.490256426
184
181

That's interesting. If you look at the NL1 column you can see that you get the wrong answer until the settlement date falls in the second quasi-coupon period, at which point everything falls into place and the NL for each of the quasi-coupon periods is correct. Now that we know that the number of days in the first quasi-coupon period is 184 and 181 in the second, we can re-calculate what the results should be and compare them to the actually calculation.

settlement
ACCRINT
 
A1
NL1
A2
NL2
41123
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A2,0.05,100,2,1)
=(D2/E2+F2/G2)*100*0.05/2
=1
184
0
181
=A2+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A3,0.05,100,2,1)
=(D3/E3+F3/G3)*100*0.05/2
=D2+1
184
0
181
=A3+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A4,0.05,100,2,1)
=(D4/E4+F4/G4)*100*0.05/2
=D3+1
184
0
181
=A4+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A5,0.05,100,2,1)
=(D5/E5+F5/G5)*100*0.05/2
=D4+1
184
0
181
=A5+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A6,0.05,100,2,1)
=(D6/E6+F6/G6)*100*0.05/2
=D5+1
184
0
181
=A6+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A7,0.05,100,2,1)
=(D7/E7+F7/G7)*100*0.05/2
=D6+1
184
0
181
=A7+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A8,0.05,100,2,1)
=(D8/E8+F8/G8)*100*0.05/2
=D7+1
184
0
181
=A8+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A9,0.05,100,2,1)
=(D9/E9+F9/G9)*100*0.05/2
=D8+1
184
0
181
=A9+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A10,0.05,100,2,1)
=(D10/E10+F10/G10)*100*0.05/2
=D9+1
184
0
181
=A10+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A11,0.05,100,2,1)
=(D11/E11+F11/G11)*100*0.05/2
=D10+1
184
0
181
=A11+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A12,0.05,100,2,1)
=(D12/E12+F12/G12)*100*0.05/2
=D11+1
184
0
181
=A12+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A13,0.05,100,2,1)
=(D13/E13+F13/G13)*100*0.05/2
=D12+1
184
0
181
=A13+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A14,0.05,100,2,1)
=(D14/E14+F14/G14)*100*0.05/2
=D13+1
184
0
181
=A14+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A15,0.05,100,2,1)
=(D15/E15+F15/G15)*100*0.05/2
=D14+1
184
0
181
=A15+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A16,0.05,100,2,1)
=(D16/E16+F16/G16)*100*0.05/2
=D15+1
184
0
181
=A16+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A17,0.05,100,2,1)
=(D17/E17+F17/G17)*100*0.05/2
=D16+1
184
0
181
=A17+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A18,0.05,100,2,1)
=(D18/E18+F18/G18)*100*0.05/2
=D17+1
184
0
181
=A18+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A19,0.05,100,2,1)
=(D19/E19+F19/G19)*100*0.05/2
=D18+1
184
0
181
=A19+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A20,0.05,100,2,1)
=(D20/E20+F20/G20)*100*0.05/2
=D19+1
184
0
181
=A20+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A21,0.05,100,2,1)
=(D21/E21+F21/G21)*100*0.05/2
=D20+1
184
0
181
=A21+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A22,0.05,100,2,1)
=(D22/E22+F22/G22)*100*0.05/2
=D21+1
184
0
181
=A22+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A23,0.05,100,2,1)
=(D23/E23+F23/G23)*100*0.05/2
=D22+1
184
0
181
=A23+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A24,0.05,100,2,1)
=(D24/E24+F24/G24)*100*0.05/2
=D23+1
184
0
181
=A24+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A25,0.05,100,2,1)
=(D25/E25+F25/G25)*100*0.05/2
=D24+1
184
0
181
=A25+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A26,0.05,100,2,1)
=(D26/E26+F26/G26)*100*0.05/2
=D25+1
184
0
181
=A26+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A27,0.05,100,2,1)
=(D27/E27+F27/G27)*100*0.05/2
=D26+1
184
0
181
=A27+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A28,0.05,100,2,1)
=(D28/E28+F28/G28)*100*0.05/2
=D27+1
184
0
181
=A28+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A29,0.05,100,2,1)
=(D29/E29+F29/G29)*100*0.05/2
=D28+1
184
0
181
=A29+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A30,0.05,100,2,1)
=(D30/E30+F30/G30)*100*0.05/2
=D29+1
184
0
181
=A30+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A31,0.05,100,2,1)
=(D31/E31+F31/G31)*100*0.05/2
=D30+1
184
0
181
=A31+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A32,0.05,100,2,1)
=(D32/E32+F32/G32)*100*0.05/2
=D31+1
184
0
181
=A32+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A33,0.05,100,2,1)
=(D33/E33+F33/G33)*100*0.05/2
=D32
184
=F32+1
181
=A33+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A34,0.05,100,2,1)
=(D34/E34+F34/G34)*100*0.05/2
=D33
184
=F33+1
181
=A34+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A35,0.05,100,2,1)
=(D35/E35+F35/G35)*100*0.05/2
=D34
184
=F34+1
181
=A35+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A36,0.05,100,2,1)
=(D36/E36+F36/G36)*100*0.05/2
=D35
184
=F35+1
181
=A36+1
=ACCRINT(DATE(2012,8,1),DATE(2013,3,1),A37,0.05,100,2,1)
=(D37/E37+F37/G37)*100*0.05/2
=D36
184
=F36+1
181

This produces the following result.

settlement
ACCRINT
 
A1
NL1
A2
NL2
8/2/2012
0.006831011
0.013586957
1
184
0
181
8/3/2012
0.020643166
0.027173913
2
184
0
181
8/4/2012
0.034455321
0.04076087
3
184
0
181
8/5/2012
0.048267475
0.054347826
4
184
0
181
8/6/2012
0.06207963
0.067934783
5
184
0
181
8/7/2012
0.075891785
0.081521739
6
184
0
181
8/8/2012
0.089703939
0.095108696
7
184
0
181
8/9/2012
0.103516094
0.108695652
8
184
0
181
8/10/2012
0.117328249
0.122282609
9
184
0
181
8/11/2012
0.131140404
0.135869565
10
184
0
181
8/12/2012
0.144952558
0.149456522
11
184
0
181
8/13/2012
0.158764713
0.163043478
12
184
0
181
8/14/2012
0.172576868
0.176630435
13
184
0
181
8/15/2012
0.186389022
0.190217391
14
184
0
181
8/16/2012
0.200201177
0.203804348
15
184
0
181
8/17/2012
0.214013332
0.217391304
16
184
0
181
8/18/2012
0.227825486
0.230978261
17
184
0
181
8/19/2012
0.241637641
0.244565217
18
184
0
181
8/20/2012
0.255449796
0.258152174
19
184
0
181
8/21/2012
0.269261951
0.27173913
20
184
0
181
8/22/2012
0.283074105
0.285326087
21
184
0
181
8/23/2012
0.29688626
0.298913043
22
184
0
181
8/24/2012
0.310698415
0.3125
23
184
0
181
8/25/2012
0.324510569
0.326086957
24
184
0
181
8/26/2012
0.338322724
0.339673913
25
184
0
181
8/27/2012
0.352134879
0.35326087
26
184
0
181
8/28/2012
0.365947033
0.366847826
27
184
0
181
8/29/2012
0.379759188
0.380434783
28
184
0
181
8/30/2012
0.393571343
0.394021739
29
184
0
181
8/31/2012
0.407383497
0.407608696
30
184
0
181
9/1/2012
0.421195652
0.421195652
31
184
0
181
9/2/2012
0.435007807
0.435007807
31
184
1
181
9/3/2012
0.448819962
0.448819962
31
184
2
181
9/4/2012
0.462632116
0.462632116
31
184
3
181
9/5/2012
0.476444271
0.476444271
31
184
4
181
9/6/2012
0.490256426
0.490256426
31
184
5
181

As you can see, prior to 2012-09-01 the ACCRINT calculation is wrong, but as we enter the quasi-coupon period the calculation (miraculously) corrects itself.
By copying all the formulas until we get to the first interest date we can see that the ACCRINT values are correct up to that point. Here are the last ten rows.

2/19/2013
2.783074105
2.783074105
31
184
171
181
2/20/2013
2.79688626
2.79688626
31
184
172
181
2/21/2013
2.810698415
2.810698415
31
184
173
181
2/22/2013
2.824510569
2.824510569
31
184
174
181
2/23/2013
2.838322724
2.838322724
31
184
175
181
2/24/2013
2.852134879
2.852134879
31
184
176
181
2/25/2013
2.865947033
2.865947033
31
184
177
181
2/26/2013
2.879759188
2.879759188
31
184
178
181
2/27/2013
2.893571343
2.893571343
31
184
179
181
2/28/2013
2.907383497
2.907383497
31
184
180
181
3/1/2013
2.921195652
2.921195652
31
184
181
181

Except, the ACCRINT on 2013-03-01 is wrong. Since the settlement date is equal to the first coupon date, the accrued interest should be zero. Further, since we have now entered a new coupon period (2013-03-01 to 2013-09-01) the number of accrued days (A) is zero and the length of the coupon period is 184 days. However 2.921195652 is the amount of the first coupon and is the amount of the interest from issue date to the settlement date. Maybe it's time to go back and check the documentation.
The documentation provides this description for the function: "Returns the accrued interest for a security that pays periodic interest." Well, it's clearly not doing that. How about those calc_method switches? Let's see what happens when we use those.

settlement
calc_method = TRUE
calc_method = FALSE
Cumulative Interest
Accrued Interest
A
NLF
3/1/2013
2.921195652
2.921195652
2.921195652
0
0
184
3/2/2013
2.935007807
2.935007807
2.934782609
0.013586957
1
184
3/3/2013
2.948819962
2.948819962
2.948369565
0.027173913
2
184
3/4/2013
2.962632116
2.962632116
2.961956522
0.04076087
3
184
3/5/2013
2.976444271
2.976444271
2.975543478
0.054347826
4
184
3/6/2013
2.990256426
2.990256426
2.989130435
0.067934783
5
184
3/7/2013
3.00406858
3.00406858
3.002717391
0.081521739
6
184
3/8/2013
3.017880735
3.017880735
3.016304348
0.095108696
7
184
3/9/2013
3.03169289
3.03169289
3.029891304
0.108695652
8
184
3/10/2013
3.045505044
3.045505044
3.043478261
0.122282609
9
184
3/11/2013
3.059317199
3.059317199
3.057065217
0.135869565
10
184

We can see that there is no difference based on the setting for the calc_method and that apart from the cumulative interest on 2013-03-01 all the interest calculations are wrong. However, it is actually possible to figure out what Excel is doing in this particular case. If we change the NL value to 181, then our cumulative interest calculation agrees with the Excel calculation.

settlement
calc_method = TRUE
calc_method = FALSE
Cumulative Interest
Accrued Interest
A
NL
3/1/2013
2.921195652
2.921195652
2.921195652
0
0
181
3/2/2013
2.935007807
2.935007807
2.935007807
0.013812155
1
181
3/3/2013
2.948819962
2.948819962
2.948819962
0.027624309
2
181
3/4/2013
2.962632116
2.962632116
2.962632116
0.041436464
3
181
3/5/2013
2.976444271
2.976444271
2.976444271
0.055248619
4
181
3/6/2013
2.990256426
2.990256426
2.990256426
0.069060773
5
181
3/7/2013
3.00406858
3.00406858
3.00406858
0.082872928
6
181
3/8/2013
3.017880735
3.017880735
3.017880735
0.096685083
7
181
3/9/2013
3.03169289
3.03169289
3.03169289
0.110497238
8
181
3/10/2013
3.045505044
3.045505044
3.045505044
0.124309392
9
181
3/11/2013
3.059317199
3.059317199
3.059317199
0.138121547
10
181

But, as we have already said, the NL value for this period is 184, not 181, and while it's interesting to infer what's going on in the Excel calculation, it is clearly wrong and cannot be relied upon.
Let's look at this another way. The coupon interest, after the first coupon, is always par * rate/frequency. Given the example that we have been using, we should see the cumulative interest increasing by 2.50 and each subsequent coupon date. And since calc_method = TRUE returns the total accrued interest from issue to settlement we should be able to compare the result.

settlement
calc_method = TRUE
Cumulative Interest
3/1/2013
2.921195652
2.921195652
9/1/2013
5.462632116
5.421195652
3/1/2014
7.921195652
7.921195652
9/1/2014
10.46263212
10.42119565
3/1/2015
12.92119565
12.92119565
9/1/2015
15.46263212
15.42119565
3/1/2016
17.93500781
17.92119565
9/1/2016
20.46263212
20.42119565
3/1/2017
22.92119565
22.92119565
9/1/2017
25.46263212
25.42119565
3/1/2018
27.92119565
27.92119565

As you can see, on the anniversary of the first interest date, the cumulative interest is correct, but when the settlement date falls on the first of September, it's incorrect. This means that the cumulative interest calculation when the settlement date is greater than the first interest date is correct about half the time.
Here's another example where we have set the settlement dates equal to the 1st of February and the 1st of August and calculated the cumulative interest and the accrued interest using the A and NL values. Compare those values to the values returned by the Excel ACCRINT function.

settlement
calc_method = TRUE
calc_method = FALSE
Cumulative Interest
Accrued Interest
A
NL
PCD
NCD
3/1/2013
2.92119565
2.92119565
2.92119565
0
 
 
 
 
9/1/2013
5.46263212
5.46263212
5.42119565
0
 
 
 
 
2/1/2014
7.53445532
7.57589178
7.53445532
2.1132597
153
181
9/1/2013
3/1/2014
8/1/2014
10.0344553
10.0758918
10
2.0788043
153
184
3/1/2014
9/1/2014
2/1/2015
12.5344553
12.6173282
12.5344553
2.1132597
153
181
9/1/2014
3/1/2015
8/1/2015
15.0344553
15.1173282
15
2.0788043
153
184
3/1/2015
9/1/2015
2/1/2016
17.5344553
17.6587647
17.522844
2.1016484
153
182
9/1/2015
3/1/2016
8/1/2016
20.0344553
20.1725769
20
2.0788043
153
184
3/1/2016
9/1/2016
2/1/2017
22.5344553
22.7140133
22.5344553
2.1132597
153
181
9/1/2016
3/1/2017
8/1/2017
25.0344553
25.2140133
25
2.0788043
153
184
3/1/2017
9/1/2017
2/1/2018
27.5344553
27.7554498
27.1132597
2.1132597
153
181
9/1/2017
3/1/2018

As you can see, there are actually some differences between the ACCRINT values based on the settings for calc_method, but the actual accrued interest amount is never returned and the cumulative interest amounts are still wrong half the time.
Finally, let's look at one more way of entering the data. First, we enter the following data and formula into a spreadsheet.

settlement
calc_method = TRUE
calc_method = FALSE
Accrued Interest
A
NL
PCD
NCD
41671
=ACCRINT(DATE(2000,9,1),H2,A2,0.05,100,2,1,TRUE)
=ACCRINT(DATE(2000,9,1),H2,A2,0.05,100,2,1,FALSE)
=100*0.05/2*E2/F2
=A2-G2
=H2-G2
41518
=EDATE(G2,6)
=EDATE(A2,6)
=ACCRINT(DATE(2000,9,1),H3,A3,0.05,100,2,1,TRUE)
=ACCRINT(DATE(2000,9,1),H3,A3,0.05,100,2,1,FALSE)
=100*0.05/2*E3/F3
=A3-G3
=H3-G3
=H2
=EDATE(G3,6)
=EDATE(A3,6)
=ACCRINT(DATE(2000,9,1),H4,A4,0.05,100,2,1,TRUE)
=ACCRINT(DATE(2000,9,1),H4,A4,0.05,100,2,1,FALSE)
=100*0.05/2*E4/F4
=A4-G4
=H4-G4
=H3
=EDATE(G4,6)
=EDATE(A4,6)
=ACCRINT(DATE(2000,9,1),H5,A5,0.05,100,2,1,TRUE)
=ACCRINT(DATE(2000,9,1),H5,A5,0.05,100,2,1,FALSE)
=100*0.05/2*E5/F5
=A5-G5
=H5-G5
=H4
=EDATE(G5,6)
=EDATE(A5,6)
=ACCRINT(DATE(2000,9,1),H6,A6,0.05,100,2,1,TRUE)
=ACCRINT(DATE(2000,9,1),H6,A6,0.05,100,2,1,FALSE)
=100*0.05/2*E6/F6
=A6-G6
=H6-G6
=H5
=EDATE(G6,6)
=EDATE(A6,6)
=ACCRINT(DATE(2000,9,1),H7,A7,0.05,100,2,1,TRUE)
=ACCRINT(DATE(2000,9,1),H7,A7,0.05,100,2,1,FALSE)
=100*0.05/2*E7/F7
=A7-G7
=H7-G7
=H6
=EDATE(G7,6)
=EDATE(A7,6)
=ACCRINT(DATE(2000,9,1),H8,A8,0.05,100,2,1,TRUE)
=ACCRINT(DATE(2000,9,1),H8,A8,0.05,100,2,1,FALSE)
=100*0.05/2*E8/F8
=A8-G8
=H8-G8
=H7
=EDATE(G8,6)
=EDATE(A8,6)
=ACCRINT(DATE(2000,9,1),H9,A9,0.05,100,2,1,TRUE)
=ACCRINT(DATE(2000,9,1),H9,A9,0.05,100,2,1,FALSE)
=100*0.05/2*E9/F9
=A9-G9
=H9-G9
=H8
=EDATE(G9,6)

This should give use the following result.

settlement
calc_method = TRUE
calc_method = FALSE
Accrued Interest
A
NL
PCD
NCD
2/1/2014
67.11325967
2.113259669
2.113259669
153
181
9/1/2013
3/1/2014
8/1/2014
69.57880435
2.078804348
2.078804348
153
184
3/1/2014
9/1/2014
2/1/2015
72.11325967
2.113259669
2.113259669
153
181
9/1/2014
3/1/2015
8/1/2015
74.57880435
2.078804348
2.078804348
153
184
3/1/2015
9/1/2015
2/1/2016
77.10164835
2.101648352
2.101648352
153
182
9/1/2015
3/1/2016
8/1/2016
79.57880435
2.078804348
2.078804348
153
184
3/1/2016
9/1/2016
2/1/2017
82.11325967
2.113259669
2.113259669
153
181
9/1/2016
3/1/2017
8/1/2017
84.57880435
2.078804348
2.078804348
153
184
3/1/2017
9/1/2017

As you can see, when the calc_method is FALSE, the Excel ACCRINT function is actually returning the correct accrued interest value. In order to do this, we had to set the issue date to a regular coupon date, 2000-09-01 in this case, and set the first interest date to the next coupon date.
How do we know what the next coupon date is? In this particular case, we forced the value, but Excel provides a function for determining the next coupon date, COUPNCD, which requires the maturity date as input. Why not just have the maturity date be input into the function? Further, the value that is being returned does not conform to the documentation as it is neither the value from the issue date to the settlement date nor is it the value from the first interest date to the settlement date. What happens if the function is suddenly 'fixed' and it starts working the way it is documented? Would we even be advised that there was a change to function's behavior or would it just be applied as part of a system update? What if you actually do want to calculate the interest in the odd first coupon period? We have already demonstrated that you cannot rely on the Excel ACCRINT results for odd first coupons.
Calculating the accrued interest is an important part of the bond figuration and settlement process and getting those amounts wrong can have real monetary consequences. We think that using the Excel ACCRINT function just increases the risk of getting those calculations wrong. Let us know what you think.

Trackback Print
Tags:
Categories:
Location: Blogs Parent Separator The WestClinTech Blog

Search Blogs

Blog Archives

Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service