View More Content
SHARE

Formula to calculate NPV In Excel

Formula to calculate NPV In Excel

This tutorial covers the formula for calculation of the net present value (NPV) in Excel. It assumes the reader is familiar with the concept of net present values, and concentrates on how to calculate an NPV in Excel and the relevant formulas and considerations to be aware of when performing this calculation.

The NPV formula can be defined as the sum of present value (PV) of future cash flows after netting out the initial cash flow/investment made. The NPV is commonly used for capital budgeting and widely throughout the economics and the finance industries.

NPV can be formulated as:

<SCREENSHOT>

What is the best way to calculate an NPV of a set of cash flows?

In this tutorial, we will discuss the possible methods to calculate the NPV:

  • Calculating from first principles without Excel function
  • Calculating NPV using Excel function NPV( )
  • Calculating NPV using (X)NPV
  • Checking the NPV calculation

In order to reference many of the examples in this tutorial, we recommend you download the associated Excel workbook prior to reading this tutorial.

Calculate NPV in Excel from first principles

This approach requires just a small amount more code than using the Excel functions, but is more transparent. Calculating the NPV from first principles allows the user to sense check every aspect of the NPV calculation.

From the NPV formula written above, we could lay out the calculation in the spreadsheet using following steps:

  • CFi – bring in the sets of future cash flows to be assessed
  • r – calculate the discount rate
  • PVi – calculate the PV of cash flow in each period
  • NPV – sum up all the PVi; remember to net out any initial cash/investment that has not been accounted in

<SCREENSHOT>

Let us work through an example to calculate NPV of project cash flows in order to calculate the project NPV – see screenshot 1:

  • CFi (Line 104) – this is the cash flow before funding that we would like to calculate the NPV
  • Annual r (Line 106) – the discount rate used is 10% p.a.
  • Periodic r (Line 107) – the periodic discount rate is calculated as shown above
  • Discount factor – we would like to discount back the cash flows to the model start date (31-Dec-08), thus calculate the discount factor as shown above
  • PVi (Line 111) – calculate the PV of each periodic cash flow in the model by dividing the CFi with the discount factor
  • NPV (Line 112) – calculate the NPV by summing up the PVi (in this example, the NPV of project cash flows as of model start date is $20.8 million)

Using the NPV( ) function in Excel to calculate net present value

NPV() syntax:

NPV(r, CF1, CF2, …)

We could calculate NPV using the Excel function NPV() but it has some limitations:

  • The CFi values in NPV calculation must be equally spaced in time and occur at the end of each period
  • The CFi must be entered in the correct sequence
  • The NPV calculation is based on future cash flows – if the first cash flow occurs at the beginning of the first period, the first value must be added to the NPV result

Using the NPV( ) formula in Excel to calculate the NPV

Due to its limitation, the NPV function (without the ‘x’) is best avoided. Cash flow models, such as in project finance transactions, are often presented in more detailed during the 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.

The more robust function would be XNPV(). It returns the net present value for a schedule of cash flows that are not necessarily periodic.

XNPV() is an added-in function in Excel and the syntax is:

XNPV(r, CFi, dates)

<SCREENSHOT>

As demonstrated in screenshot 2, calculating NPV using the Excel XNPV() function yields the same result as the manual calculation previously demonstrated in screenshot 1.

NPV calculation considerations

Considerations to be aware of when performing the NPV calculation:

  • Have you got time to calculate NPV ‘properly’ from first principles?
  • Be aware of the limitations of the NPV() function in Excel
  • Be aware of the assumptions when using the XNPV() function
  • Clearly show the units of cash flows and the discount rate in the NPV formula
  • Check and recheck the NPV calculations at every stage
  • Clearly show if the NPV is pre or post tax
  • Show if the NPV is calculated as real or nominal
  • Select the appropriate discount rate for the risk profile of the project as it is a key variable in the NPV calculation
  • Present the NPV results clearly, maybe at a range of discount rates
  • Communicate the shape of the NPV (discount rate) profile – maybe there is more than one root to NPV(x)=0
  • Identify if it is a project NPV or an equity NPV (for geared projects)

A substantial benefit of using the XNPV function is that it can be copied and used to calculate the NPV at different discount rates. However, this is not a good enough reason for using a black box formula, as a quick 1-dimension data table with discount rates as the vertical parameter will provide the same output and can be easily tested.

<SCREENSHOT>

Checking the NPV formula and calculation in Excel

NPV is related to the internal rate of return (IRR) function. IRR is the rate for which NPV equals zero. We could double-check the NPV calculation by firstly calculating the IRR and then feed the IRR back into the NPV calculation as a discount rate:

XNPV(XIRR(…), …) = 0

<SCREENSHOT>

Common applications of NPV calculations

Capital budgeting

NPV is an indicator of how much value an investment or project adds. Amongst other factors, theoretically a project with a positive NPV should probably be accepted. Or in financial theory, if there is a choice between two mutually exclusive projects, then the one with the higher NPV should be selected.

Calculate the project’s loan life cover ratio using the NPV

Loan life cover ration (LLCR) is one of the most commonly used debt metrics in project finance. It provides a measure of the number of times the NPV of projected cash flows can repay the outstanding debt balance over life of the loan.

LLCR = NPV (CFADS over loan life) / debt balance b/f

Determine debt capacity of certain project

Related to LLCR, the borrowing capacity is usually worked out by deciding the initial LLCR. For example, if the required LLCR is 3.0 to syndicate a loan of a particular project, then the debt capacity of that project is one-third of the NPV of the project’s available cash flows.