## Model TIPS - Treasury Inflation Protected Securities

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

### Model TIPS - Treasury Inflation Protected Securities

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?

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

### Re: Model TIPS - Treasury Inflation Protected Securities

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 BRow     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``

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 dateSale dateCouponPurchase priceSale pricePurchase Ref CPISale Ref CPIReal yieldAverage annual CPI increaseActual nominal return``
Then Select All, Copy, and Paste the following at cell B1:

Code: Select all

``40298421090.00599.755677100216.7392234.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.