TIPS Ladder Spreadsheets in General & Two in Particular

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

TIPS Ladder Spreadsheets in General & Two in Particular

Post by #Cruncher » Fri Mar 30, 2012 9:55 pm

Magellan has an elegant, simple-to-use spreadsheet for estimating the cost of building a TIPS ladder. (See 1 below to download.) He used the spreadsheet in this post. In the same thread I criticized it for overstating the cost of the ladder. (See this post in the same thread.) My criticism was prompted by comparing magellan's results to those from my own TIPS ladder spreadsheet. (See 2 below to download.) It turns out the error was in my spreadsheet, not his. I've since corrected my own, and now their results are pretty close. For a 30-year ladder that provides an annual $30,000 real withdrawal, his spreadsheet estimates a cost of $847,000; while mine estimates $839,000.

Magellan's sheet needs 5 interest rates for its computation. Here are today's:

Code: Select all

1 Yr  	5 Yr  	7 Yr  	10 Yr 	30 Yr
------	------	------	------	-----
-1.70%	-0.98%	-0.53%	-0.09%	0.93%
For someone considering building a TIPS ladder, I'd suggest first using magellan's spreadsheet since it is quicker and easier to use. Then, if one gets to the stage of actually choosing the TIPS maturities, I'd suggest my own. Here's an extract of what it looks like:
Image
Besides the corrections, I've added some more discussion to the workbook since I first introduced it April 2011 in this post: Re: Grok's Tip #8:Retire Worry-Free with TIPs as a foundation. I welcome any bug reports, suggestions, or comments. In a private message magellan said he liked the idea of a thread to discuss TIPS ladder spreadsheets in general, and our two in particular. So, I'm sure he'd also welcome responses about his.

As a first suggestion, magellan, consider adding a 20 Yr rate. Even though the Treasury no longer issues 20-year TIPS, it still publishes a rate for it. (3)

(1) Click for page to download magellan's file from: Flexible Retirement Planner - Additional Tools
(2) Click for page to download my file from: TIPS Ladder Builder 11/9/2012
(3) Daily Treasury Real Yield Curve Rates

Edit 11/11/2012: File updated for current prices. Because of lower real yields it now costs about $915,000 to build a 30 year X $30,000 ladder.
Last edited by #Cruncher on Sun Nov 11, 2012 9:24 pm, edited 1 time in total.

User avatar
abuss368
Posts: 15504
Joined: Mon Aug 03, 2009 2:33 pm
Location: Where the water is warm, the drinks are cold, and I don't know the names of the players!

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by abuss368 » Fri Mar 30, 2012 11:36 pm

I would rather invest in the simple and effective Vanguard TIPS fund.

Keep investing simple.
John C. Bogle: "Simplicity is the master key to financial success."

User avatar
magellan
Posts: 3469
Joined: Fri Mar 09, 2007 4:12 pm

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by magellan » Sat Mar 31, 2012 6:28 pm

abuss368 wrote:I would rather invest in the simple and effective Vanguard TIPS fund.

Keep investing simple.
Understood. But the strategy that #cruncher and I are thinking about with our ladders is very different from owning a tips fund as part of an investing portfolio.

Although it seems like a TIPS ladder is just a different way to get access to the TIPS asset class, that's not the only way to use a TIPS ladder. In fact, to me a TIP ladder is really a completely different animal that lets you exactly pre-fund essential future expenses as they hit, year-by-year. With a TIPS fund, you're subject to interest rate risk and as the years go by, it may not deliver the exact income you hope it does. With a TIPS ladder, you can be 100% (well 99.9999%) sure that you'll get exactly the income you expect during the period covered by the ladder.

So a TIPS ladder produces guaranteed income to fund expected expenses. It's really like no other investment, except possibly a life annuity that's indexed to inflation. But with the life annuity, you're paying some extra expenses to the insurance company and you're getting longevity insurance. For an ultrasafe retirement income floor, I like the idea of a TIPS ladder for the first 20-30 years, then some type of longevity insurance after that. If there's a bequest motive involved, you might not need any annuity income at all, since you could just fund the TIPS ladder out to age 100-105 and bequest anything that's left over.

Personally, I'd consider a TIPS ladder as being completely separate from the risky investment portfolio and put it in its own bucket, maybe next to SS, pensions, and life annuities. Then, once I've built a floor of income to meet minimum expenses using these guaranteed income generators, I'd layer in a risky portfolio on top of that.

Jim

User avatar
Crystal Ball
Posts: 102
Joined: Wed Jan 21, 2009 7:30 pm

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Crystal Ball » Sun Apr 01, 2012 10:42 am

Thank you both for sharing. I have downloaded both spreadsheets and am off to get to work. I tried to do one myself for my TIPS ladder a few years ago, but couldn't get the interest payments to match the actual amount I received - probably using wrong CPI numbers or something.

The fourth rung of my ladder matures next week so it's time to get back to work on a reinvesting plan.

Thanks,
Crystal
He is richest who is content with the least, for content is the wealth of nature. | Socrates

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

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by #Cruncher » Sun Apr 01, 2012 4:33 pm

Crystal Ball wrote:I have downloaded both spreadsheets and am off to get to work.
Good luck! If anything in mine looks suspicious, don't assume it's your fault. magellan was suspicious of some of the results and it turned out I'd made a miscalculation handling bonds that cover the gap years when no TIPS mature. Handling those gaps is probably the hardest part of building a ladder.
abuss368 wrote:I would rather invest in the simple and effective Vanguard TIPS fund. Keep investing simple.
While a fund definitely would be simpler, it's not the best way to fund a retirement. To elaborate on what magellan has said, I see at least three advantages of a ladder compared to a fund:
  1. If interest rates rise, the net asset value of the fund may fall when you need to make annual withdrawals.
  2. The weighting of maturities in a typical TIPS fund is related to the amount that the Treasury has sold. This has nothing to do with an individual's annual funding requirements. The chart below compares the weighting of maturities for the ladder against the weighting of any TIPS fund that follows the Barclay's TIPS index. The Vanguard fund has done so in the past.
  3. In today's rising interest rate environment, since a ladder gives more weight to later years, the average yield to maturity is higher.
Image

User avatar
tipswatcher
Posts: 301
Joined: Tue Jun 21, 2011 5:17 pm
Contact:

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by tipswatcher » Sun Apr 01, 2012 9:02 pm

Although it seems like a TIPS ladder is just a different way to get access to the TIPS asset class, that's not the only way to use a TIPS ladder. In fact, to me a TIP ladder is really a completely different animal that lets you exactly pre-fund essential future expenses as they hit, year-by-year.
Exactly. I am not a fan of TIPS mutual funds (at least at the moment, maybe later) but I am a HUGE fan of buying and holding TIPS to maturity. You can predict with absolute certainty what your money will be worth in real dollars.

Except, I would say ... not buying TIPS at the moment. Rates are lousy. (Just look back one year.)

