Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
User avatar
Topic Author
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by #Cruncher »

There are about two dozen posts in Kevin M's thread, Trading Treasuries (nominal and TIPS), on this subject. I'm starting this new thread since they were distracting from the topic Kevin is pursuing. I suggest this new thread be used for any further discussion on the rather esoteric topic of spreadsheet functions used to calculate accrued interest. There are two main issues:
  • In Excel the ACCRINT function does not work as expected if the first two parameters, "Issue" and "First_interest", are entered as described. To work properly the first two parameters need to be the previous interest payment date and the next interest payment date.
    This Sat May 28, 2022 6:52 am post in Kevin's thread discusses the problem.
  • In Excel and Google Sheets several of the functions COUPxxx return incorrect values when the maturity date of the bond is the last day of the month.
    • COUPPCD: Returns a number that represents the previous coupon date before the settlement date.
    • COUPNCD: Returns a number that represents the next coupon date after the settlement date.
    • COUPDAYS: Returns the number of days in the coupon period that contains the settlement date.
    • COUPDAYBS: Returns the number of days from the beginning of a coupon period until its settlement date.
    • COUPDAYSNC: Returns the number of days from the settlement date to the next coupon date.
    This Tue Jun 07, 2022 8:50 pm post in Kevin's thread summarizes the problem.
Here is a list of the 24 posts I find in Kevin's thread that refer to either "ACCRINT" or "COUPDAYS":
Kevin M Thu May 26, 2022 6:52 pm
Electron Thu May 26, 2022 11:52 pm
#Cruncher Fri May 27, 2022 8:45 am
Doc Fri May 27, 2022 11:03 am
Kevin M Fri May 27, 2022 12:55 pm
Doc Fri May 27, 2022 2:44 pm
Kevin M Fri May 27, 2022 6:40 pm
#Cruncher Sat May 28, 2022 6:52 am
Kevin M Sat May 28, 2022 2:02 pm
Electron Mon May 30, 2022 6:14 pm
Electron Tue May 31, 2022 6:59 pm
#Cruncher Tue May 31, 2022 8:35 pm
FactualFran Tue May 31, 2022 11:09 pm
Electron Wed Jun 01, 2022 5:25 pm
Electron Thu Jun 02, 2022 4:04 pm
FactualFran Thu Jun 02, 2022 4:39 pm
#Cruncher Sat Jun 04, 2022 12:59 pm
Kevin M Sat Jun 04, 2022 2:48 pm
Electron Sat Jun 04, 2022 4:42 pm
Electron Sat Jun 04, 2022 7:20 pm
Electron Mon Jun 06, 2022 2:25 pm
#Cruncher Tue Jun 07, 2022 8:50 pm
Electron Wed Jun 08, 2022 1:40 pm
Kevin M Wed Jun 08, 2022 2:48 pm

Again, please use this new thread for any further questions about or discussion of the subject and not further clutter Kevin's thread.
User avatar
Electron
Posts: 2658
Joined: Sat Mar 10, 2007 7:46 pm

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by Electron »

#Cruncher wrote: Thu Jun 09, 2022 8:12 am There are about two dozen posts in Kevin M's thread, Trading Treasuries (nominal and TIPS), on this subject. I'm starting this new thread since they were distracting from the topic Kevin is pursuing. I suggest this new thread be used for any further discussion on the rather esoteric topic of spreadsheet functions used to calculate accrued interest.
Thanks for setting up this new thread.

I'll pass along an update on my activities. Last night I tested all six of the referenced financial functions in Libre Office Calc. The functions included COUPPCD, COUPNCD, COUPDAYBS, COUPDAYSNC, COUPDAYS, and ACCRINT.

The five coupon related functions were tested with a settlement date of 5-12-22 and fourteen month-end maturity dates ranging from 11-30-22 through 12-31-23. The coupon frequency was two and the day count basis was actual/actual. The results all appeared to be correct. That is good news after seeing the results with Google Sheets.

The ACCRINT function was tested with the parameters from one of the Treasury notes that I purchased earlier this year. Three test cases were used with different issue and first interest dates. The settlement date was fixed. Unfortunately, the accrued interest was off by a small amount in each case. That could be a problem with the day count or day count basis. Libre Office Calc does not support the optional Calc Method for this function.

It appears that Excel, Google Sheets, and Libre Office Calc all produce slightly different results.

As mentioned earlier, I did report the problem with the Excel COUPDAYS function to Microsoft directly from the Excel 2019 application. A description was provided along with a screenshot generated by Excel. Let's hope that Microsoft does look into the problem. I hope to follow up with feedback on the ACCRINT function after a little more study.

