Microsoft Excel Approach to Estimating Alternative Option Pricing Models

Abstract

This chapter shows how Microsoft Excel can be used to estimate call and put options for (a) Black–Scholes model for individual stock, (b) Black–Scholes model for stock indices, and (c) Black–Scholes model for currencies. In addition, we are going to present how an Excel program can be used to estimate American Options. Section 26.2 presents an option pricing model for Individual Stocks, Sect. 26.3 presents an option pricing model for Stock Indices, Sect. 26.4 presents option pricing model for Currencies, Sect. 26.5 presents Bivariate Normal Distribution Approach to calculate American Call Options, Sect. 26.6 presents the Black’s approximation method to calculate American Call Options, Sect. 26.7 presents how to evaluate American Call option when dividend yield is known, and Sect. 26.8 summarizes this chapter. Appendix 26.1 defines the Bivariate Normal probability density function, and Appendix 26.2 presents the Excel program to calculate the American call option when dividend payments are known.

This chapter was written by Professor Cheng F. Lee and Dr. Ta-Peng Wu of Rutgers University.

Appendices

Appendix 26.1: Bivariate Normal Distribution

We have shown how the cumulative univariate normal density function can be used to evaluate a European call option in the previous sections of this chapter. If a common stock pays a discrete dividend during the option’s life, the American call option valuation equation requires the evaluation of a cumulative bivariate normal density function. While there are many available approximations for the cumulative bivariate normal distribution, the approximation provided here relies on Gaussian quadratures. The approach is straightforward and efficient, and its maximum absolute error is 0.00000055.

The probability that x ^' is less than a and that y ^' is less than b for the standardized cumulative bivariate normal distribution can be defined as

$$ P\left({X}^{\hbox{'}}<a,{Y}^{\hbox{'}}<b\right)=\frac{1}{2\pi \sqrt{1-{\rho}^2}}{\displaystyle {\int}_{-\infty}^a{\displaystyle {\int}_{-\infty}^b \exp \left[\frac{2{x}^{\hbox{'}2}-2\rho {x}^{\hbox{'}}{y}^{\hbox{'}}+{y}^{\hbox{'}2}}{2\left(1-{\rho}^2\right)}\right]}}d{x}^{\hbox{'}}d{y}^{\hbox{'}}, $$

where ${x}^{\hbox{'}}=\frac{x-{\mu}_x}{\sigma_x}$, ${y}^{\hbox{'}}=\frac{y-{\mu}_y}{\sigma_y}$, and p is the correlation between the random variable s x ^' and y ^'.

The first step in the approximation of the bivariate normal probability N ₂(a, b; ρ) is as follows:

$$ \phi \left(a,b;\rho \right)\approx .31830989\sqrt{1-{\rho}^2}{\displaystyle \sum_{i=1}^5{\displaystyle \sum_{j=1}^5{w}_i{w}_jf\left({x}_i^{\hbox{'}},{x}_j^{\hbox{'}}\right)}}, $$

(26.15)

where $f\left({x}_i^{\hbox{'}},{x}_j^{\hbox{'}}\right)= \exp \left[{a}_1\left(2{x}_i^{\hbox{'}}-{a}_1\right)+{b}_1\left(2{x}_j^{\hbox{'}}-{b}_1\right)+2\rho \left({x}_i^{\hbox{'}}-{a}_1\right)\left({x}_j^{\hbox{'}}-{b}_1\right)\right]$.

The pairs of weights (w) and corresponding abscissa values (x′) are

i,j	w	x '
1	0.24840615	0.10024215
2	0.39233107	0.48281397
3	0.21141819	1.0609498
4	0.033246660	1.7797294
5	0.00082485334	2.6697604

(This portion is based upon Appendix 13.1 of Stoll H. R. and R. E Whaley. Futures and Options. Cincinnati, OH: South Western Publishing, 1993.)

and the coefficients a ₁ and b ₁ are computed using ${a}_1=\frac{a}{\sqrt{2\left(1-{\rho}^2\right)}}$ and ${b}_1=\frac{b}{\sqrt{2\left(1-{\rho}^2\right)}}$.

The second step in the approximation involves computing the product abρ; if $ab\rho \le 0$, compute the bivariate normal probability, N ₂(a, b; ρ), using the following rules:

$$ \begin{array}{l}(1)\kern0.5em \mathrm{If}\kern0.5em \mathrm{a}\le 0,\kern0.5em \mathrm{b}\le 0,\kern0.5em \mathrm{a}\mathrm{nd}\kern0.5em \rho \le 0,\kern0.5em \mathrm{then}\kern0.5em {N}_2\left(a,b;\rho \right)=\phi \left(a,b;\rho \right);\\ {}(2)\kern0.5em \mathrm{If}\kern0.5em \mathrm{a}\le 0,\kern0.5em \mathrm{b}\ge 0,\kern0.5em \mathrm{a}\mathrm{nd}\kern0.5em \rho >0,\kern0.5em \mathrm{then}\kern0.5em {N}_2\left(a,b;\rho \right)={N}_1(a)-\phi \left(a,-b;-\rho \right);\\ {}(3)\kern0.5em \mathrm{If}\kern0.5em \mathrm{a}\ge 0,\kern0.5em \mathrm{b}\le 0,\kern0.5em \mathrm{a}\mathrm{nd}\kern0.5em \rho >0,\kern0.5em \mathrm{then}\kern0.5em {N}_2\left(a,b;\rho \right)={N}_1(b)-\phi \left(-a,b;-\rho \right);\end{array} $$

$$ (4)\kern0.5em \mathrm{If}\kern0.5em \mathrm{a}\ge 0,\kern0.5em \mathrm{b}\ge 0,\kern0.5em \mathrm{a}\mathrm{nd}\kern0.5em \rho \le 0,\kern0.5em \mathrm{then}\kern0.5em {N}_2\left(a,b;\rho \right)={N}_1(a)+{N}_1(b)-1+\phi \left(-a,-b;\rho \right). $$

(26.16)

If $ab\rho >0$, compute the bivariate normal probability, N ₂(a, b; ρ), as

$$ {N}_2\left(a,b;\rho \right)={N}_2\left(a,0;{\rho}_{ab}\right)+{N}_2\left(b,0;{\rho}_{ab}\right)-\delta, $$

(26.17)

where the values of N ₂(•) on the right-hand side are computed from the rules, for $ab\rho \le 0$

${\rho}_{ab}=\frac{\left(\rho a-b\right)Sgn(a)}{\sqrt{a^2-2\rho ab+{b}^2}}$, ${\rho}_{ba}=\frac{\left(\rho b-a\right)Sgn(b)}{\sqrt{a^2-2\rho ab+{b}^2}}$, $\delta =\frac{1-Sgn(a)\times Sgn(b)}{4}$,

and

$Sgn(x)=\left\{\begin{array}{l}1\begin{array}{cc}\hfill \hfill & \hfill \hfill \end{array}x\ge 0\\ {}\begin{array}{cc}\hfill -1\hfill & \hfill \hfill \end{array}x<0\end{array}\right.$,

N ₁(d) is the cumulative univariate normal probability.

Appendix 26.2: Excel Program to Calculate the American Call Option When Dividend Payments Are Known

The following is a Microsoft Excel Program which can be used to calculate the price of an American Call Option using the Bivariate Normal Distribution method (Table 26.1):

Table 26.1 Microsoft Excel program for calculating the American call options