Since this TIPS has only a 1/8% coupon, it will be auctioned at a big discount. For anyone planning to participate, the table below gives an idea of how much you might pay depending on the yield determined by the auction. Rows 13 to 25 illustrate that $10,000 face value would have a total cost (including inflation indexing and accrued interest) ranging from $9,145 down to $7,698 for yields from 0.65% up to 1.25%.
Code: Select all
Row Col A Col B Formula in Column B
1 Face value 10,000
2 Index ratio 1.06397
3 Issued 8/31/2022
4 Matures 2/15/2052
5 Coupon 0.125%
6 Previous interest date 8/15/2022 =COUPPCD(B3,B4,2,1)
7 Next interest date 2/15/2023 =COUPNCD(B3,B4,2,1)
8 Days in partial period 184 =B7-B6
9 Days after issue date 168 =B7-B3
10 Number full periods 58 =2*(YEAR(B4)-YEAR(B7))+(MONTH(B4)-MONTH(B7))/6
11 Cost Incl
12 Yield to maturity Accrued Int
Code: Select all
13 0.650% 9,145 =B$1*B$2*(-PV(A13/2,B$10,B$5/2,1,0)+B$5/2)/(1+(A13/2)*(B$9/B$8))
14 0.700% 9,014 v v v
15 0.750% 8,886
16 0.800% 8,759
17 0.850% 8,634
18 0.920% 8,462.10 =B$1*B$2*(-PV(A18/2,B$10,B$5/2,1,0)+B$5/2)/(1+(A18/2)*(B$9/B$8))
19 0.950% 8,390
20 1.000% 8,270
21 1.050% 8,152
22 1.100% 8,036
23 1.150% 7,922
24 1.200% 7,809
25 1.250% 7,698 =B$1*B$2*(-PV(A25/2,B$10,B$5/2,1,0)+B$5/2)/(1+(A25/2)*(B$9/B$8))
Edited 2:35 PM 8/18/2022 to add final results and modify row 18 above to show price calculated with actual 0.920% yield. Total cost using $84.615251 Adjusted Price per $100 and $0.05783 Adjusted Accrued Interest per $1,000 from the auction results PDF:
$8,462.10 = 10000 * (84.615251 / 100 + 0.05783 / 1000)