I am pretty much doing nothing right now, but awaiting better TIPS rates. Even slightly better. Then I will buy and hold.

If rates really improve, I would do the TIPS mutual fund thing.

Otherwise ... I Bonds up to the limit and wait it out.
TIPS: Perfect investment for imperfect times?

User avatar
Oicuryy
Posts: 1320
Joined: Thu Feb 22, 2007 10:29 pm

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Oicuryy » Sun Apr 01, 2012 9:33 pm

magellan wrote:In fact, to me a TIP ladder is really a completely different animal that lets you exactly pre-fund essential future expenses as they hit, year-by-year.
If people were actually doing that, someone would be selling TIPS STRIPS.

Ron
Money is fungible | Abbreviations and Acronyms

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

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by #Cruncher » Mon Apr 02, 2012 11:38 pm

Oicuryy wrote:
magellan wrote:... a TIP ladder ... lets you exactly pre-fund essential future expenses as they hit, year-by-year.
If people were actually doing that, someone would be selling TIPS STRIPS.
Stripped or zero-coupon TIPS aren't needed. Both magellan's and my spreadsheets take the interest into account so that a constant real amount is collected each year. For example, in my spreadsheet only 15 of the 2025 bonds need to be purchased. When these mature the redemption value is $18,000 in 2012 dollars. But the interest received in 2025 from all the bonds maturing in or after 2025 totals about $12,000 (also in 2012 dollars). These two together provide the $30,000 rung of the ladder.

Where stripped TIPS would be useful is to help fund the "gap" years. For example it would be nice to buy zero-coupon TIPS that matured in 2033 - 2039 instead of having to rely on the 2032 or the 2040 bonds to "cover" these years. I'm not sure why a market for stripped TIPS has never developed. I believe the Treasury is ready to facilitate it with its book entry system should any institutions enter the market.

However, there wouldn't be a lot available for these 7 gap years. Only the 2040, 2041, and 2042 bonds have any coupons that mature then; and the coupons are only 2.125%, 2.125%, and 0.75% respectively. This limited supply might cause the stripped coupons to sell at a premium. And the stripped principal would appeal to investors who want inflation protection coupled with no re-investment risk. Maybe a 30-year TIPS would be worth more in pieces, so the institution doing the stripping could make some money.

However, unless held in a retirement account, the tax calculation would be a bear. The owner would have to report two kinds of Original Issue Discount or OID: that which any zero-coupon bond is subject to and also that which an inflation-adjusted bond accrues.

protagonist
Posts: 5944
Joined: Sun Dec 26, 2010 12:47 pm

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by protagonist » Tue Apr 03, 2012 11:13 am

#Cruncher wrote:[If interest rates rise, the net asset value of the fund may fall when you need to make annual withdrawals.[*]]
If I understand the way bond funds work (which may or may not be the case), if one holds TIP for a duration greater than its effective duration, wouldn't the interest rate risk disappear? That is my (admittedly) limited understanding.......

dbr
Posts: 30336
Joined: Sun Mar 04, 2007 9:50 am

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by dbr » Tue Apr 03, 2012 11:37 am

protagonist wrote:
#Cruncher wrote:[If interest rates rise, the net asset value of the fund may fall when you need to make annual withdrawals.[*]]
If I understand the way bond funds work (which may or may not be the case), if one holds TIP for a duration greater than its effective duration, wouldn't the interest rate risk disappear? That is my (admittedly) limited understanding.......
The arithmetic is that if one holds the investment for its duration after a change in interest rate, the result will be indifferent to the interest rate change. If you hold the investment for duration and the last day the interest rate jumps up, the value will go down and you will be at a loss if you sell then. If there is a complicated pattern of interest rate changes, up and down, you would have to integrate the effect over an extended period of time producing some return that could be more or less than one might have expected at the time the investment was made.

Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 10:36 am

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Bongleur » Thu Apr 05, 2012 2:05 am

Might be useful to include I-bond data in those spreadsheets.
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.

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

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by #Cruncher » Fri Apr 06, 2012 8:30 pm

I've added columns to my spreadsheet to incorporate one's existing TIPS holdings. If you're like me and hold a hodge-podge of maturities, this will let you see how far you are from providing a constant stream of real dollars over the next 30 years. It also has the side-benefit of providing the current market value of your holdings.

I've updated to today's prices. (The WSJ TIPS Quotes web page has finally added the Feb 2042!) It takes about $7,000 more than it did last week to purchase enough TIPS to provide 30 years of $30,000 real withdrawals.
Bongleur wrote:Might be useful to include I-bond data in those spreadsheets.
I thought about that, but couldn't see any good way to systematically integrate I Bonds because of their flexible maturity. But a person can account for them approximately by blanking out cells in the Years to Cover column if those years could be covered by existing I Bond holdings.

User avatar
CaliJim
Posts: 3050
Joined: Sun Feb 28, 2010 8:47 pm
Location: California, near the beach

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by CaliJim » Fri Apr 06, 2012 10:17 pm

#Cruncher wrote:For someone considering building a TIPS ladder, I'd suggest first using magellan's spreadsheet since it is quicker and easier to use. Then, if one gets to the stage of actually choosing the TIPS maturities, I'd suggest my own.
Thank you for sharing this.

Question: In the spreadsheet - there are sometimes several bonds to choose from for a given year.

What criteria did you use to select - say the Jan 5, 2014 bond over the Jul 15, 2014 bond?

TIA
-calijim- | | For more info, click this Wiki

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

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by #Cruncher » Fri Apr 06, 2012 11:53 pm

CaliJim wrote:Question: In the spreadsheet - there are sometimes several bonds to choose from for a given year. What criteria did you use to select - say the Jan [1]5, 2014 bond over the Jul 15, 2014 bond?
I think I arbitrarily chose the one with the highest yield. * This doesn't mean it's the best choice, however, since the market generally has a reason for the prices it sets, and there is rarely a "free lunch". It probably doesn't make too much difference which one you choose. For example, whether you choose the January, April, or July 2014 only makes a $14 difference out of $846,000.

Of more significance, probably, is how you choose to cover gap years when no TIPS mature. I recommend using a TIPS maturity close to the years to be covered and to not to be swayed by the effect on cost or weighted average yield to maturity.

* Just noticed that in my original workbook, I'd chosen the April 2014, not the January. I must have changed it while testing the new feature for existing holdings (I own some January 2014's), and forgotten to change it back when I blanked out my holdings. For consistency with last week's version I've just changed it back to April.

beareconomy
Posts: 461
Joined: Sat Sep 11, 2010 9:01 am

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by beareconomy » Sat Apr 07, 2012 5:06 am

My plan is to retire around 60 and being in my early 30s, my plan with the TIPS ladder is perfect because they offer 30 year maturities now. I do notice there is a big hole between 2032 and 2040 for people who want to employ this strategy. My goal is maintain wealth, and I view the coupon payment as a bonus. Just looking at the stockmarket return of 3% since 2000, it has barely kept up with inflation. I do own the vangaurd s and p in my taxable though because of the low capitol gains tax rate. Also, buying long term TIPS guarantees me the highest possible real yield at the time of purchase.

