Monte Carlo Simulation in Excel — Free Template + Guide (2026)

A single-point forecast — “profit will be £42,000” — is a guess dressed up as a fact. A Monte Carlo simulation replaces that guess with a distribution: it runs your model a thousand times with randomly varying inputs and tells you the realistic range of outcomes and the probability of each. In 45 minutes you’ll build a complete simulation in plain Excel — no add-ins — using RAND, NORM.INV, and a one-column Data Table, then summarise it with P10/P50/P90 percentiles and a histogram. Works in Excel 2010 through Microsoft 365.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

What is a Monte Carlo Simulation?

A Monte Carlo simulation answers the question every forecast quietly dodges: how wrong could this be? Instead of plugging single best-guess numbers into a model, you describe each uncertain input as a probability distribution — “revenue averages £100k with a standard deviation of £15k” — then recalculate the model hundreds or thousands of times, drawing fresh random values each time. Every recalculation is one possible future; the collection of all of them is a picture of the full range of futures your assumptions allow.

The name comes from the Monte Carlo casino — the method was christened in the 1940s by physicists at Los Alamos who used random sampling to solve problems too tangled for direct mathematics. The business version is the same idea in friendlier clothes. Will the project finish on budget? What is the chance the new product loses money in year one? How much cash buffer do we need to survive a bad quarter? These are all probability questions, and a Monte Carlo simulation answers them with actual probabilities rather than gut feel.

The payoff over a normal spreadsheet model is concrete. A single-point model says “profit = £42,000.” A Monte Carlo model says “median profit is £42,000, there’s an 80% chance it lands between £18,000 and £67,000, and a 9% chance of an outright loss.” The second statement is dramatically more useful for deciding whether to proceed, how much contingency to hold, and which input is doing the most damage when things go wrong.

And here is the part most people don’t expect: you do not need an add-in. Excel ships with everything required — RAND() for randomness, NORM.INV() to shape that randomness into realistic distributions, and the humble Data Table to repeat the whole model a thousand times in one command. This guide builds the entire pipeline with those three native tools.

Before You Start

A Monte Carlo model needs three things in place before any randomness enters the picture.

A working deterministic model

First, build your model with plain fixed numbers and confirm it calculates correctly. If profit = revenue − variable costs − fixed costs, type in best-guess values and check the output by hand. Randomising a broken model just produces a thousand wrong answers. The deterministic version is also your sanity check later: the simulation’s mean should sit close to it.

A mean and a spread for every uncertain input

Each input you want to randomise needs two numbers: a central estimate (mean) and a measure of uncertainty (standard deviation). Where do they come from? History is best — =AVERAGE() and =STDEV.S() over the last 24 months of actuals. Failing that, use the expert-judgement shortcut: ask “what range would I be 95% sure of?” and set the standard deviation to roughly a quarter of that range, since ±2 standard deviations covers about 95% of a normal distribution.

Automatic calculation switched on

Check Formulas → Calculation Options → Automatic. Data Tables silently return stale, identical values if calculation is set to Manual or “Automatic except for Data Tables” — the single most common reason a first simulation appears broken.

1.Step 1 — Set Up the Model

We will simulate a simple but realistic profit model: monthly profit = revenue − variable costs − fixed costs, where revenue and the variable-cost ratio are uncertain. Lay out the assumptions block first:

Below the assumptions, reserve a model block: B9 for simulated revenue, B10 for the simulated variable-cost percentage, and B11 for the output — profit. For now, fill B9 and B10 with the plain means and write the output formula:

With the deterministic values, B11 should show 100,000 − 55,000 − 30,000 = 15,000. Confirm that by hand before going further — this number anchors everything that follows. The structure matters more than the specific model: any spreadsheet where inputs feed formulas that produce one output cell can be Monte Carlo’d the same way, whether it is a project budget, a sales pipeline, or a discounted cash flow.

2.Step 2 — Random Inputs with RAND & NORM.INV

Now we make the inputs uncertain. The pattern is a single, wonderfully reusable formula. In B9 (simulated revenue), enter:

Here is what it does. RAND() returns a uniform random number between 0 and 1 — think of it as a random percentile. NORM.INV(probability, mean, std_dev) then asks: “which value of a normal distribution with this mean and standard deviation sits at that percentile?” Feed it a random percentile and you get a random draw from the normal distribution. Statisticians call this inverse transform sampling; you can call it the one-line random-number machine.

Repeat the pattern in B10 for the variable-cost percentage:

Press F9 a few times. Revenue should dance around 100,000, mostly within ±30,000 (two standard deviations); the cost ratio should hover near 55%. About 68% of draws land within one standard deviation of the mean and 95% within two — if your values look wilder or tamer than that, re-check the assumption cells.

Other distributions, same trick

The inverse-sampling pattern generalises. Uniform between a min and max: =min+RAND()*(max-min). Lognormal (always positive, skewed high — good for durations and prices): =LOGNORM.INV(RAND(),mean_ln,sd_ln). Discrete scenarios (say 20% chance of recession): =IF(RAND()<0.2,recession_value,normal_value). Mix and match per input.

3.Step 3 — Build One Complete Trial