These functions are quite complicated and probably cannot be fixed without significant effort. It's not clear if an employee with the necessary knowledge and experience would be available. If not, someone else would need to acquire the necessary knowledge about coupon bonds, accrued interest, and day count basis.
Enjoying the Outdoors
User avatar
Topic Author
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by #Cruncher »

Electron wrote: Thu Jun 09, 2022 6:54 pmLast night I tested all six of the referenced financial functions in Libre Office Calc. The functions included COUPPCD, COUPNCD, COUPDAYBS, COUPDAYSNC, COUPDAYS, and ACCRINT. ... The results all appeared to be correct.
Good to hear that Libre Office returns correct results for the five COUPxxx functions. (I assume you tested several cases where the maturity date was the last day of a month.) But do you also mean that its ACCRINT function returns the correct accrued interest when the first two parameters, "Issue" and "First_interest", are entered as described even for bonds issued in previous years? (In Excel this function only returns the correct value when the first two parameters are the previous and next coupon dates.)
Electron in same post wrote:As mentioned earlier, I did report the problem with the Excel COUPDAYS function to Microsoft directly from the Excel 2019 application. ... These functions are quite complicated and probably cannot be fixed without significant effort.
As a reminder to readers who haven't followed this discussion in the mother thread (e.g., see this May 31, 2022 8:35 pm post), Excel and Google Sheets have a problem with one or more of these functions when the maturity date is the last day of the month. Excel's problem should be easier to fix since it only occurs for the COUPDAYS function. As shown below this should return a value equal to the number of days in the period; i.e., the number of days from COUPPCD to COUPNCD. It should also equal the sum of the number of days returned by COUPDAYBS and COUPDAYSNC. So all Microsoft needs to do is make this function consistent with the other four.
  • COUPPCD: Previous Coupon Date before settlement date.
  • COUPNCD: Next Coupon Date after settlement date.
  • COUPDAYBS: Number of days from COUPPCD until settlement date.
  • COUPDAYSNC: Number of days from settlement date to COUPNCD.
  • COUPDAYS: Number of days from COUPPCD to COUPNCD. Also equals COUPDAYBS plus COUPDAYSNC.
With Google Sheets, on the other hand, the five COUPxxx functions are consistent. So for example, when COUPDAYS returns an incorrect value, so does COUPPCD or COUPNCD. And so does COUPDAYBS or COUPDAYSNC. (See the CoupFuncsChk sheet of the Google Sheets file, COUPFuncsTest.) It shouldn't be difficult to fix for Treasury securities that use the Actual day count convention, [1] but it might be harder to fix for other day count conventions. I don't fully understand them.

It's possible that the programmers simply aren't aware of the convention that, if a bond matures on the last day of a month, all of its interest payment dates will be the last day of the month. E.g., if a bond with semi-annual interest payments matures on April 30, the other payment date will be October 31, not October 30. If it matures on February 28 (in a non-leap year), the other payment date will be the 31st of August, not the 28th.

But we don't need to wait for a fix in order to calculate accrued interest. There is a workaround, shown on rows 11 & 12 in the table at the bottom of this post from the mother thread. To calculate the previous and next coupon dates when maturity is the last day of a month, I subtract 1 from the result of COUPPCD and COUPNCD functions applied to the day following the settlement date and the day following the maturity date (i.e., the 1st day of the following month). This is shown in columns F & G in the illustrative table below of 21 Treasury Note and 5 TIPS auctions this year -- downloaded from TreasuryDirect's Auction Query. (See footnote [2] for the formulas used in row 16 for the 2-1/2% note maturing on 4/30/2024.)

Code: Select all

  1      Col A       Col B   Col C    Col D     Col E       Col F       Col G   Col H    Col I       Col J
  2                                          Reported    Previous        Next    Days  Days in  Calculated
  3                                   Index  Accr Int      Coupon      Coupon  Before   Coupon    Accr Int
  4     Issued     Matures  Coupon    Ratio  per 1000        Date        Date   Issue   Period    per 1000

