# Monte Carlo in Excel: Why Your Most Likely Estimate Is Wrong 83% of the Time

Adding up five most-likely task costs gives $121,500 and an 83% chance of overrun. Here is the simulation that produces the real distribution, built with NORM.INV, RAND, and a data table used as a trial engine.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-monte-carlo-simulation
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 11 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## Adding Estimates Adds the Wrong Thing

Five tasks, each with a most likely cost, summed to a project budget. It is how nearly every estimate is built and it produces a number with a specific, calculable, and unpleasant property: it is well below the mean of the possible outcomes.

The reason is that cost estimates are skewed. A task can be a little cheaper than expected and dramatically more expensive, so the average outcome sits above the most likely one. Summing most-likely values compounds that across every task.

## Three-point estimates

Ask for three numbers per task instead of one: the best case, the most likely, and the worst case. Those convert into a mean and a standard deviation without any simulation at all.

**The PERT conversion**

```
Mean = (Min + 4 × Likely + Max) ÷ 6    ·    Standard deviation = (Max − Min) ÷ 6
```
- The weight of 4 on the likely value is a convention, not a derivation.
- The divisor of 6 on the range assumes the extremes are roughly three standard deviations apart.
- Both are approximations, and both are far better than a single number.

**Five tasks**
| Task | Min | Likely | Max | PERT mean | SD |
| --- | --- | --- | --- | --- | --- |
| Design | $12,000 | $15,000 | $24,000 | $16,000 | $2,000 |
| Build | $30,000 | $38,000 | $60,000 | $40,333 | $5,000 |
| Test | $8,000 | $9,500 | $14,000 | $10,000 | $1,000 |
| Deploy | $45,000 | $52,000 | $78,000 | $55,167 | $5,500 |
| Train | $6,000 | $7,000 | $11,000 | $7,500 | $833 |
| Total | $101,000 | $121,500 | $187,000 | $129,000 | — |

> **Three totals, three meanings** — The sum of most-likely values is $121,500 and it is not the expected cost. The expected cost is $129,000. The sum of worst cases is $187,000 and it is close to impossible, because it requires every task to go wrong at once. None of these three is the number to put in a budget.

## Combining the uncertainty

Standard deviations do not add. Variances do, provided the tasks are independent. That single fact is why a portfolio of uncertain tasks is less uncertain in total than the sum of its parts.

**Total standard deviation**

```
=SQRT(SUMSQ(SDRange))
```
- √(2,000² + 5,000² + 1,000² + 5,500² + 833²) = $7,807
- The sum of the individual standard deviations is $14,333, nearly twice as large.
- That difference is real diversification, and it is why the worst case for the project is far below the sum of the worst cases.

## Building the simulation

With a mean and a standard deviation per task, one trial is one row of random draws.

**One random draw**

```
=NORM.INV(RAND(), TaskMean, TaskSD)
```
- RAND() returns a uniform value between 0 and 1; NORM.INV converts it into a normally distributed cost.
- One of these per task, then a total column summing the row.
- Press F9 and every trial redraws.

The awkward part is running a thousand trials and keeping the results. A one-variable data table does it, using a trick: the input cell it feeds is a cell nothing depends on.

- Put the trial numbers 1 to 1,000 down a column, say J2:J1001.
- In K1, one column right and one row up from the first trial, put =TotalCell, referencing the total of the single-trial row.
- Select J1:K1001, then Data, What-If Analysis, Data Table.
- Leave the row input cell blank and set the column input cell to any empty cell that nothing references.
- Excel recalculates the whole sheet a thousand times, and because RAND() is volatile, each pass draws fresh numbers. K2:K1001 now holds a thousand independent project totals.

> **Set the calculation mode first** — Formulas, Calculation Options, Automatic Except for Data Tables. Otherwise every keystroke reruns a thousand trials and the workbook becomes unusable. Press F9 when you want a fresh run.

## Reading the output