With B9 and B10 randomised, your output cell B11 is already a complete trial: one fully consistent random scenario flowing through the whole model. Press F9 repeatedly and watch B11. Sometimes it shows a fat profit near 30,000; occasionally it dips negative when a weak revenue draw meets a high cost draw. Each press of F9 is one spin of the wheel.

This is the moment to eyeball-test the model. Three quick checks:

1. The centre is right. Over many F9 presses, outcomes should straddle the deterministic answer from Step 1 (15,000). If they systematically sit elsewhere, a formula references the wrong cell.

2. The spread is plausible. If every press swings profit by ±80,000 on a 15,000 base, an input’s standard deviation is likely off by a factor of ten (a classic: typing 5 instead of 5% for a ratio).

3. The direction makes sense. Temporarily set revenue’s std dev to 0 and press F9: now only costs vary, and profit should move opposite to the cost draw. Restore the value afterwards. Wiring errors are far easier to spot one input at a time.

One trial is an anecdote. A thousand trials are a dataset. The next step automates the F9 key.

4.Step 4 — Run 1,000 Trials with a Data Table

Excel’s Data Table — designed for what-if analysis — has a glorious side effect: it recalculates the entire workbook once per row. Combined with volatile RAND() inputs, that means each row captures a fresh, independent trial. Here is the recipe:

1. In D2:D1001, enter trial numbers 1 to 1000 (type 1 and 2, select both, double-click the fill handle, or use =SEQUENCE(1000) in Microsoft 365).

2. In E1 — the cell one column right and one row above the first trial number — link to your output:

3. Select the whole block D1:E1001.

4. Go to Data → What-If Analysis → Data Table. Leave Row input cell blank. For Column input cell, pick any empty, unused cell — $H$1 works. Click OK.

Excel substitutes each trial number into H1 (which nothing depends on — that is the trick), recalculates everything including the RAND draws, and records the output. Seconds later, E2:E1001 holds 1,000 independent simulated profits. You have just automated a thousand F9 presses.

5.Step 5 — Summarise with Percentiles

A thousand raw numbers tell you nothing until you compress them. Build a small summary block beside the trials:

The mean tells you the central tendency; the standard deviation tells you how violently outcomes scatter around it. But the stars of a Monte Carlo summary are the percentiles:

That is P10 — the value 10% of trials fall below. Copy the formula and change the second argument to 0.5 for P50 (the median) and 0.9 for P90. Add =MIN(E2:E1001) and =MAX(E2:E1001) for the extremes, but treat them lightly — the single worst draw out of 1,000 is noisy and changes every run, whereas P10 and P90 are stable.

One more line earns its keep in every risk conversation — the probability of a loss:

If 130 of 1,000 trials are negative, the model says there is a 13% chance of losing money. That single percentage usually lands harder in a meeting than the entire histogram.

6.Step 6 — Chart the Histogram

The summary numbers compress the distribution; the histogram shows it. In Excel 2016 or later, select E2:E1001 and go to Insert → Insert Statistic Chart → Histogram. Excel bins the outcomes automatically; right-click the horizontal axis → Format Axis to set the bin width by hand (round numbers like 5,000 read better than Excel’s defaults).

On older Excel, build the bins yourself. List bin upper-bounds in a column (say G2:G16: −20,000, −15,000, … 50,000), then count trials per bin:

Fill down and chart the counts as a column chart with the gap width reduced to ~5%. Either way, study the shape. A symmetric bell says the output mirrors its normal inputs. A skewed tail says some mechanism amplifies one direction. Two humps say a discrete scenario input (recession / no recession) is splitting the futures — in which case quoting a single mean is actively misleading, and the histogram just saved you from it.

Finish by marking the P10, P50, and P90 on the chart (add them as a scatter series or simple vertical-line shapes). A histogram with its three percentiles labelled is the single most persuasive risk visual you can put in a deck.

7.Step 7 — Interpret P10, P50 and P90

The three percentiles carve the distribution into a story anyone can follow:

Together they form the P10–P90 band: 80% of simulated futures land inside it. Quoting “profit: P50 £15k, P10–P90 −£2k to £32k” conveys in one line what a point forecast cannot — both the expectation and the honest uncertainty around it. The oil and gas industry has planned reserves this way for decades; finance teams increasingly do the same for budgets.

Two interpretation habits keep you honest. First, note whether the mean and the median disagree: on a skewed distribution the mean is dragged toward the long tail, and the median is usually the better headline. Second, remember the percentiles are conditional on your assumptions — P10 is the bad case given your means and standard deviations are right. The simulation quantifies the uncertainty you told it about; it cannot conjure the uncertainty you forgot.

And resist the universal temptation to plan to P90 and present P50. Pick the percentile that matches the stakes: cash-critical commitments should survive P10; stretch targets can flirt with P75.

8.Step 8 — Use the Simulation to Decide

A simulation earns its place when it changes a decision. Three patterns cover most uses:

Threshold probabilities

Any “what are the odds” question is one COUNTIF away. Probability of beating a £25,000 target:

Probability of breaching a cash floor, missing a covenant, exceeding a budget — all the same pattern with a different threshold.

