TIPS Ladder Spreadsheets in General & Two in Particular

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
User avatar
tipswatcher
Posts: 291
Joined: Tue Jun 21, 2011 5:17 pm
Contact:

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by tipswatcher » Sun Sep 27, 2015 8:36 pm

Whatever #Cruncher says, I listen. They guy is nearly always right, but in this case he found some sort of little error in his theory?

Doesn't matter to me. Also this entire spreadsheet thing doesn't matter to me.

I just buy TIPS at auction a couple times a year, when the price (yield to maturity, actually) looks attractive. Then I hold those TIPS to maturity.

This is my simple strategy. I refuse to make it more complicated. A little error here and there isn't going to matter. Really? You think it will matter? You are right, it will matter, but not to me.

#Cruncher, however, always deserves attention. Listen to this guy.
TIPS: Perfect investment for imperfect times?

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

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by #Cruncher » Mon Sep 28, 2015 12:06 pm

Kevin M in [url=https://www.bogleheads.org/forum/viewtopic.php?p=2637462#p2637462]this post[/url] wrote:I loaded #Cruncher's TIPS spreadsheet into Google Sheets, and ... tweaked it to automate loading the WSJ TIPS quotes.
Nice work, Kevin! I tested your code with Google Docs and it's slick. But I think I'll stick with just an Excel version. I've done it so many times, I can open the WSJ web page, copy, and paste in about a minute. (Besides I suffer from the old dogs and new tricks syndrome. :? ) So, if you wish to publish your Google Sheets version, that is OK with me.
Kevin M in same post wrote:Since I understand from this thread that the sheet requires non-trivial updating to add new TIPS issues, the steps to automate WSJ quotes in the Google Sheets version will have to be redone each time #Cruncher updates the spreadsheet with new TIPS.
You may be able to delete my "WSJ" sheet and substitute your modified one. In my next update, I'll also put cells with the two Reference CPI values on the WSJ sheet to make this substitution easier. Or maybe I'll try to automate the update process. Does Google Sheets handle Excel Visual Basic macros, Kevin?
Kevin M in same post wrote:If something other than the latest WSJ quotes are desired, the value in sheet WSJ, cell C2 can be modified to point to the URL for the historical table instead.
If the desired date if in cell A1, the following will produce a link to the page:

Code: Select all

="http://wsj.com/mdc/public/page/2_3020-tips-"&text(year(A1),"0000")&text(month(A1),"00")&text(day(A1),"00")&".html?mod=mdc_pastcalendar"
I've played around a lot with the WSJ quotes for different dates and the "YYYYMMDD" is the only part of the URL that changes.

User avatar
Kevin M
Posts: 10408
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Kevin M » Mon Sep 28, 2015 6:31 pm

#Cruncher wrote:But I think I'll stick with just an Excel version
Oh well, it was worth a shot ;-)
So, if you wish to publish your Google Sheets version, that is OK with me.
I can share the link if anyone requests it, but I figured providing the modification instructions was good for now, since I assumed you'd be maintaining the Excel version. Maybe if there's any demand for the Google Sheets version, I can coordinate with you on maintaining the GS version in sync with your Excel master.
You may be able to delete my "WSJ" sheet and substitute your modified one. In my next update, I'll also put cells with the two Reference CPI values on the WSJ sheet to make this substitution easier.
Good ideas!

This brings to mind a question: when would one not want to use the most recent WSJ quotes? And a related question: when would one want to use a different date for the "base date"? I can see doing either of these for illustration purposes, and I guess for actually building a ladder, one might want to use a base date a day or two in the future?
Does Google Sheets handle Excel Visual Basic macros, Kevin?
Not as far as I know. The scripting solution for GS is an integrated JavaScript component.

The lack of any Excel macros in your spreadsheet is one reason it is trivial to "port" to Google Sheets and use as is. Another is that it is not some huge, monstrous thing that is too big for GS to handle. And of course the Excel->GS compatibility of all functions used is key.
#Cruncher wrote:
Kevin M in same post wrote:If something other than the latest WSJ quotes are desired, the value in sheet WSJ, cell C2 can be modified to point to the URL for the historical table instead.
If the desired date if in cell A1, the following will produce a link to the page:

Code: Select all

="http://wsj.com/mdc/public/page/2_3020-tips-"&text(year(A1),"0000")&text(month(A1),"00")&text(day(A1),"00")&".html?mod=mdc_pastcalendar"
I've played around a lot with the WSJ quotes for different dates and the "YYYYMMDD" is the only part of the URL that changes.
The solution I've used previously for nominal Treasury quotes is similar. With date in cell A2:

Code: Select all

=concatenate("http://online.wsj.com/mdc/public/page/2_3020-treasury-",text(A2,"yyyymmdd"),".html?mod=mdc_pastcalendar")
For the benefit of others, note that the only difference in the URLs for nominal Treasuries and TIPS is the part that is either "treasury" or "tips".

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
Kevin M
Posts: 10408
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Kevin M » Mon Sep 28, 2015 7:11 pm

#Cruncher wrote:In my next update, I'll also put cells with the two Reference CPI values on the WSJ sheet to make this substitution easier
Here's another thought, and this would be close to what I've done (without the automatic importing).

Create a new sheet called Ref-CPI, and instruct users to copy/paste the Ref CPI table from your web page into the specified cells of this sheet; this is maybe even easier than looking up and entering two numbers. Name the range RefCPI. Enter these function calls into cells G2 and K2 in the Ladder sheet:

Code: Select all

=VLOOKUP(DAY(G1),RefCPI,MONTH(G1)+1,1)

Code: Select all

=VLOOKUP(DAY(K1),RefCPI,MONTH(K1)+1,1)
Then the only GS modification for this part would be to replace the Ref-CPI sheet, or alternatively to just clear the sheet and enter the IMPORTHTML function call into the sheet.

Or, just update the Ref-CPI sheet in your spreadsheet whenever you update your Ref CPI web page, and no one will have to do any updates at all to this part.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

GulfShoresBound
Posts: 7
Joined: Thu Jan 10, 2019 8:15 pm

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by GulfShoresBound » Fri Jan 11, 2019 7:15 pm

#Cruncher - Thanks so much for all the hard work you put into creating the TIPS Ladder Builder. It really makes assembling a ladder very straightforward and easy.

I'm planning a TIPS ladder as an LMP, with maturities starting in 2024 and running through 2049, and have a number of specific questions that I'm hoping you and/or the others on this thread (or on board generally) could help answer or provide a little guidance. My questions are:

1. For the 2029 slot on the ladder, would you recommend filling that slot with the new 10 yr TIPS that is scheduled to be auctioned next week (purchased at auction), rather than with one of the existing maturities (1/15/29 - 2.5% coupon or 4/15/29 - 3.875% coupon), which would have to be purchased on the secondary market?

2. Since I also need to fill the 2030 and 2031 slots with one of these three possible maturities, any preference for those years as well vis a vis the new 10 yr TIPS purchased at auction next week?

3. If the answer is to use the new 10 yr TIPS purchased from next week's auction for one or more of years 2029, 2030 or 2031, what would be the best way to adjust the Ladder Builder spreadsheet to have it take that new 10 yr TIPS into account (once the specifics of the new 10 yr TIPS are known)?

4. Before the specifics are known, is there a way to adjust the spreadsheet now- based on a reasonable guess at the final auction price and coupon (Fidelity shows an expected coupon of 0.92%, so I'm guessing that's a pretty good indication of what to expect) - so that I would be able to know how many of the new 10 yr TIPS to purchase for my ladder?

5. To fill the 2049 slot, I plan to purchase the new 30 yr TIPS to be auctioned in February. Again, what is the best way to adjust the spreadsheet to take into account this new 30 yr TIPS once the auction closes?

6. Is there a way to adjust the spreadsheet now - again based on a reasonable guess at the final auction coupon and price of the new 30 yr TIPS - so that I can determine how it will affect my purchases of the earlier rungs/years of the ladder? For my trial runs, I just doubled the number of the 2048 TIPs to approximate my purchase of the new 30 yr TIPs next month. Is that the only/best way for now?