Code: Select all

  5  1/18/2022   1/15/2025  1.125%            0.09323   1/15/2022   7/15/2022       3      181     0.09323
  6  1/18/2022  11/15/2031  1.375%            2.43094  11/15/2021   5/15/2022      64      181     2.43094
  7  1/18/2022  11/15/2051  1.875%            3.31492  11/15/2021   5/15/2022      64      181     3.31492
  8  1/31/2022  11/15/2041  2.000%            4.25414  11/15/2021   5/15/2022      77      181     4.25414
  
  9  2/28/2022   2/15/2042  2.375%            0.85290   2/15/2022   8/15/2022      13      181     0.85290
 10  3/15/2022   2/15/2032  1.875%            1.45028   2/15/2022   8/15/2022      28      181     1.45028
 11  3/15/2022   2/15/2052  2.250%            1.74033   2/15/2022   8/15/2022      28      181     1.74033
 12  3/31/2022   2/15/2042  2.375%            2.88674   2/15/2022   8/15/2022      44      181     2.88674
 
 13  4/18/2022   4/15/2025  2.625%            0.21516   4/15/2022  10/15/2022       3      183     0.21516
 14  4/18/2022   2/15/2032  1.875%            3.21133   2/15/2022   8/15/2022      62      181     3.21133
 15  4/18/2022   2/15/2052  2.250%            3.85359   2/15/2022   8/15/2022      62      181     3.85359
 
 16  5/02/2022   4/30/2024  2.500%            0.13587   4/30/2022  10/31/2022       2      184     0.13587 [2]
 17  5/02/2022   4/30/2027  2.750%            0.14946   4/30/2022  10/31/2022       2      184     0.14946
 18  5/02/2022   4/30/2029  2.875%            0.15625   4/30/2022  10/31/2022       2      184     0.15625
 19  5/02/2022   2/15/2042  2.375%            4.98619   2/15/2022   8/15/2022      76      181     4.98619
 20  5/16/2022   5/15/2025  2.750%            0.07473   5/15/2022  11/15/2022       1      184     0.07473
 21  5/16/2022   5/15/2032  2.875%            0.07813   5/15/2022  11/15/2022       1      184     0.07813
 22  5/16/2022   5/15/2052  2.875%            0.07813   5/15/2022  11/15/2022       1      184     0.07813
 23  5/31/2022   5/15/2042  3.250%            1.41304   5/15/2022  11/15/2022      16      184     1.41304
 
 24  6/15/2022   5/15/2032  2.875%            2.42188   5/15/2022  11/15/2022      31      184     2.42188
 25  6/15/2022   5/15/2052  2.875%            2.42188   5/15/2022  11/15/2022      31      184     2.42188
 
 26  1/31/2022   1/15/2032  0.125%  1.00253   0.05539   1/15/2022   7/15/2022      16      181     0.05539
 27  2/28/2022   2/15/2052  0.125%  1.00142   0.04495   2/15/2022   8/15/2022      13      181     0.04495
 28  3/31/2022   1/15/2032  0.125%  1.01396   0.26260   1/15/2022   7/15/2022      75      181     0.26259
 29  4/29/2022   4/15/2027  0.125%  1.00424   0.04801   4/15/2022  10/15/2022      14      183     0.04802
 30  5/31/2022   1/15/2032  0.125%  1.03672   0.48685   1/15/2022   7/15/2022     136      181     0.48686
  1. To see how difficult it is, I tried to duplicate the results of COUPPCD and COUPNCD using only the Excel date functions, DATE, YEAR, MONTH, and DAY, and the INT function. It was harder than I expected, but I finally got it to work as shown on rows 8, 9, and 10 below.

    Code: Select all

    Row             Col A       Col B   Formula in Column B
      4        Settlement   5/12/2022
      5           Matures  11/30/2025
      6        Face value      20,000
      7            Coupon      0.125%
      8  Mature-Settle mo          42  =12*(YEAR(B5)-YEAR(B4))+MONTH(B5)-MONTH(B4)-IF(DAY(B5+1)=1,(DAY(B4+1)=1),(DAY(B4)>=DAY(B5)))
      9  Next coupon date   5/31/2022  =IF(DAY(B5+1)=1,DATE(YEAR(B5+1),MONTH(B5+1)-6*INT(B8/6),1)-1,DATE(YEAR(B5),MONTH(B5)-6*INT(B8/6),DAY(B5)))
     10  Prev coupon date  11/30/2021  =IF(DAY(B5+1)=1,DATE(YEAR(B9+1),MONTH(B9+1)-6,1)-1,DATE(YEAR(B9),MONTH(B9)-6,DAY(B9)))
     11  Accrued interest       11.20  =B6*(B7/2)*(B4-B10)/(B9-B10)
     12   Using ACCRINT()       11.20  =ACCRINT(B10,B9,B4,B7,B6,2,1)
    Note: The formula on row 8 calculates the number of months that the settlement date precedes the maturity date. It makes use of the fact that the logical values TRUE and FALSE are converted to 1 and 0 when used in a numerical expression. E.g., 3 = 2 + (10 > 9) and 2 = 2 + (10 > 11).
  2. Example computations for row 16:

    Code: Select all

    F16:  4/30/2022 = IF(DAY(B16+1) = 1, COUPPCD(A16+1, B16+1, 2, 1) - 1, COUPPCD(A16, B16, 2, 1))
    G16: 10/31/2022 = IF(DAY(B16+1) = 1, COUPNCD(A16+1, B16+1, 2, 1) - 1, COUPNCD(A16, B16, 2, 1))
    H16:          2 = A16 - F16 
    I16:        184 = G16 - F16
    J16:    0.13587 = 1000 * IF(D16 = "", 1, D16) * (C16 / 2) * (A16 - F16) / (G16 - F16)
