Saving for Retirement

Assume an investor begins saving for retirement at age $25$ and retires at age $65.$ Each year, she

contributes $$10,000$ to her retirement account. To keep things simple, assume that there are $40$

annual contributions that occur on the investor$$s $25-$th$,26-$th$,...,64-$th birthdays, and that the

final retirement wealth is determined on the investor$$s $65-$th birthday$\mathrm{.}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 generate returns using a Monte Carlo simulation. On

Canvas, you can find an Excel file containing historical net returns on the S&P $500$ and $3-$month

T$-$bills, as well as the consumer price index $($CPI$)$ from $1926$ to $2017.$ 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 $92$ 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 assess 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$\mathrm{.}2$ Assuming the $92$ historical returns are located in the

cell range H$12$:H$103,$ a random draw can be generated with

$=$INDEX$($H$12$:H$103,$RANDBETWEEN$(1,92))$

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.$

$1$Assume that no additional contribution is made on the $65-$th birthday.

$2$Recall that this procedure is valid under the assumption that returns are independently and identically

distributed $($i$.$i$.$d$.).$ In other words, we assume that each of the return realizations computed in step one represents

an equally likely draw from the same distribution of possible returns.

$1/2$

$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 of this was illustrated in Lecture $2.$

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 retirement savings at

age $65.$ Interpret each of these statistics, i$.$e$.$ explain in words what they tell you

in the context of the example.

C Assuming a $50/50$ mix of both assets, what amount would the investor need to save annually

such that her retirement savings are at least $$1$m with a probability of $75\%?[$Hint: $(1)$

To find this number, create an input cell for the annual savings 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$!.(3)$ Goal$-$seek or solver will not work in this context.$]$

D Assuming annual savings of $$10,000,$ what mix of the two assets ensures that the investor$$s

savings amount to $$1\mathrm{.}5$m on average? How do the standard deviation and the minimum

savings change in this case relative to the baseline scenario of a $50/50$ mix?$[$Hint: $(1)$ To

find the necessary mix, create an input cell for the asset mix 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$!.(3)$ Goal$-$seek or solver will not work in this context.$]$