NPV v IRR: Understanding the Difference

Introduction

If you find this useful and you want to understand more about the intention, interpretation and application of the NPV and IRR then ask about our two day, 100% hands-on, case study based, Project Finance Analysis course, delivered in person by Nick Crawley.

Download Workbook    Download PDF    Training Courses

As a refresher, the Net Present Value (NPV) the value in todays money of future cashflows, discounted at a given rate – usually either the weighted average cost of capital (WACCC) or the cost of debt, depending on the application. Over a time dimension (t), from the start of a set of cashflows (t=0) to the end (t=N) the NPV is mathematically expressed as.



The Internal Rate of Return (IRR) is a special member of the set of compounding, annualised, discount rates. It is the one which results in an NPV of zero. Both metrics have drawbacks but are generally outweighed by relatively straightforward mathematics and prevalence of their application. 

In the case of both the NPV and IRR there are two Excel functions, the "X" version is more precise but both XNPV() and XIRR() have an arguably incorrect default. The accompanying worbook demonstrates how to perform the calculations. This main issues being as below which lead, quickly, to incorrect results.

• XNPV() does not discount the way you would expect
• XIRR() returns 0% unless a 'trigger' value is used

Annual 

Firstly, we've set up an annual NPV and IRR calculation which is calculated as shown below as the NPV() function and first principles, both resulting in a Net Present Value of $0.66M. This calculation establishes what we are expecting in more detailed quarterly calculations.

IRR estimate

This isn't a theoretical article to derive or present the mathematics behind the NPV or IRR. A formula that is useful to know is a linear bisection estimate for the IRR to build up an estimate of the IRR rather than relying just on the IRR function which is somewhat of a black-box formula. We have built this into the workbook and it is shown below.

XIRR() and XNPV()

Working through the accompanying workbook you will see that the NPV is calculated from first principles using a calculated discount factor. The different approaches show how the XNPV does not discount the first period and that the XIRR requires a 'trigger' value. There are three cashflow profiles for you to experiment with. The NPV is a simple calculation and is not worth using a black-box calculation for. Using "Profile 1" you can see

• XNPV() = $0.679 (does not discount the first period)
• NPV from first principles = $0.663M
• A 2% difference which will vary significantly with different profiles

 

Nick's cheat sheet

I pride myself on finding errors in the IRR - quickly! Some of the things I look for immediately when assessing the NPV and IRR are:

• If used, have XNPV() / XIRR() been applied correctly.
  • Full range of (undiscounted) cashflows captured
  • Trigger value for XIRR() – see workbook
• If the IRR is used as the NPV discount rate the NPV will, by definition, be zero.
• How does NPV/IRR move under basic scenarios, symmetrical movements?

NPV as a function of discount rate

A really quick way to understand the IRR(s) of a set of cashflows is to calculate the NPV for a range of discount rates - we've done this below as a 1D data table with a plot. This table has been set up with a variable start value and step size to facilitate easy 'exploring' of the NPV vs Discount Rate relationship.

Advanced considerations

When you see any of these points, stop and just double check you know what is going on and how it is being modelled.

• Are there multiple changes in sign of the cashflow, be aware of the existence of multiple IRR
• Does the cashflow include a terminal value, does this change the sign of the NPV?
• Is the NPV particularly sensitive to discount rate?
• Is the IRR < or = 0 (part of a wider discussion)
• Does the analysis need to be precise enough to take into account leap-years, the X functions assume a constant 365 day year.
• Mathematically a delay does not impact an IRR; rarely seen in models owing to escalation

Applications of the NPV and IRR

The NPV and IRR, however calculated, are essential project / investment appraisal criteria; as such any error can have significant consequences.

• Benchmark criteria for comparing one project against another.
• NPV is the numerator the LLCR, PLCR and RLCR covenants (although only a PV).
• A constraint / optimised parameter for sizing equity, setting a tariff, regulatory frameworks.
• Credit Ratings; Loss Given Default = NPV senior cashflow / outstanding debt.

Summary

In summary both the NPV and IRR are essential calculations to master in the world of financial decision making, both of which can appear correct but be wrong!  If you found this helpful and would like to learn about other aspects of Project Finance Modelling or Advanced Financial Modelling then you would love our training courses! Check them out here or just give us a call. 
 
I hope that was useful – smooth and happy modelling!