The Secrets of Net Present Value Projection

What will my investment be worth in the future?

The core concept of present value calculation

Someone promised you to pay you an amount of $1,000 in 3 years. What would be the value of this future payment today? $1,000?, Less? or even more.

Imagine you are going to a bank to borrow money. You want to pay back the credit in one payment in 3 years with that expected amount of $1,000.

The credit sum you can get will obviously be less than $1,000, because your credit account will collect interest and collected interest. At an interest rate of 5% p.a., you could get a credit of $746.22. Why that?

If you take that amount as a credit, the account would develop as follows:

Calculating the discount rate

When making an investment, investors require a benefit to cover the sacrifice of immediate use of cash for consumption or other investments, possibility of inflation and risk.

A tool to calculate what this requirement needs for your investment is the Discount Rate which is used for Net present value calculation and other methods of discounted forecasting.

In the example the present value calculation was taken from an interest rate as a bank may charge it for a loan. But in order to calculate the present value of a future payment you may consider two more factors:

Note: To download the Microsoft Excel templates please click here.

• Risk of loss - projects are getting cancelled or their product may have to be replaced soon from whatever reason.
• Inflation - the value of money may decrease as the buying per unit will go down. With $1,000 you will be probably no more able to buy the same goods or services as you could get today.

A common way to calculate a discount rate per yearly period is:

Discount rate / y = (1 + Interest rate / y) * (1 + Inflation rate / y) - 1*

The following formula includes a yearly risk of loss rate. The resulting present values are sometimes referred to as Expected Present Value (EPV), The NPV is then the Expected Net Present Value (ENPV).

Discount rate / y = (1 + Interest rate / y) * (1 + Inflation rate / y) / (1 - Risk of loss / y) - 1

*: There is a well founded opinion that one should only either use interest rate or inflation rate, but not both at the same time. In reality, both are often used, and finally, it is the decision of the person deciding on the discount rate, how it should be developed.

An example: A present value should be calculated for the next 3 years assuming

Expectation		Rate		1 + rate		Comment
Annual interest rate:		3.0%		1.030		Average over 3 years
Annual risk of loss:		2.8%		1.028		"
Annual inflation:		3.9%		1.039		"
Annual discount rate:		10.0%		1.1		Includes expectations on interest rates, risk, and inflation

In the template, the same calculation is made on the first spread sheet:

Please note the name for the cell B9 in the name field left from the editing field (over the spread sheet). The cell is named Discount_Rate. Make sure you understand the name functionality in Microsoft Excel (to be found in the Insert menu) before you start linking from Insight Tree.

Net Present Value calculation

Projects and other investments have a typical disadvantage: The investment has to be made today or in the near future, but the return on investment will come later and should be discounted using present value calculation as seen on top.

This makes it often very difficult if you have to select which investment or combination of investments should be selected out of a larger number of options. You can apply forced choice

Net present value is more accurately the present value of the net revenues from an investment, which is revenues minus investment. See the following example:

Payment		Cash inflow			Comment
Investment:		$	-100,000		Expense, therefore negative
Revenue year 1:		$	25,000
Revenue year 2:		$	45,000
Revenue year 3:		$	55,000
Total revenues (3y):		$	125,000
Net revenues (3y):		$	25,000		Total revenues minus investment

The next step will be to apply the discount factor on the future payments. But before we can do this, we have to understand how investments including projects create their benefits.

Life cycle models for project investments

There are generally two lifecycle models for investments in form of projects:

The sequential model—type: Build a House (BaH)
A house is handed over to the family who want to live in it when it has been finished.

In this type of project there is a handover, delivery, SoP or how it may be called. This is the moment when the project deliverable(s) are being passed over to the customer, user etc. and the project is allowed to cease.

The circular model with staged deliveries—type: Build a Neighborhood (BaH)
A project to build a neighborhood hands over a series of more or less similar houses during the course of the project. It allows the first families to live in them often long before construction of the entire neighborhood has been finished.

This type of project has staged deliveries, most of them do not lead to closing of the project. The project will only be allowed to cease when all of its deliverables are handed over. Project checkpoints may be placed at various places all over the lifecycle.

BaN projects are often found in complex project/program portfolios, they are initiated according to the management's wants ("strategic plan") and needs ("business needs") or are performed to help operations maintaining a business ("upgrades").

NPV calculations for the different lifecycle models

Upfront decisions

Two decisions have to be made before the NPV calculation can be made. The decisions will have a strategic impact on the question, what kind of investments will be favored:

