Monte Carlo Simulation Implemented ENTIRELY in Excel (no VBA macros)

When I moved to NY from our Toronto office I was a bit hamstrung by needing to log into a remote server to use the stochastic software we tend to run our simulations with. Not that it didn’t work, but it was slow and a bit of a pain to use.

So the first thing I did was try to implement my own Monte Carlo simulation program. I wanted to be able to send it to people wholesale so I forced myself to use VBA.

It worked, but it was really really slow. I hate slow.

So my latest iteration is a relatively simple model written entirely in excel formulas, basically only using the rand() function, which has been greatly improved by MS in Office 2003.

The attached file simulates Poisson random numbers with the famous Knuth algorithm (a lovely bit of math, by the way).

Next up will be installing normal, lognormal and beta distributions. With those, I can probably simulate just about anything I need to, really. I’m going to limit myself to Excel’s native (C)  implementations of all of these functions, which are a ba-jillion times faster than anything you can handcode in VBA.

So why reinvent the wheel?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.