When these do mature, I may then consider SPIA inflation indexed annuities since I figure at that time I will be in a lower tax bracket. That way, there is always liquidity if someting comes up. But yes, there is that interest rate risk.

FinanceFun
Posts: 722
Joined: Mon Nov 28, 2011 9:29 am

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by FinanceFun » Sat Apr 07, 2012 6:50 am

Really interesting conversation. Thank you for sharing. Currently, I am 32. Would it make sense to build a ladder by buying 30y TIPS every year till retirement. Maximize yield, and end with a full ladder? I could do this with my wife's Roth, so $5k per year for the next 30 years?

User avatar
abuss368
Posts: 15504
Joined: Mon Aug 03, 2009 2:33 pm
Location: Where the water is warm, the drinks are cold, and I don't know the names of the players!

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by abuss368 » Sat Apr 07, 2012 9:43 am

Go with the Vanguard Inflation Protected Bond fund instead for the simplicity.
John C. Bogle: "Simplicity is the master key to financial success."

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

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by #Cruncher » Sat Apr 07, 2012 8:34 pm

FinanceFun wrote:Currently, I am 32. Would it make sense to build a ladder by buying 30y TIPS every year till retirement ... and end with a full ladder?
Sounds good to me, assuming it fits into your overall asset allocation. One benefit would the the dollar-cost-averaging over thirty years rather than buying everything at today's low yields.

However, if by "full ladder", you mean one with a constant real dollar income every year, that would be difficult to get precisely. For one thing, you'd need to adjust future purchases for changes in the CPI. For example, if you buy $5,000 this year and the CPI rises 2% over the next year, you'd need to buy $5,100 the following year to have $5,000 of 2012 dollars.

Even if you did adjust future purchases for the increases in the CPI, you'd still end up with a ladder weighted toward the earlier years. After 30 years you'd have $5,000 of principal in 2012 dollars maturing every year 2042 - 2071. But in 2042 you'd also be collecting interest on 30 TIPS, in 2043 on 29 TIPS, ... and so on until in 2071 you'd only be getting the interest on one TIPS.

This doesn't mean you shouldn't do it. It just means that you won't end up with exactly the same kind of ladder as if you bought all the rungs at once taking into account interest collections as magellan's and my spreadsheets do.

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

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by #Cruncher » Sat Sep 21, 2013 7:47 am

I've just updated my spreadsheet to include the four new TIPS issued in 2013 and to use prices from the WSJ as of 9/20/2013. Based on these prices a 30 year X $30,000 ladder can be built for about $773,000.

For the link to download the updated spreadsheet go to eyebonds.info/downloads/pages/TIPSLadder.html.

learning_head
Posts: 839
Joined: Sat Apr 10, 2010 6:02 pm

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by learning_head » Sat Sep 21, 2013 9:36 am

Thanks a lot for updates #Cruncher!

While this comes close to building a guaranteed income of 30k for life, it does not necessarily... So, what is the plan after years 31+ (for those that may live that long)? Two options that were mentioned:

- build ladder til your age 100 or however long you feel is "safe" - that seems like it could be too expensive though since "unlikely" years of life will require as much investment as "likely" years of life but you still have to fund them.

- longevity insurance was mentioned - is it possible to buy today effectively CPI-adjusted SPIA which would start 30 years from now? If so, it would be interesting to compare CPI-adjusted SPIA price vs ladder + this future insurance price (clearly, ladder has big advantage of leaving more estate money if you die earlier and guaranteed income from uncle Sam, not insurance company)...

User avatar
Raybo
Posts: 1841
Joined: Tue Feb 20, 2007 11:02 am
Location: San Francisco
Contact:

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Raybo » Sat Sep 21, 2013 10:40 am

#Cruncher

It looks to me like the date in your spreadsheet has to be updated every time a TIPS is issued. Is this correct? Also, the new CPI has to be entered.

In addition, I already own a TIPS (the July, 2018 10 year) that I bought in 2008 when the TIPS yield when crazy during the market meltdown. It appears there is a way to represent that in your spreadsheet, but I'm not certain how to do it and what information I need to do it. Can you help me?

Is there some place you've described how to keep the sheet current? If not, could you describe the process of updating it here?

I don't own a TIPS ladder, but would like to investigate it further, especially if rates tick up. I am interested in the numbers your sheet calculates.
No matter how long the hill, if you keep pedaling you'll eventually get up to the top.

User avatar
Tortuga
Posts: 118
Joined: Sat Aug 16, 2008 9:51 pm
Location: Houston, Texas

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Tortuga » Sat Sep 21, 2013 11:40 am

Raybo:

The spreadsheet tab named "Instructions" provides the sources for the numbers needed for updates to the spreadsheet.

Tortuga

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

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by #Cruncher » Sat Sep 21, 2013 11:56 am

The most frequently required update is for the current prices as reported on the WSJ web site. This is important to determine the cost of building the ladder. For example, if one had used prices from April 2013 before the big increase in TIPS yields, the cost to build a $30,000 X 30 year ladder would have been much higher than the $773,000 it is now. As Tortuga mentions, I include notes for doing this on the Instructions sheet.

The spreadsheet uses two dates and two corresponding "Reference CPI" values:
  1. WSJ Quote Date and Ref CPI on WSJ Quote Date: I entered 9/20/2013, the date of the quotes.
  2. Base Date and Ref CPI on Base Date: I entered 10/1/2013 as the base date. This means that the $30,000 desired annual real amount is in constant dollars as of this date. It's not necessary to update this when you update the WSJ prices.
Whatever dates you do update, enter the corresponding Reference CPI values. They can be obtained from eyebonds.info/tips/2013/tips00_2013.html.

Unfortunately, Raybo, adding new TIPS is much more difficult. It took me a couple of hours yesterday to add the four new ones issued in 2013. The next scheduled maturity is the January 2024 to be issued in January 2014. This would need to be added to avoid having to "double up" with a 2023 or 2025 maturity to cover the year 2024. And a month later one would want to add the scheduled new 30-year issue to mature in 2044. I do have some sketchy notes about this on the Instructions sheet. But it is still hard to do and I don't recommend you try. I'll plan to add the two of them myself after the February 2014 auction.

Erwin
Posts: 1929
Joined: Fri Apr 27, 2007 11:16 pm

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Erwin » Sat Sep 21, 2013 1:47 pm

Would you still be that excited if the TIPs ladder resides in a regular, taxable account?
Erwin

User avatar
Doc
Posts: 9214
Joined: Sat Feb 24, 2007 1:10 pm
Location: Two left turns from Larry

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Doc » Sat Sep 21, 2013 5:12 pm