**What to compute from the thousand results**
| Figure | Formula | Result |
| --- | --- | --- |
| Mean | =AVERAGE(Results) | ≈ $129,000 |
| Standard deviation | =STDEV.S(Results) | ≈ $7,800 |
| P50, the median | =PERCENTILE.INC(Results, 0.5) | ≈ $129,000 |
| P80 | =PERCENTILE.INC(Results, 0.8) | ≈ $135,570 |
| P95 | =PERCENTILE.INC(Results, 0.95) | ≈ $141,840 |
| Chance of exceeding $121,500 | =COUNTIF(Results, ">121500")/COUNT(Results) | ≈ 83% |
| Chance of exceeding $129,000 | =COUNTIF(Results, ">129000")/COUNT(Results) | ≈ 50% |

The sixth row is the finding. Budgeting at the sum of most-likely values gives roughly a one in six chance of coming in on budget. Budgeting at the mean gives a coin flip. Budgeting at P80, which is $135,570 or 12% above the naive estimate, gives a four in five chance.

> **Contingency has a number now** — The gap between P50 and P80 is $6,570, which is 5.1% of the expected cost. That is a contingency figure derived from the estimates themselves rather than a round 10% added because 10% is what people add. It is also defensible in a way a round number is not.

## You may not need the simulation

When the tasks are independent and roughly normal, the total is normal too, and the percentiles come straight from a formula.

**The closed form**

```
=NORM.INV(0.8, TotalMean, TotalSD)
```
- =NORM.INV(0.8, 129000, 7807) returns $135,571, matching the simulation.
- Use the simulation when tasks are correlated, when distributions are skewed, or when the model contains conditional logic.
- Use the formula when the model is a simple sum, which is more often than people assume.

## What the simulation does not know

- Correlation. The model above assumes the five tasks are independent. In reality a bad scope decision inflates all of them together, and correlated risks produce a much wider distribution than this shows.
- Whether the three-point estimates are honest. Estimators anchor on the likely value and give a worst case that is not really the worst case. The output inherits every bias in the input.
- Unknown unknowns. A risk nobody listed has a probability of zero in the model and a real probability in the world.
- That costs are not normally distributed. They are right-skewed and bounded below, which NORM.INV does not capture. A lognormal or triangular distribution is more realistic and the arithmetic is only slightly harder.
- Anything about schedule. A cost simulation says nothing about duration, and the two interact when delays cost money.
- Whether the mitigation is worth it. The model quantifies risk; deciding what to spend reducing it is a separate judgement.

**Why is the sum of most-likely estimates too low?**

Because cost distributions are skewed. Each task can be a little cheaper than expected and much more expensive, so its mean sits above its mode. Adding modes across five tasks accumulates that gap; here it is $7,500, or 6% of the project.

**How do I run many trials in Excel without a macro?**

Use a one-variable data table whose column input cell is an empty cell nothing references. Each row of the table forces a full recalculation, and because RAND() is volatile every recalculation draws new numbers. A thousand rows gives a thousand independent trials.

**What is P80 and why use it?**

The cost that has an 80% chance of being enough. It is a common budgeting convention because it accepts a one in five chance of overrun, which is realistic, rather than a 50% chance, which is what budgeting at the mean means.

**Why do my numbers change every time I press a key?**

RAND() and RANDBETWEEN() are volatile and redraw on every recalculation. That is what makes the simulation work. To freeze a set of results, copy the output range and paste it as values.

**How many trials do I need?**

A thousand is enough for percentiles in the middle of the distribution. Ten thousand is worth having for the tails, since P95 and beyond are estimated from few observations. Run it twice and compare: if the answers move materially, run more trials.

**Should I use a normal distribution for costs?**

It is the easiest and not the most realistic. Costs cannot go below zero and have a long right tail, so a lognormal or a triangular distribution fits better. NORM.INV is fine for a first pass and understates the upper tail, which is the part you care about.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-monte-carlo-simulation). Quote freely with attribution and a link to this page._