Monte Carlo Integration on Excel spreadsheet
January 28, 2007
Monte Carlo integration is a simple, yet powerful, technique to evaluate definite integrals. This technique can be applied to evaluation of derivatives payoffs in finance.
Say, you want to evaluate a simple definite integral shown below:
The above equation (1) is easy to solve if we know the rules of integral calculus. There is a definite formula in integral calculus for the above and it can be easily solved as:
But what if we don't know any integral calculus? What if we are not at familiar with the rules of calculus? Can we then evaluate this definite integral? The answer is "yes". All we need is Excel spreadsheet and some basic (high school) understanding of math.
If we put a bounding box around the function which is being integrated, then the integral of the function can be seen as a part of the bounding box. So if we choose a point at random uniformly within the bounding box, the probability that the point is within is given by the fraction of the area that occupies.
Another way of looking at the above is to write the equation (1), with generalized upper and lower limits as follows:
In the above equation, and takes the value as:
Thus equation (2) simply translates into an expectation equation:
And we can simply write the above as:
Implementation in Excel
The above integral, shown in equation (1), can be very easily implemented on an Excel spreadsheet using the approximation shown in equation (4).
The spreadsheet algorithm should be:
- Generate a single set of 1000 random numbers using the Excel function (or you can write your own simple code); you can generate more than 1000 numbers, say 10,000, but the accuracy for a definite integral cannot be improved much beyond a 1000 random numbers; multiply each of these 1000 random numbers by 6.
- Evaluate the value of the function for all the 1000 random numbers generated above.
- Sum up all the 1000 values for the function and divide the sum by 1000. Then multiply it by 6. The value will be somewhere between 67 and 75.
- Repeat the above steps again and again- say a hundred times - by pressing the function key F9 on your keyboard and you'll see the values change between the above bounds. If you repeat this over for, say, a thousand times (by writing a simple macro) then you get a very close approximation to the exact value of 72 for the definite integral.
Any comments and queries can
be sent through our
More on Quantitative Finance >>
back to top