User avatar
Electron
Posts: 2658
Joined: Sat Mar 10, 2007 7:46 pm

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by Electron »

#Cruncher wrote: Wed Jun 15, 2022 12:04 pm I assume you tested several cases where the maturity date was the last day of a month.
The test of the five coupon related functions used fourteen month-end dates from 11/30/22 through 12/31/23. All results were correct using Libre Office Calc. COUPDAYS was consistent with COUPDAYBS and COUPDAYSNC. The same spreadsheet was used with Excel and the COUPDAYS function had the errors that were discussed earlier.
#Cruncher wrote: Wed Jun 15, 2022 12:04 pm But do you also mean that its ACCRINT function returns the correct accrued interest when the first two parameters, "Issue" and "First_interest", are entered as described even for bonds issued in previous years? (In Excel this function only returns the correct value when the first two parameters are the previous and next coupon dates.)
The 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.

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. Rounded results are shown in all cases.

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.

Code: Select all

$2.72	 ACCRINT	Excel		$2.74	 ACCRINT	Libre Office
					
04/30/22 ISSUE				04/30/22 ISSUE	
10/31/22 FIRST INTEREST			10/31/22 FIRST INTEREST	
05/02/22 SETTLEMENT			05/02/22 SETTLEMENT	
2.5%	 RATE				2.5%	 RATE	
20000	 PAR				20000	 PAR	
2	 FREQUENCY			2	 FREQUENCY	
1	 BASIS				1	 BASIS	
					
					
$502.72	 ACCRINT	Excel		$502.74	 ACCRINT	Libre Office
					
04/30/21 ISSUE				04/30/21 ISSUE	
10/31/21 FIRST INTEREST			10/31/21 FIRST INTEREST	
05/02/22 SETTLEMENT			05/02/22 SETTLEMENT	
2.5%	 RATE				2.5%	 RATE	
20000	 PAR				20000	 PAR	
2	 FREQUENCY			2	 FREQUENCY	
1	 BASIS				1	 BASIS	
					
					
$1,002.72 ACCRINT	Excel		$1,000.00 ACCRINT	Libre Office
					
04/30/20 ISSUE				04/30/20 ISSUE	
10/31/20 FIRST INTEREST			10/31/20 FIRST INTEREST	
05/02/22 SETTLEMENT			05/02/22 SETTLEMENT	
2.5%	 RATE				2.5%	 RATE	
20000	 PAR				20000	 PAR	
2	 FREQUENCY			2	 FREQUENCY	
1	 BASIS				1	 BASIS
As mentioned earlier, these two sites show a modification in the usage of the ACCRINT Function when the settlement date is not within the first interest period.

https://exceljet.net/excel-functions/ex ... t-function

http://www.tvmcalcs.com/index.php/calcu ... cel-3-ways

"Set issue date to the previous coupon date"
"Set first interest date to the previous coupon date"

You made an excellent point that programmers may not be aware of the convention involving the last day of the month.

Excellent work once again with Excel. I intend to implement the accrued interest formula in your earlier post so that Excel and Google Sheets both provide the correct result.
Last edited by Electron on Sat Jun 18, 2022 1:55 pm, edited 1 time in total.
Enjoying the Outdoors
FactualFran
Posts: 2776
Joined: Sat Feb 21, 2015 1:29 pm

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by FactualFran »

It seems that financial functions of some spreadsheet software does not correctly calculate the interest payment dates of Treasury securities that pay interest at the end of months. Because the accrint function has the interest date as a parameter, the date is being supplied rather than being calculated by the spreadsheet software. Because the COPUxxx functions do not have the interest date as a parameter, the date is being calculated by the spreadsheet software.
Electron wrote: Fri Jun 17, 2022 7:25 pm

Code: Select all

$2.72	 ACCRINT	Excel		$2.74	 ACCRINT	Libre Office
					
