Retirement Trainer Overview

This is my first excursion into Monte Carlo modeling. I have built a couple of Retirement Trainers. They include a copy of the Stock-Return Predictor. They automatically adjust stock market returns in accordance with valuations.

I have built two copies of the Stock-Return Predictor for Years 10, 20 and 30 into the Retirement Trainer. You start by entering the initial P/E10 into the first predictor in rows 15 through 21. You use the second predictor in rows 24 through 30 to help you decide on each year’s allocation.

The instructions are at the top in rows 4 through 12.

Put in each year's total withdrawal amount and the amount taken from the stock portfolio. You can see each year’s starting value of P/E10 and all portfolio balances before making a final decision. Put a “1” into column L to proceed to the next year.

See how the numbers vary. Imagine how you would feel after each year. Learn what to expect.

Set Up Details

You start by entering the initial P/E10 into cell C15.

Because of my limited programming knowledge, it is necessary for you to scroll down to the lower section in rows 72 through 110. Press function key F9 repeatedly until cells I80 through I110 include values other than zero. Then copy cells I80 through I110. Paste Special their VALUES into cells J80 through J110.

If you make a mistake, just press F9 a few more times until another series of nonzero numbers appear. If you do not have F9 on your computer, highlight an unused (blank) cell and press “delete.” It has the same effect.

What happens is that =RAND() generates new, independent random numbers (uniformly distributed between 0 and 1) in cells B80 through B110. I use =NORMINV(value,mean,standard deviation) to convert these random numbers into a normal distribution (actually, log normal) of annual returns. I take the mean from cell M19, the most likely return of stocks at Year 30 in accordance with the initial value of P/E10. I use the single-year 20% standard deviation of the S&P500.

I convert these into Gain values (Gain=balance at the end of a period/balance at the beginning of the period=1+return), total gain (which is the balance at the end of N years/initial balance, which equals the product of gains from years 1 through N), annualized gain (which is the Nth root of the total gain after N years) and annualized return (which is the annualized gain minus 1).

At this point, I introduce a sequence selection approach inspired by Raddr’s approach to simulating Mean Reversion. My version has three screens. The first accepts a sequence if it meets either of two constraints: its return at Year 10 is within the inner confidence limits at Year 10 OR its return is within the outer confidence limits at Year 10 but within the inner confidence limits at Year 20. The second screen requires that the return at Year 20 falls within the outer confidence limits at Year 20. The third screen is that the return at Year 30 falls within the outer confidence limits at Year 30.

NOTE: The outer confidence limits correspond to 5% and 95% probabilities. They define the Lower Bound and Upper Bound of stock returns. The inner confidence limits correspond roughly to 20% and 80% probabilities. They define the Unlucky and Lucky levels of return.

NOTE: Rob Bennett made an important discovery related to Mean Reversion. He discovered that sequences that are far from the Most Likely returns at Year 10 come close to the Most Likely return at Year 20.

I take the selected returns in column J and add 1 to convert them to single-year Gains (or Gain Multipliers). I multiply the first N single-year gains to calculate the cumulative gain multiplier at year N.

I calculate the effect of a steady (real) growth of the stock market returns in column M. I convert the initial value of P/E10 into a current value of P/E10 by assuming a constant relationship to stock returns. To do this, I multiply the initial P/E10 (in cell C15) by the ratio of the cumulative multiplier and the (real 6.8%) cumulative long-term return of the stock market.

Cells N79 through N110 have each year’s value of P/E10. I copy this onto cells O37 through O67. [I do not copy N110. It is the value of P/E10 at the end of Year 30. I display the values of P/E10 at the beginning of each year.]

I copy each year’s stock gain (1+the return for that year) from cells K80 through K110. I list the gains of TIPS in cells Q80 through Q110. At this time, I use a single value, gain=1+the interest rate in cell C32 (converted to a fraction by dividing by 100).

Calculation Details

The actual calculations are very simple. You start by entering your initial balance into cell C37 and the initial balance of your stock holdings into cell D37. The difference is the initial balance of your TIPS portfolio.

You enter each year’s Total amount Withdrawn in column F. You enter each year’s total amount withdrawn From Stocks in column G. I subtract this from the initial stock balance in column D, which defines the Remaining Stocks balance. Similarly, whatever withdrawal amount remains after subtracting the amount from stocks, is the From TIPS amount in column I. I subtract this from the TIPS portfolio (column E), which leaves the Remaining TIPS balance in column J.

NOTE: A negative withdrawal is a deposit. To make a deposit, enter a negative number. To transfer money from your stock account to your TIPS account, enter a negative number into From Stocks in column G. The sum of columns G and I equals the Total Withdrawn in column F, where all values may be positive or negative.

I identify whether the sum of the balances from the stock portfolio and the TIPS portfolio are greater than zero. You can cover a deficit in one account with funds from the other.

To advance one year, enter “1” into column L. I multiply the next year’s value of P/E10, Initial Stocks, Initial TIPS, Remaining Stocks and Remaining TIPS (columns B, D, E, H and J) by the number in column L.

WARNING: I have not placed any constraints on entries for column L. If you enter something other than “1” or “0”, you will end up with meaningless numbers for the following year.

Downloading Details

I have placed this calculator (Retirement Trainer B) into my Yahoo Briefcase. I made a new Retirement Trainer folder. It is available for downloading by Everyone. My Yahoo Username is jwr19452000.

UPDATE: I have added Retirement Trainer C. Both versions make the same calculations. For the C version, I updated my instructions to make them easier to understand. I relocated sections to make them easier to use correctly.

Yahoo Briefcase

Have Fun

Play with this Retirement Trainer. Have fun. Enjoy yourself. Discover how well you react to the numbers.

Have fun.

John Walter Russell
August 25, 2006
Updated: August 26, 2006