30-Year TIPS Re-opening Auction Thursday 8/18/2022: 0.920% YTM

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:

30-Year TIPS Re-opening Auction Thursday 8/18/2022: 0.920% YTM

Post by #Cruncher »

Tomorrow the treasury will hold a re-opening auction of the 30-year Treasury Inflation Protected Security (TIPS) maturing 2/15/2052 which was first auctioned last February. Bloomberg shows this TIPS currently yielding about 0.94% in the secondary market.

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))
Formulas use the Excel COUPPCD, COUPNCD, YEAR, MONTH, and PV functions.

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)
Post Reply