How to Calculate Npv and Irr in Excel
Calculate NPV without Excel Functions
Download this workbook
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.
What is net present value (NPV)?
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:
- Value – net cash flow occurs at the end of each period i
- Rate – discount rate used to discount the cash flow
- 'n' – time period of the project
In project finance, one of Corality'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.
Interpreting NPV (net present value) for an investment decision?
- NPV>0: Theoretically, the project should be accepted; or, if there is a choice between two mutually exclusive projects, then the one which has the higher NPV should be selected.
- NPV<0: A project with negative value should probably be dropped.
- NPV=0: The 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 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() and XNPV() in Excel
NPV() syntax:
NPV(rate,value1,value2, …)
The NPV function in Excel has some limitations, as remarked below:
- NPV can only be used to calculate the NPV for a series of cash flows that is periodic
- The cash flow 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.
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).
NPV() and XNPV(): Example
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.
Checking the NPV calculation
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
Corality Academy: Corality Financial Modelling Campus
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:
- Financial Modelling for Power Generation Projects
- Financial Modelling Techniques for Valuation Analysis
Recommended Courses
How to Calculate Npv and Irr in Excel
Source: https://financialmodelling.mazars.com/resources/calculate-npv-without-excel-functions/