Financial Modelling Techniques for Valuation Analysis
2 day duration An accurate and flexible financial model is a critical component of a...
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 and, in particular, highlight how to calculate NPV without using the built-in functions in Excel. This achieves greater transparency and reduces the risk of errors.
NPV is defined as the sum of present values (PVs) of cash flows expected from future cash flows. The formula for calculating NPV could be written as:
In project finance, one of Corality’s particular focus areas, there are two types of NPV which analyses different perspectives of a project.
The cash flow used to calculate the NPV would be the future operational cash flows of the project, less initial project capital costs.
The cash flow used to calculate the NPV would be the equity distributions minus initial equity investment.
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:
A firm’s WACC (after tax) is often used in the calculation, although some might think it is appropriate to use higher discount rates to adjust for risk of ‘riskier’ projects.
The reinvestment rate can be defined as the rate of return for the firm’s investments on average. When analysing projects in a capital constrained environment, it may be appropriate to use the reinvestment rate, rather than the WACC, as the discount factor, as 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 cash flows occurring further in the timeline. However, they might not be known for the duration of the project, and are 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() syntax:
NPV(rate,value1,value2, …)
The NPV function in Excel has some limitations, as remarked below:
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.
Project finance models are often presented in more detail 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 calculations during operations.
Thus, you 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).
The attached workbook is built to illustrate the calculations and to compare the results of XNPV() vs. NPV().
The project NPV and equity NPV are both calculated. XNPV() and NPV() are then used to calculate the project NPV and equity NPV. The workbook has a monthly construction cash flow, and annual cash flow during operations.
Thus, to calculate the NPV, the monthly construction cash flow needs to be summed-up to annual cash flow to allow the NPV calculation. WACC of 9.00% p.a. is used as the discount rate.
As shown in screenshot 1, the XNPV is AUD 29.64 million, and the NPV is slightly higher at 34.96 million.
Such variance is because we assumed, in the NPV calculation, that the capital costs during the construction period occurred lump sum at the end of Year-1. Due to this, the NPV calculation is not as ‘accurate’ as in the XNPV calculation, where cash flow corresponds exactly to a schedule of payments in dates. However, such variance might not affect the decision making.
NPV is related to the internal rate of return (IRR) function. IRR is the rate for which NPV equals zero.
Thus, we could double-check the NPV calculation by first calculating the IRR, and then feeding the IRR back into the NPV calculation as a discount rate; this should yield approximately zero as shown screenshot 2.
NPV(IRR(…), …) = 0
XNPV(XIRR(…), …) = 0
There are numerous other tutorials and free resources related to financial modelling in the Corality Financial Modelling Campus.
Some of the more popular courses that relate to this topic include:
2 day duration An accurate and flexible financial model is a critical component of a...
3 days duration Financial Modelling for Renewable Energy Projects will give you the skills to...
This website uses cookies.
Some of these cookies are necessary, while others help us analyse our traffic, serve advertising and deliver customised experiences for you. For more information on the cookies we use, please refer to our Privacy Policy
This website cannot function properly without these cookies.
Analytical cookies help us enhance our website by collecting information on its usage.
We use marketing cookies to increase the relevancy of our advertising campaigns.