Calculate real YTM of TIPS bonds

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Topic Author
Erwin
Posts: 1929
Joined: Fri Apr 27, 2007 11:16 pm

Calculate real YTM of TIPS bonds

Post by Erwin »

I just bought a series of TIPS in the secondary market through Fidelity. The price was slightly different from the figure showed by Fidelity in their table. Does anyone have an Excel calculator to calculate the real YTM?
Erwin
User avatar
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Calculate real YTM of TIPS bonds

Post by #Cruncher »

mpt follower wrote:Does anyone have an Excel calculator to calculate the real YTM?
Yield-to-maturity (YTM) is calculated the same for inflation-indexed bonds as for nominal bonds. You can use the Excel YIELD function. Just make sure to enter the unadjusted price and not the "price" that includes the inflation adjustment. The following table shows the Ask Price and YTMs for selected TIPS from the 8/4/2015 WSJ TIPS Quotes. [1] The Ask price reported by the WSJ is an unadjusted price. The far right column of the table shows the result of using the Excel YIELD function. You can see that it is pretty close to the figure reported by the WSJ.

Code: Select all

                                 ------ Y T M ------
  Matures    Coupon  Ask Price      WSJ      Excel
----------   ------  ---------   --------   --------
07/15/2016   2.500%  102.46875   (0.108%)   (0.116%) [2]
07/15/2017   2.625%  105.75000   (0.319%)   (0.323%)
07/15/2018   1.375%  104.68750   (0.217%)   (0.212%)
07/15/2019   1.875%  107.46875   (0.017%)   (0.018%)
07/15/2020   1.250%  105.59375    0.113%     0.115%
07/15/2021   0.625%  102.18750    0.251%     0.254%
07/15/2022   0.125%   98.56250    0.335%     0.335%
07/15/2023   0.375%   99.68750    0.413%     0.415%
07/15/2024   0.125%   96.96875    0.471%     0.472%
07/15/2025   0.375%   98.90625    0.488%     0.488%
  1. The WSJ shows the Ask price in 32nds of a point. My table converts this to decimal. E.g., 102+15/32 = 102.46875.
  2. Example of YIELD function for the 7/15/2015 7/15/2016 maturity, where 8/5/2015 is the settlement date:

    Code: Select all

    -0.116% =YIELD(DATE(2015, 8, 5), DATE(2016, 7, 15), 2.5%, 102.46875, 100, 2, 1)
Edit to fix typo with 7/15/2016 date.
Last edited by #Cruncher on Wed Aug 05, 2015 5:06 pm, edited 1 time in total.
Topic Author
Erwin
Posts: 1929
Joined: Fri Apr 27, 2007 11:16 pm

Re: Calculate real YTM of TIPS bonds

Post by Erwin »

THANK YOU!
Erwin
Hebell
Posts: 963
Joined: Wed Aug 12, 2020 1:56 am
Location: Boca Raton, FL

Re: Calculate real YTM of TIPS bonds

Post by Hebell »

You saved me so much time. I had approximated the YIELD using the RATE function with number of semiannual payments left, but I could never spot-on match what the WSJ reports. Plus I had not noticed the fine print on the 1/32 reporting on WSJ Ask Prices. So, #cruncher thanks for your help.
TaxingAccount
Posts: 190
Joined: Sun Mar 17, 2019 8:34 am

Re: Calculate real YTM of TIPS bonds

Post by TaxingAccount »

.....
Last edited by TaxingAccount on Tue Mar 23, 2021 2:54 am, edited 1 time in total.
Robot Monster
Posts: 4215
Joined: Sun May 05, 2019 11:23 am

Re: Calculate real YTM of TIPS bonds

Post by Robot Monster »

TaxingAccount wrote: Wed Aug 12, 2020 8:27 am My broker hasn't had any TIPS for sale for MONTHS. Is there a shortage?
There's a 30-Year TIPS auction being announced tomorrow (Thursday, Aug 13, 2020 for auction on the 20th) that you may want to consider.
rlangford
Posts: 63
Joined: Thu Jun 09, 2016 11:53 am

Re: Calculate real YTM of TIPS bonds

Post by rlangford »

TaxingAccount wrote: Wed Aug 12, 2020 8:27 am My broker hasn't had any TIPS for sale for MONTHS. Is there a shortage? thanks.
There is no shortage. Some brokers do not show TIPS on their bond page because they have a negative real yield. This is to "protect" investors from buying negative yielding TIPS. However, you can always call the broker's bond desk and place a trade but enquire first about the cost of placing that trade with a rep. Otherwise wait until an auction to buy TIPS.
Post Reply