Income Stream Allocator

I have built a spreadsheet to help with income portfolios.

Corrections

My original "Income Stream Allocator" failed to adjust the TIPS principal to match inflation. As a result, the examples that I have provided show what happens when using CDs or money market funds.

I have replaced my original "Income Stream Allocator" with "CD Income Stream Allocator A," which replaces the word "TIPS" with "CD" and which identifies the CD interest rate as NOMINAL, not adjusted for inflation.

I have added "TIPS Income Stream Allocator B," which does what the original version was supposed to do.

Components

I simulate the dividend income from Stock A, the income stream from Investment B and the interest from TIPS. I do not allow any new purchases or sales from Stock A and/or Investment B. Any surplus or deficit adds to or comes from the TIPS portfolio.

Typically, Stock A would consist of individual dividend stocks and/or dividend focused Exchange Traded Funds (ETF). Typically, Investment B would be a high income investment such as REITS or a fixed annuity. Typically, TIPS would be laddered to avoid the need of making sales.

All yields, interest rates, inflation rates and growth rates are constant. This simplifies the calculations considerably at a reduction in realism.

Constraints

I constrain withdrawals from Stock A and Investment B to lie between zero and the income that each produces. Any surplus is added to the TIPS account. There are never any additions or reductions to Stock A and Investment B.

I have left TIPS unconstrained. If you enter a negative withdrawal to TIPS, it becomes a deposit. You can limit the total withdrawal during the early years by making TIPS deposits as opposed to adjusting withdrawals individually from Stock A and Investment B.

Sample Calculation

I have set up the spreadsheet with a $100000 initial amount, a Stock A allocation of 50% and an Investment B allocation of 40%. I set inflation at 3.0% and the TIPS interest rate at 2.5%.

For Stock A, I started with a 4.0% initial yield, which is high but reasonable using individual stocks. I set its dividend growth rate to 5.0% nominal, which is low but matches that of the S&P500.

For Investment B, I started with an 8.0% initial yield with a 0% growth rate, which might be from a lifetime annuity.

By varying my TIPS withdrawals, I was able to maintain a withdrawal rate above 5.0% in terms of purchasing power for 24 years. At that point, the withdrawal amount fell to $4747 (plus inflation), a real withdrawal rate of 4.747%, at year 25. It grew back above 5.0% to $5017 (plus inflation) at year 35.

This is the starting point. After this, it is time to vary allocations to meet your individual needs.

For older retirees, especially couples, I suggest looking at the income stream produced by changing the Stock A allocation to 20% and the Investment B allocation to 70%. This might lead to some useful ideas.

Yahoo Briefcase

You can download this spreadsheet from my Yahoo Briefcase. It is in the Income Stream Allocator file in the Allocators folder.

UPDATE

I have added a Microsoft Word Document that shows the spreadsheet (truncated) with several allocations to my Yahoo Briefcase.

The trick is to adjust the TIPS withdrawals (and deposits) first. Then change allocation percentages. This allows you to get the Real Withdrawal Amounts right. Later allocations will be very close to optimal.

January 30, 2007 UPDATE

I have replaced the original Income Stream Allocator with CD Income Stream Allocator A" and "TIPS Income Stream Allocator B."

Yahoo Briefcase

Have fun.

John Walter Russell
January 29, 2007
Updated: January 30, 2007