mpt follower wrote:Would you still be that excited if the TIPs ladder resides in a regular, taxable account?
The expected nominal return for a 1+ year TIPS ladder is about 2% nominal. So in the 25% tax bracket you would lose about 50 bps a year on average to taxes.

If the expected return on an S&P 500 fund was 8% and it was all capital gains the annualized effective tax rate would be 8.44% if held for 20 years so you would lose 67 bps a year on average to taxes. If you include state taxes and the fact that some of that return would be dividends taxed at a higher average rate (no deferral) the loss for the equity fund would be even higher.

Having the TIPS ladder in a regular taxable account would be more tax efficient to many of us.

People should stop using tax efficiency "guidelines" that were developed in some bygone (aka pre QE) days.
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.

learning_head
Posts: 839
Joined: Sat Apr 10, 2010 6:02 pm

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by learning_head » Sun Sep 22, 2013 7:10 am

Doc,

(1) I don't think you accounted for the fact that cap gains taxes are paid at the end. TIPS taxes are paid right away (i.e. during the 20 years in your example) when the value of money is much greater, even with your low-inflation assumption.

(2) Unlike TIPS taxes, cap gains can be taken when you want, and can be timed to reduce taxes (maybe even to 0% in some years)

(3) Now, if inflation is not as low as ~2% (*especially* because we are in post-QE world), I think you may find results will be much worse for TIPS in taxable...

P.S. How did you get 67bps from 8.44% for 20 years?

scone
Posts: 1457
Joined: Wed Jul 11, 2012 4:46 pm

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by scone » Sun Sep 22, 2013 9:51 am

Thank you very much, #Cruncher and Magellan, for doing all this work. I had been thinking of a TIPs ladder for part of our retirement income. Unfortunately, the real yields are still so poor, it makes an inflation-adjusted SPIA look good by comparison-- to me at least. Obviously with a SPIA you have solvency risk-- you lose the safety of the government bond. Sigh. Maybe there aren't any great options in the bond world at the moment. Thanks, again.
Last edited by scone on Sun Sep 22, 2013 10:26 am, edited 1 time in total.
"My bond allocation is the amount of money that I cannot afford to lose." -- Taylor Larimore

User avatar
Doc
Posts: 9214
Joined: Sat Feb 24, 2007 1:10 pm
Location: Two left turns from Larry

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Doc » Sun Sep 22, 2013 10:25 am

learning_head wrote:Doc,

(1) I don't think you accounted for the fact that cap gains taxes are paid at the end. TIPS taxes are paid right away (i.e. during the 20 years in your example) when the value of money is much greater, even with your low-inflation assumption.

Yes, I did. That is why the annualized tax rate is only ~8% not 15%.

(2) Unlike TIPS taxes, cap gains can be taken when you want, and can be timed to reduce taxes (maybe even to 0% in some years)

We look at annualized returns over long time periods to make long term estimates of portfolio choices. Any short term benefits that persist from tax loss harvesting get wrapped up in the long term average tax rate. To get more lower taxes through capital losses you need to reduce the assumed 8% return. You can't have your cake and eat it too. Any short term improvement from tax loss harvesting effectively increases the term of the investment and is accounted for in the lower effective annualized rate through the assumed 20 year term if the short term losses don't decrease the average pretax return..

If FI returns increase in the future and make bonds less tax efficient you can always reverse your position with little or no tax consequences. If you have equities in taxable and you want/need to change in the future you have to pay the taxes earlier and your effective annualized tax rate is not ~8% but nearer to the statutory 15% rate.


(3) Now, if inflation is not as low as ~2% (*especially* because we are in post-QE world), I think you may find results will be much worse for TIPS in taxable...

You are falling into the trap of trying to reduce taxes instead of maximizing after tax returns. Sometimes you get the same answer using either method but not always. In this case you should enjoy extra after tax return you got from TIPS with higher than expected tax rates not lament that higher income you also have to pay tax on these unexpected extra returns.

P.S. How did you get 67bps from 8.44% for 20 years?
You annualize the after tax returns for your assumed pretax return over the period on years that you assume for the investment. By comparing this after tax annualized return with the original pretax return you can calculate the effective annualized tax rate. This procedure eliminates the problem with trying to use a spread sheet to get the result because it eliminates the problems associated with rebalancing. The equations are basically the zero coupon and coupon bond equations on an after tax basis and have been discussed on this broad many times in the past

This thread is about TIPS but under the current QE environment even TBM is less tax efficient than an S&P 500 index fund for many people around the 25% tax bracket especially if they have state income taxes.
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.

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

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by #Cruncher » Sat Jul 26, 2014 9:03 am

I've just updated my spreadsheet to include all four TIPS issued in 2014 and to use prices from the WSJ as of 7/25/2014. Based on these prices a 30 year X $30,000 ladder can be built for about $828,000 with a weighted average yield-to-maturity of +0.4%.

For the link to download the updated spreadsheet go to eyebonds.info/downloads/pages/TIPSLadder.html.

Erwin
Posts: 1929
Joined: Fri Apr 27, 2007 11:16 pm

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Erwin » Sat Jul 26, 2014 9:14 am

#Cruncher wrote:I've just updated my spreadsheet to include all four TIPS issued in 2014 and to use prices from the WSJ as of 7/25/2014. Based on these prices a 30 year X $30,000 ladder can be built for about $828,000 with a weighted average yield-to-maturity of +0.4%.

For the link to download the updated spreadsheet go to eyebonds.info/downloads/pages/TIPSLadder.html.
Not criticizing, just asking: Is +0.4% a good deal?
Erwin

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

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by #Cruncher » Sat Jul 26, 2014 10:13 am

mpt follower wrote:Not criticizing, just asking: Is +0.4% a good deal?
It's better than the -0.1% average yield for the 1+ index of all TIPS maturing in more than one year. (See yesterday's update to my thread, Consistent Yield & Duration to Help Choose TIPS Fund.) *

However, it's not a good deal when compared to historical TIPS yields. For this reason, I'm not building a 30-year ladder myself. I'm keeping my average TIPS duration about 7 years. However, for someone at retirement age, even a low yield like this may be considered good if the ladder will produce enough cash flow to meet one's requirements. To wait for higher yields entails some risk that yields will fall even lower, and then one might not be able to lock in a sufficient cash flow.

By the way, since I don't work at the Federal Reserve, you don't have to worry that I'll think you're criticizing me for low interest rates. :wink:

* It's better because a ladder is weighted toward the longer term and currently longer term TIPS have higher yields. The reason it's weighted toward the longer term is that the proceeds each year include the interest coupons on all the bonds maturing in the future. For instance, in 2015 one would collect about $13,000 in interest from the bonds maturing 2016 - 2044; and therefore one only needs to buy $17,000 of TIPS maturing in 2015.

dbr
Posts: 30336
Joined: Sun Mar 04, 2007 9:50 am

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by dbr » Sat Jul 26, 2014 10:21 am

