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

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).$