7. With regard to those particular years where multiple TIPS issues are maturing, how do you recommend one select the particular TIPS for a ladder? For my trial runs, I've simply selected the TIPS with the highest YTM (e.g., for 2028 I selected the 4/15/28 - 3.625% coupon since it has the highest YTM out of the 4 possibilities for that year). Is that the best approach?

I'm not super fluent with Excel so am not sure how to make some of these adjustments to the spreadsheet. Simple adjustments I can handle - but more complex ones have me a bit stumped - particularly for those years where, out of necessity of course, you have multiple years covered with one TIPS maturity on one line of the spreadsheet. I'm not exactly sure how to break those apart to address the new 10 yr TIPS that will be available next week.

Thanks in advance for all of your help!

User avatar
Artsdoctor
Posts: 3691
Joined: Thu Jun 28, 2012 3:09 pm
Location: Los Angeles, CA

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Artsdoctor » Sat Jan 12, 2019 3:27 pm

Gulf,

I had forgotten about this thread, but I remember when #Cruncher's spreadsheet first became available. In a relatively short period of time, I build a TIPS ladder that would take me from 65-85 in the vein of a Liability Matching Portfolio as previously described. I'm 60 and the TIPS ladder makes up about 1/3 of my entire investment portfolio, so I won't rely on it for all of my living needs, but rather the classic "floor."

I can share a couple of thoughts when you're figuring out how to do it.

I did not invest in TIPS to build my investments or to get rich, it was all about capital preservation and nothing more. I almost viewed it as a purchase, and it was expensive (rates were much lower back then). When it kicks in, I will be spending the principal and interest, and I won't be re-investing anything. It's easier to build your ladder starting at the latest date and then working backwards because the interest in the longer bonds will make up more and more of your earlier years' spending balance.

When I was faced with buying two bonds maturing in the same year, I usually bought the one with the highest yield, although the fact is that you should probably be indifferent. I did it because some bonds had such lousy coupons that is was almost like buying zeroes and I didn't know if I'd even live long enough to cash them in! But from a math perspective, there shouldn't be much difference when you've completed your portfolio.

In years when there are no TIPS maturing, I bought enough in the earlier year to tide me over. For example, the bonds maturing in 2029 will keep me until 2032. I also decided not to roll any TIPS although there are others who prefer to roll them in order to bet on getting a higher yield. Those people will probably be right in the end, but I wanted to build my ladder and get it over with.

Hope this helps and good luck.
Last edited by Artsdoctor on Sat Jan 12, 2019 8:06 pm, edited 1 time in total.

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

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by #Cruncher » Sat Jan 12, 2019 3:31 pm

GulfShoresBound wrote:
Fri Jan 11, 2019 7:15 pm
I'm planning a TIPS ladder as an LMP [Liability Matching Portfolio], with maturities starting in 2024 and running through 2049, and have a number of specific questions ...
Before tackling your questions, GSB, I set up my TIPS Ladder Builder spreadsheet to cover the years 2024 - 2049 using the latest prices from WSJ TIPS Quotes 1/11/2019. Here is an extract of what it looks like to generate $30,000 per year in Jan 1 2019 dollars:

Code: Select all

                    Ask      Adj            Prior  Multi   #                     -- Interest --   Total
Mature  Coupon     Price    Princ    Yield  Years  plier Bonds   Cost    Princ    This    Other  Revenue

Code: Select all

 2019                                                                                    11,108   11,108
 2020                                                                                    11,108   11,108
 2021                                                                                    11,108   11,108
 2022                                                                                    11,108   11,108
 2023                                                                                    11,108   11,108