Comparing options

Duplicate the model sheet for each strategy (raise prices vs. cut costs, say), run both simulations, and compare distributions rather than point estimates. Option A with a P50 of 18k and 5% loss risk may beat option B’s P50 of 21k with 25% loss risk — a trade-off invisible to deterministic models.

Finding the input that matters

Zero one input’s standard deviation at a time and re-run. Whichever input’s removal shrinks the output spread most is your dominant risk driver — the place where better information, hedging, or contractual protection buys the most certainty. This poor-man’s tornado analysis routinely redirects effort from inputs people argue about to inputs that actually move the answer.

Whatever the decision, report three things together: the P50, the P10–P90 band, and the probability of the threshold the audience cares about. That trio is the entire executive summary of a Monte Carlo simulation.

Try It: Live Monte Carlo Simulator

This widget runs the exact model from this tutorial — profit = revenue − 55% variable costs − fixed costs, with normally distributed revenue — in your browser. Set the mean revenue, its standard deviation, and the fixed costs, then hit Run 1,000 simulations. The histogram and P10/P50/P90 stats update live, exactly as your Excel Data Table column would.

Advanced Monte Carlo Tips

Reproducible runs with a frozen snapshot

RAND() redraws on every edit, so yesterday’s numbers are gone forever unless you freeze them. After a run you want to keep, copy the outcome column and Paste Special → Values into an archive sheet, stamped with the date and assumption set. Comparing frozen runs across assumption versions is how you audit a model’s evolution.

Correlated inputs

Treating revenue and costs as independent when they move together understates or overstates risk. The lightweight fix: drive both from one shared shock. Put =NORM.INV(RAND(),0,1) in a cell Z as a common factor, then build each input as mean + std_dev*(ρ*Z + SQRT(1-ρ^2)*NORM.INV(RAND(),0,1)) with ρ as the desired correlation. It is three extra cells, and it transforms the realism of joint-risk models.

More trials with dynamic arrays

In Microsoft 365, =NORM.INV(RANDARRAY(10000),mean,sd) spills 10,000 draws in one formula — no Data Table needed for single-input models. For multi-input models, combine spilled input arrays with BYROW or MAP. Ten thousand trials stabilise P5/P95 estimates noticeably versus one thousand.

From one-off simulation to living risk dashboard

A simulation you re-run by hand goes stale the week after the meeting. Upload your model’s data to DataHub Pro and it keeps the charts, percentile bands, and forecasts current automatically — a shareable link instead of an emailed workbook, with the distribution always reflecting the latest actuals.

Worked Example: Product Launch Go/No-Go

A small e-commerce firm is deciding whether to launch a new product line. The deterministic plan says: revenue £100,000, variable costs at 55%, fixed launch costs £30,000 — so profit £15,000. The board asks the obvious question: “how confident are we?”

The analyst builds the model from this tutorial. From two years of comparable launches she estimates revenue standard deviation at £15,000 and the cost-ratio standard deviation at 5 points. Inputs become =NORM.INV(RAND(),100000,15000) and =NORM.INV(RAND(),0.55,0.05); the output cell computes profit; a Data Table runs 1,000 trials into column E.

The summary block reads: mean £15,200; P10 −£1,800; P50 £15,100; P90 £32,400; probability of loss 13%. The histogram is a clean bell, slightly wider on the left because high cost-ratio draws compound with weak revenue draws.

The interpretation changes the meeting. The central case matches the original plan, but the board now sees a 1-in-8 chance of losing money and a realistic worst-tenth around −£2,000 — survivable. They also run the sensitivity check: zeroing the cost-ratio uncertainty barely narrows the band, but zeroing revenue uncertainty halves it. Conclusion: the launch is approved, and a £3,000 pre-launch demand test is commissioned, because the simulation showed revenue uncertainty — not cost control — is where the risk lives. That reallocation of attention is exactly what Monte Carlo is for.

Common Errors & Fixes

Every Data Table row shows the same number

Calculation is set to Manual or “Automatic except for Data Tables” — press F9 or switch to Automatic. Or the model has no RAND() anywhere, so every recalculation is identical. Check both.

#NUM! from NORM.INV

The standard deviation argument is zero or negative, or the probability argument is exactly 0 or 1. Make sure the std-dev cell holds a positive number and that you are feeding RAND() (which never returns exactly 0 or 1) rather than a hard-coded probability.

Impossible outcomes in the tails

Negative revenues, cost ratios above 100%. The normal distribution is unbounded — clamp with MAX(0,...)/MIN(1,...) or switch to a bounded/lognormal distribution for that input. Check MIN and MAX of your outcome column after every structural change.

The summary statistics jump around between runs

Normal for the extremes (MIN/MAX) and mild for P10/P90 at 1,000 trials. If the mean swings noticeably, you likely have too few trials for a very wide distribution — increase to 5,000–10,000 rows, or check for a stray impossible-outcome amplifier in the model.

The workbook has become painfully slow

Volatile RAND cells plus a Data Table recalculate on every edit. Set calculation to “Automatic Except for Data Tables” while editing, and keep the simulation in its own workbook away from heavyweight reports.