Calculate NPV without Excel formulae

Net Present Value (NPV) is a standard method of using the time value of money to appraise long-term projects and investments. This tutorial will discuss the principles of NPV calculation and the discount rate.

NPV calculation introduction

NPV in project finance could be defined as the sum of present values (PVs) of cashflows expected from the project minus initial investment made. The formula for calculating NPV could be written as:

• value - net cashflow occurs at the end of each period i
• rate - discount rate used to discount the cashflow
• n – time period of the project 

There are two types of NPV:

Project NPV

The cashflow used to calculate the NPV would be the future operational cashflows of the project less initial project capital costs

Equity NPV

The cashflow used to calculate the NPV would be the equity distributions minus initial equity investment

What is NPV (Net Present Value)?

• NPV>0 Theoretically the project should be accepted. Or if there is a choice between 2 mutually exclusive projects then the one has the higher NPV should be selected.
• NPV<0 A project with negative value should probably be dropped.
• NPV=0 Decision should be based on other criteria.

Discount factor in NPV

This is the appropriate discount rate for the risk profile of the project and a key variable in the NPV calculation. The discount rates used to generate NPV could be:

• Weighted average cost of capital (WACC)
• Reinvestment rate
• Variable discount rates with higher rates
• Target rate of return

A firm’s WACC (after tax) is often used in the calculation although some might thing it is appropriate to use higher discount rates to adjust for risk of “riskier” projects.

Reinvestment rate can be defined as the rate of return for the firm's investments on average. When analyzing projects in a capital constrained environment, it may be appropriate to use the reinvestment rate rather than the WACC as the discount factor. It reflects opportunity cost of investment. This is often calculated by considering the return on an alternative investment that can be made if the current project is not taken.

Variable discount rates with higher rates could be applied to cashflows occurring further in the timeline. However, they might not be known for the duration of the project and often difficult to estimate in practice.

For certain firms, their project investments are committed to target a specified rate of return. In such cases, this rate of return could be selected as the discount rate for the NPV calculation.

NPV() and XNPV() in Excel

NPV() syntax:

NPV(rate,value1,value2, ...)

• NPV function in Excel has some limitations as remarked below:
• NPV can only be used to calculate the NPV for a series of cashflows that is periodic
• The cashflow must be entered in the correct sequence

The NPV calculation is based on future cash flows - If the first cashflow occurs at the beginning of the first period, the first value must be added to the NPV result

Project finance models are often presented in more detailed during construction period as opposed to during operations. For example we often find many project finance models have monthly calculations during construction and perhaps semi-annual / annual during operations.

Thus we might want to use XNPV() instead of NPV() as XNPV() returns the net present value for a schedule of cash flows that is not necessarily periodic. XNPV() is an added-in function in Excel and the syntax is:

XNPV(rate,values,dates)

NPV() and XNPV(): Example

The attached workbook is built to illustrate the calculations and to compare the results of XNPV() vs NPV(). The assumptions used in the workbook could be found in the “Input” sheet, the calculations are in the “Calculation” sheet and the NPV calculations are in the “Return” sheet.

Screenshot: NPV() and XNPV() calculation

Project NPV and Equity NPV are both calculated. XNPV() and NPV() are then used to calculate Project NPV and Equity NPV. The workbook has a monthly construction cashflow and annual during operations.
Thus, to calculate the NPV, the monthly construction cashflow needs to be summed-up to annual cashflow to allow the NPV calculation. WACC of 9.00% p.a. is used as the discount rate.

As shown in the screenshot, the XNPV in this example is AUD 29.64 Mil and the NPV is slightly higher AUD 34.96 Mil.

Such variance is because in the NPV calculation we assumed that the capital costs during construction period is occurred lumpsum at the end of Year-1. Due to this, the NPV calculation is not as “accurate” as in the XNPV calculation where cashflow exactly corresponds to a schedule of payments in dates. However, such variance might not affect the decision making.

Checking the NPV calculation

NPV is related to the IRR function (internal rate of return). IRR is the rate for which NPV equals zero.

Thus, we could double-check NPV calculation by firstly calculating the IRR and then feed the IRR back into the NPV calculation as a discount rate; this should yield approximately zero as shown in the screenshot below.

NPV(IRR(...), ...) = 0.

XNPV(XIRR(...), ...) = 0

Screenshot: Checking the NPV calculation

COMMENTS