Obtaining TIPS Index Ratios -- WSJ Accrued Principal

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
User avatar
Topic Author
#Cruncher
Posts: 3057
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Obtaining TIPS Index Ratios -- WSJ Accrued Principal

Post by #Cruncher »

This post has a limited audience: Holders of individual TIPS who keep track of them on a spreadsheet and are interested in knowing their inflation-adjusted principal value. The other 99.9% of you may want to proceed directly to the next thread. :happy

In order to know the inflation-adjusted value of a TIPS on a certain date you need to multiply its face value by its inflation factor or "Index Ratio" on that date. You can find these Index Ratios from the TreasuryDirect web site's TIPS/CPI Data page. You can also find them -- hopefully more easily -- from my web site, eyebonds.info/tips. Normally you could also obtain an approximate multiple of the Index Ratio from the Wall Street Journal's TIPS Quotes page. (But this hasn't been possible the past few days -- see below.)

However, if you own several individual TIPS, it is cumbersome to look up the Index Ratios separately for each one. If you have your TIPS listed on a spreadsheet, there is an easy way to determine the Index Ratios that requires entering only one number for each date that you're interested in. Here is an illustration:

Code: Select all

          Col A        Col B        Col C             Col D              Col E
       -----------  -----------  -----------    -----------------  -----------------
Row 1                     Valuation Date -->    05/01/14           06/01/14
Row 2          Ref CPI on Valuation Date -->    234.781            236.293
Row 3    Matures      Coupon     Base Ref CPI   ----------- Index Ratio ------------	
Row 4   07/15/2014    2.000%      188.49677     1.24554            1.25357 
Row 5   01/15/2015    1.625%      190.94516     1.22957            1.23749
Formulas:
Row 4                                           =ROUND(D$2/$C4,5)  =ROUND(E$2/$C4,5)
Row 5                                           =ROUND(D$2/$C5,5)  =ROUND(E$2/$C5,5)
  • Add a column [C] to your spreadsheet for the "Base Ref CPI". Fill it in with the "Ref CPI" for each TIPS you own from this web page. This number never changes.
  • Add columns [D & E] for the dates that you want to compute Index Ratios for. In one row [2] of these columns enter the "Ref CPI" for the dates. For any dates in 2014 you can use this web page.
  • In the cell [D4] for the first TIPS row of the first date column enter a formula to divide the Ref CPI for that date by the Base Ref CPI and round to 5 decimal places [=ROUND(D$2/$C4,5)].
  • If the formula above [in D4] is entered with the $ signs as shown, it can be copied down to the other TIPS rows and right to the other valuation dates.
On a related subject, I've noticed that the Accrued Principal reported on the WSJ TIPS quotes web page has been wrong since May 1st for all TIPS maturing January 2023 or later. For example, the right column below shows the value reported for May 1 2014:

Code: Select all

                                                  WSJ
                           Base        Index    Accrued
  Matures      Coupon     Ref CPI      Ratio   Principal
-----------    ------    ---------    -------  ---------
2014 Jul 15    2.000%    188.49677    1.24554     1245
2015 Jan 15    1.625%    190.94516    1.22957     1229
2015 Apr 15    0.500%    216.71220    1.08338     1083
2015 Jul 15    1.875%    194.50968    1.20704     1207
2016 Jan 15    2.000%    198.47742    1.18291     1182
2016 Apr 15    0.125%    220.72980    1.06366     1063
2016 Jul 15    2.500%    201.95161    1.16256     1162
2017 Jan 15    2.375%    201.66452    1.16422     1164
2017 Apr 15    0.125%    227.13073    1.03368     1033
2017 Jul 15    2.625%    207.25639    1.13280     1132
2018 Jan 15    1.625%    209.49645    1.12069     1120
2018 Apr 15    0.125%    231.16013    1.01566     1015
2018 Jul 15    1.375%    215.63997    1.08876     1088
2019 Jan 15    2.125%    214.69971    1.09353     1093
2019 Apr 15    0.125%    234.31967    1.00197     1001
2019 Jul 15    1.875%    213.51819    1.09958     1099
2020 Jan 15    1.375%    216.24610    1.08571     1085
2020 Jul 15    1.250%    218.08532    1.07656     1076
2021 Jan 15    1.125%    218.75255    1.07327     1073
2021 Jul 15    0.625%    225.38381    1.04169     1041
2022 Jan 15    0.125%    226.33474    1.03732     1037
2022 Jul 15    0.125%    229.96306    1.02095     1020
2023 Jan 15    0.125%    230.82203    1.01715     1007  <--
2023 Jul 15    0.375%    232.71797    1.00886     1017  <--
2024 Jan 15    0.625%    233.33058    1.00622     1008  <--
2025 Jan 15    2.375%    188.49677    1.24554     1006  <--
2026 Jan 15    2.000%    198.47742    1.18291     1245  <--
2027 Jan 15    2.375%    201.66452    1.16422     1182  <--
2028 Jan 15    1.750%    209.49645    1.12069     1164  <--
2028 Apr 15    3.625%    161.74000    1.45160     1120  <--
2029 Jan 15    2.500%    214.69971    1.09353     1451  <--
2029 Apr 15    3.875%    164.39333    1.42817     1093  <--
2032 Apr 15    3.375%    177.50000    1.32271     1428  <--
2040 Feb 15    2.125%    216.13950    1.08625     1322  <--
2041 Feb 15    2.125%    218.99100    1.07210     1086  <--
2042 Feb 15    0.750%    225.96062    1.03904     1072  <--
2043 Feb 15    0.625%    229.91100    1.02118     1039  <--
2044 Feb 15    1.375%    233.05900    1.00739     1021  <--
  • Base Ref CPI is the Reference CPI on the "Dated Date" of the TIPS which is usually the 15th of the month it is first issued from here.
  • Index Ratio is for May 1, 2014 and is calculated by dividing the Base Ref CPI into the Reference CPI of 234.781 for 5/1/2014 as shown here. For example 1.24554 = 234.781 / 188.49677.
  • WSJ Accrued Principal is from WSJ TIPS Quotes 5/1/2014
For maturities up through July 2022 you can see that the WSJ Accrued Principal is the Index Ratio multiplied by 1,000 and then truncated. But beginning with the January 2023 maturity they are incorrect. The WSJ has inadvertently used the Feb 2044 figure for Jan 2023 and shifted the subsequent figures down by one row. This error has continued at least through today, 5/6/2014.

Edit 5/14/2014: Elaborated sentence explaining the WSJ error. Same error has continued at least through May 13, 2014. I'll post to this thread when I see that the error has been fixed.
Last edited by #Cruncher on Wed May 14, 2014 6:32 am, edited 1 time in total.
User avatar
Whiggish Boffin
Posts: 274
Joined: Sun Dec 09, 2007 10:20 pm

Re: Obtaining TIPS Index Ratios -- WSJ Accrued Principal

Post by Whiggish Boffin »

Thanks. I needed that.
(I never dreamed I'd be one of the 0.1% ...)
User avatar
ThePrune
Posts: 959
Joined: Wed Nov 10, 2010 9:38 am
Location: Midland, MI

Re: Obtaining TIPS Index Ratios -- WSJ Accrued Principal

Post by ThePrune »

Although I don't track my individual TIPS quite the way you do, I appreciated your posting. Thanks.
Investment skill is often just luck in sheep's clothing.
User avatar
Doc
Posts: 9807
Joined: Sat Feb 24, 2007 1:10 pm
Location: Two left turns from Larry

Re: Obtaining TIPS Index Ratios -- WSJ Accrued Principal

Post by Doc »

For those of you that have Vanguard accounts there is another way to get there. Use Vanguard's ladder page to set up your own portfolio. The data shows unadjusted price, coupon, YTM (#cruncher method), and adjusted principle. A click on the actual note links to a detailed page which gives you the factor but that is already used in the calculation. You also get some nice graphs and tables showing cash flow, coupon payments and other things. You can also click the "get current quotes" icon if you absolutely must have the very latest data. :D

The drawback is that it may not work during non trading hours. Of course this only works for now whereas #crunchers spreadsheet works with any date.
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.
richard
Posts: 7961
Joined: Tue Feb 20, 2007 3:38 pm
Contact:

Re: Obtaining TIPS Index Ratios -- WSJ Accrued Principal

Post by richard »

I believe the WSJ has finally fixed this, at least the few issues I checked.
User avatar
Topic Author
#Cruncher
Posts: 3057
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Obtaining TIPS Index Ratios -- WSJ Accrued Principal

Post by #Cruncher »

richard wrote:I believe the WSJ has finally fixed this ...
Yes, the major problem I reported in the original post has been fixed for today, 5/27/2014, and Friday, 5/23/2014. And I got an email from Dow Jones saying it will be correcting the old pages, which are in error going back to 5/01/2014.

There is still a small error remaining for all TIPS. It looks like the Accrued principal shown for 5/27/2014 is actually based on the index ratios for two days earlier, 5/25/2014. (See TIPS Index Ratios May 15 - June 1 2014.) I've emailed Dow Jones, so hopefully this too will be fixed.

Edit 5/28/14: The small error mentioned yesterday has been fixed for today, 5/28/2014.
Edit 5/31/14: All historical pages going back to 5/01/2014 have been corrected.
User avatar
Topic Author
#Cruncher
Posts: 3057
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Obtaining TIPS Index Ratios -- WSJ Accrued Principal

Post by #Cruncher »

#Cruncher wrote: Tue May 06, 2014 10:29 pm
  • Add a column ... to your spreadsheet for the "Base Ref CPI". Fill it in with the "Ref CPI" for each TIPS you own ...
  • ... for the dates that you want to compute Index Ratios ... enter the "Ref CPI" for the dates. ...
(underlines added)
I've created a Google sheets workbook that looks up the Ref CPI for any date; so it isn't necessary to enter them manually. [1] The "CPI" sheet calculates the Reference CPI for every date from January 1 1997 to October 1 2020. By entering the August CPI-U next month when it's released [2], 31 more daily Reference CPIs will be automatically calculated for October 2 to November 1 2020.

The "Example" sheet shows how to look these up with the VLOOKUP function using the Dated Date [3] of each TIPS and the dates for which it's desired to calculate index ratios.

  1. I did this after seeing the following request by Seasonal in another thread. My spreadsheet doesn't answer his request, but it does provide a workaround.
    Seasonal wrote: Sat Aug 22, 2020 5:35 pmIs there an api for getting the index ratio directly from the Treasury?
  2. Look up the CPI-U by selecting U.S. city average, All items - CUUR0000SA0 on BLS Top Picks.
  3. A TIPS' Dated Date is the date six months before the first interest payment date. It is usually the same month and day as the maturity date. E.g., the Dated Date for the 30 year TIPS maturing February 15 2050 is February 15 2020. The index ratio for a given date equals the Reference CPI on that date divided by the Reference CPI on the Dated Date.
Seasonal
Posts: 2101
Joined: Sun May 21, 2017 1:49 pm

Re: Obtaining TIPS Index Ratios -- WSJ Accrued Principal

Post by Seasonal »

Very nice.

If you want a bit more automated way of getting the CPI-U, see https://www.bls.gov/developers/ and for Ref CPI, see https://www.treasurydirect.gov/webapis/ ... rities.htm (e.g., https://www.treasurydirect.gov/TA_WS/se ... ormat=json - the example uses a cusip that doesn't work)
Post Reply