Monte Carlo Simulation Formula in Excel – Tutorial and Download

What is Monte Carlo Simulation?

Monte Carlo Simulation is a process of using probability curves to determine the likelihood of an outcome.  You may scratch your head here and say…  “Hey Rick, a distribution curve has an array of values.  So how exactly do I determine the likelihood of an outcome?”  And better yet, how do I do that in Microsoft Excel without any special add-ins

Thought you would never ask.

This is done by running the simulation thousands of times and analyzing the distribution of the output.  This is particularly important when you are analyzing the output of several distribution curves that feed into one another.

Example:

  • # of Units Sold may have a distribution curve
  • multiplied by Market price, which may have another distribution curve
  • minus variable wages which have another curve
  • etc., etc.

Once all these distributions are intermingled, the output can be quite complex.  Running thousands of iterations (or simulations) of these curve may give you some insights.  This is particularly useful in analyzing potential risk to a decision.

Describe Monte Carlo

When describing Monte Carlo Simulation, I often refer to the 1980’s movie War Games, where a young Mathew Broderick (before Ferris Bueller) is a hacker that uses his dial up modem to hack into the Pentagon computers and start World War 3.  Kind of.  He then had the Pentagon computers do many simulations of the games Tic Tac Toe to teach the computer that no one will will a nuclear war – and save the world in the process.

Thanks Ferris. You’re a hero.

Here’s a glimpse of the movie to show you big time Monte Carlo in action.  I am assuming that you will overlook the politics, the awkward man hugging and of course, Dabney Coleman.

The Monte Carlo Simulation Formula

Distribution Curves

There are various distribution curves you can use to set up your Monte Carlo simulation.  And these curves may be interchanged based on the variable.  Microsoft doesn’t have a formula called “Do Monte Carlo Simulation” in the menu bar 🙂

Uniform Distribution

In a uniform distribution, there is equal likelihood anywhere between the minimum and a maximum.  A uniform distribution looks like a rectangle.

Normal (Gaussian) Distribution

This is also your standard bell shaped curve.  This Monte Carlo Simulation Formula is characterized by being evenly distributed on each side (median and mean is the same – and no skewness).  The tails of the curve go on to infinity.  So this may not be the ideal curve for house prices, where a few top end houses increase the average (mean) well above the median, or in instances where there is a hard minimum or maximum.  An example of this may be the minimum wage in your locale. Please note that the name of the function varies depending on your version.

Lognormal Distribution

A distribution where the logarithm is normally distributed with the mean and standard deviation.  So the setup is similar to the normal distribution, but please note that the mean and standard_dev variables are meant to represent the logarithm.

2014-10-28_14-55-49

Poisson Distribution

This is likely the most underutilized distribution.  By default, many people use a normal distribution curve when Poisson is a better fit for their models.  Poisson is best described when there is a large distribution near the very beginning that quickly dissipates to a long tail on one side.  An example of this would be a call center, where no calls are answered before second ZERO.  Followed by the majority of calls answered in the first 2 intervals (say 30 and 60 seconds) with a quick drop off in volume and a long tail, with very few calls answered in 20 minutes (allegedly).

2014-10-28_15-16-32

The purpose here is not to show you every distribution possible in Excel, as that is outside the scope of this article.  Rather to ensure that you know that there are many options available for your Monte Carlo Simulation.  Do not fall into the trap of assuming that a normal distribution curve is the right fit for all your data modeling.  To find more curves, to go the Statistical Functions within your Excel workbook and investigate.  If you have questions, pose them in the comments section below.

Building The Model

For this set up we will assume a normal distribution and 1,000 iterations.

bullet step 1

Input Variables

The setup assumes a normal distribution. A normal distribution requires three variables; probability, mean and standard deviation.  We will tackle the mean and standard deviation in our first step.  I assume a finance forecasting problem that consists of Revenue, Variable and Fixed Expenses.  Where Revenue minus Variable Expenses minus Fixed Expenses equals Profit.  The Fixed expenses are sunk cost in plant and equipment, so no distribution curve is assumed.  Distribution curves are assumed for Revenue and Variable Expenses.

2014-10-28_15-36-48

bullet step 2

First Simulation

The example below indicates the settings for Revenue.  The formula can be copy and pasted to cell D6 for variable expenses.  For Revenue and expenses we you the function NORM.INV() where the parameters are:

  • Probability = the function RAND() to elicit a random number based on the other criteria within the distribution.
  • Mean = The mean used in the Step 1.  For Revenue it is C3.
  • Standard Deviation = The Standard Deviation used in Step 1.  For Revenue it is C4

2014-10-28_16-32-27

Since RAND() is used as the probability, a random probability is generated at refresh.  We will use this to our advantage in the next step.

2014-10-28_17-20-23

1,000 Simulations

There are several ways to do 1,000 or more variations.  The simplest option is to take the formula from step #2 and make it absolute.  Then copy and paste 1,000 times.  That’s simple, but not very fancy.  And if Ferris Bueller can save the world by showing a new Tic Tac Toe game to a computer, then we can spice up this analysis as well. Let’s venture into the world of tables.

  • First we want to create an outline for a table.  We do this by listing the numbers 1 to 1,000 in rows.  In the example image below, the number list starts in B12.
  • in the next column, in cell C12, we will reference the first iteration.

2014-10-28_17-40-43

  • Next highlight the area where we want to house the 1,000 iterations
  • Select Data > Data Tables
  • For Column input cell: Select a blank cell.  In the download file, cell D11 is selected
  • Select OK

2014-10-28_18-00-111

  • Once OK is selected from the previous step, a table is inserted that autopopulates the 1,000 simulations

2014-10-28_18-14-55

