In this assignment, you will use a Monte Carlo simulation to investigate the effect of randomness

in returns on an individual's future retirement wealth.

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

Using the simulated return path, compute the investor's wealth at age $65.$