Electron wrote: ↑Fri Jun 17, 2022 7:25 pmThe spreadsheet included three test cases for the ACCRINT function using different issue and first interest dates. Results are shown below for both Excel and Libre Office Calc.
The results are actually correct for Excel. As defined by Microsoft, the function returns Total Accrued Interest
from Issue to Settlement when using Calc Method 1 which is the default. (underlines added)
It's debatable whether Excel's results are "correct". Every other reference I've seen for a bond's "accrued interest" only includes the interest accrued
since the previous interest payment date. I've rearranged the table from your post and added a few rows. Row 10 shows the correct accrued interest based on the previous and next coupon dates.
Cell B13 shows that with Excel the
ACCRINT function returns the correct value
when "Issue" and "First_interest" are set to these dates. But cells C13 and D13 return much larger values when "Issue" and "First_interest" are set to previous years. (C13 is $2.72 plus one full year's interest. D13 is $2.72 plus two full year's interest.) I don't consider either of these to be "correct".
Code: Select all
Row Col A Col B Col C Col D Formula in Column B
1 Settlement 5/02/2022
2 Rate 2.500%
3 Par 20,000.00
4 Fequency 2
5 Basis 1
6 Previous coupon date 4/30/2022
7 Next coupon date 10/31/2022
8 Days before settlement 2 =B1-B6
9 Days in period 184 =B7-B6
10 Correct accrued interest 2.72 =B3*(B2/2)*(B8/B9)
Code: Select all
Row Col A Col B Col C Col D Formula in Col B Copied to Col D
11 Issue 4/30/2022 4/30/2021 4/30/2020
12 First_interest 10/31/2022 10/31/2021 10/31/2020
13 Excel (Calc_method 1) 2.72 502.72 1,002.72 =ACCRINT(B11,B12,$B1,$B2,$B3,$B4,$B5,1)
14 Excel (Calc_method 0) 2.72 498.64 994.57 =ACCRINT(B11,B12,$B1,$B2,$B3,$B4,$B5,0)
15 Libre Office 2.74 502.74 1,000.00 (copied from by Electron's table)
Electron, in same post, wrote:The results in Libre Office Calc were off by small amounts. Note that the first two results were $0.02 higher while the third result was $2.72 lower.
Strange. Don't know why Libre Office's value in B15 is $0.02 higher. It's not because it assumes the period ends on October 30th instead of the 31st. That would make the accrued interest $2.73, not $2.74. Note: I don't have Libre Office. I'm just showing the values from your post.
Electron, in same post, wrote:As a side note, the results in Excel using Calc Method 0 in these three cases appears to be incorrect. Calc Method 0 provides Total Accrued Interest from First Interest to Settlement.
Cell B14 shows this also returns the correct $2.72 with Excel when Issue and First_interest are set to the previous and next coupon dates. Don't know where the $498.64 and $994.57 are coming from. According to the
ACCRINT notes it should return $252.72 (10/31/21 - 5/2/22: 250 + 2.72) and $752.72 (10/31/20 - 5/2/22: 500 + 250 + 2.72).
ACCRINT notes wrote:Calc_method Optional. A logical value that specifies the way to calculate the total accrued interest when the date of settlement is later than the date of first_interest. A value of TRUE (1) returns the total accrued interest from issue to settlement. A value of FALSE (0) returns the accrued interest from first_interest to settlement. If you do not enter the argument, it defaults to TRUE. (underline added)
Kevin M wrote: ↑Sat Jun 18, 2022 5:54 pm
FactualFran wrote: ↑Sat Jun 18, 2022 4:49 pmAccording to the
auction results the accrued interest per $1,000 was $0.15625. ... It would be nice if a spreadsheet function was able to return the amount of accrued interest calculated by the Treasury.
=ACCRINT("4/30/2022", "10/31/2022", "5/2/2022",2.875%, 1000,2,1) = 0.15625
The problem here isn't the ACCRINT function, but this:
=COUPNCD("5/2/2022", "4/30/2029",2,1) = 10/30/2022
It should return 10/31/2022.
I use the COUPNCD in the ACCRINT function in my spreadsheets, and this is what causes the problem for cases like this.
Note: I believe Kevin is using Google Sheets. In Excel,
=COUPNCD("5/2/2022", "4/30/2029",2,1) does return the correct, 10/31/2022. However, in both Excel and Google Sheets
=COUPDAYS("5/2/2022", "4/30/2029",2,1) returns 183 while there are actually 18
4 days in the period 4/30 to 10/31. (May, July, August, and October have 31 days). See the
Tue Jun 07, 2022 8:50 pm post from the mother thread or open the Google Sheets file,
CoupFuncsTest.
As I pointed out near the end of the
Tue Jun 07, 2022 8:50 pm post, there is a workaround that should work with both Excel and Google Sheets:
- Avoid COUPxxx functions except for COUPPCD and COUPNCD.
- When the maturity date is the last day of a month, apply these two functions to the day following the maturity date (the 1st day of the following month) and then subtract 1 from the result.
- Calculate the results of the following COUPxxx functions by using the dates returned by the adapted COUPPCD and COUPNCD functions:
Code: Select all
DAYS = NCD - PCD Days in coupon period
DAYBS = Settle - PCD Days before settlement
DAYSNC = NCD - Settle Days after settlement
Here is an example:
Code: Select all
Row Col A Col B Formula in column B
1 Settlement 5/02/2022
2 Matures 4/30/2029
3 Face value 1,000
4 Coupon 2.875%
5 Prev coupon date 4/30/2022 =IF(DAY(B2+1)=1,COUPPCD(B1+1,B2+1,2,1)-1,COUPPCD(B1,B2,2,1))
6 Next coupon date 10/31/2022 =IF(DAY(B2+1)=1,COUPNCD(B1+1,B2+1,2,1)-1,COUPNCD(B1,B2,2,1))
7 Days before settle 2 =B1-B5
8 Days in period 184 =B6-B5
9 Accrued interest 0.15625 =B3*(B4/2)*(B7/B8)
10 In one step 0.15625 =B3*(B4/2)*IF(DAY(B2+1)=1,(B1+1-COUPPCD(B1+1,B2+1,2,1))/(COUPNCD(B1+1,B2+1,2,1)-COUPPCD(B1+1,B2+1,2,1)),(B1-COUPPCD(B1,B2,2,1))/(COUPNCD(B1,B2,2,1)-COUPPCD(B1,B2,2,1)))
11 ACCRINT 0.15625 =ACCRINT(B5,B6,B1,B4,B3,2,1)
12 In one step 0.15625 =ACCRINT(IF(DAY(B2+1)=1,COUPPCD(B1+1,B2+1,2,1)-1,COUPPCD(B1,B2,2,1)),IF(DAY(B2+1)=1,COUPNCD(B1+1,B2+1,2,1)-1,COUPNCD(B1,B2,2,1)),B1,B4,B3,2,1,)