Jan-24  0.625%   99.25000  1,082.28  0.781%           1    18   19,395   19,509     61   10,986   30,555
Jan-25  0.250%   96.68750  1,066.18  0.817%           1    18   18,579   19,218     24   10,938   30,180
Jan-26  0.625%   98.43750  1,062.79  0.854%           1    18   18,890   19,157     60   10,818   30,035
Jan-27  0.375%   96.06250  1,045.42  0.888%           1    18   18,110   18,844     35   10,747   29,627
Jan-28  0.500%   96.40625  1,023.77  0.916%           1    19   18,799   19,479     49   10,650   30,177
Jan-29  2.500%  115.09375  1,176.20  0.917%           3    52   71,269  [61,248]   766   27,356   89,370
Apr-32  3.375%  129.93750  1,422.70  0.962%           5    87  162,187  123,949  2,092   24,676  150,717
Feb-40  2.125%  118.50000  1,168.36  1.135%    3      4    89  124,303  104,131  4,426   10,890  119,447
Feb-41  2.125%  119.15625  1,153.15  1.142%           1    24   33,267   27,714    294    2,134   30,143
Feb-42  0.750%   91.62500  1,117.58  1.164%           1    25   25,679   27,979    105    1,924   30,008
Feb-43  0.625%   88.56250  1,098.38  1.171%           1    26   25,357   28,598     89    1,745   30,433
Feb-44  1.375%  104.37500  1,083.54  1.173%           1    26   29,572   28,212    194    1,357   29,763
Feb-45  0.750%   90.40625  1,072.40  1.178%           1    27   26,258   28,996    109    1,140   30,244
Feb-46  1.000%   95.87500  1,065.78  1.178%           1    27   27,703   28,817    144      851   29,812
Feb-47  0.875%   92.87500  1,046.14  1.174%           1    28   27,303   29,333    128      595   30,056
Feb-48  1.000%   95.81250  1,024.06  1.170%           2    58   57,143   59,479    297            59,776
                                                     --   ---  -------  -------  -----  -------  -------
Total    2024 - 2049                                 26   560  703,813  644,663  8,873  126,805  780,341
Interest 2019 - 2023                                                                     55,538   55,538
Total    2019 - 2049                                 26   560  703,813  644,663  8,873  182,343  835,879
Note firstly that the ladder generates about $11,000 per year in interest each of the years before the first TIPS matures in 2024. This means that the $704,000 cost provides a big bonus above the $30,000 per year for the 26 years beginning 2024.

Regarding the February 2049 TIPS to be issued next month, you've accounted for that pretty well by doubling the multiplier for the February 2048. Based on current yields, the 2049 will likely also have a coupon of about 1% so it will throw off the same interest to earlier years as the 2048.

Regarding the January 2029, I would probably buy at the upcoming auction on Thursday. I would buy enough for the three years 2029-2031. The ladder shows that, for each $30,000 of annual proceeds, you'd need 52 of the outstanding 2.5% Jan 2029 having $61,000 adjusted principal to cover the three years. Since the new TIPS will sell with an index ratio close to 1.0, you'd need 60 of them to provide about the same $60,000 of adjusted principal. Their coupon will be less than 2.5% so less interest will be thrown off for earlier years, but I wouldn't worry about it.

While you could jigger with my spreadsheet to add an entry for the new Jan 2029 (or modify one of the existing issues), I don't recommend it. Modifying the spreadsheet for new issues is complicated. And the additional accuracy isn't worth the effort in my opinion. After the February 2049 is issued, I'll add it and the January 2029 to the spreadsheet.
GulfShoresBound in same post wrote:With regard to those particular years where multiple TIPS issues are maturing, how do you recommend one select the particular TIPS for a ladder? For my trial runs, I've simply selected the TIPS with the highest YTM …
I would ignore the yield. There are various reasons why it may differ for TIPS maturing in the same year. Most significant is the use of the non-seasonally adjusted CPI to index TIPS principal. Other reasons are coupon size (which affects duration), size of the index ratio (which affects the likelihood of a deflation bonus at maturity), and liquidity. The market takes all these into account when pricing TIPS. Ignoring yield, I'd probably pick the issues that even out the intervals between redemptions as much as possible. (E.g., if it had already been auctioned, I would probably forswear the new 10-year Jan 2029 in favor of the April 2029 just to reduce a little the gap between the redemptions in 2029 and 2032.)

