Formulas discussed: NPV, IRR, XNPV, XIRR
The workbook used throughout this article is available here.
Net Present Value (NPV) and Internal Rate of Return (IRR) are thought to be the two of the most common tools for evaluating the financial viability of projects. What is also apparent is that there is significant debate as to the appropriateness of these measures, which is something we’ll leave to those other forums (such as here and here).
What we will cover in this article is their incorrect use in Excel, due largely to people misusing the inbuilt formulas or not understanding their mathematical construction. This is often apparent in financial models, where the a single outcome is sought, and the formula syntax is simple to use, but the modeller may not completely understand what is going on under the hood of these formulas and produces an answer that is not what they intended (whether they detect the error or not).
The formula for NPV can best be represented as:
where i is the discount rate, t is the time period, N is the number of periods and C is the cashflow series. Unfortunately, Excel uses a slightly different formula – and can you spot the difference?
The Excel formula starts from time period 1 rather than 0. Excel assumes that the first cashflow you input is at the end of the first period, rather than the beginning. So if you want to calculate the NPV as at the date of the first cashflow, you would need to use an equation such as:
which in Excel formulation would be:
=NPV(i, cashflow range starting from second cashflow) + first cashflow cell or =NPV(i, cashflow range starting from first cashflow) * (1+i)
IRR is the discount rate that produces an NPV=0 for a series of cashflows. But, just to make things interesting, Excel’s IRR formula makes a different assumption to the NPV formula – it calculates from the date of the first cashflow, not a whole period earlier (although it can be quite hard to notice any real difference in a lot of circumstances).
A major problem with IRR in general, is the multiple solutions problem. If the first cashflow is negative (or positive) and the rest of the cashflows are positive (or negative where the first cashflow is positive), then IRR has only one solution. If however, cashflow signs change over time, as in our example below, then IRR may have multiple solutions (but it won’t always).
So the first question is, which is the “correct” answer? Or doesn’t it matter? But more importantly, how do you know if there are multiple answers?
In this example, there are two possible answers – 6.92% and 21.90%. We can tell there are two answers by constructing a table that calculates the NPV for a series of different discount rates and look where it changes from positive to negative and vice versa.
But if we haven’t built one of these tables, how do we know which result Excel will return in an IRR function? The general answer, is that we change the guess value that is an input to the IRR function. In this example, we’ve put in two options for the guess value – 10% and 50% – and they each return the IRR value that is closest to them. So no problem right?
However, what if you have no idea whether there are multiple solutions, so you change the guess to a big value, just to check if there are any other solutions. So change the guess to 100% (do this in cell L86 in the downloadable workbook). 100% is closer to 21.9% than it is to 6.9%, so it should product the 21.9% answer, shouldn’t it? As it turns out, due to the way that Excel iterates to an answer, it still produces the 6.9% answer, so you decide there is only one answer and move on, which is wrong (for those interested, the answer crosses over at a guess of 81.66%).
Both NPV and IRR assume that the period of time between each cashflow is exactly the same length. However, a lot of models are built with varying time periods across a sheet (a practice which I hate, but I can’t change the whole world yet). To cater for this, it is possible to complete a manual calculation of NPV through individual discount factors for each period (and then use this to goal seek an IRR), or you can use another formula, XNPV, discussed below.
Annual versus semi-/quarterly/monthly
Both NPV and IRR are based on compounding rather than simple interest rates. A common practice when working with say a quarterly cashflow (see section 1b of the downloadable workbook), is to just divide the discount rate by 4. However, as you can see in cell H26, Excel now produces a different NPV result to cell H16, despite the cashflows being exactly the same. Instead, a compounding formula, as in cell H28 should be used, which produces the same NPV result as for the annual table.
A partial solution – XNPV and XIRR
Microsoft has built in a partial solution to the differing periods problem. XNPV and XIRR include as inputs the dates of the cashflows, rather than assuming that all cashflows are evenly spaced. And the table does not need to be in exact date order, providing further flexibility. But even with what may appear to be the same data, XNPV can produce a slightly different result to NPV. The below example shows an NPV of 1,362, but an XNPV of 1,360.
The reason for this is that the last period is actually 366 days whereas the other periods are 365 days. XNPV takes this into account, but NPV doesn’t. Try changing the date of the final cashflow to 30/12/2016, thereby making all periods the same length, and you should get 1,362.
The other key difference between XNPV and NPV is that XNPV calculates the return as of the date of the first cashflow, rather than at the beginning of the period, so you do not need to add in the initial cashflow separately. XNPV also requires an annual discount rate, rather than a per-period rate.
But be warned, they’re not bullet proof
We’ll go back to our previous example of multiple IRRs (section 3 of the downloadable workbook). What you can see (cells L89, L90) is that XIRR is not as stable as IRR, producing a #NUM! error for this example. This would usually occur because Excel only allows 20 iterations before returning the error if it hasn’t found an answer, a problem that can usually be solved by using a different guess value that is closer to the answer. But for this particular scenario, I am yet to find a guess that will help the XIRRs return a result. The best solution is to use XNPV over the cashflow series and to then use the goalseek function to determine the IRR (or iterate manually using the bisection method or similar approach).
In a future article, (which you can now find here) we’ll cover some transparent, first principles methods for calculating NPVs, without using the built-in functions. In the meantime, be aware that before using NPV, IRR, XNPV and XIRR, you need to remember what sort of answer you’re seeking to produce, and adjust your formula construction accordingly. And that’s without getting into the argument about whether IRR is an appropriate measure in the first place.
And if you have any questions, feel free to post a comment, or email me at [email protected].