What is the chance of a negative NPV?
In this business case for a new product line, the NPV is negative in 29.6% of 10,000 simulated outcomes, even though the base case gives an NPV of 562 (USD thousands) and an IRR of 17.2% at a 9% discount rate. To find that chance, run the cash-flow model thousands of times with its uncertain assumptions drawn from ranges, a Monte Carlo simulation, and count the outcomes with an NPV below zero.
The average NPV is 268, and the IRR falls short of 9% in exactly the outcomes where the NPV is negative. The base case is a single outcome, and a favorable one: 73.7% of the simulated outcomes are worse.
The model
The workbook is an ordinary five-year business case on two sheets. Assumptions holds a table with Low, Base and High columns: the investment, the first year’s volume and its growth, the price and the unit cost, plus fixed costs, working capital, tax and the discount rate at one value each. Model turns the Base column into yearly revenue, EBITDA, depreciation, tax and the change in working capital, and ends in free cash flow, the NPV and the IRR, with plain formulas and no add-in functions. The product line runs five years: working capital comes back in year 5 and nothing is earned after it. Tax is 25% of EBIT, and a loss lowers the tax on the company’s other profits.
For the simulation, each ranged assumption becomes a PERT distribution from Low through Base to High, and its three parameters point at those cells: edit the table and the simulation follows, with no second copy of the numbers. A competitor enters with a 30% chance and cuts the price by 10% from year 3. The price and the unit cost move together, with a rank correlation of 0.6, because material costs push both.
| Assumption | Low | Base | High |
|---|---|---|---|
| Investment | 1,800 | 2,000 | 2,600 |
| Year-1 volume | 30 | 40 | 50 |
| Volume growth per year | 2% | 8% | 13% |
| Price per unit (USD) | 54 | 60 | 65 |
| Unit cost (USD) | 31 | 34 | 40 |
| Fixed costs, year 1 | 400 |
|---|---|
| Fixed cost growth per year | 3% |
| Price cut if a competitor enters | 10% |
| Working capital (share of revenue) | 15% |
| Tax rate | 25% |
| Discount rate | 9% |
| Chance a competitor enters (the price cut applies from year 3) | 30% |
The NPV cell is =B14+NPV(Assumptions!C16,C14:G14). Excel’s NPV() discounts the first value it gets by a full year, so the year-0 investment is added outside it; putting it inside would discount every cash flow a year too many, which divides the NPV by 1 + rate: 515 instead of 562 here. The IRR cell is =IRR(B14:G14), over all six cash flows.
Why the base case is not the expected case
A business case is usually presented at its base case: every assumption at its most likely value. Two things pull the simulated outcomes below it. The competitor is not in the base case at all, yet has a 30% chance of entering, and when it does, the NPV averages −62 instead of 410. And the ranges are lopsided where it hurts: the investment can come in 1,800 at best but 2,600 at worst around its base of 2,000, so its simulated average is 2,067; the unit cost reaches further up (40) than down (31) from 34, while the price and the volume growth reach further down than up.
Together they put the average NPV at 268, 294 below the base case of 562. Each Base value is the most likely one; the base case just leaves out what can go wrong.
Results
| Base case (every assumption at Base, no competitor) | 562 |
|---|---|
| Mean of the simulated NPVs | 268 |
| P10 (10% of outcomes are lower) | −328 |
| P50 | 253 |
| P90 | 884 |
| Average of the worst 5% | −626 |
| Chance of a negative NPV | 29.6% |
| Chance the IRR is below 9% | 29.6% |
| IRR: base case / P50 | 17.2% / 12.7% |
NPV and IRR tell the same story
A negative NPV at 9% means the project earns less than 9% a year, which is what an IRR below 9% says. In this model both happen in the same 29.6% of outcomes, trial by trial: the cash flows start with the investment and turn positive once, so each outcome has one IRR. The IRR’s own range runs from 3.8% at P10 through 12.7% at P50 to 21.4% at P90, against 17.2% in the base case.
The NPV is the better result to simulate and report. It adds up, so the mean of the simulated NPVs is the NPV of the expected cash flows, and it is defined in every outcome. A project whose cash flows change sign more than once, for example with a large mid-life overhaul or a clean-up cost at the end, can have more than one IRR, or none.
What drives the value
The contribution to variance shows which assumptions account for the spread of the NPV; xellstorm estimates it from the ranks of the trials, as the app shows it. Year-1 volume comes first with 32.8%, then the price with 24.1%, the competitor with 18.0% and the unit cost with 15.8%. These shares treat each input as if it moved alone. The price and the unit cost move together, so their shares overlap: taken as a pair they account for about 22% of what the regression explains, not the 39.9% their two shares add up to. The investment, often the most debated number in a business case, explains only 4.5%. Market research on volume, and a pricing plan for the day a competitor arrives, would narrow the range more than another round on the capital budget. Tornado charts and sensitivity analysis explains these measures.
Assumptions that move together
If the price and the unit cost were drawn independently, the simulation would pair high costs with low prices more often than the business allows, and overstate the risk: the standard deviation of the NPV would be 535 instead of 463, the chance of a negative NPV 31.9% instead of 29.6%, and the P5 −590 instead of −473. Correlations that are left out can err in either direction; here, where costs and prices rise together, leaving it out makes the case look riskier than it is. In xellstorm a correlation reorders the draws without changing any input’s own distribution, and it can be estimated from historical data.
Try it yourself
- Open the model in xellstorm. Its inputs, outputs, the correlation and the target “NPV below zero” are already set; there is nothing to install and no sign-up, and the workbook is calculated in your browser.
- Run it: with the same seed and number of trials you get the numbers on this page. Hover over the S-curve to read the chance of the NPV falling below any value.
- Change a range on the Distributions step, or set the competitor’s chance in a scenario, and run again to see how the chance of a negative NPV moves.
- Then open your own business case and pick the assumptions you are unsure of on the sheet, or on the model map. On the Distributions step, a Low / Base / High table next to an assumption links to its distribution in one click.
Can I do this in plain Excel?
Yes, with more work: replace each assumption with a formula that draws a random value, repeat the calculation with a data table, and count the negative NPVs. Monte Carlo simulation in Excel shows the formulas, and what changes when a tool runs the workbook for you.
Questions
Does a negative NPV mean the project loses money?
Not necessarily. A negative NPV means the project earns less than the discount rate, here 9% a year; it may still pay back its investment in cash. In this model 89% of the outcomes with a negative NPV still return more cash than was invested. What a negative NPV destroys is value compared with the return the money could earn elsewhere at the same risk.
Why does Excel’s NPV function give a different answer?
NPV(rate, values) treats its first value as arriving one period from now. With the year-0 investment inside the range, every cash flow is discounted one year too many. Add the year-0 cash flow outside the function, as this workbook does, or use XNPV with dates.
What discount rate should the simulation use?
The project’s cost of capital: the return investors require for its market risk. The simulation puts the project’s own uncertainties into the cash flows, so a rate padded for those same risks, as hurdle rates often are, would count them twice.
Should I report the mean NPV or the base case?
The mean NPV is the expected value of the project, the figure to compare with alternatives; the base case is one outcome among many. Report the mean together with the chance of a negative NPV and a range such as P10 to P90 (−328 to 884 here), so that readers see the downside as well as the middle.
How many trials are enough?
Enough that the numbers you report stop moving from run to run: the noise in a simulated mean halves each time the number of trials quadruples. This example uses 10,000; xellstorm can also keep running until the mean and percentiles settle within a tolerance you choose. See how many trials.
Related
- Guide
Tornado charts and sensitivity analysis explained
A tornado chart moves each input alone, from P10 to P90 by default in xellstorm. In a building estimate, one risk moves the cost by 150 (USD thousands). - Guide
P50, P80 and P90: what they mean and which to budget at
P80 is the cost 80% of simulated outcomes stay under. Six building cost items’ P80s add up to 2,843, but their sum’s P80 is 2,756 (USD thousands). - Guide
PERT distribution in Excel: formula, mean and when to use it
A PERT is a beta distribution from min to max with mean (min + 4 × likely + max)/6. The Excel BETA.INV formula, and PERT vs triangular. - Guide
Monte Carlo simulation in Excel, with or without add-ins
Three ways to run a Monte Carlo simulation on an Excel model: RAND() and a data table, an add-in, or a browser tool with no add-in. PERT formula included.
xellstorm is a browser-based Monte Carlo simulation tool for Excel models: no add-in, and the workbook never leaves your computer.