Black function with Excel

A black function {Bl} is given by

\displaystyle Bl(K, S, \nu, w) = \mathbb E [(w(Se^Y - K))^+],

where {Y\sim \mathcal{N}(-\nu^2/2, \nu^2)} is a normal random variable for some {\nu>0} and {w} is either {+1} or {-1}. One can define {Bl} function equivalently through its analytic formula, i.e.

\displaystyle Bl(K, S, \nu, w) = w S \Phi(w d_{1}) - K w \Phi(wd_{2}),

where

\displaystyle d_{1} = \frac{\frac 1 2 \nu^{2} - \ln \frac K S}{\nu}, \quad d_{2} = \frac{-\frac 1 2 \nu^{2} - \ln \frac K S}{\nu}.

This function is said to be Black function, since it is useful to compute European call and put prices in Black-Scholes model in the following sense. BS model usually assumes the lognormal distribution for the stock price at time {T}, that is,

\displaystyle S_{T} \sim S_{0} e^{(r-\delta)T + Y},

where {r} is short rate, {\sigma_{t}} is volatility, {\delta} is dividend yield, and {Y \sim \mathcal{N}(-\frac 1 2 \nu^{2}, \nu^{2})} for {\nu = (\int_{0}^{T} \sigma_{t}^{2} dt)^{1/2}.}

Therefore, if {t}-price of call option with maturity {T} and strike {K} is denoted by {C_{t}}, then its risk-neutral pricing is given by

\displaystyle C_{0} = e^{-rT} \mathbb [(w(S_{T} - K))^{+}] = e^{-\delta T}Bl(Ke^{(\delta-r)T}, S_{0}, \nu, w),

where {w = +1}.

Similarly, if {t}-price of put option with maturity {T} and strike {K} is denoted by {P_{t}}, then its risk-neutral pricing is given by

\displaystyle P_{0} = e^{-rT} \mathbb [(w(S_{T} - K))^{+}] = e^{-\delta T}Bl(Ke^{(\delta-r)T}, S_{0}, \nu, w),

where {w = -1}.

Ex. Suppose stock spot price {S = 10}, volatility {\sigma = .4}, interest rate is {r = .0427}, and no dividend. For a put with strike {K = 10} and maturity {T = 10} years, we can use Black function to compute as follows.

\displaystyle P = Bl(10 \cdot \exp\{-.0427 \times 10\}, 10, .4 \sqrt{10}, -1) = 2.358741.

See the excel file (download).

 

 

Ex. Today’s SPY spot price is {S = 198.97}, and dividend yield is {\delta = .0183}. The spot price of call with maturity June 2016 ({ T = 23/12}) and strike {K = 200} is quoted {C_{0} = 14.25}. 2-year US treasury yields is {r = 0.0049}. We want to compute implied volatility based on the above information. That is to solve for {\sigma} from

\displaystyle 14.25 = e^{-.0183\cdot (23/12)} Bl(200* e^{(.0183-.0049)\cdot (23/12)}, 198.97, \sigma \sqrt{23/12}, 1).

This can be computed again by excel file with guess and check (download). As a result the implied volatility is 0.159.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s