An example of a Monte Carlo simulation risk analysis model for NPV and business cashflow modeling. Last updated September 2026.

Technical difficulty: 2

Techniques used: Monte Carlo simulation in Excel

ModelRisk functions used: VoseCombined,VoseDiscrete,VoseOgive

What is a probability analysis for NPV?

A probability analysis for NPV replaces the single-point assumptions in a discounted cashflow model with probability distributions and runs a Monte Carlo simulation, producing the full distribution of possible NPVs instead of one number. From that distribution you read the expected NPV, the realistic range, and — usually the decision-maker's first question — the probability that the investment loses money.

A conventional DCF hides its own uncertainty: every input is somebody's best guess presented as a fact, and the single NPV it prints inherits all of those guesses at once. The model on this page is a worked example of the probabilistic alternative. Its static version prints an NPV of −$37,134 — reject. The simulated version of the same model shows a 21% probability of a negative NPV and a mean NPV of about $57,000 — a different decision, from the same business case.

How do you run a probability analysis for NPV in Excel?

Build the DCF as normal, then replace each uncertain input with a probability distribution: three-point estimates become Modified PERT distributions, historical data is fitted or used directly, and discrete events enter with their probabilities. Mark the NPV cell as the simulation output, run a few thousand samples, and read the probability of a loss straight off the result.

Step	What you do	In this model
1. Start static	Build (or keep) the ordinary point-estimate DCF	The 10-year fuel-cell business case below, NPV −$37,134 at a 10% discount rate
2. Add uncertainty	Replace uncertain inputs with distributions: (min, most likely, max) → Modified PERT; historical series → a fitted distribution or an Ogive	Development cost, market volume and growth, unit cost and price, inflation from historic rates
3. Add discrete events	Model events that either happen or don't, with their probability and conditional impact	A 20% chance the tax rate drops after the election; competitors entering at defined market-volume triggers
4. Handle disagreement	When experts disagree, combine their opinions with weights — never average the distributions	Two development-cost estimates combined 50:50 with VoseCombined
5. Simulate and read	Mark the NPV as output, run the simulation, read P(NPV < 0), the mean NPV, and the tornado of what drives the risk	21% probability of negative NPV; mean $57k; sales price is the top uncertainty driver

If your forecast currently shows one number per year, the article The number your forecast is hiding walks through what that single number conceals; Monte Carlo simulation explained covers the method itself.

Model description

One of the most common applications, perhaps the most common, of risk modeling is to add uncertainty to discounted cashflow models. The example model presented here is typical of this kind of analysis.

The problem: You are evaluating a new company making fuel cells for hospital power plants. Currently there are no competitors. The figure below shows the NPV calculation for the project's 10-year life. This calculation is static, and no uncertainty is included; all input values are considered as most likely. The example model contains three sheets – a description of the problem, a static model shown below that you can add uncertainty to, and a solution with all the uncertainties included.

The NPV, discounted at 10% shows a negative figure of -$37,134.

Let's see how different uncertainties can affect our NPV result. The list of uncertainties is shown below:

(a, b, c) notation means a distribution with min = a, most likely = b, and max = c.

1. Product development cost have been estimated by F Gibbons to be (70000, 80000, 120000) spread over 2004 to 2006 in the ratio 5:2:1. However P Gumbel estimates the product development costs are (70000, 100000, 140000) in the same ratio over the same period. Capital expenses and overheads are assumed to be well defined and are not subject to change.
2. The tax rate is fixed at 46% unless the Conservatives get in at the next election in two years (20% chance) when the rate would drop to (32%, 35%, 46%).
3. Market volume is expected to grow each year by (10%, 20%, 40%) beginning in three years at (2500, 3000, 5000) up to a maximum of 20,000 units. The cost per unit in the first year of sales is estimated at (22.75, 23.25, 24.5) and the sales price per unit is estimated at (45, 58, 65). Both the cost and sales price per unit are subject to inflation from then on at a rate starting at (3%, 4%, 6%) and varying yearly in a similar fashion to historic rates.
4. You expect one competitor to emerge as soon as the market volume reaches 3,500 units in the previous year. A second would appear at 8,500 units. Your competitors' shares of the market would grow linearly until you all have equal market share after three years.

