Monte Carlo Methods in Excel: Part 1 – Introduction

If we toss a coin 1000 times, it is pretty easy to calculate the probability of tossing exactly 537 heads. For better or for worse, the real world rarely offers us such cut-and-dry calculations. What if we were trying to calculate neutron density in a critical mass of plutonium? A mathematically precise result is beyond our reach.

During the early development of nuclear weapons, scientists faced just such problems. The answer was impossible to calculate, but they had to get an answer anyway. Mathematician Stanislaw Ulam developed a method that has come to be known as the Monte Carlo method, after the casino resort town in Monaco. You get an estimate by plugging in some random numbers. You get another estimate by plugging in some more random numbers. After you do this again and again, you end up with a distribution of answers that may permit you to estimate what will “really” happen.

Monte Carlo methods are now routinely used to create a wide variety of statistical models in an even wider variety of fields, including estimating the fluctuations in inventory levels and the requirements for worker overtime, the probability of products failing in the field and the utilization of medical facilities. Of course, the valuation of real estate, securities, and stock options figure prominently in the application of Monte Carlo methods. Microsoft Excel turns out to be a terrific platform for developing Monte Carlo models, and we shall explore some of the techniques that may be used.

Random Numbers

Monte Carlo methods invariably require a source of random numbers. Excel, of course, has a built-in RAND( ) function, but this is rarely enough. The RAND( ) function returns a value greater than or equal to zero and less than one. Furthermore, all values in this range might be returned with equal probability, making this a uniform random distribution. Uniform random distributions are simple and easy-to-use, but rarely describe real world events outside of the toss of a die or the draw of a card. Real world data generally follow other distributions, distributions that are not uniform. The height of people in your workplace is likely very close to a normal distribution. The number of people in line at the 10-items-or-less checkout line probably follows a Poisson distribution. If we are to use Excel to build Monte Carlo models, we must first understand the nature and quality of the random numbers that Excel can provide for us.

A Historical Note

The foundations for modern probability theory were developed by mathematicians in the 17th century. Apparently unaware of the Excel RAND( ) function, they needed a source of random numbers for their work. At that time, newspapers published winning roulette numbers for the previous day, much like today’s papers publish winning racehorses and lotto numbers. It was, quite literally, a “Monte Carlo” method, and it is over 400 years old!

Type to search

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.