New York University



Solution to Retirement Planning[1]

Amanda has 30 years to save for her retirement. At the beginning of each year, she puts $5000 into her retirement account. At any point in time, all of Amanda's retirement funds are tied up in the stock market. Suppose the annual return on stocks follows a normal distribution with mean 12% and standard deviation 25%. What is the probability that at the end of 30 years, Amanda will have reached her goal of having $1,000,000 for retirement? Assume that if Amanda reaches her goal before 30 years, she will stop investing.

We start with this spreadsheet model:

[pic]

• The annual investment activities (columns A-D, beginning in row 5) actually extend down to row 35, to include 30 years of simulated returns.

• The range C6:C35 will be random numbers, generated by Crystal Ball.

• We could track Amanda’s simulated investment performance either with cell F5 (simply =D35, the final amount in Amanda’s retirement account), or with F4 (the maximum amount over 30 years). Using F4 allows us to assume that she would stop investing if she ever reached $1,000,000 at any time during the 30 years, which is the assumption given in the problem statement.

• Cell H1 is either 1 (she made it to $1 million) or 0 (she didn’t). Over many trials, the average of this cell will be out estimate of the probability that Amanda does accumulate $1 million. This will be a Crystal Ball forecast cell.

We define an assumption for the first year’s percent return on her investment:

[pic]

This cell can be copied and pasted to the other 29 yearly percent returns.

We define three forecasts:

|[pic] |[pic] |

[pic]

As an added touch, we create a graph showing the amount of money in Amanda’s retirement account during the simulation (this adds little to our understanding, but it’s fun to watch):

[pic]

We run the simulation for 1,000 trials, and get the following results:

|[pic] |[pic] |

|[pic] |[pic] |

|[pic] |[pic] |

It looks like Amanda has about a 48% chance of meeting her goal of $1 million in 30 years.

-----------------------

[1] Based on 12-18 (p. 666-667) in Practical Management Science (2nd ed., Winston and Albright, 2001 Duxbury Press). Solution by David Juran, 2002.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download