#Cruncher wrote: However, it's not a good deal when compared to historical TIPS yields. For this reason, I'm not building a 30-year ladder myself. I'm keeping my average TIPS duration about 7 years. However, for someone at retirement age, even a low yield like this may be considered good if the ladder will produce enough cash flow to meet one's requirements. To wait for higher yields entails some risk that yields will fall even lower, and then one might not be able to lock in a sufficient cash flow.
Yep, you can't asset allocate out of the hand history deals you.

Erwin
Posts: 1929
Joined: Fri Apr 27, 2007 11:16 pm

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Erwin » Sat Jul 26, 2014 10:03 pm

#Cruncher wrote:
mpt follower wrote:Not criticizing, just asking: Is +0.4% a good deal?
It's better than the -0.1% average yield for the 1+ index of all TIPS maturing in more than one year. (See yesterday's update to my thread, Consistent Yield & Duration to Help Choose TIPS Fund.) *

However, it's not a good deal when compared to historical TIPS yields. For this reason, I'm not building a 30-year ladder myself. I'm keeping my average TIPS duration about 7 years. However, for someone at retirement age, even a low yield like this may be considered good if the ladder will produce enough cash flow to meet one's requirements. To wait for higher yields entails some risk that yields will fall even lower, and then one might not be able to lock in a sufficient cash flow.

By the way, since I don't work at the Federal Reserve, you don't have to worry that I'll think you're criticizing me for low interest rates. :wink:

* It's better because a ladder is weighted toward the longer term and currently longer term TIPS have higher yields. The reason it's weighted toward the longer term is that the proceeds each year include the interest coupons on all the bonds maturing in the future. For instance, in 2015 one would collect about $13,000 in interest from the bonds maturing 2016 - 2044; and therefore one only needs to buy $17,000 of TIPS maturing in 2015.
I am retired so your work (thank you for the effort) does pertain to me. Here are my questions:

1. Since 0.4% of $848,000 is only a bit over $3,000 a year, I guess that I will need to eat up capital to meet my cash flow needs. Right?
2. Even if I decide to do that, the ladder does not have a bond expiring every year. So how can I physically meet the annual expenses?
3. And the bigger issue. TIPs can only be held in IRAs, otherwise you end up paying phantom taxes. So, I assume that unless you find a way to hold them in an IRA (remember RMD), the strategy is not good. Correct?
Please help me understand your logic.
Erwin
Erwin

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

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Artsdoctor » Sun Jul 27, 2014 7:13 am

^ Erwin,

When you consider building a TIPS ladder for retirement, you are assuming that you will spend the bond at the maturity date. You would not be expected to live off of the coupons. The cash flow each year is made up of the maturing bond and the coupons generated from the longer bonds as well.

The fact that some years have no maturing TIPS creates a suboptimal situation. You have a few options but none of them are perfect. For example, there's a long stretch between 2033 and 2039 where there are no maturing TIPS; you can bunch those years in the 2032 bond, you can combine the income for those years in both the 2032 and the 2040 bonds, but no option ideal and you will take some risk no matter what you choose.

Regarding the RMDs, yes, you are correct. If you consider building a TIPS ladders, think about how you're going to get the money out of your tax-advantaged account(s). Having a TIPS ladder secures a "floor" for your retirement income; if your floor requirements are more than your IRA can handle, you'll need to find money elsewhere to supplement your TIPS ladder. Everyone's situation is completely different but most would find that holding TIPS in a taxable account to be onerous from a bookkeeping point of view and expensive from a tax point of view.

dbr
Posts: 30336
Joined: Sun Mar 04, 2007 9:50 am

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by dbr » Sun Jul 27, 2014 8:21 am

Artsdoctor wrote:^ Erwin,

When you consider building a TIPS ladder for retirement, you are assuming that you will spend the bond at the maturity date. You would not be expected to live off of the coupons. The cash flow each year is made up of the maturing bond and the coupons generated from the longer bonds as well.
This is an important and sometimes misunderstood point. Living on interest from a conservative investment is risky because yield is variable and can be too low to meet needs. That would be one of the key ideas in understanding why it is income and not wealth that matters. The concept of an LMP uses the misleading word portfolio when in fact the investments, in this case a TIPS ladder, are being used in a completely different way than they are in what we think of as an investment portfolio. Specifically they are targeted to be liquidated according to a plan that is intended from the beginning to leave the investments completely consumed. This plan takes the flaw of the SWR approach and embraces it to the ultimate. The virtue in doing this is that uncertainty is replaced by certainty, which is a definition of eliminating risk. Of course, longevity risk is not managed by this approach on its own.

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

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by #Cruncher » Sun Jul 27, 2014 8:38 am

Erwin, to expand on Artsdoctor's and dbr's answers ...
  1. Yes, you will "eat up capital". That's the main idea of this type of ladder.
  2. Yes, the gaps in TIPS maturities do cause a problem, particularly the one from 2033-2039. The default entries in my spreadsheet show one option: buy enough of the 2040 to cover the years 2037-2040 and enough of the 2032 to cover the years 2032 - 2036. An alternative would be buy double the amounts for 2023 - 2029. When these mature, spend half and use the other half to buy 10-year TIPS maturing in 2033-2039. Unfortunately my spreadsheet doesn't help with this approach.
  3. Yes, it is better to hold TIPS in an IRA. (a) The required minimum distribution beginning at age 70-1/2 from a traditional IRA likely won't be a problem. Here is a possible withdrawal schedule for a TIPS ladder covering ages 66-95: (b)

    Code: Select all

         Starting    $30K     Life (c)             Excess
    Age   Balance  Withdraw  Expectancy   RMD      vs RMD
    ---   -------  --------  ----------  -----     ------
    66      900      3.3%                           3.3%
    67      870      3.4%                           3.4%
    68      840      3.6%                           3.6%
    69      810      3.7%                           3.7%
    70      780      3.8%        27.4     3.6%      0.2%
    71      750      4.0%        26.5     3.8%      0.2%
    72      720      4.2%        25.6     3.9%      0.3%
    73      690      4.3%        24.7     4.0%      0.3%
    74      660      4.5%        23.8     4.2%      0.3%
    75      630      4.8%        22.9     4.4%      0.4%
    76      600      5.0%        22.0     4.5%      0.5%
    77      570      5.3%        21.2     4.7%      0.5%
    78      540      5.6%        20.3     4.9%      0.6%
    79      510      5.9%        19.5     5.1%      0.8%
    80      480      6.3%        18.7     5.3%      0.9%
    81      450      6.7%        17.9     5.6%      1.1%
    82      420      7.1%        17.1     5.8%      1.3%
    83      390      7.7%        16.3     6.1%      1.6%
    84      360      8.3%        15.5     6.5%      1.9%
    85      330      9.1%        14.8     6.8%      2.3%
    86      300     10.0%        14.1     7.1%      2.9%
    87      270     11.1%        13.4     7.5%      3.6%
    88      240     12.5%        12.7     7.9%      4.6%
    89      210     14.3%        12.0     8.3%      6.0%
    90      180     16.7%        11.4     8.8%      7.9%
    91      150     20.0%        10.8     9.3%     10.7%
    92      120     25.0%        10.2     9.8%     15.2%
    93       90     33.3%         9.6    10.4%     22.9%
    94       60     50.0%         9.0    11.1%     38.9%
    95       30    100.0%         8.4    11.9%     88.1%
    In no year does the RMD force one to withdraw more than the planned amount.
  1. By the way I think the main problem with holding a TIPS ladder in a taxable account is that the after-tax real proceeds depend on the level of inflation. While, if the ladder is held in an IRA (either traditional or Roth), one can predict the after-tax real proceeds regardless of how much the CPI rises. My post, Re: LMP at age 50 (help with TIPS Ladder), has a table that shows an example of this.
  2. For simplicity I'm assuming the market value of the ladder equals the remaining years times the annual withdrawal. If interest rates are positive, the market value will be less than this, reducing the RMD.
  3. Life expectancy for ages 70-90 is from this Vanguard web page. I guessed the figures for age 91-95.

