TIPS Ladder Spreadsheets in General & Two in Particular

[tipswatcher]

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.

[#Cruncher]

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.

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?

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.

[Kevin M]

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.

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

[Kevin M]

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

[GulfShoresBound]

#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!

[Artsdoctor]

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.

[#Cruncher]

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.

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]

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]

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.

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!

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!

[Wrench]

#Cruncher - your are the master!

Here is a question that I bet you can answer, but I am having trouble figuring out. I buy a 30 year TIPS bond ladder in a tax deferred account paying out $40K/year. That costs me ~$1.08M. Will the $40K per year cover the required minimum distributions for all 30 years? It will for year one when the RMD is 3.65%. But in later years when the RMD is larger, the balance is lower from each years cashed out bond, the remaining bonds are higher value (assuming a positive inflation rate) when adjusted for inflation, but so is the actual cash payout. Given some fixed nominal inflation rate of the 30 years like 2%, does the RMD get covered every year or not? If it doesn't then I either have to cash out a bond, or cover the deficit from other tax deferred accounts. Either way works I suppose, but it would be nice to be able to plan for it ahead of time...

Thanks for your amazing posts!

[#Cruncher]

I buy a 30 year TIPS bond ladder in a tax deferred account paying out $40K/year. … Will the $40K per year cover the required minimum distributions for all 30 years?

I don't believe the amount of inflation matters. The determining factor is the real yield of the TIPS remaining in the ladder. Take a simple hypothetical case of 30 zero-coupon TIPS, one maturing in each year of the ladder. Assume for simplicity that every TIPS has the same yield. The value of the ladder will then depend on what that yield is. In turn the RMD will be a percent of that market value. For a 30-year ladder starting at age 70 the following table shows the yield that would make the RMD equal the redemption value of the TIPS maturing in the next year. (The RMD divisors are taken from IRS Table III.)

Code: Select all

Row  ColA    Col B   Col C   Col D    Col E
           ----- RMD -----   Years    = RMD
      Age  Divisor    Mult  Remain    Yield

Code: Select all

  5    70     27.4   3.65%      30    0.60% 
  6    71     26.5   3.77%      29    0.61% 
  7    72     25.6   3.91%      28    0.63% 
  8    73     24.7   4.05%      27    0.65% 
  9    74     23.8   4.20%      26    0.67% 
 10    75     22.9   4.37%      25    0.69% 
 11    76     22.0   4.55%      24    0.71% 
 12    77     21.2   4.72%      23    0.69% 
 13    78     20.3   4.93%      22    0.71% 
 14    79     19.5   5.13%      21    0.68% 
 15    80     18.7   5.35%      20    0.65% <--
 16    81     17.9   5.59%      19    0.60% 
 17    82     17.1   5.85%      18    0.55% 
 18    83     16.3   6.13%      17    0.47% 
 19    84     15.5   6.45%      16    0.38% 
 20    85     14.8   6.76%      15    0.17% 
 21    86     14.1   7.09%      14   (0.09%)
 22    87     13.4   7.46%      13   (0.43%)
 23    88     12.7   7.87%      12   (0.86%)
 24    89     12.0   8.33%      11   (1.42%)
 25    90     11.4   8.77%      10   (2.31%)
 26    91     10.8   9.26%       9   (3.50%)
 27    92     10.2   9.80%       8   (5.10%)
 28    93      9.6  10.42%       7   (7.33%)
 29    94      9.1  10.99%       6  (10.74%)
 30    95      8.6  11.63%       5  (15.72%)
 31    96      8.1  12.35%       4  (23.27%)
 32    97      7.6  13.16%       3  (35.20%)
 33    98      7.1  14.08%       2  (54.77%)
 34    99      6.7  14.93%       1  (85.07%)

For example, at age 80 if the TIPS real yield is more than 0.65%, the next ladder rung will exceed the 5.35% RMD. However, if it is less than 0.65%, the rung will be less than the RMD, and more money must be withdrawn from a tax deferred account to meet it. For instance with each rung being $40,000:

Code: Select all

 TIPS    Market   $40K
Yield     Value    Pct
-----   -------   -----
0.60%   751,743   5.32%  Rung shy of RMD [1]
0.65%   747,908   5.35%  Rung equals RMD
0.70%   744,101   5.38%  Rung exceeds RMD

For those wishing to do this calculation for ladders with other than 30 year length or beginning at an age other than 70, follow these steps:

• Select All, Copy, and Paste [2] the following at cell A1 of a blank Excel sheet:

  Code: Select all

  Ladder years	30
  Age ladder starts	70
  
  Age	Divisor	Mult	Remain	Yield
  70	27.4	=1/B5	=MAX(0,IF(AND(A5=A$5,A$5>B$2),B$1-(A$5-B$2),IF(A5<B$2,0,IF(A5=B$2,B$1,D4-1))))	=IF(D5<=0,0,RATE(D5,C5,-1,0,0))
  71	26.5
  72	25.6
  73	24.7
  74	23.8
  75	22.9
  76	22
  77	21.2
  78	20.3
  79	19.5
  80	18.7
  81	17.9
  82	17.1
  83	16.3
  84	15.5
  85	14.8
  86	14.1
  87	13.4
  88	12.7
  89	12
  90	11.4
  91	10.8
  92	10.2
  93	9.6
  94	9.1
  95	8.6
  96	8.1
  97	7.6
  98	7.1
  99	6.7
  100	6.3
  101	5.9
  102	5.5
  103	5.2
  104	4.9
  105	4.5
  106	4.2
  107	3.9
  108	3.7
  109	3.4
  110	3.1
  111	2.9
  112	2.6
  113	2.4
  114	2.1
  115	1.9

• Format for readability.
• Copy cells C5:E5 down to the row for age 115.
• Revise assumptions in cells B1 & B2 as desired.

By the way, I've just updated my Ladder Builder spreadsheet. It now handles cases where the last year of the desired ladder ends in one of the "gap" years when no TIPS mature.

1. Market value calculated with Excel PV function:
   751,743 = -PV(0.60%, 20, 40000, 0, 0)
2. If you have trouble pasting, try "Paste Special" and "Text".

[garlandwhizzer]

A rolling TIPS ladder is a bullet proof way to fund a given amount of fixed real income in retirement if you have the massive asset base that allows you to reduce risk to zero by accepting an absurdly low rate of real return for 30 or more years. Most of us either lack that massive asset base. Others are not so risk averse that they are willing to accept such a low guaranteed rate of return in order to remove 100% of risk from our plans. Whatever plan we choose it is important unless we have substantial existing medical problems to plan to live well into our 90s in retirement. Increasing longevity is an appropriate input in formulating retirement plans IMO.

Garland Whizzer

[Bongleur]

#Cruncher -- why is "Mult" equal to 1/B5. Did you mean eighty five insead of Bravo?

[Bongleur]

#Cruncher -- does your existing spreadsheet give the data that needs to be compared to Col E "$40k RMD %" for each year?

[Wrench]

Thanks #Cruncher

It makes sense that inflation rate does not matter but I was having trouble wrapping my head around the RMD changes. Your solution is elegant and simple!

[#Cruncher]

A rolling TIPS ladder is a bullet proof way to fund a given amount of fixed real income in retirement … (underline added)

Only a non rolling ladder can do this. A rolling ladder would be subject to interest rate risk and wouldn't be "bullet proof".

#Cruncher -- why is "Mult" equal to 1/B5. Did you mean eighty five insead of Bravo?

Cell B5 has 27.4, the age 70 RMD divisor. Cell C5 simply takes the reciprocal of this (1 / B5) to get the 3.65% to multiply against the retirement account balance to determine the RMD.

#Cruncher -- does your existing spreadsheet give the data that needs to be compared to Col E "$40k RMD %" for each year?

I'm not sure what you mean here, Bongleur. Col E shows the yield on the TIPS ladder such that the RMD equals the amount for one rung of the ladder. The calculation doesn't depend on the size of the ladder; only on the RMD divisor and the number of years left in the ladder.

But if it helps to see a concrete case, the following shows the RMD for the case where each rung is $40,000. It is for age 80 in the table from my previous post when 20 years remain in the ladder. You can see that if the ladder yields 0.65%, the $40,000 rung equals the RMD. But if the yield were only 0.60%, the market value of the ladder would increase making the RMD $218 more than the $40,000 rung.

Code: Select all

 TIPS    Market   RMD @
Yield     Value   5.35%
-----   -------   ------
0.60%   751,743   40,218   Rung shy of RMD [*]
0.65%   747,908   40,013   Rung equals RMD
0.70%   744,101   39,809   Rung exceeds RMD

* Market value calculated with Excel PV function:
751,743 = -PV(0.60%, 20, 40000, 0, 0)

[Stormbringer]

By the way, I've just updated my Ladder Builder spreadsheet.

This spreadsheet is great. I'm curious what you've observed in terms of interest rate sensitivity over the years, and how it affects the cost of the ladder.

For example, using the latest spreadsheet a $40K annual income would cost $1,068,354. If nominal rates were higher, would the cost of the ladder come down much? Or would that require real rates to increase? And if so, would the cost difference be significant?

I know that annuities have had a fairly lousy payout rate for the past decade because of low rates, so I assume a TIPS ladder would suffer from the same problem.

[Bongleur]

Bongleur wrote: ↑
Sun May 05, 2019 4:00 pm
#Cruncher -- does your existing spreadsheet give the data that needs to be compared to Col E "$40k RMD %" for each year?
>
#Cruncher said:
I'm not sure what you mean here, Bongleur. Col E shows the yield on the TIPS ladder such that the RMD equals the amount for one rung of the ladder. The calculation doesn't depend on the size of the ladder; only on the RMD divisor and the number of years left in the ladder
>>
The sheet you just created shows the interest rate you need to have for each year given a yearly start value etc.
So when you are preparing to purchase all these TIPs, you need to see IF they will generate at least that percent, each year; and find out IF they will have the required start value every year. So is that what your original ladder spreadsheet will show? Seems like the calc you did could be incorporated into the "main" TIPS spreadsheet as another column.

[Wrench]

Here's another way to look at it. Assume you buy the ladder in #Cruncher's latest spreadsheet spending $801,460 to get $30,000 per year real income. If in 2020 you are 70 so you owe 3.65% of the value of your tips. That value at the end of the year is 801,460 minus the principal paid back to you (value in column V) in year one or 801,460-18,358 = $783,102. 3.65% of that is $28583. So, your $30K payout from the account covers the RMD. The next year, when you are 71, the balance is the balance from previous year, less principal outlay from year 2, or 783102-17072 = $766030. Your RMD at 71 is 1/26.5=3.773%, so the RMD is .03773*766030=$28902. Again the RMD is covered. And so forth for all years. [#Cruncher - I think I got this right - do you agree?] When I did it for my case there WERE years where the distribution did not cover the RMD. (Similar result when I used #Cruncher's approach). It depends on the age you start, the interest rates, and the number of years in the ladder. As #Cruncher pointed out, it does NOT depend on the inflation rate. With current rates, for a 30 year ladder if you start at 70, it won't be covered every year. If you start earlier, say at age 65 or 66 with a 30 year ladder, it will always be covered with current rates. That makes sense because the principal is decreasing for the first 4 or 5 years before you even have an RMD. If the ladder is shorter, more years tend to be covered too, even if you start at age 70.
Bottom line - you have to be aware of this issue and be ready to either (1) cover any shortfall in RMD from other tax deferred accounts; (2) reduce the amount of TIPs you buy in the account where you are buying them and hold some cash or other liquid investment so you can cover the RMD in years when the TIPS income does not meet the RMD; or (3) be ready and willing to sell one or more bonds when needed to cover the RMD.
If this is too much of a PITA, consider a single premium immediate annuity (SPIA) with a CPI adjustment. When I got a quote (The Principal sells them), the payout on a joint life annuity with CIP is actually higher than the TIPs ladder because of mortality credits, at least for a 70 year old male/69 year old female. But of course the risk is also higher because it is backed by an insurance company, not the US government. There's no such thing as a free lunch On the other hand, the SPIA is for a lifetime, so if you expect to (or want to plan for a case where you) live to beyond the life of a 30 year TIPs ladder the SPIA wins as long as the insurance company remains solvent.
I have no idea how RMD works for those CPI indexed SPIAs - I am currently exploring that question with The Principal. I do know for a QLAC that the payout is always considered to cover the RMD of the initial cost. But, I don't know how it works for an SPIA held in a tax deferred account. My guess is the insurance company will give you the numbers and you will have to meet them. But, I am on a quest to find out ...

Wrench

[#Cruncher]

#Cruncher -- does your existing spreadsheet give the data that needs to be compared to Col E "$40k RMD %" for each year?

Now I understand your question, Bongleur. By "existing spreadsheet" you mean my ladder builder spreadsheet. Yes, it does have the "data" I believe you're referring to. For example the latest default ladder using WSJ TIPS Quotes 5/3/2019 shows the following cash flows 2020-2049 (in hidden cells AE50:BH50 of the Ladder sheet). The spreadsheet uses these figures along with the $801,460 cost to calculate a 0.783% Internal Rate of Return (in cell M51).

Code: Select all

2020    30,400
2021    29,092
2022    30,715
2023    30,255
2024    29,875
2025    29,910
2026    29,648
2027    30,299
2028    29,755
2029    71,825
2030     9,101
2031     9,101
2032   129,558
2033     4,966
2034     4,966
2035     4,966
2036     4,966
2037    30,172
2038    30,172
2039    30,172
2040    30,172
2041    30,148
2042    30,013
2043    29,342
2044    30,853
2045    29,172
2046    30,880
2047    29,008
2048    30,190
2049    30,300
       -------
Total  899,990

I'm curious what you've observed in terms of interest rate sensitivity over the years, and how it affects the cost of the ladder.

Here are the costs of a 30-year $30,000 per year ladder from some historical copies of my ladder builder spreadsheet. The greatest range in cost occurred during the first year after I developed the spreadsheet: from $696,000 in April 2011 to $846,000 in April 2012. During that year the yield on the longest TIPS fell about 1% point from 1.8% to 0.8%. (Long term TIPS are disproportionally weighted in the ladder.) The weighted average yield-to-maturity (YTM) of all the TIPS used in the ladder also fell about 1% point during that time. (Starting last year I've added an Internal Rate of Return calculation to the spreadsheet which gives a better measure of the ladder's overall yield.)

Code: Select all

           - Longest TIPS -   Wtd Avg
 Prices    Matures     YTM      YTM      IRR      Cost
04/01/11   2/15/41   1.833%   1.167%            695,960 
04/06/12   2/15/42   0.829%   0.176%            846,074
09/20/13   2/15/43   1.410%   0.769%            772,693
02/14/14   2/15/44   1.420%   0.785%            769,977 
02/20/15   2/15/45   0.810%   0.553%            811,801
02/19/16   2/15/46   1.090%   0.774%            787,948 
02/17/17   2/15/47   0.900%   0.550%            813,478 
02/16/18   2/15/48   1.000%   0.834%            793,785 
02/22/19   2/15/49   1.051%   0.876%            787,372 
09/02/19   2/15/49   0.888%   0.848%   0.745%   806,314 
05/03/19   2/15/49   0.971%   0.778%   0.783%   801,460

If nominal rates were higher, would the cost of the ladder come down much? Or would that require real rates to increase? And if so, would the cost difference be significant?

The cost of the ladder depends on the cost of each bond and they in turn correspond to each bond's yield, which in the case of TIPS is a real yield. The following shows that if the Internal Rate of Return (IRR) of the ladder were to fall 0.5% points, the cost would rise about $61,000 or 7.6%. If it were to rise 0.5%, the cost of the ladder would fall about $55,000 or 6.9%. (See my response to Bongleur above for the numbers used. The 0.783% yield was calculated with the Excel IRR function and the Cost for the lower and higher yields was calculated with the Excel NPV function.)

Code: Select all

  IRR     Cost
------   -------
0.283%   862,474  0.5% lower yield
0.783%   801,460  from 5/3/2019 ladder
1.283%   746,411  0.5% higher yield

The sheet you just created [in this post] shows the interest rate you need to have for each year given a yearly start value etc. So when you are preparing to purchase all these TIPs, you need to see IF they will generate at least that percent, each year; and find out IF they will have the required start value every year. So is that what your original ladder spreadsheet will show? Seems like the calc you did could be incorporated into the "main" TIPS spreadsheet as another column. (underline added)

The ladder builder spreadsheet calculates the cost of the ladder, which is its market value based on TIPS prices on a certain date. The cost for $30,000 per year for the latest default 30-year ladder using WSJ TIPS Quotes 5/3/2019 is $801,000. This can be compared against the RMD for a given age. For example, at age 70 with a divisor of 27.4, the RMD would be $31,500. At age 80 with a divisor of 18.7, it would be $42,800. So the $30,000 rung would meet the first year RMD if it starts when one is age 70; but not if it starts when one is age 80.

But it's not possible to use the ladder spreadsheet to determine the future market value of the ladder and consequent RMDs. This will depend on the future price of each TIPS in the ladder. Even if the yields were the same, the prices would not be. For example, on 5/3/2019 the 3.375% TIPS maturing April 2032 is priced at 133+14/32 with a YTM of 0.674%. But in ten years it will be priced much closer to 100 even if the yield were to remain at 0.674%. [*]

Here's another way to look at it. Assume you buy the ladder in #Cruncher's latest spreadsheet spending $801,460 to get $30,000 per year real income. If in 2020 you are 70 so you owe 3.65% of the value of your tips. That value at the end of the year is 801,460 minus the principal paid back to you (value in column V) in year one or 801,460-18,358 = $783,102. 3.65% of that is $28583. So, your $30K payout from the account covers the RMD. ... [#Cruncher - I think I got this right - do you agree?] (underline added)

No, Wrench, you can't just subtract the principal from the initial cost of the ladder to get the value of the remaining ladder at the end of 2020. As I explain in my previous response to Bongleur, that will depend on the price of each of the remaining TIPS at that time.

* Using the Excel PRICE function:
133.44 = PRICE(DATE(2019, 5, 3), DATE(2032, 4, 15), 3.375%, 0.674%, 100, 2, 1) -- price now
107.88 = PRICE(DATE(2029, 5, 3), DATE(2032, 4, 15), 3.375%, 0.674%, 100, 2, 1) -- price in 10 years

[Wrench]

Thanks, #Cruncher. You are right again! Clearly your insights and analysis related to the spreadsheet are to be trusted, and mine are not!
So because of unknown future changes in interest rates, it is not possible to know the principal value of the TIP bonds at any future date. Therefore, it is not possible to calculate the RMD at some future date. Is that the basic gist of it? If so, that's a problem. I am forced into active management of the TIPs portfolio and may have to sell some bonds at some point to meet the RMD. At 85 or 90, should I be lucky (unlucky? ) enough to live that long, will I have the mental acuity to make wise choices in how best to meet the RMD? Or will I screw up and have to pay a huge penalty? There is a reasonable probability for a mistake at advanced ages. So now I have to pay someone to look after the TIPs, making an already low yield even lower.
Sadly, the TIPs ladder is looking less and less attractive to me the more I learn...

Wrench

[Bongleur]

"So because of unknown future changes in interest rates, it is not possible to know the principal value of the TIP bonds at any future date. Therefore, it is not possible to calculate the RMD at some future date."
***
Yea, if that's true then its not useful to simply add the calculation you showed in your post of Sun May 05, 2019 1:31 pm
to the "ladder builder" spreadsheet as another "sheet" along with the answer "(in hidden cells AE50:BH50 of the Ladder sheet)" so a person can easily confirm that he won't have to sell bonds when he doesn't want to.

And give that Sun May 05, 2019 1:31 pm bit of code a name so we can talk about it by name.

Is the answer to hold some TIPS in taxable for the purpose of paying extra taxes on an unexpectedly high RMD?
Can the dataset -- a particular ladder -- be analyzed to give a clue as to how much any extra taxes might be and when? A confidence level that it won't be more than some fraction of the expected RMD? Unexpected tax coming later is less harmful, since the "reserve TIP ladder" will accrue for longer until it one needs to be sold or rolled.

[#Cruncher]

So because of unknown future changes in interest rates, it is not possible to know the principal value of the TIP bonds at any future date. Therefore, it is not possible to calculate the RMD at some future date. Is that the basic gist of it?

Change "principal value" to "market value" and this is correct.

If so, that's a problem. I am forced into active management of the TIPs portfolio and may have to sell some bonds at some point to meet the RMD. At 85 or 90, should I be lucky (unlucky? ) enough to live that long, will I have the mental acuity to make wise choices in how best to meet the RMD?

Every tax deferred account requires some management during retirement. Even without the RMD issue the gap years in a TIPS ladder (currently 2030, 2031, and 2033-2039) must be handled. A 30-year ladder starting at age 70 or later [1] and comprising all of one's tax deferred account may well require some TIPS to be liquidated before maturity in order to meet RMDs.

One possible solution is to not put all of the retirement account into the TIPS ladder. Putting some of it into a TIPS fund may avoid having to liquidate some individual TIPS to meet RMDs. The fund serves as a reservoir to hold excess principal and interest collections and to provide extra money to meet RMDs when needed.

Consider the case of the default ladder from my TIPS Ladder Builder spreadsheet using WSJ TIPS Quotes 5/3/2019. To exacerbate the problem of meeting RMDs we'll assume all the TIPS have only a 0% yield to maturity. The following table below shows that without a "reservoir" fund, the RMD exceeds the ladder collections in some years. For example the $30,400 TIPS principal and interest collected at age 70 is $2,446 short of the $32,846 RMD.

Code: Select all

Row Col C    Col D    Col E Col F   Col G   Col H   Col I   Col J    Col K
                                                           ---- Fund -----
     Year   Ladder    Value   Age   RMD %   RMD $  W'draw  Income  Balance

Code: Select all

  6  2019           899,992    69                                        0
  7  2020   30,400  869,592    70   3.65%  32,846  32,846           (2,446)
  8  2021   29,092  840,500    71   3.77%  32,722  32,722           (6,077)
  9  2022   30,715  809,785    72   3.91%  32,595  32,595           (7,957)
 10  2023   30,255  779,530    73   4.05%  32,463  32,463          (10,164)
 11  2024   29,875  749,655    74   4.20%  32,326  32,326          (12,616)
 12  2025   29,910  719,745    75   4.37%  32,185  32,185          (14,891)
 13  2026   29,648  690,097    76   4.55%  32,039  32,039          (17,281)
 14  2027   30,299  659,798    77   4.72%  31,737  31,737          (18,719)
 15  2028   29,755  630,043    78   4.93%  31,580  31,580          (20,544)
 16  2029   71,825  558,218    79   5.13%  31,256  31,256           20,024 
 17  2030    9,101  549,117    80   5.35%  30,922  30,922           (1,797)
 18  2031    9,101  540,016    81   5.59%  30,577  30,577          (23,272)
 19  2032  129,558  410,458    82   5.85%  30,219  30,219           76,067 
 20  2033    4,966  405,492    83   6.13%  29,848  29,848           51,185 
 21  2034    4,966  400,526    84   6.45%  29,463  29,463           26,688 
 22  2035    4,966  395,560    85   6.76%  28,866  28,866            2,788 
 23  2036    4,966  390,594    86   7.09%  28,252  28,252          (20,498)
 24  2037   30,172  360,422    87   7.46%  27,619  27,619          (17,945)
 25  2038   30,172  330,250    88   7.87%  26,967  26,967          (14,740)
 26  2039   30,172  300,078    89   8.33%  26,293  26,293          (10,860)
 27  2040   30,172  269,906    90   8.77%  25,370  25,370           (6,058)
 28  2041   30,148  239,758    91   9.26%  24,430  24,430             (340)
 29  2042   30,013  209,745    92   9.80%  23,472  29,419              253 
 30  2043   29,342  180,403    93  10.42%  21,875  30,000             (405)
 31  2044   30,853  149,550    94  10.99%  19,780  30,000              449 
 32  2045   29,172  120,378    95  11.63%  17,442  30,000             (379)
 33  2046   30,880   89,498    96  12.35%  14,815  30,000              501 
 34  2047   29,008   60,490    97  13.16%  11,842  30,000             (491)
 35  2048   30,190   30,300    98  14.08%   8,451  30,000             (300)
 36  2049   30,300             99  14.93%   4,478  30,000                0

But if the retirement account includes a TIPS fund to act as a reservoir, initially with $50,000, no TIPS will need to be liquidated to meet RMDs. The closest this comes to happening in in the year 2036 when only $4,966 TIPS interest is collected and the remainder of the $29,821 RMD is taken from the fund.

Code: Select all

Row Col C    Col D    Col E Col F   Col G   Col H   Col I   Col J    Col K
                                                           ---- Fund -----
     Year   Ladder    Value   Age   RMD %   RMD $  W'draw  Income  Balance

Code: Select all

  6  2019           899,992    69   0.00%                           50,000 
  7  2020   30,400  869,592    70   3.65%  34,671  34,671           45,729 
  8  2021   29,092  840,500    71   3.77%  34,540  34,540           40,280 
  9  2022   30,715  809,785    72   3.91%  34,405  34,405           36,590 
 10  2023   30,255  779,530    73   4.05%  34,266  34,266           32,579 
 11  2024   29,875  749,655    74   4.20%  34,122  34,122           28,331 
 12  2025   29,910  719,745    75   4.37%  33,973  33,973           24,268 
 13  2026   29,648  690,097    76   4.55%  33,819  33,819           20,097 
 14  2027   30,299  659,798    77   4.72%  33,500  33,500           16,897 
 15  2028   29,755  630,043    78   4.93%  33,335  33,335           13,317 
 16  2029   71,825  558,218    79   5.13%  32,993  32,993           52,149 
 17  2030    9,101  549,117    80   5.35%  32,640  32,640           28,610 
 18  2031    9,101  540,016    81   5.59%  32,275  32,275            5,436 
 19  2032  129,558  410,458    82   5.85%  31,898  31,898          103,096 
 20  2033    4,966  405,492    83   6.13%  31,506  31,506           76,556 
 21  2034    4,966  400,526    84   6.45%  31,100  31,100           50,422 
 22  2035    4,966  395,560    85   6.76%  30,469  30,469           24,918 
 23  2036    4,966  390,594    86   7.09%  29,821  29,821               63 
 24  2037   30,172  360,422    87   7.46%  29,154  29,154            1,082 
 25  2038   30,172  330,250    88   7.87%  28,465  28,465            2,789 
 26  2039   30,172  300,078    89   8.33%  27,753  27,753            5,208 
 27  2040   30,172  269,906    90   8.77%  26,779  26,779            8,600 
 28  2041   30,148  239,758    91   9.26%  25,788  25,788           12,961 
 29  2042   30,013  209,745    92   9.80%  24,776  31,054           11,920 
 30  2043   29,342  180,403    93  10.42%  23,090  31,666            9,595 
 31  2044   30,853  149,550    94  10.99%  20,879  31,666            8,782 
 32  2045   29,172  120,378    95  11.63%  18,411  31,666            6,288 
 33  2046   30,880   89,498    96  12.35%  15,638  31,666            5,501 
 34  2047   29,008   60,490    97  13.16%  12,500  31,666            2,843 
 35  2048   30,190   30,300    98  14.08%   8,920  31,666            1,366 
 36  2049   30,300             99  14.93%   4,726  31,666                0

To prepare this table with a different starting age for the ladder, a different yield assumption, or a different ladder entirely, follow these steps:

• Select All, Copy, and Paste [2] the following at cell A1 of a blank Excel sheet:

  Code: Select all

  Age ladder starts	70
  Yield	0
  Month born	6
  Rung size	=(E6+K6)/COUNT(D7:D52)								------ Fund ------
  Table	Divisor	Year	Ladder	Value	Age	RMD %	RMD $	W'draw	Income	Balance
  		2019		=NPV(B$2,D7:D$36)	=B$1-1					0
  70	27.4	=C6+1	30400	=NPV(B$2,D8:D$36)	=F6+1	=IF(F7<70+IF(B$3>6,1,0),0,1/VLOOKUP(F7,A$7:B$52,2,FALSE))	=G7*(E6+K6)	=MAX(B$4*(C7-C$6)-SUM(I$6:I6),H7)	=K6*B$2	=K6+D7-I7+J7
  71	26.5	=C7+1	29092
  72	25.6	=C8+1	30715
  73	24.7	=C9+1	30255
  74	23.8	=C10+1	29875
  75	22.9	=C11+1	29910
  76	22	=C12+1	29648
  77	21.2	=C13+1	30299
  78	20.3	=C14+1	29755
  79	19.5	=C15+1	71825
  80	18.7	=C16+1	9101
  81	17.9	=C17+1	9101
  82	17.1	=C18+1	129558
  83	16.3	=C19+1	4966
  84	15.5	=C20+1	4966
  85	14.8	=C21+1	4966
  86	14.1	=C22+1	4966
  87	13.4	=C23+1	30172
  88	12.7	=C24+1	30172
  89	12	=C25+1	30172
  90	11.4	=C26+1	30172
  91	10.8	=C27+1	30148
  92	10.2	=C28+1	30013
  93	9.6	=C29+1	29342
  94	9.1	=C30+1	30853
  95	8.6	=C31+1	29172
  96	8.1	=C32+1	30880
  97	7.6	=C33+1	29008
  98	7.1	=C34+1	30190
  99	6.7	=C35+1	30300
  100	6.3
  101	5.9
  102	5.5
  103	5.2
  104	4.9
  105	4.5
  106	4.2
  107	3.9
  108	3.7
  109	3.4
  110	3.1
  111	2.9
  112	2.6
  113	2.4
  114	2.1
  115	1.9

• Format for readability.
• Copy cell E7 down to row 35 and cells F7:K7 down to row 36.
• Enter age the ladder will start in cell B1.
• Enter assumed real yield of the TIPS ladder and the TIPS fund in cell B2.
• Enter birth month in cell B3. (This is only used to determine whether the first RMD occurs at age 70 or 71.)
• If they've changed, copy and paste onto column D the 30 years of ladder cash flow from cells AE50:BH50 on the Ladder sheet of the Ladder Builder spreadsheet. [3]
• Enter starting balance of TIPS fund in cell K6. Through trial and error see what number is needed so that there are no negative balances in column K.

1. Unless TIPS yields become negative again, meeting RMDs is unlikely to be a problem for a 30-year ladder if it starts at age 65 or younger.
2. If you have trouble pasting, try "Paste Special" and "Text".
3. Choose "Paste Special", "Values", and "Transpose".

[Wrench]

We have arrived at the same conclusion by different means. Yes, if the ladder starts at younger age like 65 the likelihood of not meeting RMDs is low with a 30 year ladder. Also, if the ladder is shorter than 30 years, that lowers the probability of not meeting RMDs too.
Your solution of holding a reserve to cover RMDs is one way to deal with it. This reserve can be in unspent TIPS income, or in another tax-deferred account. (I would NEVER put all my retirement money in one strategy, even a conservative one like a TIPs bond ladder - but maybe that's just me). But, my concern remains about active management. In my business I deal with many older individuals and I have personally observed their ability to deal with financial matters decline. Many do NOT have dementia, but they have decreased judgement and reasoning compared to when they were in their prime. Frankly, they make "dumb" mistakes - ones they would never make when they were 30 years younger (they tell me this all the time!). It is pretty well documented that this happens a lot with older seniors. Will I be one of them when I am 85? I hope not, but I would like to protect my family's interests by reducing the chance. Thus, I am looking for a bullet proof, set-it-and-forget-it means to generate income that can't be screwed up. If I screw up something in the rest of my portfolio, I want to have that base income that will always be there no matter what I do. For me, TIPs alone does not look like the answer because of this RMD issue. A TIPs ladder may be part of my portfolio, but not in the bucket that is going to produce guaranteed inflation adjusted income that I count on to live. But I want to stress, this is JUST ME. The TIPs ladder is a wonderful tool and I applaud #Cruncher and anyone else who is a proponent of it and wants to use it. But like any financial investment, it has to be applied with the end goal in mind, and suited to the individual using it.
What AM I going to use? I am exploring annuities with a CPI adjusted inflation rider. It avoids the RMD issue, provides inflation adjusted income for life (not just 30 years), and provides higher returns to boot, although with higher risk because it is backed by an insurance company rather than the US government.

Wrench

[Bongleur]

How can you predict withdrawal rate from a TIPS fund? 1)You steal underwear ^h^h^h^h^h^h buy a lump sum today ... 2) (???) ... and 3) PROFITS.

[Wrench]

More thoughts about CPI adjusted immediate annuities: they are guaranteed by state guarantor agencies. Each state is different. See list here:
https://www.immediateannuities.com/stat ... ociations/
In my state, defaults are covered up to $250K per contract holder of present value at the time of default. So this reduces the risk for this product - maybe not quite as solid as the US government but probably good enough for me.
If you want/need more income than your state agency guarantee ($250K for me) will buy, then use multiple companies. Unfortunately, I have only found one company that offers a CPI-U inflation rider (The Principal), but all the companies offer fixed inflation protection from 1-5%. In studying the rate structure it appears The Principal has baked in between 2-3% inflation estimate into their premium for CPI-U inflation protection. So that might be a good staring point for other inflation protected annuities. Or hedge your bets on inflation even occurring and buy one with no inflation protection, and another with a small amount of inflation protection along with one that has CIP-U protection.

[archbish99]

One possible solution is to not put all of the retirement account into the TIPS ladder. Putting some of it into a TIPS fund may avoid having to liquidate some individual TIPS to meet RMDs. The fund serves as a reservoir to hold excess principal and interest collections and to provide extra money to meet RMDs when needed.

It may also be possible to meet your RMD by moving a TIPS security to the taxable account without sale, if the account holder allows it. IIUC, this would be treated as a distribution of whatever that TIPS's market value was on the day of transfer.

[Wrench]

When I explored purchasing a TIPS ladder through an account at Schwab I talked to one of the their bond trading experts. His advice was to only buy bonds close to par or at a discount because if there were periods of deflation you could end up receiving less than you invested in the bond. But if you buy at par or at a discount, then you will at least receive your principal back no matter what. I am curious about thoughts here on this line of thinking ...

[Kevin M]

When I explored purchasing a TIPS ladder through an account at Schwab I talked to one of the their bond trading experts. His advice was to only buy bonds close to par or at a discount because if there were periods of deflation you could end up receiving less than you invested in the bond. But if you buy at par or at a discount, then you will at least receive your principal back no matter what. I am curious about thoughts here on this line of thinking ...

It's something to consider, but that criterion may restrict you to buying only at auction, and even then your adjusted price could be above par.

For example, the TIPS maturing 2/15/2049 sold at auction on 2/21/2019 for adjusted price 97.5, but is now trading at about 107.7.

EDIT: All TIPS on the secondary market currently are selling at a premium--most at a significant premium.

The TIPS maturing 04/15/2024 originally sold at auction on 4/18/2019 for 100.14, but was reissued and sold again at auction on 06/20/2019 for 102.96. These are inflation-adjusted prices.

Kevin

[Wrench]

Unless I am missing something, there are a bunch of issues selling near par or at a discount. From WSJ data of 7/5/2019 a list of bonds with ask prices of 101 or lower is shown below. One could easily construct a ladder from these issues, adding later issue dates from auction if one did not want to buy too many of any one issue to cover the "gap" years (or you felt yields were going to be better in the future).
I guess my basic question is: what is the risk to my principal of buying bonds that are far above par, particularly when they mature 20-30 years in the future? My gut says it is quite low, but since buying TIPS in the first place is largely about risk mitigation (against inflation) why take even that very low risk if I can easily avoid it? Am I missing something? I am certainly not a TIPS expert.

7/15/19 100.00000
1/15/20 99.71875
4/15/20 98.87500
7/15/20 100.50000
1/15/21 100.59375
4/15/21 98.93750
7/15/21 100.46875
1/15/22 99.18750
4/15/22 99.06250
7/15/22 99.50000
1/15/23 99.25000
4/15/23 100.96875
7/15/23 100.62500
7/15/24 99.65625
1/15/25 99.90625
7/15/25 100.87500
7/15/26 99.12500
2/15/42 100.31250
2/15/43 96.93750
2/15/45 98.87500

[Kevin M]

Unless I am missing something, there are a bunch of issues selling near par or at a discount. From WSJ data of 7/5/2019 a list of bonds with ask prices of 101 or lower is shown below.

What you are missing is that you are showing unadjusted price quotes. These are multiplied by the inflation factors, which currently are all greater than 1, to get the adjusted price, which is what you actually pay. As an example, here are the numbers for the 1/15/2020 TIPS using Fidelity quotes (unadjusted prices are a bit different, but close enough to the WSJ quotes for this to apply).

Ask price: 99.783
Inflation factor: 1.18231
Adjusted ask price: 117.974 (= 99.783 * 1.18231)

So you'd actually pay about 118 instead of about 100.

I guess my basic question is: what is the risk to my principal of buying bonds that are far above par, particularly when they mature 20-30 years in the future?

I think the common wisdom is that having net deflation (inflation < 0%) over 20-30 years is highly unlikely, so chances are low that you'll lose any principal over a long period of time. It's certainly possible over a shorter period of time, as negative inflation does occur, and negative inflation would result in a decrease to the inflation index, and thus to the adjusted price.

My gut says it is quite low, but since buying TIPS in the first place is largely about risk mitigation (against inflation) why take even that very low risk if I can easily avoid it? Am I missing something? I am certainly not a TIPS expert.

The common wisdom agrees with your gut. The reason to take the risk is that it can be difficult to buy TIPS at adjusted prices of <= 100, unless you stick to auctions where that's not the case. So it depends on how patient you want to be in constructing the ladder.

Kevin

[Kevin M]

I think the common wisdom is that having net deflation (inflation < 0%) over 20-30 years is highly unlikely, so chances are low that you'll lose any principal over a long period of time.

But it has happened! Here is a chart of end of year CPI values since 1913 (using log scale):



I downloaded the non-log values to do a bit more analysis. CPI value at end of 1920 was 19.4, after which it fell, and did not recover to that value sometime in 1946. So this was a period of more than 25 years of net deflation. The end of year low was at the end of 1932, when it was 13.1, for a net change of -32.5%.

We can see from the chart that there have been no other extended periods of net deflation since then.

So if we assume we won't have another great depression or something else cataclysmic enough to cause an extended period of deflation, your TIPS principal is safe for periods of longer than a few months.

Looking at the extended, net deflationary period at monthly resolution with a linear scale:



From this data we determine that the monthly peak was 20.9 in June 1920, and recovery to that level occurred after October 1946 (20.8), for a net deflationary period of 316 months or 26.3 years. The monthly low was 12.6 (net change of -39.7%), which occurred in March, April and May of 1933.

Kevin

[Wrench]

Thank you Kevin. I understand better now. Just beginning to evaluate TIPS so have not bought/sold any yet.
So I cannot easily remove the risk of loss of principal if I buy in the secondary market, but I can minimize it. If I want to minimize potential loss of principal from long periods of deflation (such as occurred in the Great Depression as you so carefully analyzed), I can either (i) purchase only those issues with low adjusted principal, i.e., <108, for which there are more than a dozen, or (ii) buy at auction over time as close to par as possible. I suppose if you are far away from retirement, then the latter makes the most sense, while if you are in retirement, the former probably works better. If you are not retired, but close (my situation) then a combination of the two might balance the risk with the need for inflation adjusted income in the future.

[Kevin M]

<snip>
If I want to minimize potential loss of principal from long periods of deflation (such as occurred in the Great Depression as you so carefully analyzed), I can either (i) purchase only those issues with low adjusted principal, i.e., <108, for which there are more than a dozen, or (ii) buy at auction over time as close to par as possible.

Sure, but if you are trying to build a ladder with TIPS maturing annually, as much as possible with the existing maturity gaps, you will be able to fill fewer rungs of the ladder.

With 108 as your limit, you can fill an annual ladder out to 10-year maturity. There are no TIPS maturing in years 11 and 12. Year 13 is 97% over par (with par = 100), then there are no TIPS maturing until year 21, with year 21 at 52% over, year 22 at 51% over, and year 23 at 14% over par. You can then fill years 24, 26, 28, and 30 with that criterion (rounded to 0 decimal places).

Keep in mind that you are not risking CPI-based purchasing power by buying TIPS over 100 (adjusted price), since any reduction in adjusted price will reflect a similar reduction in the CPI, so TIPS bought over par (on an adjusted basis) still meet the goal of hedging your purchasing power against unexpected inflation. It's just that your effective "put" at maturity is not as good, so your hedge against deflation is not as good. Nominal Treasuries probably are a better deflation hedge anyway.

Others with more TIPS expertise can weigh in if they disagree, or want to further clarify.

Kevin

[Artsdoctor]

Deflation can occur, but a sustained period of deflation would be rare. Nonetheless, that is a weakness of buying individual TIPS at premium prices.

If you're going to buy TIPS at auction, you will always be able to get the bond at maturity back at par regardless of how much deflation has occurred.

The flip side is that if you're buying TIPS at par or at a discount, your coupon is going to be extremely low--almost as if you've purchased a zero coupon bond. Theoretically, this should not make a difference with your ladder but you're really going to have very little income to accompany that ladder.

Conversely, some of the older TIPS (bought at a premium) will have a coupon of over 2% to nearly 4%. These coupons can then be invested elsewhere, depending on where you are in your investing life.

To say that one should not buy TIPS at a significant premium seems draconian and I would disagree with the Schwab rep. The likelihood of severe and prolonged deflation would be very unusual. But not impossible.

[Wrench]

The way it looks to me, if one is concerned about a large loss of principal, you can buy a TIPS ladder where there is a limited loss of principal in deflationary times, but it will be missing many rungs. But that is no different than filling in for the years 2030-2031 and 2033-2039. It just means you have a ladder that requires more work to maintain moving forward. I do understand with deflation the buying power will, in theory, be maintained. But it still leaves me uncomfortable, I guess because I suspect that what I will need to buy in 20-30 years (e.g. medical care) will not see the same loss in buying power that the CPI-U measures.

Relative to the potential for a deflationary period, Kevin clearly showed that in the last 100 years, there was a period of ~26 years where deflation would have impacted TIPS principal seriously. Each of us can evaluate how likely that is to occur within the next 30 years. I don't disagree it is unlikely. If you are willing to take the risk it won't happen, then buy TIPS with high premiums. If you want to reduce your potential losses just in case there is a deflationary period (as I do), then try to fill your ladder with bonds that are as close to par as you can, even if it leaves some rungs empty. The whole purpose of a TIPS ladder is to mitigate the risks of loss in buying power. Why wouldn't you want to protect yourself as best you can for both deflation as well as inflation, especially if it does not cost you much? (the ladders I have built using #cruncher's spreadsheet with bonds with lower premiums have nearly the same IRR as those with higher premiums).

[Artsdoctor]

^ I'm trying to figure out how you'd build a TIPS ladder without paying premiums. Yes, there are some close to par but in practice, it's going to be difficult.

Years ago, I built a TIPS ladder along the lines of a classic Liability Matching Portfolio which would take me from 65-85. It makes up about 1/3 of my portfolio. If someone is going to build a ladder, they're usually going to do it before retirement.

The only holes I had were the previously mentioned 2030-2031 and 2033-2039. At the time, I had to make a decision about how to fill the holes: buy more 2029's and a LOT of 2032's or buy 10-year TIPS and roll them accordingly. Neither option was great but I decided that I wanted to set it and forget it so I bough plenty of 2029's and 2032's to get me through. #Cruncher's spreadsheet was invaluable in doing this (I think it was his first spreadsheet with TIPS ladders).

This all happened in tax-advantaged accounts and when all was said and done, the interest will take care of over 1/3 of the annual income during the early years. But in the meantime, I have a lot of income from those premium TIPS which I've used to just purchase LifeStrategy shares, something which has multiplied quite a bit because of the bull market which seems to be continuing longer than anyone expected. The interest from those TIPS paying less than 1% is so insignificant that it's almost meaningless (although they were cheap to buy). Additionally, if I would have waited to buy my TIPS ladder until TIPS became cheaper, I'd still be waiting.

So I can appreciate the concept of buying TIPS at par or at a discount, but I don't see how it's practical to build a multi-decade ladder without buying some premium bonds. It's becoming easier of course since interest rates have been low for so long. But if there's a prolonged deflationary cycle, then my nominal bonds should hold up well to decompensate for the TIPS issues.

Bill Bernstein's book on Deep Risk tackles different types of risks and deflation is one of them. However, the risk is just not that significant.

[Kevin M]

<snip>
I do understand with deflation the buying power will, in theory, be maintained. But it still leaves me uncomfortable, I guess because I suspect that what I will need to buy in 20-30 years (e.g. medical care) will not see the same loss in buying power that the CPI-U measures.
<snip>

But this is an argument against TIPS in general, not just TIPS in deflationary times. To the extent you're uncomfortable about the CPI-U reasonably tracking your personal inflation rate, you should be uncomfortable about TIPS, period. But I don't know what a better choice is to protect purchasing power--perhaps a healthcare fund if healthcare costs are a big concern, but of course there are other risk factors involved in that other than just healthcare costs.

Kevin

[#Cruncher]

I guess my basic question is: what is the risk to my principal of buying bonds that are far above par, particularly when they mature 20-30 years in the future?

First off, a TIPS can cost "above par" either because the index ratio (aka inflation factor) is more than 1.0; or because the unadjusted price is more than 100%. [1] Only the index ratio is relevant to the deflation floor feature. (See TreasuryDirect's FAQ What happens to TIPS if deflation occurs?.)

That taken care of, I would say that there is no deflation risk for any TIPS. The real return at time of purchase is guaranteed for any TIPS if it's held to maturity regardless of its index ratio. In the unlikely event that the index ratio of a TIPS is less than 1.0 at maturity, it may produce a higher real return depending on its index ratio when purchased. But I would call this a "bonus" for those TIPS that get the higher return; not a "risk" for those that don't.

The following table illustrates this for a hypothetical zero-coupon TIPS yielding 1% with 25 years remaining until maturity. Over the remaining 25 years the CPI is assumed to fall 2% per year. Row 11 shows that the TIPS with initial index ratios closer to 1.0 get a return more than 1.0% (columns B, C, & D); but no matter how large the index ratio all TIPS get at least the 1.0% real return at time of purchase.

Code: Select all

Row  Col A                     Col B     Col C     Col D     Col E     Col F
  1  Face value             1,000.00
  2  Stated yield             1.000%
  3  Remaining years              25
  4  Annual inflation        (2.000%)
  5  Price (1 / 1.01 ^ 25)   77.977%
  6  Index ratio purchase    1.00000   1.25000   1.50000   1.75000   2.00000
  7  Cost                     779.77    974.71  1,169.65  1,364.59  1,559.54
  8  Index ratio maturity    0.60346   0.75433   0.90520   1.05606   1.20693
  9  Redemption amount      1,000.00  1,000.00  1,000.00  1,056.06  1,206.93
 10  Real value             1,657.10  1,657.10  1,657.10  1,750.00  2,000.00
 11  Real return              3.061%    2.145%    1.403%    1.000%    1.000%

I wouldn't let this possible bonus influence which TIPS I purchased for a ladder. I consider the chance of the index ratio being less than 1.0 at maturity to be very remote for any maturity TIPS. But if there is any possibility at all, the market will factor this in to prices. TIPS with lower index ratios will have lower yields to compensate for the possible deflation bonus. This is shown for the following five pairs of 20-year and 10-year TIPS maturing in January 2025 - 2029. The 10-year TIPS all have lower index ratios and also slightly lower yields. [2] Prices are from WSJ TIPS Quotes for 7/5/2019.

Code: Select all

  Matures    Coupon   Index   Yield    Diff
-----------  ------   -----   -----   ------
2025 Jan 15   2.375   1.356   0.285
2025 Jan 15   0.250   1.079   0.269   (0.016)
2026 Jan 15   2.000   1.287   0.292
2026 Jan 15   0.625   1.075   0.285   (0.007)
2027 Jan 15   2.375   1.267   0.319
2027 Jan 15   0.375   1.058   0.310   (0.009)
2028 Jan 15   1.750   1.220   0.338
2028 Jan 15   0.500   1.036   0.332   (0.006)
2029 Jan 15   2.500   1.190   0.361
2029 Jan 15   0.875   1.012   0.339   (0.022)

1. The unadjusted price will be above 100% when the yield-to-maturity (YTM) is less than the coupon. The unadjusted price will of course fall to 100% at maturity, but this does not represent a loss. It simply means that you're getting your money back sooner as you collect coupons in excess of the YTM.
2. The effect of lower index ratios may be more than the differences shown. The 10-year TIPS with the smaller index ratios all have smaller coupons than the 20-year TIPS. This means they have higher durations. Since the yield curve rises over these 2025 to 2029 maturities, one would expect -- other things being equal -- that the 10-year TIPS would have higher, not lower, yields than the 20-year maturities.

[Artsdoctor]

Very very nice analysis, #Cruncher.

Your terminology is far more accurate than mine, and clearer as well.

The concept above was that IF one buys a 10-TIPS at auction, for example, and for the sake of simplicity, the price is 100 and there has not yet been any inflation adjustment, the cost basis/purchase price is 100. If you'd have a 10-year deflationary period during which the CPI continually decreases, you'd see your interest paid at the inflation factor rate of 1, then 0.98, then 0.96, then 0.94, etc. (for example), but you'd always have the maturity inflation factor of 1 if you held it to maturity no matter how much deflation had occurred. At least that's how I'd interpret the concerns expressed above.

[Wrench]

Thank you all for a complete explanation, and especially #Cruncher. I don't know what my Schwab bond guy was thinking. Makes me wonder...

[Kevin M]

Thank you all for a complete explanation, and especially #Cruncher. I don't know what my Schwab bond guy was thinking. Makes me wonder...

There's not necessarily any discrepancy between what your Schwab bond guy was thinking, and what we are saying. #Cruncher put some numbers to it, and spun what I called a put that was a better deflation hedge as a potential deflation bonus. But I think we're saying the same thing in different ways.

Keep in mind that you are not risking CPI-based purchasing power by buying TIPS over 100 (adjusted price), since any reduction in adjusted price will reflect a similar reduction in the CPI, so TIPS bought over par (on an adjusted basis) still meet the goal of hedging your purchasing power against unexpected inflation. It's just that your effective "put" at maturity is not as good, so your hedge against deflation is not as good. Nominal Treasuries probably are a better deflation hedge anyway.

However you look at it, you would earn more with a higher lower initial index ratio if deflation is bad enough over your holding period. I think that's basically what your bond guy was thinking. #Cruncher did point out that there is a yield difference that apparently the market judges as equalizing the expected return vs. risk tradeoff.

Kevin

[#Cruncher]

There's not necessarily any discrepancy between what your Schwab bond guy was thinking, and what we are saying. … However you look at it, you would earn more with a higher initial index ratio if deflation is bad enough over your holding period. I think that's basically what your bond guy was thinking.

We do agree on the facts of the case, Kevin, and that's the main thing. (However, I believe you meant to type "lower" instead of "higher".) I don't think we should get bogged down in the different terms we use to describe it. But it's unclear what the bond guy at Schwab meant. According to Wrench in this post,

His advice was to only buy bonds close to par or at a discount because if there were periods of deflation you could end up receiving less than you invested in the bond. But if you buy at par or at a discount, then you will at least receive your principal back no matter what. (underlines added)

Usually people use "discount" to mean the unadjusted price is less than 100%; not to mean that the index ratio is less than 1.0.

But this got me thinking and I realized that the underlined part of the following statement I made in this post isn't entirely correct:

First off, a TIPS can cost "above par" either because the index ratio (aka inflation factor) is more than 1.0; or because the unadjusted price is more than 100%. Only the index ratio is relevant to the deflation floor feature. (See TreasuryDirect's FAQ What happens to TIPS if deflation occurs?.)

In the unlikely event that the index ratio will be less than 1.0 at maturity, a TIPS with a lower coupon will benefit slightly more from the deflation floor even if both TIPS have the same index ratio. This is because, as Artsdoctor points out in this post, the deflation floor applies only to the principal redemption at maturity. Coupon payments have no such minimum guarantee. This can be illustrated with the two TIPS maturing January 2029 listed last in the 2nd table of my previous post, one with a 0.875% coupon and the other with a 2.5% coupon.

For illustration I'll assume both TIPS have exactly 10 years until maturity, coupon interest paid at the end of each year, a 0.35% yield-to-maturity (YTM), and a 1.0 index ratio. The following table shows the nominal cash flows for both bonds assuming the CPI increases 2% every year. The last line (computed by applying the Excel IRR function to the cash flows) shows that both bonds have the same 2.357% nominal return. (1.02 * 1.0035 - 1).

Code: Select all

Year     Index     0.875%      2.500%
   0   1.00000  (1,051.50)  (1,210.92)
   1   1.02000       8.93       25.50 
   2   1.04040       9.10       26.01 
   3   1.06121       9.29       26.53 
   4   1.08243       9.47       27.06 
   5   1.10408       9.66       27.60 
   6   1.12616       9.85       28.15 
   7   1.14869      10.05       28.72 
   8   1.17166      10.25       29.29 
   9   1.19509      10.46       29.88 
  10   1.21899   1,229.66    1,249.47 
 IRR               2.357%      2.357%

But if the CPI were to decrease 2% every year, the last line shows that the lower coupon bond has a slightly higher nominal return. (0.262% vs0.118%).

Code: Select all

Year     Index     0.875%      2.500%
   0   1.00000  (1,051.50)  (1,210.92)
   1   0.98000       8.58       24.50 
   2   0.96040       8.40       24.01 
   3   0.94119       8.24       23.53 
   4   0.92237       8.07       23.06 
   5   0.90392       7.91       22.60 
   6   0.88584       7.75       22.15 
   7   0.86813       7.60       21.70 
   8   0.85076       7.44       21.27 
   9   0.83375       7.30       20.84 
  10   0.81707   1,007.15    1,020.43 
 IRR               0.262%      0.118%

At the current time, TIPS with higher coupons were generally issued earlier and therefore have larger index ratios. Here is the same case of the assumed 2% annual fall in the CPI, but with the actual 1.01 and 1.19 index ratios of the two January 2029 TIPS. The lower index TIPS will have a 1.58% higher nominal return (0.166% - -1.414%).

Code: Select all

Year   Index 0.875% CFlow    Index 2.500% CFlow
   0   1.01000  (1,062.02)   1.19000  (1,440.99) [*]
   1   0.98980       8.66    1.16620      29.16 
   2   0.97000       8.49    1.14288      28.57 
   3   0.95060       8.32    1.12002      28.00 
   4   0.93159       8.15    1.09762      27.44 
   5   0.91296       7.99    1.07567      26.89 
   6   0.89470       7.83    1.05415      26.35 
   7   0.87681       7.67    1.03307      25.83 
   8   0.85927       7.52    1.01241      25.31 
   9   0.84209       7.37    0.99216      24.80 
  10   0.82524   1,007.22    0.97232   1,024.31 
 IRR               0.166%               (1.414%)

However, if the coupon are reversed, the lower index ratio TIPS still has a 1.42% higher nominal return (0.029% - -1.393%. So, while having the smaller coupon does accentuate the better performance of the TIPS with the lower index ratio (1.58% vs only 1.42%), it doesn't do so by much.

Code: Select all

Year   Index 2.500% CFlow    Index 0.875% CFlow
   0   1.01000  (1,223.03)   1.19000  (1,251.29)
   1   0.98980      24.75    1.16620      10.20 
   2   0.97000      24.25    1.14288      10.00 
   3   0.95060      23.77    1.12002       9.80 
   4   0.93159      23.29    1.09762       9.60 
   5   0.91296      22.82    1.07567       9.41 
   6   0.89470      22.37    1.05415       9.22 
   7   0.87681      21.92    1.03307       9.04 
   8   0.85927      21.48    1.01241       8.86 
   9   0.84209      21.05    0.99216       8.68 
  10   0.82524   1,020.63    0.97232   1,008.51 
 IRR               0.029%               (1.393%)

* Cost computed with Excel PV function:
-1,062.02 = 1.01 * 1000 * PV(0.35%, 10, 0.875%, 1, 0)
-1,440.99 = 1.19 * 1000 * PV(0.35%, 10, 2.500%, 1, 0)

[Wrench]

Here is another way to say the same thing kevin and #cruncher are saying:
https://thefinancebuff.com/tips-during-deflation.html
(the spreadsheet link is dead in that link)

[Kevin M]

We do agree on the facts of the case, Kevin, and that's the main thing. (However, I believe you meant to type "lower" instead of "higher".)

Yes, of course; thanks. Corrected.

<snip>
At the current time, TIPS with higher coupons were generally issued earlier and therefore have larger index ratios.
<snip>
Here is the same case of the assumed 2% annual fall in the CPI, but with the actual 1.01 and 1.19 index ratios of the two January 2029 TIPS. The lower index TIPS will have a 1.58% higher nominal return (0.166% - -1.414%).

OK, so still consistent with what we've said so far, despite the general benefit of a lower coupon in a period of extended net deflation (and the latter part is quite interesting, and an new addition to the conversation).

However, if the coupon are reversed, the lower index ratio TIPS still has a 1.42% higher nominal return (0.029% - -1.393%. So, while having the smaller coupon does accentuate the better performance of the TIPS with the lower index ratio (1.58% vs only 1.42%), it doesn't do so by much.

Interesting. Thanks for the balanced presentation of this interesting addition to the conversation!

Kevin

[Kevin M]

Here is another way to say the same thing kevin and #cruncher are saying:
https://thefinancebuff.com/tips-during-deflation.html
(the spreadsheet link is dead in that link)

Yes! That's an excellent article, as are all of TheFinanceBuff's blog posts. I think he hits all of the points we've discussed, including the coupon topic that #Cruncher introduced.

If you're interested in the spreadsheet, you might try sending a PM to forum member tfb, who is the author of that blog.

A couple of minor quibbles. From the blog post:

If you buy bonds with a long maturity, say 10 years or more, it’s hard to imagine there will be cumulative deflation for that long. Even Japan didn’t have deflation for more than a few years.

First, I don't know why it's hard to imagine when it's actually happened. Second, I don't know why Japan was used as an example when we have the example of 25+ years of net deflation in the US. Finally, net cumulative deflation includes the inflationary recovery period from the bottom of the deflationary period, so it's not just the years of deflation that matter.

The peak to trough for US deflation was from Jun 1920 to Mar/Apr/May 1933, so about 13 years. It's just that full CPI recovery didn't occur until after Oct 1946, so more than 13 years of net inflation to recover from 13 years of net deflation.

If you buy TIPS with short maturities, like a 5-year TIPS, it’s possible to have deflation for 5 years.

Not sure why five years was mentioned. Other than the 1920-1946 period (and its subperiods), it's hard to see a period of net deflation that long by eyeballing the chart since 1913. The closest I could find was Oct 1953 with CPI = 27.00 to April 1956 with CPI = 26.90, so less than three years.



Kevin

[Wrench]

Yes, Kevin. I agree. Looking further back, there have been at least 2 other periods of net deflation over decades. Though I don't have the hard numbers the following chart indicates periods from about 1815 - 1835, and 1865 - 1895 also were deflationary. See:
http://www.in2013dollars.com/assets/img ... ncient.svg
In fact, that figure indicates that the last ~60 years have been anomalous relative to deflationary periods. I am sure some will argue that "things are different now", and they certainly are. A strong and active Fed for one. But basic human nature has not changed. Avarice, speculation and human fallibility have not been repealed. So, looking 30 years out I think one would be wise to be aware of, and be prepared for, longer periods of deflation.
As this discussion has so wonderfully illuminated, that does not mean TIPS are still not a valuable investment for retirees. Just that one may want to consider which TIPS will offer better returns in the perhaps unlikely case we do fall into a patch of deflation over many years. Or, maybe even hedge against deflation by owning some non-inflation protected treasury bonds, which would have fixed "inflated" interest payments during deflationary periods.

One other question: I am curious on the experts view on: does it matter whether one buys a TIPS bond at auction, or a bond at the same maturity date in the secondary market? It seems to me that it should not make a difference as both respond directly to market forces, but being a newbie to TIPS purchases, just wanted to get some expert views. Thanks.

wrench

[Kevin M]

One other question: I am curious on the experts view on: does it matter whether one buys a TIPS bond at auction, or a bond at the same maturity date in the secondary market? It seems to me that it should not make a difference as both respond directly to market forces, but being a newbie to TIPS purchases, just wanted to get some expert views.

One benefit of buying at auction for smaller purchases (less than say $150K) might be that you get the same price/yield as institutional investors buying much larger quantities.

However, your broker may offer competitive pricing for smaller purchases, perhaps on some issues but not others. For example, looking now at Fidelity at the TIPS maturing 7/15/2026, I see the best price for minimum quantity 10 ($10K face value), with a slightly higher price for minimum quantity 100. This is the kind of deal for smaller buyers that Schwab has been noted to do for nominal Treasuries, so perhaps other brokers have noticed, and are doing the same--in this case, for TIPS.

But I see the best price for the TIPS maturing 7/15/2024 for minimum quantity 100, so it's not consistent at Fidelity as of now.

Even if you get a retail buyer deal, there's still a bid/ask spread, and if we assume that about half of that is a markup for purchase, in theory you get a better deal at auction. For example, in the case noted above, the unadjusted bid price is 99.167, and the ask price is 99.285. On the other hand, the price/yield could change enough between your purchase date and the auction date, or vice versa, that you get a better price on the secondary market, but that's just a crap shoot.

I don't know how much it affects TIPS, but there is a liquidity premium for recently issued (on the run) nominal Treasuries, so slightly higher price and slightly lower yield relative to more aged Treasuries.

Price you pay has nothing to do with the way TIPS prices respond to market forces, so getting a better price at auction isn't relevant from this perspective. Liquidity has some impact on pricing, but I don't know what that might be for recently issued TIPS vs. more aged TIPS. I would suspect it's not enough to make a difference in purchase decisions.

So, I think it would have more to do with how quickly and how fully you wanted to populate a TIPS ladder. From what I see, there is only one TIPS auction per month, with some auctions being a re-issue of a previous issue. For example, maturities of the last 12 auctions:

10-Year
4-Year 10-Month
9-Year 8-Month
5-Year
9-Year 10-Month
30-Year
10-Year
4-Year 4-Month
9-Year 8-Month
29-Year 4-Month
9-Year 10-Month
4-Year 8-Month

So basically in the last year you could have populated the 5y, 10y and 30y rungs of a ladder. In one year these will become the 4y, 9y, and 29y rungs, and you could repopulate the 5y, 10y, and 30y rungs over the next year (or something like that).

So, after 10 years, you'll have rungs 1-10 and 20-30 populated, of course with some of your TIPS maturing during that time, and it will take 20 years to fully populate your ladder. This assumes that TIPS continue to be issued in a similar way in years to come.

I think some people buy a 30y each year, starting early enough that they'll have their 30y ladder populated by retirement.

If you want to build a 30-year ladder (or as close as you can get with the gaps) in less than 20 years, you really have to go to the secondary market.

Kevin

[Wrench]

Thanks, Kevin. The index ratio should be lower at auction, too, so that is a (small) benefit for the reasons discussed above. I will proceed buying mostly at auction over the coming years, filling in the future gap years by purchasing the max amount of i-Bonds each year as well. That way I should be able to cover an ~30 year ladder with only a few purchases on the secondary market. i-Bonds are awesome because they never mature (or at least they get interest up to 30 years) and you can cash them at full value anytime after 5 years from purchase. No spreads, no commissions, no fuss, no muss. Just get the money when you want it The current interest rate, 0.5%, is better than the current 5 year TIPS YTM rate, though of course that could change in the future. This flexibility should allow me to adjust spending "on the fly" so to speak if there are years when I need more or less anytime during that 30 year window. I only wish I had started buying them years ago ...

wrench

[#Cruncher]

... From what I see, there is only one TIPS auction per month, with some auctions being a re-issue of a previous issue. For example, maturities of the last 12 auctions:

Code: Select all

[Jul 2019] 10-Year
[Jun 2019]  4-Year 10-Month
[May 2019]  9-Year  8-Month
[Apr 2019]  5-Year
[Mar 2019]  9-Year 10-Month
[Feb 2019] 30-Year
[Jan 2019] 10-Year
[Dec 2018]  4-Year  4-Month
[Nov 2018]  9-Year  8-Month
[Oct 2018] 29-Year  4-Month
[Sep 2018]  9-Year 10-Month
[Aug 2018]  4-Year  8-Month

At the time of Kevin's post (July 2019) I believe the Treasury was changing its TIPS auction schedule to introduce an October 5-year maturity and to have only one annual reopening of each 5-year and of the 30-year maturity. Here is the current schedule of (O)riginal and (R)eopening auctions according to the TreasuryDirect FAQ, How do I know when TIPS will be auctioned?:

Code: Select all

            Apr 5Yr  Oct 5Yr  Jan 10Yr  Jul 10Yr   Feb 30Yr
January                          O
February                         |                    O
March                            R                    |
April          O                 |                    |
May            |                 R                    |
June           R                                      |
July                                        O         |
August                                      |         R
September                                   R
October                 O                   |
November                |                   R
December                R

I've just updated my TIPS Ladder Builder Excel workbook to add the February 2051 maturity auctioned yesterday. A default 30,000 constant dollar per year ladder covering 2022-2051 would cost $955,000 based on today's WSJ TIPS Quotes. [*] Here is the default ladder:

Code: Select all

 1/01/2021 base dt                          Mult  Nbr    Cost  Principal
 2/22/2021 quote dt                    Sum    30  661  954,982   749,951
                                  Interest                       150,236
                                  Combined                       900,188
  Matures    Coupon    Yield       CUSIP

Code: Select all

 4/15/2022   0.125%   (2.257%)   912828X39     1   19   20,920    20,343
 4/15/2023   0.625%   (2.060%)   9128284H0     1   19   21,141    19,918
 4/15/2024   0.500%   (1.894%)   9128286N5     1   20   22,320    20,649
 4/15/2025   0.125%   (1.788%)   912828ZJ2     1   20   21,836    20,162
 7/15/2026   0.125%   (1.638%)   912828S50     1   19   22,709    20,640
 7/15/2027   0.375%   (1.418%)   9128282L3     1   19   22,675    20,225
 7/15/2028   0.750%   (1.236%)   912828Y38     1   20   23,968    20,747
 7/15/2029   0.250%   (1.066%)   9128287D6     1   21   23,864    21,377
 7/15/2030   0.125%   (0.915%)   912828ZZ6     1   20   22,392    20,312
 1/15/2031   0.125%   (0.816%)   91282CBF7     1   21   23,052    21,006
 4/15/2032   3.375%   (0.820%)   912810FQ6     5   84  185,186   123,225
 2/15/2040   2.125%   (0.289%)   912810QF8     4   86  152,488   103,606
 2/15/2041   2.125%   (0.243%)   912810QP6     1   23   40,627    27,348
 2/15/2042   0.750%   (0.161%)   912810QV3     1   24   33,040    27,657
 2/15/2043   0.625%   (0.118%)   912810RA8     1   25   33,010    28,314
 2/15/2044   1.375%   (0.110%)   912810RF7     1   26   39,100    29,049
 2/15/2045   0.750%   (0.076%)   912810RL4     1   26   34,507    28,750
 2/15/2046   1.000%   (0.045%)   912810RR1     1   26   36,082    28,572
 2/15/2047   0.875%   (0.041%)   912810RW0     1   27   36,105    29,125
 2/15/2048   1.000%   (0.034%)   912810SB5     1   28   37,863    29,566
 2/15/2049   1.000%   (0.022%)   912810SG4     1   28   37,295    28,974
 2/15/2050   0.250%   (0.006%)   912810SM1     1   30   32,649    30,384
 2/15/2051   0.125%   (0.030%)   912810SV1     1   30   32,155    30,004

* The WSJ Quotes omits the Feb 2051 maturity, so I pulled it from Bloomberg United States Rates & Bonds as of 3:33 PM 2/19/2021.