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