The solution sheet looks like this:

There are three points in the model that need special attention:

1. Cell C41 uses a VoseCombined function to combine the experts’ opinions and return values from "Gibbons" and "Gumbel" with equal probabilities. Since we assumed both experts have equal weights, we assign 50% to each of them. If one of them was more experienced or trusted we would have assign different weights to their opinions.
2. A common mistake here is to multiply their opinions by the weights and then take the sum: = Gibbons*50%+Gumbel*50%. This would result in the decrease of the spread in the final outcome and underestimation of the risk arising from that particular risk factor, explained in detail in the Wiki topic about Incorporating Differences in Expert Opinion. Note that we could also use the VoseDiscrete function.
3. A VoseOgive function was used to model the inflation, taken historic inflation rates.

If we run a simulation and graph the output cell, we will get the following distribution of NPV:


As we can see from the chart above, there is only 21% probability that the project will have a negative NPV.

NPV calculations performed in a risk analysis spreadsheet model are usually presented as a distribution of NPVs because the cashflows selected in the NPV calculations are their distributions rather than their expected values. Theoretically, this is however incorrect. Since a NPV is the net present value, it can have no uncertainty. The NPV is the amount of money that the company values the project at today. The problem is that we have double counted the risk of the project by first discounting at the risk-adjusted discounted rate r and then showing the NPV as a distribution (i.e. it is uncertain). The actual NPV to quote in a report would be the expected value (mean) of the NPV distribution, which in this case equals $ 57k.

The mean NPV value can only determined if one runs a simulation, but there is another good reason for doing the simulation – namely, that we can see what is driving the uncertainty. The Tornado plot provides this:

The sales price is the greatest uncertainty driver, followed by market growth, and the market volume. If the level of risk is too great to make the investment now, these are the variables one would research more thoroughly to get narrower estimates and thus obtain the probability of a positive NPV closer to 0 or 1 which would clarify whether to make the investment.

Frequently asked questions

What is a probability analysis for NPV?
It is a discounted cashflow model in which the uncertain inputs are probability distributions rather than single values, evaluated by Monte Carlo simulation. The result is the full distribution of possible NPVs, from which you read the expected NPV, the realistic range, and the probability that the investment produces a negative NPV.

How is it different from scenario or sensitivity analysis?
Scenario analysis evaluates a handful of hand-picked cases with no probabilities attached; simple sensitivity analysis moves one input at a time. A Monte Carlo probability analysis varies all uncertain inputs together, thousands of times, weighted by how likely each value actually is — so the output carries probabilities you can act on, and a tornado plot still shows which input drives the risk.

Should I report the NPV distribution or a single NPV?
Strictly, the NPV to quote is the mean of the simulated distribution: discounting at a risk-adjusted rate and then also presenting NPV as uncertain double-counts risk. The distribution is still worth producing — the mean itself is only obtainable from it, and the probability of a loss and the tornado of risk drivers are what change decisions.

Which distributions should cashflow inputs use?
Three-point expert estimates (minimum, most likely, maximum) are best modelled with a Modified PERT distribution. Where history exists — inflation in this model — an Ogive built from the data, or a fitted distribution, uses the evidence directly. When two experts disagree, combine their opinions with weights (VoseCombined); averaging the two distributions understates the risk.

Do I need special software for NPV simulation in Excel?
You need a Monte Carlo add-in; the model stays an ordinary Excel workbook. This example uses ModelRisk, which adds the distributions, the simulation engine and the results charts to Excel — the fully functional 15-day free trial is enough to download this model, run it, and adapt it to your own business case.

Run this model yourself — free 15-day trial