Erwin
Posts: 1929
Joined: Fri Apr 27, 2007 11:16 pm

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Erwin » Sun Jul 27, 2014 8:43 am

Thank you ALL!
Erwin

mindbogle
Posts: 140
Joined: Sun Feb 10, 2013 11:28 am

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by mindbogle » Sat Aug 02, 2014 11:53 am

#Cruncher: nice job on your very useful ladder spreadsheet. One question though - wouldn't it be useful to most users to be able to vary the target amount by year to account for planned one-time purchases and income step-changes associated with SS and/or annuity income? Like in the "target income" column below? Thanks, MB

Image

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

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by #Cruncher » Sat Aug 02, 2014 8:00 pm

mindbogle wrote:#Cruncher: nice job on your very useful ladder spreadsheet.
Thank you, MB. I'm glad you find it useful.
mindbogle wrote:One question though - wouldn't it be useful to most users to be able to vary the target amount by year to account for planned one-time purchases and income step-changes associated with SS and/or annuity income?
That's a good suggestion. A step-down in the annual amount required when Social Security kicks in, would be fairly common. Being able to handle it on the spreadsheet would indeed be useful.

I've been planning to make a change to the spreadsheet for another reason. But after reading your post, I see that the change would also handle doing what you suggest. My plan is to eliminate the "Years to Cover" column and replace it with a "Multiplier" column. Here's how it could handle your example:

Code: Select all

Maturity    Target Income   Years to Cover    Multiplier
--------    -------------   --------------    ----------
Feb 2044        40,000           2044             1
Feb 2043        40,000           2043             1
Feb 2042        40,000           2042             1
Feb 2041        70,000           2041             1.75
Feb 2040        40,000        2037-2040           4
Apr 2032        40,000        2032-2036           5
Apr 2029        40,000        2029-2031           3
Apr 2028        40,000           2028             1
Jan 2027        40,000           2027             1
Jan 2026        40,000           2026             1.75
Jan 2025        40,000           2025             1.75
The "Multiplier" column does double duty. For the 2029 - 2040 maturities it indicates that more bonds are to be purchased to cover "gap" years. For the Feb 2041 maturity it indicates an extra amount is needed to cover a special expense. And for 2026 and earlier years, it indicates that more is needed since SS hasn't yet started. This assumes that one has entered $40,000 as the "Desired Annual Real Amount" in cell B2 since $40,000 X 1.75 is $70,000.

My original reason for making this change was to allow the user to "double up" on some earlier years with the intent to roll half over into 10-year TIPS to fund the gap years. For example one could enter a multiplier of 2 for TIPS maturing in 2020-2021 and 2023-2029. The spreadsheet would then compute double the normal amount to be purchased. The idea is that, when they mature, half would be spent and the other half used to buy 10-year TIPS maturing in the gap years of 2030-2031 and 2033-2039.

I don't know when I'll get around to doing this change. Until then a workaround to handle your situation would be:
  1. Do all years 2015-2044 with a $40,000 annual amount. Save the results.
  2. Enter $30,000 as the annual amount.
  3. Blank all cells in the "Years to Cover" column from 2027 - 2044, to produce a ladder for only 2015-2026.
  4. Combine the results for 2015-2026 from each run.

mindbogle
Posts: 140
Joined: Sun Feb 10, 2013 11:28 am

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by mindbogle » Sat Aug 02, 2014 8:55 pm

Thanks #Cruncher.

As for the work-around, I just redirected the formula references from $B$2 to the target income column that I added. But then I got to thinking that you do such a great job of updating the spreadsheet with new bonds that it would be easier to maintain if I didn't have to make the redirection changes myself each time I download your spreadsheet. That is why I decided to check with you (and users) to see if my suggestion would appeal.

In the past, I developed my own simple model similar to Magellan's, that feeds off of the treasury TIPS yield curve and doesn't account for TIPS bond details and gaps such as your model does. This was fine for the purposes that I was using it for, but now that I am contemplating actual implementation, I think I need something with more practical nuts and bolts - yours fits that bill. I could be wrong but I would think that if someone is going to make the effort to get familiar with using your model, then it would seem like only fractionally more to fill out a target income column like in my snapshot above, as oppose to the rather limiting $B$2 constant income method, which has little value to me for ladder implementation. But that's how I see it - you and others might feel differently.

As for your contemplated change to a multiplier - I don't know all of the reasons why you are considering this but I rather like your current "years to cover" feature. That along with my added target column makes for a very natural way to match up the required income stream to available bonds.

Thanks again,

MB

mindbogle
Posts: 140
Joined: Sun Feb 10, 2013 11:28 am

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by mindbogle » Sun Aug 03, 2014 10:53 am

While I am at it, just fyi as a usage case.... to best meet my needs, I reordered the ladder from earliest to latest to mature, and then added a birthdate input in $B$2 and an age column with formula that depends on $B$2 and column O. Thanks again for the excellent work.

MB

Image

Erwin
Posts: 1929
Joined: Fri Apr 27, 2007 11:16 pm

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Erwin » Sun Aug 03, 2014 2:21 pm

mindbogle wrote:While I am at it, just fyi as a usage case.... to best meet my needs, I reordered the ladder from earliest to latest to mature, and then added a birthdate input in $B$2 and an age column with formula that depends on $B$2 and column O. Thanks again for the excellent work.

MB

Image
Can you share your speeadsheet?
Erwin

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

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by #Cruncher » Sun Aug 03, 2014 3:44 pm

