Spreadsheet Assignment 2: Simulation

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

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 t-

statistic 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):

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):

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:

𝑆𝑡 = 𝑆0exp⁡[(𝑟𝑓 − 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.

Payoff

Stock Price (𝑆𝑡)

SSSSSS(S(ei

Call

K

Payoff

Stock Price (𝑆𝑡)

Put Option

K

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 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 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))?