Saving for Retirement
Assume an investor begins saving for retirement at age 25 and retires at age 65. Each

year, she
contributes $5,000 to her retirement account and her employer matches this contribution for a
total of $10,000 in annual savings. To keep things simple, assume that each of the 40 annual
payments is added to the account at the end the calendar year. 1 Savings are invested as follows:
50% in a broad stock market index and 50% in T-Bills.
Your task is to compute the accumulated real retirement savings (at age 65) for different return
realizations. As explained below, you will generatedreturns using two alternative Monte Carlo
simulation techniques. On Blackboard, you will find an Excel file containing historical net
returns on the S&P 500, 3-month T-bills and the CPI from 1928 to 2013. The return on the
CPI serves as a measure of inflation.
STEPS:
1. Compute the annual real return on the 50/50 portfolio for each year in the sample. The
resulting set of 86 portfolio returns represents the empirical distribution. These are the
returns investors historically realized when investing in a 50/50 mix of stocks and T-bills
over this time period.
2. We will use the historical data to access what may happen in the future via a Monte Carlo
simulation. To generate a possible path of future returns, draw 40 times with replacement
from the empirical distribution. 2 Assuming the historical returns are located in the cell
range H11:H96, a random draw can be generated with
=INDEX(H11:H96,RANDBETWEEN(1,86))
The set of 40 draws you generated can be viewed as one scenario of what may happen in
the next 40 years.
3. Using the simulated return path, compute the investor’s wealth at age 65

4. Repeat steps two and three 1,000 times. The most efficient way of doing so in Excel is

to use a data table. An example is contained in the Excel file ”Monte Carlo Simulation

Example” on Blackboard.

QUESTIONS:

A Report the mean and standard deviation of the portfolio returns computed in step one.

B Report the mean, standard deviation, 25 th and 75 th percentiles, minimum, maximum as well

as a histogram of the 1,000 values you generated for the wealth at age 65. What do these

numbers mean in the context of the example?

C Next, you will repeat the analysis using an alternative Monte Carlo simulation technique.

Instead of drawing returns from the empirical distribution (step 2), assume that log returns

follow a normal distribution and simulate from this distribution. 3 Specifically, replace

steps one and two above with the following:

1. Compute the annual real log return of the 50/50 portfolio for each year in the sample.

Next compute the sample mean and standard deviation of these returns.

2. Generate a path of returns by drawing 40 times from the normal distribution with

mean and standard deviation equal to the sample moments computed in the previous

step. Assuming the sample mean and sample variance are located in the cells I3 and

I4 respectively, a random draw can be generated with

= NORMINV(RAND(),I3,I4)

Convert the log returns to net returns before continuing the analysis with step three.

i Report the mean, standard deviation, 25 th and 75 th percentiles, minimum, maximum

as well as a histogram of the 1,000 values you generated for the wealth at age 65.

Do these statistics differ substantially from the results of the first approach? [Hint:

They should not.]

ii Assuming that the investor chooses a 50/50 mix of the two assets, what amount would

she need to save annually such that her real retirement wealth at at least $1m with

a probability of 75%? Assume that the employer matches the investor’s contribu-

tion. [Hint: (1) To find this number, create a cell for the annual savings (sum of

the investor’s and her employers contributions) and use trial-and-error to determine

the required amount. (2) The number you find will only be approximate because of

simulation noise – that is ok!.]

iii Think about advantages and disadvantages of the two simulation approaches (re-

sampling vs. log-normal draws). For example, are there situations where one ap-

proach may work better than the other? [Hint: No need to hand part iii in!]

 

Looking for a Similar Assignment? Order now and Get 10% Discount! Use Coupon Code "Newclient"