Monte Carlo simulation in Excel for decision making
How to build a Monte Carlo simulation in an Excel spreadsheet model, run iterations, read the histogram of outcomes and compare options for a shortlist decision.
Contents
- What a Monte Carlo simulation does
- Set up the spreadsheet model first
- Draw random values with the RAND function
- Match probability distributions to inputs
- Repeat the model with a Data Table
- Read the histogram of outcomes
- Turn results into a decision rule
- Extend Excel with add-ins and limits
- Questions about Excel Monte Carlo simulation
- What is Monte Carlo simulation in Excel for decision making?
- Do I need an add-in to run a simulation in Excel?
- How many simulation iterations should I run?
- Why does my RAND result change every time the sheet recalculates?
- Which probability distribution should I use for an input?
- How do I compare two options with simulation results?
A Monte Carlo simulation in Excel runs a spreadsheet model many times, each run drawing random values for the uncertain inputs. The results form a range of outcomes rather than a single number, which lets you compare options by how likely each one is to land near the result you want. Decision making improves because you see the spread of possible results, not one forecast.
- A Monte Carlo simulation replaces fixed inputs with random draws, so one model produces a range of outcomes.
- The RAND function refreshes with every calculation, which makes it the engine of the draws.
- The Data Table tool repeats the model across many simulation iterations and stores each result.
- A histogram of outcomes turns the stored results into a picture of the risk in each option.
- Excel add-ins can extend probability distributions beyond the built-in random tools.
What a Monte Carlo simulation does
Monte Carlo simulation in Excel answers a question by playing out the model many times with different random inputs. Each play is one iteration, and the collection of results is the outcome distribution. You read the distribution instead of trusting a single estimate.
The method fits decisions where at least one input is uncertain, such as a cost, a demand level or a conversion rate. A spreadsheet model states how the output depends on those inputs. Simulation then shows how sensitive the output is to that uncertainty.
The value for a shortlist decision is comparison. Two options may share the same average result yet differ in how widely the outcomes scatter. Simulation exposes that difference, which a single-point forecast hides.
Set up the spreadsheet model first
A clean spreadsheet model matters more than any simulation add-on. Separate the uncertain inputs into their own cells, keep the calculation formulas in a linked block, and put the single output in one named cell. The simulation then points at clear targets.
Label each uncertain input with its units and a plausible range. A range needs a low and a high value drawn from your own records or expert judgment, not from guesswork. Store the range next to the input so the simulation formulas can read it.
Test the model with fixed values before adding randomness. Change one input by hand and confirm the output moves in the expected direction. A model that fails this check will produce misleading results once random draws are attached.
Draw random values with the RAND function
The RAND function returns a decimal between zero and one and recalculates whenever the sheet recalculates. That behavior makes RAND the source of randomness in an Excel simulation. Wrap it in a formula to convert the decimal into a value inside your input range.
A simple uniform draw uses the low value plus the range width multiplied by RAND. A triangular or normal shape needs a longer formula or a built-in distribution function. The shape should match what you know about the input, not convenience.
Every recalculation changes the draws, so lock the results before you analyze them. Copy the results and paste them as values, or freeze calculation while you read the histogram. Unlocked results shift under you.
Match probability distributions to inputs
Probability distributions in Excel describe how likely each value of an input is. A uniform draw treats every value in the range as equally likely, which suits an input with no known shape. A triangular draw puts more weight near a stated middle value.
A normal distribution suits an input that clusters around an average with symmetric spread. Excel offers NORM.INV and related functions for that shape. A lognormal shape fits quantities that cannot go below zero and have a long upper tail.
Choose the shape from the evidence you hold, and state the choice in the model notes. The distribution shapes the outcome range as much as the input values do. A reader who cannot see the assumption cannot judge the result.
| Distribution | Shape of input | Excel route |
|---|---|---|
| Uniform | Any value in a range equally likely | Low plus width times RAND |
| Triangular | Weight near a stated middle value | Formula from three points |
| Normal | Symmetric spread around an average | NORM.INV with RAND |
| Lognormal | Positive values with a long upper tail | LOGNORM.INV with RAND |
Repeat the model with a Data Table
The Excel Data Table tool repeats a formula across many rows and stores each result. You place a column of empty input cells beside the output cell, then run the table as a one-variable what-if. Each row becomes one simulation iteration.
A Data Table needs a trigger to recalculate on each row, and RAND inside the model supplies that trigger. The tool then records the output for every row in the column you prepared. The stored column is your outcome sample.
The number of simulation iterations depends on how precise the outcome picture must be. More iterations smooth the histogram, while fewer run faster on a large model. Record the count you used so the analysis can be repeated.
Read the histogram of outcomes
A histogram of outcomes groups the stored results into bins and shows how often each range occurred. The shape tells you where the result is likely to fall and how far it can stray. Build it from the stored values, not from the live random cells.
Read the histogram for its middle, its spread and its tails. A narrow shape means the option is stable under uncertainty, while a wide shape means the result is exposed. The tails show the results that would hurt most.
For a shortlist, place the histograms of the options side by side on the same axis. Overlapping ranges show where one option stops being clearly better. A decision rule built on percentiles of the stored results then follows from the picture.
Turn results into a decision rule
A decision rule states which option you pick for a given pattern of outcomes. One rule compares the chance that each option clears a target value. Another rule compares the worst plausible outcome each option carries.
Percentiles from the stored results make these rules concrete. The fifth percentile describes a poor outcome and the ninety-fifth a strong one. Comparing those points across options shows the trade between expected result and downside risk.
Write the rule down before you compare the options, so the numbers cannot bend it afterward. State which uncertain inputs drive the result and how confident you are in their ranges. A decision that survives a change in those ranges is more robust than one that depends on a single draw.
Extend Excel with add-ins and limits
Excel add-ins add distribution libraries, dedicated simulation buttons and stored iteration logs. They save formula work on a large model and reduce manual copying. The built-in RAND and Data Table route covers the same logic without extra software.
Excel simulation has limits worth naming. A large model with many iterations can be slow to recalculate, and every change reruns the whole sample. Correlated inputs need extra formulas, because RAND draws each cell independently.
Treat the results as a guide to the range of outcomes, not as a forecast you can rely on. The quality of the output depends on the input ranges and distribution shapes you set. A model built on weak ranges gives a confident-looking answer with no real support.
Questions about Excel Monte Carlo simulation
What is Monte Carlo simulation in Excel for decision making?
Monte Carlo simulation in Excel repeats a spreadsheet model many times with random inputs and collects the outcomes. You compare options by the range of results each one produces. The method suits decisions where at least one input is uncertain.
Do I need an add-in to run a simulation in Excel?
No, the RAND function and the Data Table tool can run a basic simulation without an add-in. Excel add-ins add distribution libraries and stored iteration logs for larger models. The built-in route follows the same logic with more manual setup.
How many simulation iterations should I run?
The iteration count depends on the size of the model and the precision you need from the histogram. More iterations smooth the outcome picture, while fewer recalculate faster. Record the count you used so the analysis can be repeated.
Why does my RAND result change every time the sheet recalculates?
The RAND function refreshes with every recalculation by design, which is what drives the draws. Copy the stored results and paste them as values before you analyze them. Frozen values keep the histogram stable while you read it.
Which probability distribution should I use for an input?
The distribution should match what you know about the input, not convenience. A uniform draw suits a range with no known shape, and a triangular draw weights a stated middle value. State the choice in the model notes so a reader can judge it.
How do I compare two options with simulation results?
Compare the histograms of both options on the same axis and look at the overlap. Percentiles of the stored results show the poor and strong ends of each range. Write the decision rule down before you compare, so the numbers cannot bend it.