Question

2. Set up an Excel spreadsheet that can be used to calculate the price of a call option using the Black- Scholes formula. Assume So is 45, o=40%, the one-year continuously compounded risk-free rate is 2.0% per year, the strike price is 46, and the time to expiration is one-year. a. Use the Excel formula for the cumulative standard normal distribution. b. Set up the spreadsheet so that you can change any of the parameters that are used (S. X T.1F.) and obtain the correct option price.

Answer 1

We use Black-Scholes Model to calculate the price of the call option.

The price of a call option is:

C = (S₀ * N(d₁)) - (Ke^-rt * N(d₂))

where :

S₀ = current spot price

K = strike price

N(x) is the cumulative normal distribution function

r = risk-free interest rate

t is the time to expiry in years

d₁ = (ln(S₀ / K) + (r + σ²/2)*T) / σ√T

d₂ = d₁ - σ√T

σ = standard deviation of underlying stock returns

First, we calculate d₁ and d₂ as below :

• ln(S₀ / K) = ln(45 / 46). We input the same formula into Excel, i.e. =LN(45 /46)
• (r + σ²/2)*T = (0.02 + (0.40²/2)*1
• σ√T = 0.40 * √1

d₁ = 0.1951

d₂ = -0.2049

N(d₁) and N(d₂) are calculated in Excel using the NORMSDIST function and inputting the value of d₁ and d₂ into the function.

N(d₁) = 0.5773

N(d₂) = 0.4188

Now, we calculate the price of the call option as below:

C = (S₀ * N(d₁))   - (Ke^-rt * N(d₂)), which is (45 * 0.5773) - (46 * e^{(-0.02 * 1)})*(0.4188)    ==> $7.0960

Price of call option is $7.0960

D 1 Current Stock Price 2 strike price 3 Risk Free Rate 4 Time to maturity(in years) 5 volatility of underling stock C $45.00 $46.00 2% 1.00 40% 7 In(So / K) 8 (r+ 02/2)*T 9 OVT 10 di 11 d2 12 N(di) 13 Nd2) -0.0220 0.1000 0.4000 0.1951 -0.2049 0.5773 0.4188 15 Call 7.0960 25.9796 18.8836

45 46 0.02 t 0 0.4 1 Current Stock Price 2 strike price 3 Risk Free Rate 4 Time to maturity in years) 5 volatility of underling stock 6 7 In(So / K) 8 (r+ 02/2)*T 9 OVT 10 di 11 d2 12 N(di) 13 N(dz) =LN(C1/C2) =(C3+(C5^2)/2)*04 =C5*(C4^0.5) =(B7+B8)/B9 =B10-B9 =NORMSDIST(B10) =NORMSDIST(B11) 15 Call =C15-D15 =B12*C1 =C2*EXP(-C3*C4) *B13