Decision	Strategic impact
Period under review:	A short period favors quick win projects which achieve their revenues early. A long period favors expansive projects, i.e.projects that have a long term potential for growth of revenues. Period under review should be made according to the allover lifecycle of the project and its deliverable, but is often simply a management decision based on strategic considerations. IT environments often review 2 or 3 years. Most construction project NPVs are calculated in decades.
Discount rate:	A high discount rate favors quick win projects. A low discount rate favors expansive projects. The calculation shown above can be used, but often the discount rate simply is defined on management level.

NPV in a simple BaH model

NPV calculation for a short and simple BaH project is not that complicated. It follows the concept shown in the table above. You define the time before the handover as "year 0" and count the following years, when revenues are expected.

The example uses a discount rate of 5% and reviews a period of 3 years after handover:

Discount rate:	5%
Payment	Year	Cash inflow		Discounted cash flow
Investment:	0	$	-100,000	-100,000
Revenue year 1:	1	$	25,000	23,810
Revenue year 2:	2	$	45,000	40,816
Revenue year 3:	3	$	55,000	47,511
Total revenues (3y):		$	125,000	112.137
Net revenues (3y):		$	25,000	12.137	(NPV)

NPV in a more complex BaH model

Your project may take longer than just a year, but there is still no overlapping between the investment and the revenues. In such a case you may also want to discount future payments which are part of your investment:

The following example uses a discount rate of 5%, a project duration of 3 years and reviews a period of 5 years after handover, which is marked with a green line:

Discount rate:	5%
Payment	Year	Cash inflow		Discounted cash flow
Investment year 1:	0	$	-80,000	-80,000
Investment year 2:	1	$	-100,000	-95,238
Investment year 3:	2	$	-75,000	-68,027
Revenue year 1:	3	$	55,000	23,810
Revenue year 2:	4	$	75,000	47,511
Revenue year 3:	5	$	90,000	61,703
Revenue year 4:	6	$	100,000	70,517
Revenue year 5:	7	$	105,000	74,622
Total revenues (5y):		$	125,000	112.137
Net revenues (5y):		$	25,000	12.137	(NPV)

NPV in a BaN model

There is not a single handover but staged deliveries. The project starts creating revenues while it is still ongoing.

The following example uses a discount rate of 5%, a project duration of 3 years and reviews a period of 7 years after the first delivery. This first delivery takes place at the end of the first year. The period of two years when there are both, costs and revenues is marked as a green area:

Discount rate:	5%
Year	Outflow	Inflow	Net cash flow	Discounted cash flow
0	80,000		-80,000	-80,000.00
1	100,000	25,000	-75,000	-71,428.57
2	75,000	50,000	-25,000	-22,675.74
3		90,000	90,000	77,745.38
4		100,000	100,000	82,270.25
5		105,000	105,000	82,270.25
6		110,000	110,000	82,083.69
Total	255,000	480,000	225,000	150,265	(NPV)

Using NPV calculations in Insight Tree

Select one project out of a number of options

Click the right mouse key on the option's payoff and select Edit Payoff (advanced...).

Then select Import and click the ... button.

Select Microsoft Excel Workbook as datasource, and find the workbook. If you are using a named cell (as we propose and as is supported in our templates), select Address type Named cell and select the name from the list.

The names for the NPV fields in the template are:

• NPV_simple_BaH
• NPV_complex_BaH
• NPV_BaN

Otherwise link to Row and Col. But then you have to take care that you do not change these later when you edit the spreadsheet.

Now, you can add more information (here: additional running costs in a best and worst case scenario) and select the best project from the list of options available.

Select several projects out of a number of options, but stay inside a budget

To optimize against a financial constraint, you need different links to the investments as well as to the revenues.

Put the project costs to the branches right from the selection decision. Now you can use the next branches to calculate revenues and further costs as you like.

Now set the decision type to Choose multiple:

The next step is to set a budget: Click on the little arrow at the bottom of the decision node to unhide the budget and then set the budget, against which you want to optimize the investment:

Now link to the investments and to the revenues of each project in Excel as shown above for the selection of the single project.

In the template, investment fields are named

• Investment_simple_BaH
• Investment_complex_BaH
• Outflows_BaN

These fields must feed the nodes right from the decision button. Revenue fields must feed the buttons right from them, there names in the template are

• Revenues_simple_BaH
• Revenues_complex_BaH
• Inflows_BaN

If you click the Optimize decision button, Insight Tree will find the best combination of investments, i.e. the combination with the highest Net Present Value.

A full scale NPV/ENPV calculation sheet can be downloaded here.