04/30/22 ISSUE				04/30/22 ISSUE	
10/31/22 FIRST INTEREST			10/31/22 FIRST INTEREST	
05/02/22 SETTLEMENT			05/02/22 SETTLEMENT	
2.5%	 RATE				2.5%	 RATE	
1000	 PAR				1000	 PAR	
2	 FREQUENCY			2	 FREQUENCY	
1	 BASIS				1	 BASIS	
That snipped part of the example does not make sense. An annual interest rate of 2.5% on $1,000 would be $25 a year. Regardless of details of the day counting convention, the accrued interest for two days from the issue date (04/30/22) to the settlement date (05/02/22) should be about $0.14 (about 2/365 of the interest for the year).
User avatar
Electron
Posts: 2658
Joined: Sat Mar 10, 2007 7:46 pm

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by Electron »

FactualFran wrote: Sat Jun 18, 2022 12:58 pm Regardless of details of the day counting convention, the accrued interest for two days from the issue date (04/30/22) to the settlement date (05/02/22) should be about $0.14 (about 2/365 of the interest for the year).
Those examples were actually for 20 bonds and the ACCRINT function in the spreadsheet was preceded by a multiplier of 20. I will edit the post and change the par value from 1000 to 20000. Thanks for taking a close look at the numbers. :happy
Enjoying the Outdoors
User avatar
Kevin M
Posts: 15787
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by Kevin M »

FactualFran wrote: Sat Jun 18, 2022 12:58 pm It seems that financial functions of some spreadsheet software does not correctly calculate the interest payment dates of Treasury securities that pay interest at the end of months. Because the accrint function has the interest date as a parameter, the date is being supplied rather than being calculated by the spreadsheet software. Because the COPUxxx functions do not have the interest date as a parameter, the date is being calculated by the spreadsheet software.
Electron wrote: Fri Jun 17, 2022 7:25 pm

Code: Select all

$2.72	 ACCRINT	Excel		$2.74	 ACCRINT	Libre Office
					
04/30/22 ISSUE				04/30/22 ISSUE	
10/31/22 FIRST INTEREST			10/31/22 FIRST INTEREST	
05/02/22 SETTLEMENT			05/02/22 SETTLEMENT	
2.5%	 RATE				2.5%	 RATE	
1000	 PAR				1000	 PAR	
2	 FREQUENCY			2	 FREQUENCY	
1	 BASIS				1	 BASIS	
That snipped part of the example does not make sense. An annual interest rate of 2.5% on $1,000 would be $25 a year. Regardless of details of the day counting convention, the accrued interest for two days from the issue date (04/30/22) to the settlement date (05/02/22) should be about $0.14 (about 2/365 of the interest for the year).
That's what I get with this in Google Sheets:

=ACCRINT("4/30/2022", "10/31/2022", "5/2/2022",2.5%,1000,2,1)

= 0.14

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
Kevin M
Posts: 15787
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by Kevin M »

Electron wrote: Sat Jun 18, 2022 1:34 pm
FactualFran wrote: Sat Jun 18, 2022 12:58 pm Regardless of details of the day counting convention, the accrued interest for two days from the issue date (04/30/22) to the settlement date (05/02/22) should be about $0.14 (about 2/365 of the interest for the year).
Those examples were actually for 20 bonds and the ACCRINT function in the spreadsheet was preceded by a multiplier of 20. I will edit the post and change the par value from 1000 to 20000. Thanks for taking a close look at the numbers. :happy
Yep:

=ACCRINT("4/30/2022", "10/31/2022", "5/2/2022",2.5%,20000,2,1)

= 2.72

Kevin
If I make a calculation error, #Cruncher probably will let me know.
FactualFran
Posts: 2776
Joined: Sat Feb 21, 2015 1:29 pm

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by FactualFran »

There has recently been an auction of a Treasury Note similar to example posted by Electron. Relevant parameters are:

04/30/22 Dated Date
10/31/22 First Interest Date
05/02/22 Issue Date
2.875% Rate

According to the auction results the accrued interest per $1,000 was $0.15625. The accrued interest amount is semi-annual coupon payment per $1,000 of $14.375 times 2/184 (the number of days of accrued interest divided by the number of days in the interest period).

It would be nice if a spreadsheet function was able to return the amount of accrued interest calculated by the Treasury.
User avatar
Electron
Posts: 2658
Joined: Sat Mar 10, 2007 7:46 pm

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by Electron »

FactualFran wrote: Sat Jun 18, 2022 4:49 pm According to the auction results the accrued interest per $1,000 was $0.15625.
The ACCRINT function in Excel provides the same result when using 5-02-22 as the settlement date.

The other formulas mentioned in these threads should produce the same result.

Note that some of the bond related financial functions provide incorrect results depending on the specific case. The results may also be different in Microsoft Excel, Google Sheets, and Libre Office Calc.

Code: Select all

$0.15625 ACCRINT
	