2014-10-28_18-21-58

Summary Statistics

Once the simulations are run, it is time to gather summary statistics.  This can be done a number of ways.  In this example I used the COUNTIF() function to determine the percentage of simulations that are unprofitable, and the likelihood of a profit greater than $1 Million.  As expected, the likelihood of greater than $1M hovers around 50%.  This is because we used normal distribution curves that are evenly distributed around the mean, which was $1M.  The likelihood of losing money is 4.8%.  This was gathered by using the COUNTIF() function to count the simulations that were less than zero, and dividing by the 1,000 total iterations.

2014-10-28_18-30-21
Monte Carlo Simulation Formula

Get the Download

Now What?

In the video above, Oz asks about the various uses for Monte Carlo Simulation.  What have you used it for?  Are there any specific examples that you can share with the group?  If so, leave a note below in the comments section.  Also, feel free to sign up for our newsletter, so that you can stay up to date as new Excel.TV shows are announced.  Leave me a message below to stay in contact.

Rick Grantham
Follow Me

Tags


You may also like

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

October 26, 2014

Monte Carlo Simulation Formula in Excel – Tutorial and Download

September 17, 2012

The Excel Rollover Mini FAQ
  • Hi Rick – great post. One typo though – on second last line the copy should read “COUNTIF()” not “COUNTIT()”

  • Hi Rick,

    This is a very useful article and I’ve been able to employee the lessons learned in my own work. However, is there a way to record the randomly generated values used to calculate each case or iteration? For instance, what if in addition to finding the likelihood of losing money, I wanted to find the likelihood of losing money when Condition A is met, then Condition B, and so on? I think it would be easier to conditionally analyze a full table rather than generating a new Monte Carlo simulation for each condition.

    Thanks,
    Kevin

  • Great article and explanation of Monte Carlo simulation. That analogy to that scene in War Games is brilliant and makesbtotal sense.

  • Hi Rick
    Thank you for the lesson.
    I have a question, which probably sounds STUPID.
    When you have a distribution such as the Normal or LogNormal most of the data is close to the mean or mode etc. when you sample in say Excel what ensures that you are not giving equal weight to the tails where there is little data. Is it using the inverse function.
    This has been bugging me for days. I’m trying to write a simulation for the effect of risk drivers in VBA (as per Dr David Hulett) on project estimates but I cannot find a proper explanation in the books I’ve consulted or else the level of math Stats is above the level that I used to understand 30 years ago. Thank You Braam Botha

      • Hi Jordan I have a simulator and if I give you an example. If I have ratings of 161, 163, 133, 168, 142, 133 for one ATHLETE and simulate against other athletes. If I replace the 3rd and 5th number in this athletes sequence to something both in mid 160’s the chances of this athlete actually reduces which seems crazy as it should increase as rating is higher. I assume this is a SD issue. HELP!!! Thanks mate.

  • Rick,

    I am a novice on monte carlos and only in the last week started learning as much as I can since I am interviewing for a job.
    I would like one on one coaching on this. Would like your help.

  • Hi Rick, thanks for the great article. I have a question for you. I need to use the poisson distribution for my data since it’s definitely non-normal, however, Excel does not have the inverse function for Poisson as it does for the normal distribution (and some others). How would you recommend to work around this issue? Thanks

  • Hi Rick,

    I have a fairly complex model (hundreds of rows across multiple worksheets). I’d like to run monte carlo simulations on it by testing how the end results of the model vary when I vary one (or two or three) of the core input variables at a time.

    This is more complex than the example in this article because I can’t calculate the end results just using a few cells – it takes multiple worksheets to produce the output.

    I’m trying to extrapolate your example to my model, but I can’t figure it out yet. How can I simulate changing value G11 on Worksheet A then logging value G123 on Worksheet E a thousand times?

    Thanks!

  • Hi Rick, please I need a little clarification concerning the countif function you used. Do you mean counting value obtained from iteration less than 1,000? because I can see any value less than zero

  • How do you do the simulation if you have a Poisson distribution? We need another article to cover this example. It would be useful.

  • Hi Rick, thank you for providing such a great article on Monte Carlo (MC) simulation. I have read all the previous comments to make sure my simple question has not been answered elsewhere.

    In the sample worksheet that I downloaded, the formula in cell K2 shows “=COUNTIF(C12:C1011,”1000000″)/1000”. As you can see, the row references in the formula in K2 capture 1000 rows beginning at C12 whereas the row references in the formula in K3 start at C11 so a different block of 1000 rows is captured. The difference between the two blocks of 1000 rows is very slight (1 row) and there is no appreciable differences in the values of K2 and K3 when “C12:C1011” is used in the formula in K3.

    My question: is the starting row of C11 for the formula in K3 done on purpose or was a reason for this that I missed in the article (or maybe an unintentional copy and paste error)

    Many thanks in advance for your clarification and again for providing such a clear example of how to use MS Excel for MC simulations.

  • Hi Rick, Thank you very much for this presentation. It has wet my appetite to research this topic further. You described the Monte Carlo simulation clearly which made it easy to understand and follow.

    Cheers
    Nadarajan

  • If you do this and calculate the standard deviation of the numbers generated, it is not C4. It is generally materially higher than C4. Because the probabilities picked by RAND(0) are random you will pick numbers near the tail of the normal distribution more often than if the probability selection was itself normal. This seems to inflate the measured standard distribution. The mean is unaffected.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Free 24 Excel Tips Guide

    Discover the secrets to Excel efficiency with our free guide: '24 Proven Microsoft Excel Tips to Help You Save Time'

    A humble gift from us to you, to make your Excel journey smoother and more productive. Grab your copy now and start mastering Excel like never before!

    >