Calculate real YTM of TIPS bonds
Calculate real YTM of TIPS bonds
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
Re: Calculate real YTM of TIPS bonds
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.mpt follower wrote:Does anyone have an Excel calculator to calculate the real YTM?
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%
- The WSJ shows the Ask price in 32nds of a point. My table converts this to decimal. E.g., 102+15/32 = 102.46875.
- 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)
Last edited by #Cruncher on Wed Aug 05, 2015 5:06 pm, edited 1 time in total.
Re: Calculate real YTM of TIPS bonds
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.
-
- Posts: 190
- Joined: Sun Mar 17, 2019 8:34 am
Re: Calculate real YTM of TIPS bonds
.....
Last edited by TaxingAccount on Tue Mar 23, 2021 2:54 am, edited 1 time in total.
-
- Posts: 4215
- Joined: Sun May 05, 2019 11:23 am
Re: Calculate real YTM of TIPS bonds
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.TaxingAccount wrote: ↑Wed Aug 12, 2020 8:27 am My broker hasn't had any TIPS for sale for MONTHS. Is there a shortage?
Re: Calculate real YTM of TIPS bonds
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.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.