What are the odds this project clears a positive NPV?

What it does

This model is not ours. Professor Sylwia Gornik-Tomaszewski of St John's University built it in Excel, published it in Management Accounting Quarterly in Summer 2014, and teaches it to graduate managerial accounting classes. She shared the working file, and the sheet here is that workbook laid out the way hers is, row for row, so the two can sit open side by side and be compared.

A firm is deciding whether to put 10,000 into a new product with a five-year life and no further investment along the way. Three things about that product are genuinely unknown, and each one is normally distributed: the price per unit, mean 10 and standard deviation 2; the number of units sold in a year, mean 2,000 and standard deviation 300; and the unit production cost excluding depreciation, mean 2 and standard deviation 0.25.

The unit selling cost of 1, the 2,000 of annual depreciation, the 40% marginal tax rate and the 10% required rate of return are fixed. The five-year block underneath is her calculation in her own three steps: the amount is q(p) minus q(c + s) minus D, the tax effect is 1 minus T, and the after-tax net cash flow multiplies the two and adds the depreciation back, because depreciation is not a cash cost but it does shield income from tax.

The NPV cell discounts the five net cash flows at the required rate of return and adds the investment at t = 0. As typed, with each uncertain input sitting at its mean, the NPV reads 24,875 and the project is an easy yes. That single figure is the thing a spreadsheet can show and the thing you should not trust: recalculate the workbook and it becomes a different number every time.

Click Run. The seed is set to 7 and the trials to 20,000 so your first run reproduces these figures exactly. NPV comes back with a mean of 24,879.1 and a median of 24,291.1, a standard deviation of 10,439.4, a fifth percentile of 8,770.8 and a ninety-fifth of 43,027.3. The answer to the question on the front is 99.6%: NPV clears zero in 99.6% of the runs, so a negative NPV turns up in 0.4% of them.

The project is worth doing, and now you know how far from the edge it is rather than only which side of it you landed on. Now compare it with hers, and with a third answer that needs no simulation at all. Because p, q and c are drawn once and held for all five years, every year in a trial is the same and the model has a closed form: the average NPV is exactly 24,875.24 and its standard deviation is exactly 10,429.30, both of which come out of a calculator rather than a computer.

That is why the sheet as typed already reads 24,875. Her published 100 iterations average 24,329.62 with a standard deviation of 10,808.35. Against the closed form her mean is 545.62 out, and the standard error of a mean of 100 draws here is 10,429.30 divided by the square root of 100, which is 1,042.93, so she is half a standard error away.

Her standard deviation is 379.05 out against a standard error of about 737.5, which is also half a standard error away. Two runs of the same model, one of 100 and one of 20,000, both landing half a standard error from the arithmetic answer. There is one place the two answers genuinely differ, and it is the most useful thing in the comparison.

To get the chance of a negative NPV her article takes the z score of zero against the mean and standard deviation of her 100 iterations, gets minus 2.25 standard deviations, looks it up in a normal table and reports 1.22%. Her own 100 draws contained no negative NPV at all, with a minimum of 1,537.28, so the 1.22% comes entirely from assuming the distribution is normal.

It is not. NPV is driven by price multiplied by quantity, so it leans right, with a long upper tail and a short lower one, and a normal curve fitted to the same mean and standard deviation puts more weight below zero than the model really has. Exact numerical integration on her assumptions gives 0.3783%. Apply her z method to this run instead of to hers and it gives about 0.86%, still more than twice the truth, so most of the gap is the normality assumption and the rest is the noise in a mean and a standard deviation taken from 100 draws.

None of this is a criticism of her paper, which is explicit about its simplifications, and the z table is the standard teaching move. It is the honest difference between the two tools: with 100 iterations you have to assume a shape before you can say anything about a tail, and with 20,000 you can count instead. The shapes agree everywhere else.

Her frequency table puts 8 iterations in 100 between zero and 10,000, then 30, 30, 27, 4 and 1 across the bins above; this run puts 6, 27, 37, 21, 7 and 1 per 100 on the same bins, and the largest difference between the two tables is smaller than one and a half of the standard deviations you would expect from a sample of 100. Her median bin is our median bin, the median here is 24,291.1 and the ninety-ninth percentile is 51,502.0.

The tornado is the part her workbook cannot produce at all. Price leads at a rank correlation of 0.87 and carries about 78% of the spread on its own, the number of units sold follows at 0.44 with about 20%, and the unit production cost comes last at minus 0.11 with about 1%. That ranking is not obvious from the assumptions, because unit cost has the tightest range of the three in percentage terms and price has the widest in dollars that reach the bottom line.

If this were a real appraisal, the market research budget would go on the price, not on the cost. Two things about the model are deliberately hers and would have been built differently from scratch. The three inputs are drawn once per trial and held for all five years, so a lucky price is lucky in every year and the five years are perfectly correlated.

That widens the spread compared with redrawing each year, and it is the more conservative assumption for a product whose price is set once at launch. And the three draws are plain normal curves, so a trial can in principle return a negative price or a negative unit count; at these means and standard deviations that is five standard deviations away or more, and her article states the rule that keeps it that way, which is to keep the standard deviation below the mean and the price above the cost.

Neither was changed, because changing either would move the answer and the two workbooks would stop agreeing. What the model cannot tell you: the five years are identical by construction, so no growth, no price erosion and no ramp-up are in it; the three inputs are drawn independently of each other, when a price rise usually costs you volume, and that independence flatters the spread; the discount rate is fixed rather than uncertain; and nothing here is a forecast of the market, only of the arithmetic once you have named the ranges.

To make it yours, put in your own price, quantity and cost with the ranges you would defend, change the investment, the depreciation, the tax rate and the required rate of return in the fixed block, and set the same three means and standard deviations on the matching draws in the Risk Analysis panel, because the mean and standard deviation columns on the sheet are documentation and the panel draws from its own rows.

Next question

• When does your startup actually run out of money?When Does the Startup Run Out of Cash?
• What is the business really worth?What Is the Business Worth, as a Range?
• After this round, what do you actually walk away with?What Do the Founders Keep After the Round and the Exit?
• Does the launch still pay after it eats the flagship?Launch With Cannibalization
• What are the odds your product launch actually makes money?Product Launch Go/No-Go NPV
• How big could this product's revenue be by year three?Market Size to Revenue Forecast

Every model like this one, and the method behind them: Monte Carlo simulation.