555 Option Greeks
Excel can be used to examine the sensitivities of the Greeks to various
parameters. For example, Figure 20.1 shows the deltas as functions of the
stock price, and Figure 20.2 shows them as functions of the moneyness
of the call option.
Figure 20.3 shows the theta of a call as a function of the stock price,
and Figure 20.4 shows it as a function of the time to option expiration.
20.3 Delta Hedging a Call
Delta hedging is a fundamental technique in option pricing. The idea is
to replicate an option by a portfolio of stocks and bonds, with the port-
folio proportions determined by the Black-Scholes formula.
Suppose we decide to replicate an at-the-money European call option
that has 12 weeks to run until expiration. The stock on which the option
is written has S
0
= $40 and exercise price X = $45, the interest rate is
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
AB C D E
S 100 Current stock price
X 90 Exercise price
T 0.5 Time to maturity of option (in years)
r 6.00% Risk-free rate of interest
k 2.00% Dividend yield
Sigma 35% Stock volatility
d
1
0.6303 <-- =(LN(B2/B3)+(B5-B6+0.5*B7^2)*B4)/(B7*SQRT(B4))
d
2
0.3828
<-- d
1
-sigma*SQRT(T)
N(d
1
)
0.7357
<-- Uses formula NormSDist(d
1
)
N(d
2
)
0.6491
<-- Uses formula NormSDist(d
2
)
Call price 16.1531 <-- =B2*EXP(-B6*B4)*B12-B3*EXP(-B5*B4)*B13
16.1531 <-- =bsmertoncall(B2,B3,B4,B5,B6,B7)
Put price 4.4882 <-- =B3*EXP(-B5*B4)*NORMSDIST(-B10)-B2*EXP(-B6*B4)*NORMSDIST(-B9)
4.4882 <-- =bsmertonput(B2,B3,B4,B5,B6,B7)
Greeks: Brute force
Call Put
Delta 0.7284 <-- =EXP(-B6*B4)*NORMSDIST(B9) -0.2616 <-- =-EXP(-B6*B4)*NORMSDIST(-B9)
Gamma 0.0195 <-- =EXP(B9^2/2-B6*B4)/(B2*B7*SQRT(2*B4*PI())) 0.0195 <-- =EXP((B9^2)/2-B6*B4)/(B2*B7*SQRT(2*B4*PI()))
Vega 22.8976 <-- =B2*SQRT(B4)*EXP(-(B9^2)/2)*EXP(-B6*B4)/SQRT(2*PI()) 22.8976 <-- =B2*EXP(-(B9^2)/2-B6*B4)*SQRT(B4)/SQRT(2*PI())
Theta -9.9587
<-- =-B2*EXP(-(B9^2)/2-
B6*B4)*B7/SQRT(8*B4*PI())+B6*B2*EXP(-B6*B4)*B12-
B5*B3*EXP
(-B5*B4)*B13
-6.6984
<-- =-B2*EXP(-(B9^2)/2-B6*B4)*B7/SQRT(8*B4*PI())-
B6*B2*EXP(-B6*B4)*(1-B12)+B5*B3*EXP(-B5*B4)*(1-
B13)
Rho 28.3446 <-- =B3*B4*EXP(-B5*B4)*NORMSDIST(B10) -15.3255 <-- =-B3*B4*EXP(-B5*B4)*NORMSDIST(-B10)
Greeks: VBA functions
Call Put
Delta 0.7284 <-- =deltacall(B2,B3,B4,B5,B6,B7) -0.2616 <-- =deltaput(B2,B3,B4,B5,B6,B7)
Gamma 0.0195 <-- =gamma(B2,B3,B4,B5,B6,B7) 0.0195 <-- =gamma(B2,B3,B4,B5,B6,B7)
Vega 22.8976 <-- =vega(B2,B3,B4,B5,B6,B7) 22.8976 <-- =vega(B2,B3,B4,B5,B6,B7)
Theta -9.9587
<-- =Thetacall(B2,B3,B4,B5,B6,B7)
-6.6984
<-- =Thetaput(B2,B3,B4,B5,B6,B7)
Rho 28.3446 <-- =rhocall(B2,B3,B4,B5,B6,B7) -15.3255 <-- =rhoput(B2,B3,B4,B5,B6,B7)
Black-Scholes Greeks
This spreadsheet uses the Merton model for a continuously dividend-paying stock