Monte Carlo Simulation in Excel: A No-Add-in Guide - and When Scenarios Beat It
A Monte Carlo simulation can be built in Excel without any add-in: uncertain drivers are modeled as probability distributions (RAND with NORM.INV or a triangular distribution), the model is recalculated thousands of times - classically via the data-table trick - and the resulting distribution shows the range and probabilities of the target metric. The setup takes about an hour. The more important question is a different one: for many steering questions in controlling, a few deliberately chosen scenarios are the sharper tool than ten thousand random runs.
Step by step: Monte Carlo with built-in functions
- Build the driver model. As with any simulation, you first need a formula chain from drivers to target metric - in essence a small value driver tree with input, formula and result cells.
- Define uncertainty per driver. Instead of a fixed value, each uncertain driver gets a distribution: NORM.INV(RAND(), mean, standard deviation) for normal distributions, or a triangular distribution from min/most likely/max - often the more honest choice, because business units can estimate exactly those three values.
- Generate the runs. The standard trick: run a data table with 1,000 to 10,000 rows over an empty input cell - Excel recalculates the model for every row, and each row becomes one simulation run.
- Evaluate the distribution. The runs yield summary statistics (mean, P10/P50/P90 percentiles) and a histogram: how likely is EBIT to miss the target?
Where the Excel version struggles
Technically the setup works; practically there are three brakes. First, performance: ten thousand recalculations of a larger model make the workbook sluggish. Second, correlations: real drivers move together (raw material prices and sales prices), which is tedious to model with built-in functions - uncorrelated random runs systematically underestimate tail risks. Third, model quality itself: according to spreadsheet research by Raymond Panko (University of Hawaii), 94 percent of audited operational workbooks contain at least one error, and a simulation multiplies a model error into every one of the ten thousand iterations.
Monte Carlo or scenarios: which fits when?
| Criterion | Monte Carlo simulation | Deterministic scenarios |
|---|---|---|
| Core question | How likely is a range of outcomes? | What do we do if case X happens? |
| Input | Distributions per driver | Consistent assumption sets |
| Output | Range with probabilities | Comparable, explainable futures |
| Traceability for management | Low (“black-box cloud”) | High (every assumption nameable) |
| Link to action | Indirect | Direct (measures per scenario) |
| Typical use | Risk capital, project risks, portfolios | Corporate planning, forecasting, board discussions |
The rule of thumb: Monte Carlo answers probability questions, scenarios answer decision questions. A board rarely reacts to “EBIT exceeds 90 million with 78 percent probability” - it reacts to “in the tariff scenario we lose 96 million, with countermeasures 18”. That is why most planning processes work with a few deliberately chosen scenarios; according to the BARC Planning Survey, 47 percent of companies use scenario simulations. For how the two worlds fit together methodically, see scenario planning in controlling; for the Excel practice, the guide on scenario analysis in Excel.
The foundation is the same in both cases: a clean driver model. The free Driver Tree Assistant generates an interactive starting point in 60 seconds.
Frequently asked questions about Monte Carlo simulation in Excel
Do I need an add-in for Monte Carlo in Excel?
No. With RAND, NORM.INV and the data-table trick, a simulation works with built-in functions. Add-ins mainly add convenience: ready-made distributions, correlation matrices and faster runs.
How many iterations do I need?
For stable means, 1,000 runs often suffice; for reliable tail percentiles (P5, P95), 10,000 is common. In Excel, computation time becomes the limit before statistics do.
What are the biggest sources of error?
Unrealistic distributions (estimated too narrow), ignored correlations between drivers, and errors in the base model that propagate into every iteration. The simulation is never better than the driver model underneath it.
When are scenarios the better choice?
Whenever decisions should follow from the result: scenarios connect assumptions to actions and remain explainable to management. Monte Carlo complements them where probability statements are needed - for example in investment or project risk.