Question

A stock is currently priced at $62 and has an annual standard deviation of 42 percent. The dividend yield of the stock is 3 percent, and the risk-free rate is 5 percent. What is the value of a call option on the stock with a strike price of $59 and 50 days to expiration?

How do you find the call option using Excel?

I got a really far off answer

Answer 1

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

The value of a call option is:

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

where :

S₀ = current spot price

K = strike price

N(x) is the cumulative normal distribution function

q = dividend yield

r = risk-free interest rate

t is the time to maturity 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(62 / 59). We input the same formula into Excel, i.e. =LN(62/59)
• (r + σ²/2)*t = (0.05 + (0.42²/2)*(50/365)
• σ√t = 0.42 * √(50/365)

d₁ = 0.4408

d₂ = 0.2854

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

N(d₂) = 0.6123

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

C = (S₀ * e^-qt * N(d₁))   - (Ke^-rt * N(d₂)), which is (62 * e^{(-0.03 * (50/365))} * 0.6703) - (59 * e^{(-0.05 * (50/365))} * 0.6123)    ==> $5.5096

Value of call option is $5.5096