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.
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.
Swipe sideways to compare columns.
| 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 | — |
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.
Building the simulation
With a mean and a standard deviation per task, one trial is one row of random draws.
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.
Reading the output
Swipe sideways to compare columns.
| 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.
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.
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.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team