mindbogle in [url=http://www.bogleheads.org/forum/viewtopic.php?p=2142464#p2142464]this post[/url] wrote:As for your contemplated change to a multiplier - I don't know all of the reasons why you are considering this but I rather like your current "years to cover" feature.
I liked the "Years to Cover" column also, MB. But replacing it with a "Multiplier" column makes the spreadsheet more flexible. For one thing it allows the purchase of different bonds maturing in the same year. And it allows a user to build a ladder of uneven amounts as you described in this post. *

But primarily it allows the user to "Double Up" in purchasing some maturities with the intention of rolling half into new 10-year TIPS when they mature; as explained in my previous post. A user might want to do this ...
  1. So TIPS will mature in each year and not be bunched in years like 2032 and 2040 before and after a gap.
  2. To avoid buying so many longer term TIPS in the current low interest rate environment.
I've added a sheet to the workbook, "Ladder-Dbl", that illustrates this. It builds a ladder with double purchases of TIPS maturing in 2020-2021 & 2023-2029. The idea is that when these mature half will be rolled over into 10-year TIPS maturing in the gap years of 2030-2031 and 2033-2039.

It shows the cost of $848,000 for a $900,000 ladder compared to a cost of $819,000 for a ladder that bunches extra TIPS purchases into the 2029, 2032, and 2040 maturities. Corresponding to the higher cost, it has a lower weighted average yield of 0.29% compared to 0.47%. This is to be expected since it purchases shorter term bonds on average. (Both costs and yields based on 8/1/2014 prices from the WSJ.)

The new Excel file can be downloaded from the same web page: eyebonds.info/downloads/pages/TIPSLadder.html.

* In your modification you show $70,000 / year required before Social Security kicks in and $40,000 afterward. One could handle this by entering $40,000 as the annual amount, 1.75 as the multiplier for the years before SS, and 1 for the years afterward. Or one could enter $1,000 as the annual amount, 70 for the multiplier for the years before SS, and 40 for the years afterward. (Larger multipliers would handle buying extra to cover gap years or to "double up".)

Edit 11:50 PM: $2,000 increase in ladder costs after bug fix. Everyone: Please let me know If you encounter any more bugs in the new spreadsheet.

mindbogle
Posts: 140
Joined: Sun Feb 10, 2013 11:28 am

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by mindbogle » Tue Aug 05, 2014 9:20 pm

MPT follower wrote:
Can you share your speeadsheet?
Erwin, is there a to share on this forum?

MB

Erwin
Posts: 1929
Joined: Fri Apr 27, 2007 11:16 pm

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Erwin » Tue Aug 05, 2014 10:50 pm

#Cruncher wrote:
mindbogle in [url=http://www.bogleheads.org/forum/viewtopic.php?p=2142464#p2142464]this post[/url] wrote:As for your contemplated change to a multiplier - I don't know all of the reasons why you are considering this but I rather like your current "years to cover" feature.
I liked the "Years to Cover" column also, MB. But replacing it with a "Multiplier" column makes the spreadsheet more flexible. For one thing it allows the purchase of different bonds maturing in the same year. And it allows a user to build a ladder of uneven amounts as you described in this post. *

But primarily it allows the user to "Double Up" in purchasing some maturities with the intention of rolling half into new 10-year TIPS when they mature; as explained in my previous post. A user might want to do this ...
  1. So TIPS will mature in each year and not be bunched in years like 2032 and 2040 before and after a gap.
  2. To avoid buying so many longer term TIPS in the current low interest rate environment.
I've added a sheet to the workbook, "Ladder-Dbl", that illustrates this. It builds a ladder with double purchases of TIPS maturing in 2020-2021 & 2023-2029. The idea is that when these mature half will be rolled over into 10-year TIPS maturing in the gap years of 2030-2031 and 2033-2039.

It shows the cost of $848,000 for a $900,000 ladder compared to a cost of $819,000 for a ladder that bunches extra TIPS purchases into the 2029, 2032, and 2040 maturities. Corresponding to the higher cost, it has a lower weighted average yield of 0.29% compared to 0.47%. This is to be expected since it purchases shorter term bonds on average. (Both costs and yields based on 8/1/2014 prices from the WSJ.)

The new Excel file can be downloaded from the same web page: eyebonds.info/downloads/pages/TIPSLadder.html.

* In your modification you show $70,000 / year required before Social Security kicks in and $40,000 afterward. One could handle this by entering $40,000 as the annual amount, 1.75 as the multiplier for the years before SS, and 1 for the years afterward. Or one could enter $1,000 as the annual amount, 70 for the multiplier for the years before SS, and 40 for the years afterward. (Larger multipliers would handle buying extra to cover gap years or to "double up".)

Edit 11:50 PM: $2,000 increase in ladder costs after bug fix. Everyone: Please let me know If you encounter any more bugs in the new spreadsheet.
I have started using your spreadsheet and consider the column "multiplier" key (assuming I understand its use). I have an uneven annual need, meaning that stating my "desired annual real amount" does not entirely do it for me. So what I began doing is that I do set my average annual need during the 30 years in this place, but then in the multiplier column, and as needed, I adjust the multiplier number for each year to match as closely as possible my annual requirements. Question: that is possible? right?
Erwin

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

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by #Cruncher » Wed Aug 06, 2014 9:02 am

mpt follower wrote:So what I began doing is that I do set my average annual need during the 30 years in this place [ cell B2, "Desired Annual Real Amount" ], but then in the multiplier column, and as needed, I adjust the multiplier number for each year to match as closely as possible my annual requirements. Question: that is possible? right?
Yes, that is OK. The spreadsheet simply multiplies the value in cell B2 by the multiplier to determine the amount needed for any indicated maturity. [ 1 ] For example, $30,000 X 1 or $20,000 X 1.5 or $1,000 X 30 would all be equivalent. An advantage of making B2 the average annual amount over 30 years is that the total of the multipliers should add up to 30. This serves as a check that you've entered values correctly.

However, even though the spreadsheet supports it, I wouldn't get carried away with precision. I don't think there is a need to match spending requirements "as closely as possible". [ 2 ] The unknowns [ 3 ] over a 30 year period are so big that, in my opinion, it is overkill to try for precision in year-to-year spending requirements. The flexibility of multipliers other than 1 is intended for three main uses:
  • Buying extra amounts of 2029, 2032, or 2040 maturities to cover the "gap" years of 2030-2031 and 2033-2039 when no TIPS mature.
  • Buying extra in some years with the intent to roll over some of the redemption proceeds into new 10-year bonds.
  • Handle major changes in requirements like the reduction that occurs when Social Security benefits kick in.
  1. Actually the spreadsheet computes the requirement cumulatively. E.g., Assume B2 equals $30,000 and 1 is entered as the multiplier for both the 2044 and 2043 maturities. The spreadsheet computes that $60,000 is needed for 2044 and 2043 combined (30000 X 2). If, because of rounding, $29,600 was computed for 2044, the spreadsheet will calculate the number of bonds needed to provide $30,400 for 2043. This reduces the cumulative rounding error.
  2. For example, I wouldn't bother trying to estimate the years of new car purchases.
  3. Some unknown are
    • When a spouse will die.
    • Future medical spending including nursing homes.
    • Taxes. If the ladder is in a traditional IRA you are subject to an unknown tax rate at the time of withdrawal. If the ladder is in a taxable account, your after-tax real proceeds are also subject to the rate of inflation.

Erwin
Posts: 1929
Joined: Fri Apr 27, 2007 11:16 pm

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Erwin » Wed Aug 06, 2014 12:22 pm

#Cruncher wrote:
mpt follower wrote:So what I began doing is that I do set my average annual need during the 30 years in this place [ cell B2, "Desired Annual Real Amount" ], but then in the multiplier column, and as needed, I adjust the multiplier number for each year to match as closely as possible my annual requirements. Question: that is possible? right?
Yes, that is OK. The spreadsheet simply multiplies the value in cell B2 by the multiplier to determine the amount needed for any indicated maturity. [ 1 ] For example, $30,000 X 1 or $20,000 X 1.5 or $1,000 X 30 would all be equivalent. An advantage of making B2 the average annual amount over 30 years is that the total of the multipliers should add up to 30. This serves as a check that you've entered values correctly.

However, even though the spreadsheet supports it, I wouldn't get carried away with precision. I don't think there is a need to match spending requirements "as closely as possible". [ 2 ] The unknowns [ 3 ] over a 30 year period are so big that, in my opinion, it is overkill to try for precision in year-to-year spending requirements. The flexibility of multipliers other than 1 is intended for three main uses:
  • Buying extra amounts of 2029, 2032, or 2040 maturities to cover the "gap" years of 2030-2031 and 2033-2039 when no TIPS mature.
  • Buying extra in some years with the intent to roll over some of the redemption proceeds into new 10-year bonds.
  • Handle major changes in requirements like the reduction that occurs when Social Security benefits kick in.
  1. Actually the spreadsheet computes the requirement cumulatively. E.g., Assume B2 equals $30,000 and 1 is entered as the multiplier for both the 2044 and 2043 maturities. The spreadsheet computes that $60,000 is needed for 2044 and 2043 combined (30000 X 2). If, because of rounding, $29,600 was computed for 2044, the spreadsheet will calculate the number of bonds needed to provide $30,400 for 2043. This reduces the cumulative rounding error.
  2. For example, I wouldn't bother trying to estimate the years of new car purchases.
  3. Some unknown are
    • When a spouse will die.
    • Future medical spending including nursing homes.
    • Taxes. If the ladder is in a traditional IRA you are subject to an unknown tax rate at the time of withdrawal. If the ladder is in a taxable account, your after-tax real proceeds are also subject to the rate of inflation.
Thank you again. You right, no point in getting very precise. But the reason that I feel the need to look into it is to account for MRD begining at age 71. The issue is as follows: the TIP ladder that matches my annual needs is not consistent with the MRD (smaller need) and I am yet not sure how to solve the issue, unless I only build a TIP ladder with part of the money and leave the rest in a mutual fund that I can use to cover the additional IRA withdrawal requirements beyond the TIP expiring that year. Do you have any other ideas?
Erwin

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

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by #Cruncher » Thu Aug 07, 2014 10:35 am

mpt follower wrote:The issue is as follows: the TIP ladder that matches my annual needs is not consistent with the [ Required Minimum Distribution (RMD) ] (smaller need)...
I'm having trouble following you, Erwin. Are you saying that the annual proceeds from the TIPS ladder would be less than the RMD for some years? The rough calculation I made in item 3 of this post above shows that the TIPS proceeds would be more than the RMD every year. Can you provide the figures that lead you to your conclusion?

Erwin
Posts: 1929
Joined: Fri Apr 27, 2007 11:16 pm

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Erwin » Fri Aug 08, 2014 6:28 am

#Cruncher wrote:
mpt follower wrote:The issue is as follows: the TIP ladder that matches my annual needs is not consistent with the [ Required Minimum Distribution (RMD) ] (smaller need)...
I'm having trouble following you, Erwin. Are you saying that the annual proceeds from the TIPS ladder would be less than the RMD for some years? The rough calculation I made in item 3 of this post above shows that the TIPS proceeds would be more than the RMD every year. Can you provide the figures that lead you to your conclusion?
Sorry for not responding earlier. I was unavailable.
Now I think that I know what to do. Much thanks for your help!
Erwin

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

Re: TIPS Ladder Spreadsheets in General & Two in Particular

Post by Kevin M » Sun Sep 27, 2015 8:24 pm

In case anyone is interested, I loaded #Cruncher's TIPS spreadsheet into Google Sheets, and although it works fine as is, I tweaked it to automate loading the WSJ TIPS quotes. 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. Here are the steps to do the tweak (hopefully not forgetting something).
  • In the WSJ sheet, modify cell C2 to eliminate the parentheses and HYPERLINK function, so only the bare URL remains:

    Code: Select all

    http://online.wsj.com/mdc/public/page/2_3020-tips.html?mod=mdc_bnd_pglnk
    Note that a bare URL works as a hyperlink in Google Sheets, although here it will be used as a parameter in a function call instead.
  • Unmerge cells C4:I5 (the header rows)
  • Delete cells C4:I44.
  • Enter this function in cell C5:

    Code: Select all

    =IMPORTHTML(C2,"table",3)
    This imports the latest version of the TIPS table from the WSJ page specified by URL in cell C2. Note that the header rows also are imported, which is why cells C4:I5 were unmerged and deleted.
  • Format column C as Date.
  • Modify cells B6:B44 to simply copy the value from cells C6:C44. For example, value in cell B6 is

    Code: Select all

    =C6
    Comment: #Cruncher uses a formula to convert the date strings in column C to dates in column B, but this is unnecessary with Google Sheets, even if the values are copied/pasted. Does it not work this way in Excel?
I think that's it. As far as I can tell, everything works fine with this modification.

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, or this cell could be coded to parse the Date of WSJ Quotes and create the URL with the appropriate date. For example, the URL for 9/15/2015 looks like this:

Code: Select all

http://online.wsj.com/mdc/public/page/2_3020-tips-20150915.html?mod=mdc_pastcalendar
Also, I don't think it would be too hard to automate pulling the reference CPI values from either #Cruncher's web page or from some other source that might have them in more convenient format (suggestions?).

The entire ref CPI table for 2015 can be loaded from #Cruncher's page into a new sheet with this function:

Code: Select all

=IMPORTHTML("http://eyebonds.info/tips/2015/tips00_2015.html","table",1)
I think a VLOOKUP call could be coded to pull the reference CPI based on month and day of date. Yes, this works. Here's an example, with reference date in cell D1 and the IMPORTHTML function in cell A3, this pulls the ref CPI for the month and day for the ref date:

Code: Select all

=VLOOKUP(DAY($D$1),$A$4:$L$34,MONTH($D$1)+1,1)
Now if I could just convince #Cruncher to do his updates in a Google Sheets version, these enhancements could be carried forward into the updates ;-)

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

Post Reply