GulfShoresBound
Posts: 7
Joined: Thu Jan 10, 2019 8:15 pm

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by GulfShoresBound » Sat Jan 12, 2019 7:28 pm

Artsdoctor wrote:
Sat Jan 12, 2019 3:27 pm
Gulf,

I had forgotten about this thread, but I remember when #Cruncher's spreadsheet first became available. In a relatively short period of time, I build a TIPS ladder that would take me from 65-85 in the vein of a Liability Matching Portfolio as previously described. I'm 60 and the TIPS ladder makes up about 1/3 of my entire investment portfolio, so I won't rely on it for all of my living needs, but rather the classic "floor."

I can share a couple of thoughts when you're figuring out how to do it.

I did not invest in TIPS to build my investments or to get rich, it was all about capital preservation and nothing more. I almost viewed it as a purchase, and it was expensive (rates were much lower back then). When it kicks in, I will be spending the principal and interest, and I won't be re-investing anything. It's easier to build your ladder starting at the latest date and then working backwards because the interest in the longer bonds will make up more and more of your earlier years' spending balance.

When I was faced with buying two bonds maturing in the same year, I usually bought the one with the highest yield, although the fact is that you should probably be indifferent. I did it because some bonds had such lousy coupons that is was almost like buying zeroes and I didn't know if I'd even live long enough to cash them in! But from a math perspective, there shouldn't be much difference when you've completed your portfolio.

In years when there are TIPS maturing, I bought enough in the earlier year to tide me over. For example, the bonds maturing in 2029 will keep me until 2032. I also decided not to roll any TIPS although there are others who prefer to roll them in order to bet on getting a higher yield. Those people will probably be right in the end, but I wanted to build my ladder and get it over with.

Hope this helps and good luck.
Artsdoctor - thanks so much for your very helpful insight and advice. I am in a similar position, just a couple of years behind you, with similar goals for my TIPS ladder - to provide a floor of income to cover a portion of retirement expenses for the years 2024 - 2049. I have no plans to roll any portion of my ladder - instead I will use the proceeds from interest and maturities to fund a portion of my expenses.

GulfShoresBound
Posts: 7
Joined: Thu Jan 10, 2019 8:15 pm

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by GulfShoresBound » Sat Jan 12, 2019 7:36 pm

#Cruncher wrote:
Sat Jan 12, 2019 3:31 pm
GulfShoresBound wrote:
Fri Jan 11, 2019 7:15 pm
I'm planning a TIPS ladder as an LMP [Liability Matching Portfolio], with maturities starting in 2024 and running through 2049, and have a number of specific questions ...
Before tackling your questions, GSB, I set up my TIPS Ladder Builder spreadsheet to cover the years 2024 - 2049 using the latest prices from WSJ TIPS Quotes 1/11/2019. Here is an extract of what it looks like to generate $30,000 per year in Jan 1 2019 dollars:

Code: Select all

                    Ask      Adj            Prior  Multi   #                     -- Interest --   Total
Mature  Coupon     Price    Princ    Yield  Years  plier Bonds   Cost    Princ    This    Other  Revenue

Code: Select all

 2019                                                                                    11,108   11,108
 2020                                                                                    11,108   11,108
 2021                                                                                    11,108   11,108
 2022                                                                                    11,108   11,108
 2023                                                                                    11,108   11,108