4/30/22	 ISSUE
10/31/22 FIRST INTEREST
5/02/22	 SETTLEMENT
2.875%	 RATE
1000	 PAR
2	 FREQUENCY
1	 BASIS
Last edited by Electron on Wed Jun 22, 2022 1:50 pm, edited 2 times in total.
Enjoying the Outdoors
User avatar
Kevin M
Posts: 15787
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by Kevin M »

FactualFran wrote: Sat Jun 18, 2022 4:49 pm There has recently been an auction of a Treasury Note similar to example posted by Electron. Relevant parameters are:

04/30/22 Dated Date
10/31/22 First Interest Date
05/02/22 Issue Date
2.875% Rate

According to the auction results the accrued interest per $1,000 was $0.15625. The accrued interest amount is semi-annual coupon payment per $1,000 of $14.375 times 2/184 (the number of days of accrued interest divided by the number of days in the interest period).

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.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
Topic Author
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by #Cruncher »

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 184 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,)
User avatar
Electron
Posts: 2658
Joined: Sat Mar 10, 2007 7:46 pm

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by Electron »

#Cruncher wrote: Sun Jun 19, 2022 4:03 pm As I pointed out near the end of this post, there is a workaround that should work with both Excel and Google Sheets.
That equation returns the correct results in both Excel and Google Sheets for the three Treasury notes I purchased earlier this year. All of the securities have month-end maturity dates and previous results in Google Sheets were incorrect.

Very nice solution. I see from your post in the earlier thread that COUPPCD and COUPNCD may return incorrect results in Google Sheets with month-end maturity dates.

viewtopic.php?p=6715206#p6715206

However, the date modification using the first day of the following month resolves the problem.
Enjoying the Outdoors
User avatar
Electron
Posts: 2658
Joined: Sat Mar 10, 2007 7:46 pm

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by Electron »

I just noticed a case where the YIELD function returns different results in Excel and Google Sheets.

Here is the information for a Treasury note maturing on 11-30-22.

Settlement 5-12-22
Coupon 0.125%
Price 99.26171
Redemption 100
Frequency 2
Basis 1

The brokerage confirmation and Excel report a yield of 1.472%. Google Sheets returns 1.478%.
Enjoying the Outdoors
User avatar
Topic Author
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by #Cruncher »

Electron wrote: Thu Jun 30, 2022 12:50 pmI just noticed a case where the YIELD function returns different results in Excel and Google Sheets. Here is the information for a Treasury note maturing on 11-30-22.
Settlement 5-12-22
Coupon 0.125%
Price 99.26171
...
The brokerage confirmation and Excel report a yield of 1.472%. Google Sheets returns 1.478%.
I see the same thing, Electron. 1.472% is the correct yield-to-maturity (YTM). [1] I believe Google Sheets returns the wrong value because it thinks the next coupon payment after the settlement is 5/30/2022, not the correct 5/31/2022. I say this because that is what the COUPNCD function returns in Google Sheets: [2]
5/30/2022 = COUPNCD(DATE(2022, 5, 12), DATE(2022,11,30), 2, 1)

Therefore instead of the correct 1 + 19/182 periods, Google Sheets thinks the bond will mature in a slightly shorter 1 + 18/181 periods, leading to a slightly higher YTM. This is shown with more precision below where cell B6 assumes the correct 5/31/2022 next payment date leading to the correct 1.472% YTM. Cell C6 assumes the wrong 5/30/2022 date leading to the wrong 1.478% YTM that Google Sheets' YIELD function returns. [3]

Code: Select all

Row               Col A        Col B       Col C   Formula in Column B copied to Column C
  1          Face value          100
  2          Settlement    5/12/2022
  3             Matures   11/30/2022
  4              Coupon       0.125%
  5  Prev interest date   11/30/2021  11/30/2021
  6  Next interest date    5/31/2022   5/30/2022
  7      Days in period          182         181  =B6-B5
  8  Days before settle          163         163  =$B2-B5
  9   Days after settle           19          18  =B6-$B2
 10    Accrued interest      0.05598     0.05628  =$B1*($B4/2)*(B8/B7)
 11               Yield       1.472%      1.478%  <===
 12         Clean price     99.26171    99.26171  =$B1*((1+$B4/2)/(1+B11/2)+$B4/2)/(1+B11/2)^(B9/B7)-B10
  1. This is the value returned in Excel by the YIELD function:
    1.472% = YIELD(DATE(2022, 5, 12), DATE(2022,11,30), 0.125%, 99.26171, 100, 2, 1)
  2. See the Tue Jun 07, 2022 8:50 pm post in the mother thread for a discussion of problems Excel and Google Sheets COUPxxx functions have when the bond matures on the last day of a month. With a 11/30 maturity date Excel's COUPNCD function returns the correct 5/31/2022 next payment date even though its COUPDAYS functions returns the incorrect 181 days.
  3. I used Excel's Goal Seek tool to back into the yields in B11 and C11 that produce the actual 99.26171 clean price net of accrued interest. To six percentage places, the backed-in values agree with the results of the YIELD function:
    B11: 1.472321% in Excel
    C11: 1.478405% in Google Sheets
