Monte Carlo simulation in Excel, with or without add-ins
You can run a Monte Carlo simulation on an Excel model in three ways: with RAND() formulas and a data table, with an add-in, or with a tool that runs the workbook outside Excel. Each recalculates the model thousands of times with the uncertain inputs drawn from ranges, and reads the results as probabilities: the chance of going over budget, the cost you stay under with 80% confidence, the inputs that matter most.
Why simulate instead of using one number
A spreadsheet gives one answer for one set of inputs. When the inputs are estimates, that answer hides how far off it can be, and it is often optimistic: adding up most likely costs ignores that costs overrun more easily than they underrun. In our project cost example, the base estimate, the sum of the most likely costs, is exceeded in 94.2% of simulated outcomes. Best, base and worst cases do not fix this: they give three answers without saying how likely each is.
1. Plain Excel, no add-in: RAND() and a data table
Excel has no simulation command, but its functions are enough for a small model:
- Replace each uncertain input with a random draw.
RAND()returns a uniform number between 0 and 1; an inverse distribution function turns it into a value from the range you want (formulas below). - Repeat the calculation. Put trial numbers 1 to 5,000 in a column and, in the next column one row above the first number, a reference to the output cell. Select both columns from that row down, choose Data › What-If Analysis › Data Table, and enter any empty cell on the same sheet as the Column input cell. Every row recalculates the workbook, so every row gets new random draws.
- Summarize the column.
AVERAGE,PERCENTILE.INCandCOUNTIF(divided by the number of trials) give the mean, the P80 and the chance of exceeding a target; a histogram chart shows the shape.
| Input | Formula |
|---|---|
| Normal (mean, standard deviation) | =NORM.INV(RAND(), mean, sd) |
| PERT (min, most likely, max) | =BETA.INV(RAND(), 1+4*(ml-min)/(max-min), 1+4*(max-ml)/(max-min), min, max) |
| Triangular (min, most likely, max), with the draw in U | =IF(U<(ml-min)/(max-min), min+SQRT(U*(max-min)*(ml-min)), max-SQRT((1-U)*(max-min)*(max-ml))) |
| Risk event (probability p, cost c) | =IF(RAND()<p, c, 0) |
This works, and it is a good way to learn. Its limits show on real models:
- A data table recalculates the whole workbook for every trial, so large models get slow. Setting calculation to “Automatic except for data tables” keeps the workbook usable in between.
RAND()draws again on every recalculation, so the results change each time the workbook recalculates, unless you paste them as values.- Correlated inputs, sensitivity analysis and reports all need formulas and charts of your own.
2. An Excel add-in
Add-ins such as @RISK, Crystal Ball, Analytic Solver and ModelRisk add distribution functions to Excel (for example a PERT function you type into a cell), run the trials for you, and produce charts, sensitivity analysis and reports. They run inside Excel, so each user needs Excel and the add-in installed, which in many companies means a request to IT.
3. A tool that runs the workbook outside Excel
A third option is a tool with its own calculation engine that reads the .xlsx file and runs the trials itself. xellstorm works this way, in the browser:
- Open the workbook you already have; it is calculated in your browser tab and never uploaded. A compatibility check compares its formulas with the values Excel saved and flags any cell the engine cannot calculate.
- Click the uncertain inputs on a map of the model and give them ranges; click the results to track. If the workbook already uses
NORM.INV(RAND(), …)or the common distribution functions of @RISK, ModelRisk or Analytic Solver, supported formulas become inputs with their parameters still linked to your cells. TheBETA.INVformula for a PERT above also converts: its arithmetic shape arguments remain linked and are evaluated before each run (see PERT distribution in Excel). - Runs use a seed, so the same model gives the same numbers every time, and in most workbooks only the cells a trial changes are recalculated.
The three ways side by side
| Plain Excel | Excel add-in | xellstorm | |
|---|---|---|---|
| Install | Nothing | An add-in in Excel | Nothing: runs in the browser |
| Distributions | Built from formulas | Many, as functions | 48, including your own tables |
| Repeating trials | A data table, recalculating the whole workbook | Built in | Built in, recalculating only what changes in most workbooks |
| Same numbers next time | No, unless pasted as values | With a fixed seed | With a seed, whatever the number of CPU cores |
| Correlation, sensitivity, reports | Your own formulas and charts | Built in | Built in |
How to read the results
- Mean
- The average of all simulated outcomes. With skewed inputs it differs from the result of the most likely inputs.
- Percentiles (P10, P50, P80, P90)
- The value that 10%, 50%, 80% or 90% of outcomes stay at or below. P50 is the median; P80 and P90 are common budget levels (more on P-levels).
- Probability of a target
- The share of outcomes that meet a condition, such as total cost above the budget.
- S-curve
- The cumulative probability plotted against the result: read any percentile, or the chance of staying under any value.
- Tornado chart
- How far the result moves when each input goes from a low to a high percentile, the others held at their median; the widest bars are the inputs that matter most (tornado charts).
- CVaR (worst 5% average)
- The average of the worst 5% of outcomes: how bad the bad cases are, not only how often they happen (sizing a reserve from the tail).
How many trials?
Enough that the numbers you report stop moving. The noise in a simulated mean shrinks with the square root of the number of trials: quadrupling the number of trials halves it. A few thousand trials are a common start; tail percentiles such as P95 need more than the mean. xellstorm can run until the mean and percentiles settle within a tolerance you choose.
Questions
Is a Monte Carlo simulation the same as scenario analysis?
No. Scenario analysis calculates a few chosen cases, such as best, base and worst, without saying how likely each is. A simulation draws thousands of combinations from the ranges and tells you how likely each result is.
Which distribution should I use for an estimate?
For expert estimates given as min, most likely and max, PERT or triangular. For quantities that cannot go below zero and have a long right tail, lognormal. For an event that happens or not, a probability with a cost if it happens. With data, fit a distribution to it.
Do I need to change my workbook?
Not with xellstorm: the distributions are kept next to the workbook, not in it, and can read their numbers from cells you already have, such as Min / Likely / Max columns; your formulas stay as you wrote them. With plain Excel or an add-in, the ranges are kept in the workbook itself, as formulas or as the add-in’s own definitions.
How do I make a PERT distribution in Excel?
Excel has no PERT function, but a PERT distribution is a beta distribution scaled to run from min to max, so BETA.INV draws from it: =BETA.INV(RAND(), 1+4*(ml-min)/(max-min), 1+4*(max-ml)/(max-min), min, max), with min, most likely (ml) and max replaced by cell references. Its mean is (min + 4 × most likely + max) / 6. PERT distribution in Excel compares it with the triangular distribution.
Can I run a Monte Carlo simulation in Excel without an add-in?
Yes. Draw each uncertain input with a formula such as =NORM.INV(RAND(), mean, sd), repeat the model with a data table, and summarize the results with AVERAGE, PERCENTILE.INC and COUNTIF. Or open the workbook in xellstorm, which runs the trials in the browser with no add-in and no Excel needed.
Related
- Guide
PERT distribution in Excel: formula, mean and when to use it
A PERT is a beta distribution from min to max with mean (min + 4 × likely + max)/6. The Excel BETA.INV formula, and PERT vs triangular. - Guide
P50, P80 and P90: what they mean and which to budget at
P80 is the cost 80% of simulated outcomes stay under. Six building cost items’ P80s add up to 2,843, but their sum’s P80 is 2,756 (USD thousands). - Guide
Tornado charts and sensitivity analysis explained
A tornado chart moves each input alone, from P10 to P90 by default in xellstorm. In a building estimate, one risk moves the cost by 150 (USD thousands). - Project cost and schedule
How much contingency does a construction project need?
Worked example: a building estimate’s most likely costs are exceeded in 94.2% of simulated outcomes. Size the construction cost contingency with Monte Carlo.
xellstorm is a browser-based Monte Carlo simulation tool for Excel models: no add-in, and the workbook never leaves your computer.