Jan-24  0.625%   99.25000  1,082.28  0.781%           1    18   19,395   19,509     61   10,986   30,555
Jan-25  0.250%   96.68750  1,066.18  0.817%           1    18   18,579   19,218     24   10,938   30,180
Jan-26  0.625%   98.43750  1,062.79  0.854%           1    18   18,890   19,157     60   10,818   30,035
Jan-27  0.375%   96.06250  1,045.42  0.888%           1    18   18,110   18,844     35   10,747   29,627
Jan-28  0.500%   96.40625  1,023.77  0.916%           1    19   18,799   19,479     49   10,650   30,177
Jan-29  2.500%  115.09375  1,176.20  0.917%           3    52   71,269  [61,248]   766   27,356   89,370
Apr-32  3.375%  129.93750  1,422.70  0.962%           5    87  162,187  123,949  2,092   24,676  150,717
Feb-40  2.125%  118.50000  1,168.36  1.135%    3      4    89  124,303  104,131  4,426   10,890  119,447
Feb-41  2.125%  119.15625  1,153.15  1.142%           1    24   33,267   27,714    294    2,134   30,143
Feb-42  0.750%   91.62500  1,117.58  1.164%           1    25   25,679   27,979    105    1,924   30,008
Feb-43  0.625%   88.56250  1,098.38  1.171%           1    26   25,357   28,598     89    1,745   30,433
Feb-44  1.375%  104.37500  1,083.54  1.173%           1    26   29,572   28,212    194    1,357   29,763
Feb-45  0.750%   90.40625  1,072.40  1.178%           1    27   26,258   28,996    109    1,140   30,244
Feb-46  1.000%   95.87500  1,065.78  1.178%           1    27   27,703   28,817    144      851   29,812
Feb-47  0.875%   92.87500  1,046.14  1.174%           1    28   27,303   29,333    128      595   30,056
Feb-48  1.000%   95.81250  1,024.06  1.170%           2    58   57,143   59,479    297            59,776
                                                     --   ---  -------  -------  -----  -------  -------
Total    2024 - 2049                                 26   560  703,813  644,663  8,873  126,805  780,341
Interest 2019 - 2023                                                                     55,538   55,538
Total    2019 - 2049                                 26   560  703,813  644,663  8,873  182,343  835,879
Note firstly that the ladder generates about $11,000 per year in interest each of the years before the first TIPS matures in 2024. This means that the $704,000 cost provides a big bonus above the $30,000 per year for the 26 years beginning 2024.

Regarding the February 2049 TIPS to be issued next month, you've accounted for that pretty well by doubling the multiplier for the February 2048. Based on current yields, the 2049 will likely also have a coupon of about 1% so it will throw off the same interest to earlier years as the 2048.

Regarding the January 2029, I would probably buy at the upcoming auction on Thursday. I would buy enough for the three years 2029-2031. The ladder shows that, for each $30,000 of annual proceeds, you'd need 52 of the outstanding 2.5% Jan 2029 having $61,000 adjusted principal to cover the three years. Since the new TIPS will sell with an index ratio close to 1.0, you'd need 60 of them to provide about the same $60,000 of adjusted principal. Their coupon will be less than 2.5% so less interest will be thrown off for earlier years, but I wouldn't worry about it.

While you could jigger with my spreadsheet to add an entry for the new Jan 2029 (or modify one of the existing issues), I don't recommend it. Modifying the spreadsheet for new issues is complicated. And the additional accuracy isn't worth the effort in my opinion. After the February 2049 is issued, I'll add it and the January 2029 to the spreadsheet.
GulfShoresBound in same post wrote:With regard to those particular years where multiple TIPS issues are maturing, how do you recommend one select the particular TIPS for a ladder? For my trial runs, I've simply selected the TIPS with the highest YTM …
I would ignore the yield. There are various reasons why it may differ for TIPS maturing in the same year. Most significant is the use of the non-seasonally adjusted CPI to index TIPS principal. Other reasons are coupon size (which affects duration), size of the index ratio (which affects the likelihood of a deflation bonus at maturity), and liquidity. The market takes all these into account when pricing TIPS. Ignoring yield, I'd probably pick the issues that even out the intervals between redemptions as much as possible. (E.g., if it had already been auctioned, I would probably forswear the new 10-year Jan 2029 in favor of the April 2029 just to reduce a little the gap between the redemptions in 2029 and 2032.)
#Cruncher - thanks so much! This is just the help and advice I needed! :sharebeer

With regard to the interest income in 2019 - 2023, my plan is to use that income to purchase additional TIPS maturities (e.g., 2050, 2051) as they become available to push out my ladder a couple more years! :mrgreen:

Post Reply