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!]

PROF. DAVID SCHREINDORFER FIN 421 - SPRING 2016 Assignment 2 Due: Wednesday, 01.27.16 In this assignment you will use a Monte Carlo simulation to investigate the eect 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 $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 dierent return realizations. As explained below, you will generatedreturns using two alternative Monte Carlo simulation techniques. On Blackboard, you will nd an Excel le 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 ination. 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. 1 The rst payment is added at the end of \"year 24\" and rst earns returns in \"year 25\". The last payment is added at the end of \"year 64\" and rst earns returns in \"year 65\" 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 ecient way of doing so in Excel is to use a data table. An example is contained in the Excel le \"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, 25th and 75th 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 Specically, 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, 25th and 75th percentiles, minimum, maximum as well as a histogram of the 1, 000 values you generated for the wealth at age 65. Do these statistics dier substantially from the results of the rst 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 contribution. [Hint: (1) To nd 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 nd will only be approximate because of simulation noise - that is ok!.] iii Think about advantages and disadvantages of the two simulation approaches (resampling vs. log-normal draws). For example, are there situations where one approach may work better than the other? [Hint: No need to hand part iii in!] 3 We will simulate log returns rather than gross returns because normally distributed variables take on values on the whole real line. Gross returns are bounded below by zero, i.e. they cannot take on numbers lower than this threshold. If we simulate gross returns from a normal distribution, we may get some returns that don't make sense because they are negative. 2/2 Assumptions yearly savings portfolio weight Historical net returns Year 1 1928 2 1929 3 1930 4 1931 5 1932 6 1933 7 1934 8 1935 9 1936 10 1937 11 1938 12 1939 13 1940 14 1941 15 1942 16 1943 17 1944 18 1945 ... ... 86 2013 $10,000 stocks bills 50% 50% Stocks 0.44 0.08 0.25 0.44 0.09 0.50 0.01 0.47 0.32 0.35 0.29 0.01 0.11 0.13 0.19 0.25 0.19 0.36 ... 0.32 TBills 0.03 0.03 0.05 0.02 0.01 0.01 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 ... 0.00 CPI -0.01 0.01 -0.06 -0.09 -0.10 0.01 0.02 0.03 0.01 0.03 -0.03 0.00 0.01 0.10 0.09 0.03 0.02 0.02 ... 0.02 50/50 0.01 0.01 0.07 0.10 0.11 0.01 0.01 0.03 0.01 0.03 0.03 0.00 0.01 0.09 0.08 0.03 0.02 0.02 ... 0.01 Resampled returns & portfolio value Age Return Wealth Path 25 0.00 0 26 0.00 0 27 0.00 0 28 0.00 0 29 0.00 0 30 0.00 0 31 0.00 0 32 0.00 0 33 0.00 0 34 0.00 0 35 0.00 0 36 0.00 0 37 0.00 0 38 0.00 0 39 0.00 0 40 0.00 0 41 0.00 0 42 0.00 0 ... ... ... 64 0.00 #REF! Sim # 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 ... 1000 Final Wealth #REF! 703,367 899,547 834,304 1,006,138 541,642 739,156 1,100,466 398,118 619,513 1,908,123 678,391 931,348 841,644 434,752 1,072,540 1,516,353 536,715 ... Summary Stats mean #REF! std #REF! 25 pct #REF! 75 pct #REF! min #REF! max #REF! Histogram #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 Year 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 Stocks 0.44 -0.08 -0.25 -0.44 -0.09 0.50 -0.01 0.47 0.32 -0.35 0.29 -0.01 -0.11 -0.13 0.19 0.25 0.19 0.36 -0.08 0.05 0.06 0.18 0.31 0.24 0.18 -0.01 0.53 0.33 0.07 -0.10 0.44 0.12 0.00 0.27 -0.09 0.23 0.16 0.12 -0.10 0.24 0.11 -0.08 0.04 0.14 0.19 -0.14 -0.26 0.37 0.24 -0.07 0.07 0.19 0.32 -0.05 0.20 0.22 0.06 0.31 0.18 0.06 0.17 0.31 -0.03 0.30 0.07 0.10 0.01 0.37 0.23 0.33 0.28 0.21 -0.09 -0.12 -0.22 0.28 0.11 0.05 0.16 T-Bills 0.03 0.03 0.05 0.02 0.01 0.01 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.01 0.01 0.01 0.01 0.01 0.02 0.02 0.01 0.02 0.03 0.03 0.02 0.03 0.03 0.02 0.03 0.03 0.04 0.04 0.05 0.04 0.05 0.07 0.07 0.05 0.04 0.07 0.08 0.06 0.05 0.05 0.07 0.10 0.11 0.14 0.11 0.08 0.10 0.07 0.06 0.06 0.06 0.08 0.08 0.06 0.03 0.03 0.04 0.06 0.05 0.05 0.05 0.05 0.06 0.04 0.02 0.01 0.01 0.03 0.05 CPI -0.01 0.01 -0.06 -0.09 -0.10 0.01 0.02 0.03 0.01 0.03 -0.03 0.00 0.01 0.10 0.09 0.03 0.02 0.02 0.18 0.09 0.03 -0.02 0.06 0.06 0.01 0.01 -0.01 0.00 0.03 0.03 0.02 0.02 0.01 0.01 0.01 0.02 0.01 0.02 0.03 0.03 0.05 0.06 0.06 0.03 0.03 0.09 0.12 0.07 0.05 0.07 0.09 0.13 0.13 0.09 0.04 0.04 0.04 0.04 0.01 0.04 0.04 0.05 0.06 0.03 0.03 0.03 0.03 0.03 0.03 0.02 0.02 0.03 0.03 0.02 0.02 0.02 0.03 0.03 0.03 80 81 82 83 84 85 86 2007 2008 2009 2010 2011 2012 2013 0.05 -0.37 0.26 0.15 0.02 0.16 0.32 0.05 0.02 0.00 0.00 0.00 0.00 0.00 0.04 0.00 0.03 0.01 0.03 0.02 0.02 CONSTRUCTING A HISTOGRAM These bins are constructed to lie between the minimum and maximum of the data values. some numbers -0.135 1.613 -0.387 3.185 -4.849 -1.831 -0.061 4.742 3.444 0.857 3.803 3.654 5.919 -4.717 1.076 -1.227 -3.699 5.748 0.824 2.467 -3.821 2.223 -1.165 0.848 3.201 0.199 2.279 3.008 -0.813 1.563 0.223 1.227 0.204 1.806 -2.238 -3.006 3.256 4.117 -4.365 -3.896 3.372 -1.030 -1.725 0.006 -1.006 1.046 -1.825 0.688 -1.053 1.578 -0.524 -0.133 -1.684 -2.449 -0.091 1.459 5.458 5.155 4.349 3.628 2.044 1.403 0.805 -2.502 1.675 -0.747 2.630 1.737 -1.043 -2.726 1.155 1.752 -4.129 stats min max -5.909 6.086 FREQUENCY is an array function! Select the output cells (here: H8:H16), enter the formula, then press SHIFT, CTRL, ENTER bins frequency -5.909 1 -4.576 3 -3.244 5 -1.911 6 -0.578 18 0.755 15 2.088 22 3.421 12 4.754 10 6.086 8 25 20 15 10 5 0 -5.909 -4.576 -3.244 -1.911 -0.578 0.755 2.088 3.421 4.754 6.086 4.850 0.112 -0.546 4.297 -4.648 6.086 -0.799 0.627 -1.831 -1.456 2.678 -1.899 4.443 -5.909 3.840 0.881 3.344 -1.414 0.975 -1.238 4.999 2.250 -2.850 -0.025 4.805 1.696 1.124 Monte Carlo Simulation Example Steps [1] generate a random sample (B14:B18; note that when you press F9, Excel generates new random numbers) [2] compute a statistic of interest based on the sample (B21; in the example, I'm computing the sample mean) [3] additional simulations of the statistic [a] make a column containing a counter that indexes the simulation (E14:E23) [b] in the cell next to the counter value 1 (F14), reference the cell containing the statistic (B21) [c] select the cells containing the counter and the cells to their right (E14:F23) [d] click DATA -> What-If-Analysis -> Data Table [e] keep "Row input cell" empty and reference an arbitrary cell not containing a calculation (e.g. E13) as "Column input cell" [1] random sample 0.4984 0.5601 0.1339 0.7264 0.9462 [2] statistic 0.5730 [3] additional simulations 1 0.5730 2 0.5730 3 0.5730 4 0.5730 5 0.5730 6 0.5730 7 0.5730 8 0.5730 9 0.5730 10 0.5730 annual savings = 10000 log return net return wealth 1 0.040 0.040 10404 2 0.055 0.057 20994 3 -0.071 -0.068 29559 4 0.100 0.105 42674 5 0.201 0.222 62169 6 0.307 0.359 94509 7 0.077 0.080 112112 8 -0.017 -0.017 120217 9 0.065 0.067 138231 10 0.063 0.065 157264 11 0.164 0.178 195213 12 0.070 0.072 219311 13 0.069 0.071 244961 14 0.043 0.044 265640 15 0.009 0.009 278142 16 0.000 0.000 288128 17 0.041 0.041 310072 18 -0.002 -0.002 319323 19 0.146 0.157 379343 20 0.209 0.232 477483 21 -0.016 -0.015 480089 22 0.084 0.088 532383 23 -0.041 -0.040 520911 24 0.008 0.008 535231 25 -0.193 -0.176 451187 26 -0.142 -0.133 401375 27 -0.041 -0.041 395095 28 -0.107 -0.102 364843 29 -0.045 -0.044 358951 30 0.004 0.004 370373 31 -0.169 -0.156 322705 32 0.012 0.012 336613 33 0.079 0.082 374180 34 -0.140 -0.131 335183 35 -0.115 -0.108 308843 36 0.148 0.159 367969 37 0.111 0.117 421088 38 0.156 0.169 502384 39 -0.020 -0.020 502528 40 0.193 0.213 619509 David Schreindorfer: You'll need to use the numbers computed in step C.1 instead of 0.04 and 0.1 David Schreindorfer: This is part of step C.2, i.e. we convert the log returns to net returns. David Schreindorfer: Use this as the input of your data table (the first cell in the table) to do the Monte Carlo simulation