Monte Carlo Simulation in Financial Models: A Practical Guide
02/03/26
Get your FREE Monte Carlo Simulation Download:
Download NOW!Most financial models have three scenarios: base case, upside, downside. You present them to the board, the investors nod, and everyone pretends the world will behave like one of those three tidy columns.
It won’t. The world doesn’t come in three flavours. Revenue could be 5% above base case while costs are 12% above and interest rates are 80 basis points higher than forecast. Your three scenarios don’t capture that combination. Monte Carlo simulation does.
What Is Monte Carlo Simulation?
Monte Carlo simulation runs your financial model thousands of times, each time with a different set of randomly generated inputs drawn from probability distributions you define. Instead of three scenarios, you get 10,000 outcomes — a full probability distribution that tells you not just “what happens if revenue falls 10%” but “what is the probability that the project IRR falls below the hurdle rate?”
That’s a fundamentally different — and far more useful — question for decision-makers.
When It’s Worth the Effort
Monte Carlo isn’t for every model. It adds complexity, takes time to set up properly, and requires you to define probability distributions for your key inputs — which is itself a judgment call.
It’s worth using when:
- The decision is large and irreversible (major capital expenditure, acquisition, infrastructure investment)
- Multiple uncertain inputs interact in non-obvious ways (revenue, costs, and financing terms all uncertain simultaneously)
- Stakeholders need to understand the probability of downside outcomes, not just the magnitude
- Regulatory or lender requirements demand probabilistic analysis (increasingly common in infrastructure and energy)
- You need to justify a decision with more rigour than “the base case IRR is above hurdle”
For a simple budget forecast or a three-year operating plan, Monte Carlo is overkill. For a £200m infrastructure investment decision, it’s arguably negligent not to use it.
Setting It Up in Excel
You don’t need specialist software. Excel can handle Monte Carlo simulation with VBA or, for simpler models, with Data Tables. Here’s the practical approach.
Step 1: Identify Your Key Uncertain Inputs
Not every input is uncertain. Some are contractually fixed (a lease payment, a debt coupon rate). Focus on the inputs that genuinely drive uncertainty in your model. Typically these are:
- Revenue growth rates or volumes
- Operating cost inflation
- Construction cost estimates
- Interest rates or discount rates
- Exchange rates
- Commodity prices
- Timing assumptions (construction duration, ramp-up period)
Limit yourself to 5-10 key variables. More than that and the analysis becomes unwieldy without adding proportionate insight.
Step 2: Define Probability Distributions
For each uncertain input, you need to specify a probability distribution — not just a single number, but a shape that describes the range of plausible values and how likely each is.
The most common distributions in financial modelling:
Normal distribution — symmetric, bell-shaped. Use when the input is equally likely to be above or below the expected value. Example: inflation rate, where 2.5% is the best estimate but it could be anywhere from 1% to 4%.
Triangular distribution — defined by a minimum, most likely, and maximum value. Use when you have a best estimate and a reasonable range. Example: construction cost, where the best estimate is £150m, the minimum is £130m, and the worst case is £200m.
Uniform distribution — every value in a range is equally likely. Use when you genuinely have no idea which value is more probable. Example: a regulatory decision that could set a tariff anywhere between £50 and £70 per unit.
Lognormal distribution — skewed right, cannot go below zero. Use for values that are naturally positive and have more upside risk than downside. Example: commodity prices, property values.
In Excel, you generate random values from these distributions using formulas:
Normal: =NORM.INV(RAND(), mean, std_dev)
Triangular: requires a custom formula or VBA function
Uniform: =min + RAND() * (max - min)
Lognormal: =LOGNORM.INV(RAND(), mean_of_ln, std_dev_of_ln)Step 3: Link Random Inputs to Your Model
Create a section in your model — an “assumptions” or “simulation” sheet — where the random values are generated. Your model’s input cells reference these random values instead of fixed numbers.
When you press F9 (recalculate), the RAND functions generate new random numbers, the inputs change, and the entire model recalculates with a new set of assumptions. Each recalculation is one “trial” of the simulation.
Step 4: Capture the Outputs
For each trial, you need to record the key output metrics: project IRR, NPV, equity IRR, DSCR minimum, payback period, or whatever matters for the decision.
The simplest method is a VBA macro that:
- Recalculates the workbook
- Reads the output cells
- Writes the values to a results table
- Repeats 5,000 or 10,000 times
A basic macro looks like this:
Sub RunMonteCarlo()
Dim i As Long
Dim nTrials As Long
nTrials = 10000
For i = 1 To nTrials
Application.Calculate
Sheets("Results").Cells(i + 1, 1).Value = Sheets("Outputs").Range("IRR_Cell").Value
Sheets("Results").Cells(i + 1, 2).Value = Sheets("Outputs").Range("NPV_Cell").Value
Next i
End SubFor models with circular references or iterative calculations, you may need to add Application.CalculateFull or manage the iteration settings explicitly.
Step 5: Analyse the Results
With 10,000 output values, you can now answer probabilistic questions:
Histogram — plot the distribution of outcomes. Is the IRR distribution symmetric or skewed? Where does the mass of the distribution sit?
Percentiles — what is the P10 (10th percentile) IRR? The P50? The P90? These tell the board: “there is a 90% probability the IRR exceeds X%.”
Probability of threshold breach — what is the probability the DSCR falls below 1.2x in any year? What is the probability the NPV is negative? These are the questions that actually drive investment decisions.
Sensitivity — which inputs drive the most variation in the output? You can calculate the correlation between each random input and the output metric to rank the drivers of risk.
In Excel, use PERCENTILE.INC, COUNTIF (for probability calculations), and CORREL (for sensitivity). For the histogram, a simple FREQUENCY function with bins works well, or use a pivot chart.
Common Pitfalls
Correlated inputs. If your revenue and costs are both uncertain, they may be correlated — when the economy is strong, both revenue and costs tend to be higher. If you simulate them independently, you’ll overstate the range of outcomes. For correlated inputs, you need to generate correlated random variables (using Cholesky decomposition or a simpler rank-correlation approach).
Too many iterations. 10,000 trials is usually sufficient for stable percentile estimates. Beyond 50,000, you’re adding computation time without meaningfully improving accuracy.
Garbage in, garbage out. The quality of the analysis depends entirely on the quality of the input distributions. If your revenue distribution is a guess, the output distribution is a precisely quantified guess. Be honest about what you know and what you don’t.
Over-interpreting precision. Don’t report “the P50 IRR is 12.37%.” Report “the P50 IRR is approximately 12%.” The false precision undermines credibility.
What It Gives Decision-Makers
A deterministic model gives the board three numbers: base case, upside, downside. The board picks one and makes a decision.
A Monte Carlo model gives the board a probability distribution and specific answers to specific questions: “What is the probability we fail to meet the hurdle rate?” “What is the probability the debt service cover ratio breaches covenant?” “What is the range of outcomes we should plan for?”
That’s a qualitatively different conversation. It’s not a guarantee of better decisions, but it’s a better foundation for them.
Will Wardle builds financial models with integrated Monte Carlo simulation for clients making complex investment decisions. He has 25 years’ experience in financial modelling, starting at a Big 4 accountancy firm.