User avatar
Electron
Posts: 2658
Joined: Sat Mar 10, 2007 7:46 pm

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by Electron »

#Cruncher wrote: Thu Jun 30, 2022 10:23 pm I see the same thing, Electron. 1.472% is the correct yield-to-maturity (YTM). [1] I believe Google Sheets returns the wrong value because it thinks the next coupon payment after the settlement is 5/30/2022, not the correct 5/31/2022. I say this because that is what the COUPNCD function returns in Google Sheets: [2] 5/30/2022 = COUPNCD(DATE(2022, 5, 12), DATE(2022,11,30), 2, 1)
Thanks once again for your analysis! I just checked a number of additional maturity dates. The same problem occurs when there are 28, 29, or 30 days in the month.

Code: Select all

11/30/22  2/28/23  4/30/23  6/30/23  9/30/23  11/30/23  2/29/24
It's disappointing that we have a number of problems with Google Sheets along with the ACCRINT and COUPDAYS problems in Excel. Libre Offfice also has some differences.

I suspect that very few people are aware of these problems. If the companies receive nothing more than a very occasional complaint, they are probably not motivated to make any changes. It would also be a major task for someone to fully understand the various financial functions and make the necessary changes.

In the meantime, it looks like users of these functions will need to be aware of the differences.

Thanks for mentioning the Goal Seek feature in Excel. :happy That looks like a great feature. I found the What-If Analysis under the Data tab in Excel 2019.
Enjoying the Outdoors
User avatar
Electron
Posts: 2658
Joined: Sat Mar 10, 2007 7:46 pm

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by Electron »

#Cruncher wrote: Thu Jun 30, 2022 10:23 pm I used Excel's Goal Seek tool to back into the yields in B11 and C11 that produce the actual 99.26171 clean price net of accrued interest.
I tried the Goal Seek feature in Excel and came up with the same exact result. The Excel PRICE function was used and Goal Seek found the yield that produced the known price.

The spreadsheet includes a lot of other information and was later stored on Google Drive for access by my smart phone. I happened to look at the PRICE function and the price value was different in Google Sheets!

It looks like the month end problem extends to additional financial functions in Google Sheets. In this case, the price in Excel was 99.26171 and the price in Google Sheets was 99.26501. The yield was 1.472321%. The maturity date was 11-30-22 and the settlement date was 5-12-22.

This discussion reminds me that I used the Solver many years ago in Excel 5. I don't remember the details but assume it had the same capabilities as the Goal Seek tool.
Enjoying the Outdoors
User avatar
Topic Author
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by #Cruncher »

Electron wrote: Mon Jul 04, 2022 3:58 pmIt looks like the month end problem extends to additional financial functions in Google Sheets. In this case, the price in Excel was 99.26171 and the price in Google Sheets was 99.26501. The yield was 1.472321%. The maturity date was 11-30-22 and the settlement date was 5-12-22.
I'd be surprised if this were not the case, Electron. PRICE and YIELD are complementary functions. Each takes the result of the other as a parameter. And since we know that the Excel and Google Sheets versions of YIELD return different values for the same price (see your post and note 3 in my post), we'd expect their versions of PRICE to return different values for the same yield.

But if we feed each version of PRICE the value returned by its YIELD function, we get the same price:
99.26171 = PRICE(DATE(2022,5,12), DATE(2022,11,30), 0.125%, 1.472321%, 100, 2, 1) - Excel
99.26171 = PRICE(DATE(2022,5,12), DATE(2022,11,30), 0.125%, 1.478405%, 100, 2, 1) - Google Sheets

Electron, in same post, wrote:This discussion reminds me that I used the Solver many years ago in Excel 5. I don't remember the details but assume it had the same capabilities as the Goal Seek tool.
I've never used the Solver tool in Excel. But it appears to be much more complicated than Goal Seek. Here is how I used the latter to back into the yield in cell B11 of the table from this post: [*]

Code: Select all

Set cell          B12
To value          99.26171
By changing cell  B11
* Actually, to improve accuracy I created a helper cell B13 equal to 1,000 X the value in B12 and used goal seek thus:

Code: Select all

Set cell          B13
To value          99261.71
By changing cell  B11
MisterMister
Posts: 527
Joined: Thu Nov 01, 2018 9:50 pm

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by MisterMister »

