The Black–Scholes Formula
The Black–Scholes formula in cell E5 is:
where the two discounting factors in cells B15 and B16 and the cumulative normals in
cells E11 and E16 provide intermediate calculation stages. The call value in cell E5 is
9.73. The Black–Scholes value has also been calculated in cell E8 via the user-deﬁned
function, BSOptionValue, whose code is described in section 11.7.
As discussed in the previous section, the hedge ratio is given by the product of
expqT and Nd
. Here the hedge ratio has value 0.718 (that is 0.9851
risk-neutral probability that the option will be exercised is Nd
, which has value 0.680.
For a put on the same share, the Black–Scholes formula in cell H5 is:
Whereas the call formula has Nd
in the ﬁrst term, the put has Nd
because of the symmetry of the normal probability distribution. Hence the E11 term for
the call is replaced by (E11 1) for the put. Similar remarks apply to the change in
the second term. The put value in cell H5 is 2.49. Once again, the Black–Scholes value
has been calculated in cell H8 using the same user-deﬁned function BSOptionValue as
previously used for the call. The function has the important parameter ‘iopt’ which takes
value 1 for a call, 1 for a put, thus providing one general function in place of two
separate ones for call and put respectively. As we have seen, the algebraic expressions
for put and call are similar, except for several minus signs.
It is of interest to ‘what-if’ on details of the underlying share and the option to inves-
tigate the effect on the option value. In particular, it will be found that the option value
is very sensitive to changes in the volatility of the share. This sensitivity analysis can be
implemented easily with one or more Data Tables (as outlined in section 2.7).
11.3 OPTIONS ON CURRENCIES AND COMMODITIES
So far our discussion of option valuation has concentratedon options on equities. However,
the Black–Scholes framework with continuously-paid dividends also allows options on
foreign currencies and commodity futures contracts to be valued. As we have seen, the
Black–Scholes call formula for a share with continuous dividend rate q is:
cD S expqTNd
Acurrency can be treated in the same way as a stock that pays a continuous dividend, with
the foreign interest rate R replacing the continuous dividend yield q in the Black–Scholes
formula. The domestic interest rate is equivalent to the risk-free rate r. Hence the value
of the call on a currency with foreign interest rate R is:
This is sometimes known as the Garman–Kohlhagen formula (Garman and Kohlhagen,
1983). Figure 11.2 shows an example in the Currency sheet of valuing a call on a currency
with foreign interest rate 4%. The call value is 0.0044 (in cell E11). It has been calculated
using the BSOptionValue user-deﬁned function with appropriate inputs.