Download (direct link):
Simulations are comprised of many thousands of repeated trials and can take a long time to execute. If the user can tolerate Excel being tied up during the simulation, then running it from a VB or an XLL command is a sensible choice. If long simulations need to be hidden within worksheet functions, then the use of background threads becomes necessary. The following sections discuss both of these options.
Each MC trial is driven by one or more random samples from one or more probability distributions. Once the outcome of a single trial is known, the desired quantity can be calculated. This is repeated many times so that an average of the calculated quantity can be derived.
In general, a large number of trials need to be performed to obtain statistically reliable results. This means that MC simulation is usually a time-consuming process. A number of techniques have been developed for the world of financial derivatives that reduce the number of trials required to yield a given statistical accuracy. Two important examples are variance reduction and the use of quasi-random sequences (see above).
Variance reduction techniques aim to find some measure, the control, that is closely correlated to the required result, and for which an exact value can be calculated analytically. With each trial both the control and the result are calculated and difference in
6 There are numerous excellent texts on the subject of Monte Carlo simulation, dealing with issues such as numbers of trials, error estimates and other related topics such as variance reduction. Numerical Recipes in C contains an introduction to Monte Carlo methods applied to integration. Implementing Derivative Models (Clewlow and Strickland), published by Wiley, contains an excellent introduction of MC to financial instrument pricing.
Example Add-ins and Financial Applications
value recorded. Since the error in the calculation of the control is known at each trial, the average result can be calculated from the control’s true value and the average difference between the control and the result. With a well-chosen control, the number of required trials can be reduced dramatically.
The use of quasi-random sequences aims to reduce the amount of clustering in a random series of samples. (See section 10.9 above.) The use of this technique assumes that a decision is made before running the simulation as to how many trials, and therefore samples, are needed. These can be created and stored before the simulation is run. Once generated, they can be used many times of course.
Within Excel, there are a number of ways to tackle MC simulation. The following sub-sections discuss the most sensible of these.
10.11.1 Using Excel and VBA only
A straightforward approach to Monte Carlo simulation is as follows:
1. Set up the calculation of the one-trial result in a single worksheet, as a function of the random samples from the desired distribution(s).
2. Generate the distribution samples using a volatile function (e.g., RAND()).
3. Set up a command macro that recalculates the worksheet as many times as instructed, each time reading the required result from the worksheet, evaluating the average.
4. Deposit the result of the calculation, and perhaps the standard error, in a cell or cells on a worksheet, periodically or at the end of the simulation.
Using Excel and VBA in this way can be very slow. The biggest optimisation is to control screen updating, using the Application.ScreenUpdating = True/False statements, analogous to the C API xlcEcho function, and speeds things up considerably.
The following VB code example shows how this can be accomplished, and is included in the example workbook MCexample1.xls on the CD ROM. The workbook calculates a very simple spread option payoff, MAX(asset_price_1 -asset_price_2, 0), using this VB command attached to a button control on the worksheet. The worksheet example assumes that both assets are lognormally distributed and uses an on-sheet Box-Muller transform. The VB command neither knows nor cares about the option being priced nor the pricing method used. A completely different option or model could be placed in the workbook without the need to alter the VB command. (Changing the macro so that it calculates and records more data at each trial would involve some fairly obvious modifications, of course.)
Private Sub CommandButton1_ClickO
Dim trials As Long, max_trials As Long
Dim dont_do_screen As Long, refresh_count As Long
Dim payoff As Double, sum_payoff As Double
Dim sum_sq_payoff As Double, std_dev As Double
Dim rAvgPayoff As Range, rPayoff As Range, rTrials As Range
Dim rStdDev As Range, rStdErr As Range
Excel Add-in Development in C/C++
' Set up error trap in case ranges are not defined ' or calculations fail or ranges contain error values On Error GoTo handleCancel
' Set up references to named ranges for optimum access Set rAvgPayoff = Range("AvgPayoff")
Set rPayoff = Range("Payoff")