deleted
Last edited by MisterMister on Sun Jul 17, 2022 8:50 pm, edited 2 times in total.
MisterMister
Posts: 527
Joined: Thu Nov 01, 2018 9:50 pm

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by MisterMister »

#Cruncher wrote: Sun Jun 19, 2022 4:03 pm
Electron wrote: Fri Jun 17, 2022 7:25 pmThe spreadsheet included three test cases for the ACCRINT function using different
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.
Libre Office's result = Excel's result * 368 / 365. With some other sets of dates, Libre's result = Excel's result * 366 / 365 (there may be other results as well, I've not tested extensively). Though they have COUPPCD and COUPNCD correct for the end-of-month cases, ACCRINT still behaves oddly. It's safe to say ACCRINT is broken in Libre Office. This leaves us with a very long formula indeed if we are doing accrued interest without the benefit of helper columns. Some Excel aficionados might prefer to implement the function in VBA, but that's another rabbit hole altogether.
Last edited by MisterMister on Sun Jul 17, 2022 8:54 pm, edited 1 time in total.
User avatar
Electron
Posts: 2658
Joined: Sat Mar 10, 2007 7:46 pm

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by Electron »

MisterMister wrote: Fri Jul 15, 2022 8:33 am Libre Office's result = Excel's result * 368 / 365. With some other sets of dates, Libre's result = Excel's result * 366 / 365 (there may be other results as well, I've not tested extensively). Though they have COUPPCD and COUPNCD correct for the end-of-month cases, ACCRINT still behaves oddly. It's safe to say ACCRINT is broken in Libre Office. For our purposes, it's also broken in Excel since it can't handle Coupon Date = Settlement Date. This leaves us with a very long formula indeed if we are doing accrued interest without the benefit of helper columns. Some Excel aficionados might prefer to implement the function in VBA, but that's another rabbit hole altogether.
Thanks for the updates.

I was not able to duplicate your results with the ACCRINT function using Excel 2019. However, with three dates specified our cases might not have been exactly the same. I do see the #NUM! result if the date combination is invalid.

This also brings up the question on whether all versions of Excel work the same including cloud based Office 365.

I have tested at least two of the long equations for accrued interest posted by #Cruncher. Entry of the equations took a little effort but fortunately that only needed to be done once. I was able to copy the block of cells containing the equation and function parameters to other locations in the worksheet without problem.
Enjoying the Outdoors
MisterMister
Posts: 527
Joined: Thu Nov 01, 2018 9:50 pm

Re: Treasury Accrued Interest - ACCRINT & COUPDAYS Functions

Post by MisterMister »

Electron wrote: Sun Jul 17, 2022 6:18 pm
MisterMister wrote: Fri Jul 15, 2022 8:33 am Libre Office's result = Excel's result * 368 / 365. With some other sets of dates, Libre's result = Excel's result * 366 / 365 (there may be other results as well, I've not tested extensively). Though they have COUPPCD and COUPNCD correct for the end-of-month cases, ACCRINT still behaves oddly. It's safe to say ACCRINT is broken in Libre Office. This leaves us with a very long formula indeed if we are doing accrued interest without the benefit of helper columns. Some Excel aficionados might prefer to implement the function in VBA, but that's another rabbit hole altogether.
Thanks for the updates.

I was not able to duplicate your results with the ACCRINT function using Excel 2019. However, with three dates specified our cases might not have been exactly the same. I do see the #NUM! result if the date combination is invalid.
(1) ACCRINT("4/30/2022","10/31/2022","5/2/2022",2.5%,20000,2,1) = 2.717391304 in Excel, ACCRINT("4/30/2022","10/31/2022","5/2/2022",2.5%,20000,2,1) = 2.739726027 in Libre Calc. 2.739726027 = 2.717391304 (368/365). I suspect Libre Calc is calculating the number of days in the period to be 365/2 rather than the actual number of days in the period.

(2) Yes, the Excel #NUM! result should not occur AS LONG AS the dates are input as #Cruncher points out. I've edited my post and removed some comments about the #NUM! which may have been misleading.
Electron wrote: Sun Jul 17, 2022 6:18 pm This also brings up the question on whether all versions of Excel work the same including cloud based Office 365.

I have tested at least two of the long equations for accrued interest posted by #Cruncher. Entry of the equations took a little effort but fortunately that only needed to be done once. I was able to copy the block of cells containing the equation and function parameters to other locations in the worksheet without problem.
Yes, I am using one of #Cruncher's formulas as well. If you avoid the one with ACCRINT, it will likely work in all spreadsheet programs including Libre Calc and ZOHO Sheet (which currently has the same ACCRINT bug as Libre Calc).
Post Reply