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,$dots$\mathrm{.}64-$th birthdays, and that the

final retirement wealth is determined on the investor's $65-$th birthday. ${?}^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:

Compute the annual real return on the $\frac{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 $\frac{50}{50}$mix of stocks and T$-$bills

over this time period.

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. ${?}^2$ Assuming the $92$ historical returns are located in the

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

$=$INDEx$(H12$:$H103,$ 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$ Ising the simulated return nath, compute the investor's wealth at age $65.$

${?}^2$ 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.

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 $$1m$ 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{.}5m$ on average? How do the standard deviation and the minimum

savings change in this case relative to the baseline scenario of a $\frac{50}{50}$ mix? $/$Hint: $(1)$ Ta

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

$!!!!!!!$

I NEED THE excel equations for this We are given the DATE $($A$1),$ T$-$bills $($B$1),$ stocks $($C$1),$ and CPI $($D$1)$