Model TIPS - Treasury Inflation Protected Securities

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Eric3232
Posts: 2
Joined: Thu Sep 15, 2016 3:54 pm

Model TIPS - Treasury Inflation Protected Securities

Post by Eric3232 » Fri Feb 17, 2017 11:31 am

Hi,
Is anyone aware of a spreadsheet available that looks at what the return for a TIPS security is over a specific period with the change in inflation and change in treasury yields as variables?

User avatar
#Cruncher
Posts: 2416
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Model TIPS - Treasury Inflation Protected Securities

Post by #Cruncher » Fri Feb 17, 2017 8:37 pm

You can just enter the cash flows and their dates onto a spreadsheet and use the Excel XIRR function. For example here they are for $1,000 face value of the 0.5% 5-Year TIPS due April 15, 2015 [1] purchased at the initial auction and held until maturity:

Code: Select all

       Col A       Col B
Row     Date     Cash Flow
 2   04/30/2010   (997.88)
 3   10/15/2010      2.52
 4   04/15/2011      2.55
 5   10/15/2011      2.61
 6   04/15/2012      2.62
 7   10/15/2012      2.65
 8   04/15/2013      2.67
 9   10/15/2013      2.70
10   04/15/2014      2.70
11   10/15/2014      2.75
12   04/15/2015  1,083.31
The following formula shows the return to be 2.139% [1]:

Code: Select all

2.139% =XIRR(B2:B12, A2:A12)

You can approximate the return by ignoring the actual semi-annual CPI-adjusted interest payments and just combining the calculated real yield with the overall increase in the CPI from the purchase to the sale date. For example, for the purchase and redemption above, this method produces an annual return of 2.133% instead of the 2.139% produced by XIRR:

Code: Select all

Row     Col A                    Col B
 1  Purchase date              04/30/2010
 2  Sale date                  04/15/2015
 3  Coupon                         0.500%
 4  Purchase price              99.755677
 5  Sale price                 100.000000
 6  Purchase Ref CPI            216.73920 [2]
 7  Sale Ref CPI                234.18067 [2]
 8  Real yield                     0.550%
 9  Average annual CPI increase    1.573%
10  Actual nominal return          2.133%
To use this method first Select All, Copy, and Paste the following into cell A1 of a blank Excel sheet:

Code: Select all

Purchase date
Sale date
Coupon
Purchase price
Sale price
Purchase Ref CPI
Sale Ref CPI
Real yield
Average annual CPI increase
Actual nominal return
Then Select All, Copy, and Paste the following at cell B1:

Code: Select all

40298
42109
0.005
99.755677
100
216.7392
234.18067
=YIELD(B1,B2,B3,B4,B5,2,1)
=(B7/B6)^(1/((B2-B1)/365.25))-1
=(1+B8/2)^2*(1+B9)-1
Then format B1 & B2 as dates and finally enter new assumptions in cells B1:B7.

  1. 2.139%, the return from XIRR, is the annual (365 day) return. 2.128%, the return shown on the web page, is the semi-annual compounded return which is the convention for Treasury bonds. They are approximately related as follows:

    Code: Select all

    2.139% =(1 + 2.128% / 2) ^ 2 - 1
  2. From 2010 and 2015 yearly Reference CPI pages.

Post Reply