Kevin M wrote: ↑Thu May 26, 2022 5:52 pm
<snip>
Ah, gotcha. You can use the spreadsheet ACCRINT function to get the accrued interest. The formula below calculates the accrued interest shown for the 7/15/2023 nominal purchased on 5/18/2022.
=ACCRINT("1/15/2022", "7/15/2022","5/18/2022", 0.125%, 10000, 2, 1).
This returns 4.25, which is indeed the accrued interest shown for the Treasury I bought on 5/18/2022.
I developed a more generic version of this. I added a column to calculate accrued interest to the right of the column where I entered the accrued interest. In the screenshot below, the formula in cell L2 is this:
=ACCRINT(D2-COUPDAYBS(D2,E2,2,1),D2+COUPDAYSNC(D2,E2,2,1),D2,F2,I2,2,1)*N2
Remember that for existing bonds that have already paid a coupon, the parameter named "issue" is the date of the previous coupon payment, and "first_payment" is the date of the next coupon payment.
Column D2 is the settlement date and column E2 is the maturity date.
The previous coupon date is settlement minus number of coupon days before settlement, the latter given by the COUPDAYBS function.
The next coupon date is settlement plus number of coupon days before the next coupon date, the latter given by the COUPDAYNC function.
As you can see, it works perfectly for the Treasuries I've bought lately.
To use this to get accrued interest for a bond you own as of any date, just use that date as the settlement date.
EDIT: To handle the T bill case, where there is no accrued interest, I actually use this (example for row 2):
=IFERROR(ACCRINT(D2-COUPDAYBS(D2,E2,2,1),D2+COUPDAYSNC(D2,E2,2,1),D2,F2,I2,2,1)*N2, 0)
I could check for a non-zero value in the Coupon column, but instead I just wrapped the ACCRINT in an IFERROR, returning 0 if there's an error (which there will be for a 0% coupon rate).
Kevin
If I make a calculation error, #Cruncher probably will let me know.