Determining the NPV of a capital investment

This is a typical discounted cash flow problem. To illustrate the importance of including uncertainty in an NPV calculation, we will discuss a simple model. Due to its simplicity, it cannot reflect the reality, but instead can provide with a set of useful techniques one could apply when modelling real data.

Let's consider the following simple 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 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. Include these uncertainties in the model. Capital expenses and overheads are assumed to be well defined and are not subject to change.

2. Tax rate is fixed at 46% unless the Conservatives get in at the next election in 2007 (20% chance) when the rate would drop to (32%, 35%, 46%). Include this extra uncertainty in the model.

3. Market volume is expected to grow each year by (10%, 20%, 40%) beginning in 2006 at (2500, 3000, 5000) up to a maximum of 20,000 units. The cost per unit in 2006 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 2006 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 to the model is provided in the following spreadsheet: NPV of a capital investment (click on the tab called "Solution")

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

1. Cell C39 uses a VoseDuniform function to model expert's opinion 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 and used a VoseDiscrete function instead.

2. A common mistake here is to multiply their opinions by the weights and then take the sum : y = Gibbons*50%+Gumbel*50%. The reason why this is wrong is that the outcome value will always take the value somewhere in the middle and use it for further calculations. This will result in the decrease of the spread in the final outcome and underestimation of the risk arising from that particular risk factor. The correct way to model this variable is therefore: y = VoseDuniform (Gibbons, Gumbel), as is explained in detail in the section about Incorporating Differences in Expert Opinion. Note that we could also use the Vosecombined function for directly constructing the distributions of the combined expert opinions.

3. A VoseCumulA function was constructed in the table (cells N9:P32), which was then used in the main table to model the inflation for the last 6 years.

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 17% 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).

Two theoretically correct methods for calculating an NPV in risk analysis are discussed below, along with a more practical, but strictly speaking incorrect, alternative:

• Theoretical approach 1: Discount the cashflow distributions at the risk free rate rf.
  This produces a distribution of NPVs at rf and ensures that the risk is not double-counted. However, such a distribution is not at all easy to interpret since decision-makers will almost certainly never have dealt with risk free rate NPVs and therefore have nothing to compare the model output against.

• Theoretical approach 2: Discount the expected value of the project at the risk-adjusted discount rate.
  This approach results in a single figure for the NPV of the project. A risk analysis is run to determine the expected value and spread of the cashflows in each period. The discount rate is usually determined by comparing the riskiness associated with the project's cashflows against the riskiness of other projects in the company's portfolio. The company can then assign a discount rate above or below its usual discount rate depending on whether the project being analyzedexhibits more or less risk than the average. Some companies determine a range of discount rates (three or so) to be used against projects of different riskiness.

The major problems of this method are that it assumes the cashflow distributions are symmetric and that no correlation exists between cashflows. We have seen that distributions of costs and returns very often exhibit some form of asymmetry. In addition, in a typical investment project, there is also almost always some form of correlation between cashflow periods: for example, sales in one period will be affected by previous sales, a capital injection in one period often means that it doesn't occur in the next one (e.g. expansion of a factory) or the model may include a time series forecast of prices, production rates or sales volume that are autocorrelated. If there is a strong positive correlation between cashflows, this method will overestimate the NPV. Conversely, a strong negative correlation between cashflows will result in the NPV being underestimated. The correlation between cashflows may take any number of, sometimes complex, forms.

• The practical approach: The above two theoretical approaches are difficult to apply or interpret and beg an alternative. In practice, it is easier to apply the risk-adjusted discount rate r to the cashflow distributions to produce a distribution of NPVs. This method incorporates correlation between distributions automatically and enables the decision-maker to compare directly with past NPV analyses.

As it has been already explained above, the problem associated with this technique is that it will double count the risk: firstly in the discount rate and then by representing the NPV as a distribution. However, if one is aware of this shortfall, the result is very useful in determining the probability of achieving the required discount rate (i.e. the probability of a positive NPV). The actual NPV to quote in a report would be the expected value of the NPV distribution, which in our case equals $ 65,776.00