Part 1: Market Timing

This question explores uncertainty in investing. Assume you plan to invest in a broad-basedequity index. You start with a zero balance account. Each year, you plan to contribute an extra$10,000 at year-end. (Assume annual compounding and uncorrelated market returns from yearto year.)a. If you invest (and contribute) for 30 years and the equity index return each year isnormally distributed with expected return of 10% and standard deviation 20% (i.e., adifferent realized return each year), what is the expected value of your investmentaccount in 30 years (just after your last payment)?b. What is the likelihood that you end up with less than $600,000 in 30 years (i.e., twicewhat you contributed)?c. How does the expected balance and likelihood change if you move your money to cash inyears where the realized index return in the previous year was negative? (Note: You stillmake a contribution to your account every year, but your allocation to the market is zeroin years where the prior year realized market return was negative.)d. Let?s explore the assumption of uncorrelated market returns. The accompanyingspreadsheet provides annual realized market excess returns. Run the following regressionof current year returns on last-year returns (you?ll need to lag the returns one year to formthe x-variable):???????????????????? = ???? + ?????????????1???????????? + ????What is the estimate and t-statistic for the coefficient ????? What is the estimate and tstatisticfor the intercept ????? Please interpret both economically and statistically.(see page 2 for Part 2)Part 2: Option PricingWith @Risk, we now have the tools to price derivatives. Recall that a call option is the right, butnot the obligation, to buy a stock at a predetermined strike price K. The payoff of a call optionas a function of the stock price at maturity is max(???????? ? ????, 0):Similarly, a put option is the right, but not the obligation, to sell a stock at a predetermined strikeprice K. The put option payoff as a function of the stock price at maturity is max(???? ? ????????, 0):In this question, we will simulate stock prices and find the values of call and put options usingsimulation and using the Black-Scholes formula. A common way to simulate stock prices is byassuming future stock prices are log-normal. In particular, the (random) future stock price is:???????? = ????0exp[(???????? ? 0.5????2) ? ???? + ????????? ? ???? ]where ???????? is the risk-free rate, t is the time to maturity of the option (expressed in years), and ???? isthe annual volatility. z is a standard normal variable, and this will be the source of randomnessin the model.a. The first step in the model is to draw z using RiskNormal(0,1).b. Using @Risk, create a formula that calculates a random future stock price 3 months inthe future (t=0.25 years) using the expression above and the parameters in theaccompanying spreadsheet. Define this cell as an @Risk output.PayoffStock Price (????????)SSSSSS(S(eiCallKPayoffStock Price (????????)Put OptionKc. A call option pays off only if the stock price at maturity is greater than the strike price, K.Its payoff function is: max(???????? ? ????, 0). Create a cell with the call option payoff formulain 3-month?s time and define this as an @Risk output.d. A put option pays off only if the stock price at maturity is less than the strike price, K. Itspayoff function is: max(???? ? ????????, 0). Create a cell with the put option payoff formula in 3-month?s time and define this as an @Risk output.Simulate the future stock price using 5000 iterations.e. What is the probability that the future stock price is above the strike price (i.e., the calloption is in the money)?f. Paste images of the distributions of the future stock price, the call payoff, and the putpayoff.g. What is the expected payoff of the call at maturity?h. Calculate the current call price by discounting the average future call payoff (its expectedvalue) to the present at the risk-free rate:????????????????0 = exp(????????? ? ????) ? ???????????????????????????????? ???????????????????????? ???????????????? ????????????????????i. What is the expected payoff of the put at maturity?j. Calculate the current put price by discounting the average future put payoff (its expectedvalue) to the present at the risk-free rate:????????????0 = exp(????????? ? ????) ? ???????????????????????????????? ???????????????????????? ???????????? ????????????????????Now let?s calculate the Black-Scholes call price. The Black-Scholes formula for a call option is:????????????????0 = ????0????(????1) ? ???? ? exp(????????? ? ????) ????(????2)where ????1 = [ln (????0????? ) + (???????? + 0.5 ? ????2) ? ????]?????????? and ????2 = ????1 ? ?????????. ????(?) denotes the normalcumulative distribution, which can be evaluated in Excel using the NORM.S.DIST(?,1) function.Similarly, the Black-Scholes formula for a put option is:????????????0 = ???? ? exp(????????? ? ????) ????(?????2) ? ????0????(?????1)k. What are the Black-Scholes call and put prices? How do they compare to the call andoption prices calculated from simulated stock prices?l. What is the value of ????(????2)? How does it compare to the probability that the simulatedstock price is above the strike price (from (e))?

Year Mkt-RF 1927 0.2960 1928 0.3545 1929 -0.1925 1930 -0.3117 1931 -0.4527 1932 -0.0973 1933 0.5672 1934 0.0322 1935 0.4476 1936 0.3206 1937 -0.3492 1938 0.2833 1939 0.0286 1940 -0.0712 1941 -0.1038 1942 0.1615 1943 0.2806 1944 0.2093 1945 0.3833 1946 -0.0671 1947 0.0296 1948 0.0107 1949 0.1911 1950 0.2883 1951 0.1921 1952 0.1180 1953 -0.0105 1954 0.4934 1955 0.2375 1956 0.0591 1957 -0.1316 1958 0.4346 1959 0.0976 1960 -0.0146 1961 0.2481 1962 -0.1290 1963 0.1784 1964 0.1254 1965 0.1052 1966 -0.1351 1967 0.2449 1968 0.0879 1969 -0.1754 1970 -0.0649 1971 0.1178 1972 0.1305 1973 -0.2624 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 2007 2008 2009 2010 2011 2012 2013 -0.3574 0.3245 0.2189 -0.0827 0.0103 0.1308 0.2212 -0.1813 0.1066 0.1375 -0.0606 0.2491 0.1012 -0.0387 0.1155 0.2049 -0.1395 0.2917 0.0623 0.0821 -0.0411 0.3121 0.1597 0.2597 0.1946 0.2056 -0.1760 -0.1520 -0.2276 0.3075 0.1072 0.0309 0.1060 0.0104 -0.3835 0.2827 0.1739 0.0047 0.1629 0.3521 Inputs Risk-free rate (rate) Volatility (sigma) Time (t) Current Stock Price (S) Strike Price (K) @Risk Calculations Random Normal Future Stock Price Future Call Value Future Put Value Expected Future Stock Price Expected Future Call Value Discounted Call Value Expected Future Put Value Discounted Put Value Probability that future price is above strike Analytical Black-Scholes Price d1 d2 N(d1) N(d2) Call Price Put Price 4% 30% 0.25 $100 $110 Spreadsheet Assignment 2: Simulation NOTE: For any @RISK output, please copy and paste as an image or paste as values in adjacent cells. Please also display your primary formulas using FORMULATEX. Part 1: Market Timing This question explores uncertainty in investing. Assume you plan to invest in a broad-based equity index. You start with a zero balance account. Each year, you plan to contribute an extra $10,000 at year-end. (Assume annual compounding and uncorrelated market returns from year to year.) a. If you invest (and contribute) for 30 years and the equity index return each year is normally distributed with expected return of 10% and standard deviation 20% (i.e., a different realized return each year), what is the expected value of your investment account in 30 years (just after your last payment)? b. What is the likelihood that you end up with less than $600,000 in 30 years (i.e., twice what you contributed)? c. How does the expected balance and likelihood change if you move your money to cash in years where the realized index return in the previous year was negative? (Note: You still make a contribution to your account every year, but your allocation to the market is zero in years where the prior year realized market return was negative.) d. Let's explore the assumption of uncorrelated market returns. The accompanying spreadsheet provides annual realized market excess returns. Run the following regression of current year returns on last-year returns (you'll need to lag the returns one year to form the x-variable): = + 1 + What is the estimate and t-statistic for the coefficient ? What is the estimate and tstatistic for the intercept ? Please interpret both economically and statistically. (see page 2 for Part 2) Part 2: Option Pricing With @Risk, we now have the tools to price derivatives. Recall that a call option is the right, but not the obligation, to buy a stock at a predetermined strike price K. The payoff of a call option as a function of the stock price at maturity is max( , 0): Call Payoff K Stock Price ( ) SSSSSS(S(ei Similarly, a put option is the right, but not the obligation, to sell a stock at a predetermined strike price K. The put option payoff as a function of the stock price at maturity is max( , 0): Put Option Payoff K Stock Price ( ) In this question, we will simulate stock prices and find the values of call and put options using simulation and using the Black-Scholes formula. A common way to simulate stock prices is by assuming future stock prices are log-normal. In particular, the (random) future stock price is: = 0 exp[( 0.5 2 ) + ] where is the risk-free rate, t is the time to maturity of the option (expressed in years), and is the annual volatility. z is a standard normal variable, and this will be the source of randomness in the model. a. The first step in the model is to draw z using RiskNormal(0,1). b. Using @Risk, create a formula that calculates a random future stock price 3 months in the future (t=0.25 years) using the expression above and the parameters in the accompanying spreadsheet. Define this cell as an @Risk output. c. A call option pays off only if the stock price at maturity is greater than the strike price, K. Its payoff function is: max( , 0). Create a cell with the call option payoff formula in 3-month's time and define this as an @Risk output. d. A put option pays off only if the stock price at maturity is less than the strike price, K. Its payoff function is: max( , 0). Create a cell with the put option payoff formula in 3month's time and define this as an @Risk output. Simulate the future stock price using 5000 iterations. e. What is the probability that the future stock price is above the strike price (i.e., the call option is in the money)? f. Paste images of the distributions of the future stock price, the call payoff, and the put payoff. g. What is the expected payoff of the call at maturity? h. Calculate the current call price by discounting the average future call payoff (its expected value) to the present at the risk-free rate: 0 = exp( ) i. What is the expected payoff of the put at maturity? j. Calculate the current put price by discounting the average future put payoff (its expected value) to the present at the risk-free rate: 0 = exp( ) Now let's calculate the Black-Scholes call price. The Black-Scholes formula for a call option is: 0 = 0 (1 ) exp( ) (2 ) where 1 = [ln ( 0 ) + ( + 0.5 2 ) ] and 2 = 1 . () denotes the normal cumulative distribution, which can be evaluated in Excel using the NORM.S.DIST(,1) function. Similarly, the Black-Scholes formula for a put option is: 0 = exp( ) (2 ) 0 (1 ) k. What are the Black-Scholes call and put prices? How do they compare to the call and option prices calculated from simulated stock prices? l. What is the value of (2 )? How does it compare to the probability that the simulated stock price is above the strike price (from (e))