<FOOLISH FOUR PORTFOLIO>

Retirement Planning, 6
A spreadsheet to play with

by Ann Coleman (TMF [email protected])

Reston, VA (July 12, 1999) -- We've been talking about Retirement Planning for a while now, and the subject has certainly been a popular one. Today, I offer a spreadsheet that you can download and see how a dynamic investing and retirement strategy looks year by year.

(See the caveats below for the link to download it.)

This spreadsheet is something like those retirement/investing calculators that you see "everywhere." On the first page you enter a starting value and annual contributions, and out pops an ending value. (Actually, you scroll down to see what the value is at various points in the future.) On the second page, you can enter an ending value and see how long it will last given various withdrawal rates and inflation adjustments.

The big difference is that I have designed it to use real world investing returns. Every other calculator I've seen asks you to assume an average rate of return. As we discussed on Friday, the Average Rate of Return only applies to past returns. In the real world, an average rate of return that may turn out to be perfectly valid in the future, given a long enough time span, may not apply to an actual portfolio for rather long time periods.

For example: The CAGR for the Standard & Poor's 500 Index for the past 38 years (1961-1998) was 12.32%, but for the 10-year period from 1965 through 1974 it was 1.24%. That was the Foolish Four's worst 10-year period as well. It averaged only 5.7% per year. And there were several 20-year spans in that 38 year period (all in the early part) where the average was below 10%. I am not suggesting that this will happen again, nor am I suggesting that it won't. But there are times when the market does not boom along at 20%+ a year and it would be (small f) foolish to forget that.

I'm not trying to scare anyone -- really -- but assuming a particular rate of return can be dangerous to your retirement plans. Especially if you start out with a really bad year or two. In the same way that you need to plan to live longer than is likely, I think you need to plan for the possibility of bad times after you retire.

Illustrating that is what I had in mind when I set up this spreadsheet. Instead of asking you to assume a particular rate of return, you select a series of annual returns from a Returns database on the third page and copy that column into the Accumulation or Payout pages. These are real word investing returns from the S&P, the Dow, and the Foolish Four.

You can see that as long as you start with the 1961, things are fine. In fact, you end up with so much money in your estate that it's embarrassing. Obviously you can take out much more money once your portfolio grows to beyond what you could possibly need to support yourself. I don't see this as a problem: There are lots of worthy causes, and this old world can use some help if you are ever in that enviable situation.

But try this: Copy the S&P returns starting with 1973 into the top of your returns column. That changes things a bit. With a 5% withdrawal rate and 2% inflation adjustment, you don't run out of money, but it is 11 years before your portfolio regains its original value.

Far from scaring you, I hope that looking at worst-case scenarios and seeing how they play out in virtual time will actually help you feel better about a retirement plan that includes a healthy proportion of your cash in the stock market. It's very instructive to watch want happens when a couple of bad years hit. The recovery is also very instructive, and, I hope, comforting.

Thursday we will look at real worst-case scenarios and see if stock investing is even remotely viable if something like a 1929 crash hits.

Now for a few caveats -- in fact, a whole bunch of them. You will need Excel 5 or higher to run this spreadsheet. Sorry -- it only comes in one flavor. Also, please keep in mind that this is an amateur spreadsheet. It is a slightly dressed up version of something I use myself to play around with. It's not goof-proof. If you have never used a spreadsheet before, you might find it wonderfully exciting or you might find it hopelessly useless. In either case, I will be happy to answer questions about the strategy, but when it comes to learning Excel, I will have to refer you to various books which, thankfully, are not in short supply. We also have a message board for questions about spreadsheets that can be very helpful. I will check in there and help with the questions.

Caveats, Part Deux: I have not included taxes in the calculations. I am assuming that the funds in question are all in a tax-deferred or tax-free account. There are limits to what can be accumulated in an IRA that I have not addressed, nor have I addressed the IRS's minimum distribution rules. Both of those issues will have to wait for a later date.

Third set of caveats: Nothing in the spreadsheet, from the returns to the inflation factor, is cast in concrete. If you think 2% is too low -- change it. If you think we are headed for a global crash, use the returns starting in 1929 (I've included them) and see what happens. If you think the withdrawal rate is absurd, change it. I'm not making any predictions here, and my assumptions are not necessarily right or applicable to anyone else. The important thing is to think seriously about what assumptions are appropriate for you.

OK, if you've read all the caveats, you can download the spreadsheet by clicking here.

Fool on and prosper!

Today's Stock Lists | 1999 Dow Returns

07/12/99 Close

Stock Change Last -------------------- CAT -3/16 61.00 JPM +5/16 140.56 MMM -1 1/4 88.31 IP +1 3/4 53.50

Day Month Year History FOOL-4 +0.37% +2.26% 28.61% 30.52% DJIA +0.07% 2.10% 22.78% 22.29% S&P 500 -0.30% 1.92% 14.40% 14.67% NASDAQ -0.09% 3.90% 27.26% 29.01% Rec'd # Security In At Now Change 12/24/98 24 Caterpillar 43.08 61.00 41.60% 12/24/98 9 JP Morgan 105.51 140.56 33.22% 12/24/98 22 Int'l Paper 43.55 53.50 22.85% 12/24/98 14 3M 73.57 88.31 20.04% Rec'd # Security In At Value Change 12/24/98 24 Caterpillar 1034.00 1464.00 $430.00 12/24/98 9 JP Morgan 949.62 1265.06 $315.44 12/24/98 22 Int'l Paper 958.12 1177.00 $218.88 12/24/98 14 3M 1030.00 1236.38 $206.38 Dividends Received $49.99 Cash $28.26 TOTAL $5220.69