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 Mazars Financial Modelling’s particular focus areas, there are two types of NPV which analyses different perspectives of a project.
1. Project NPV
The cash flow used to calculate the NPV would be the future operational cash flows of the project, less initial project capital costs.
2. Equity NPV
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. Cost of equity of 10.00% p.a. is used as the discount rate.
As shown in screenshot 1, the XNPV is USD 11.70 million, and the NPV is lower at 10.53 million.
Such variance is because the NPV calculation assumes cashflow periods of equal number of days. 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
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.