Question

For the next two questions please ensure that you plot graphs in excel and paste them and the solver output in a word document. I do not need to see your excel files.

4. Use the information below to find the implied volatilities for Exxon Mobil May call prices. Plot the implied volatilities versus the strike prices. What are your inferences from the shape of the implied volatility curve?

Exxon Mobil (XOM) 04/29/2010 call prices
Current Stock Price	Strike Price	Risk-Free rate	Time to Maturity	Observed call price	Implied Volatility
67.3	45	0.25%	0.145	22.4
67.3	55	0.25%	0.145	12.425
67.3	60	0.25%	0.145	7.475
67.3	65	0.25%	0.145	3.15
67.3	70	0.25%	0.145	0.715
67.3	75	0.25%	0.145	0.105

5. Use the information below to find the implied volatilities for Ford May call prices. Plot the implied volatilities versus the strike prices. What are your inferences from the shape of the implied volatility curve?

Ford (F) 04/29/2010 call prices
Current Stock Price	Strike Price	Risk-Free rate	Time to Maturity	Observed call price	Implied Volatility
13.57	10	0.25%	0.145	3.65
13.57	11	0.25%	0.145	2.75
13.57	12	0.25%	0.145	1.925
13.57	13	0.25%	0.145	1.24
13.57	14	0.25%	0.145	0.74
13.57	15	0.25%	0.145	0.41
13.57	16	0.25%	0.145	0.225
13.57	17	0.25%	0.145	0.12
13.57	18	0.25%	0.145	0.07

Answer 1

(4)

lt S=stock price; X=strike price; r=risk free rate; c=call price (European); T=time to maturity; sigma=volatility

BSM formula can be written as:

c= S N(d₁) - X exponential(-rT) N(d₂)

(In excel we can put above formula in a cell. We may reference the values of S, X, T, r, sigma, N(d₁) & N(d₂) from some other cells. We can use EXP() function in excel for exponent term.

d₁= [ln(S/X) + (r + (sigma² / 2))t]/ (sigma * squareroot(T))

(In excel we can use function LN() for natural log & SQRT() for obtaining squareroot)

d2= d₁ - sigma squareroot(T)

To obtain N(d₁) & N(d₂), we simply use function in excel =NORMDIST(d₁ reference cell,0,1,TRUE)

Now we put values for S, X, r, T in the reference cells. Put some dummy value for sigma, say 10%.

In Excel, go to Data tab -> select 'What-If Analysis' -> Goal Seek; 'Set Cell' should be the cell of option price formula c; 'To value' should be value of call option provided; 'By changing cell' should be the value of sigma (volatility).

S=67.3; X=45; r =0.25%; T=0.145; c=22.4 => sigma=52.07%

S=67.3; X=55; r =0.25%; T=0.145; c=12.425 => sigma=29.96%

S=67.3; X=60; r =0.25%; T=0.145; c=7.475 => sigma=20.51%

S=67.3; X=65; r =0.25%; T=0.145; c=3.15 => sigma=17.40%

S=67.3; X=70; r =0.25%; T=0.145; c=0.715 => sigma=16.64%

S=67.3; X=75; r =0.25%; T=0.145; c=0.105 => sigma=17.50%

(Note: Sigma value might slightly differ on each run due to optimization approach)

Plot in Excel: attachedIdeally we would expect same volatility across different strikes but using market call prices we get different Implied volatilities for different strikes, resulting into volatility Smirk Curve.Volatility Smirk might imply that in the money calls are more expensive as compared to out of money calls.