| Using Microsoft Excel to price financial options |
Back |
| George Levy explains how Excel can be used to evaluate an option’s value that depends on the value of one underlying asset as well as multiple underlying assets. |
We begin by showing how the Visual Basic within Excel can be used to create powerful derivative pricing applications. We will first explain how Excel’s Visual Basic can be used to create an application that prices a selection of simple European put and call options at the press of a button.
This function returns the normal cumulative distribution for the specified mean and standard deviation.
Function parameters:
x: is the value for which you want the distribution.
mean: is the arithmetic mean of the distribution.
standard_dev: is the standard deviation of the distribution.
cumulative: is a logical value that determines the form of the function. If cumulative is TRUE, NORMDIST returns the cumulative distribution function; if FALSE, it returns the probability density function.
If mean = 0 and standard_dev = 1, NORMDIST returns the standard normal distribution.
This function can be used to create the following Visual Basic function to calculate European option values within Excel.
Once the function has been defined it can be accessed interactively using the paste function facility within Excel.
The function bs_opt can also be incorporated into other Visual Basic code within Excel. To illustrate, if the following Visual Basic subroutine is defined:
then when the button labelled “CALCULATE OPTIONS” is clicked, the values of 65.
European options will be calculated using the data in columns 1-3 on worksheet 1.
Multiple underlying values
The cumulative standard normal distribution can also be used to provide analytic solutions for a range of other exotic options such as:
• Barrier options • Exchange options
• Lookback options • Binary options
It should be mentioned that although the term asset in the Black-Scholes formula was originally taken to mean a particular stock with a value measured in a given currency (say dollars or pounds), it has recently been used in a much broader context.
Cumulative standard normal distributions can also be used to provide closed form solutions for rainbow options involving multiple assets.
The bivariate cumulative standard normal distribution is represented by the term
Unfortunately Excel does not provide a function to evaluate the integral . This means that software developers are faced with the choice of either writing their own function or using a commercially available function. Although there are various methods to approximate this integral, for example [1], it would not be computationally efficient to implement these in Visual Basic. This means that the developer would have to write the function in another language say C++ and then call it from Excel [3][4]. Since this approach could be time consuming and error prone it might be more cost effective to make use of commercially available software. However, once the choice has been made a rainbow pricing function (say bs_opt_rainbow) can be added to the paste function facility in a similar way to how the function bs_opt was created. This approach can be used to customise Excel to the needs of a particular individual or companies and can also greatly enhance the power of Excel for financial analysis.
George Levy, PhD, is a member of the Numerical Algorithms Group. The group specialises in the provision of numerical and statistical components to aid the solving of complex mathematical problems. COMIT Gruppe are the Irish representative for NAG Group.
References
[1] J C Hull, Options, Futures and other Derivatives, Prentice Hall International Inc, 3rd Edition 1997.
[2] R McIntyre, Black-Scholes will do, Energy Power and Risk Management, November 1999.
[3] G F Levy, Calling 32-bit NAG C DLL functions from Visual Basic 5 and
Microsoft Office, NAG Technical Report, TR2/98, 1998
[4] Such as the G01 chapter of the NAG Numerical Library form NAG Ltd and NAG Inc. |
|
| Article appeared in the November 2002 issue.
|
|
|
