I am assuming that you are carried forward from Part 1 of the IRR series. Where I talked about : What is IRR, Its calculations, IRR based decision making and Interpreting positive or negative IRR, all of that in simple English 🙂
In this post I am going to talk about
- 4 problems associated with IRR
- The XIRR function to handle irregular cash flows
- Why does IRR change when consolidating annual cash flows from quarterly cash flows ?
- Why is IRR or XIRR is not a very robust metric ?
Some of these problems have solutions and some don’t (or lets say that I don’t know about the solution). Let’s get started!
Problems with IRR
Problem #1 IRR functions gives a #NUM! error
- The IRR function will give a #NUM! error when it takes more than 20 iterations to find the discount rate (IRR)
- Also if the IRR function does not find at-least one positive and one negative cash flow, it would again throw #NUM! error
Unfortunately there isn’t a solution to this problem
Problem #2 : Positive IRR even if the total net cash flows are negative
- The IRR is positive even if the total net cash flows (sum of all cash flows) are negative
- Even if you don’t discount the cash flows you still lose $175
- The reason why this happens is because the IRR function is just meant for solving this equation, whether or not it makes sense on not
--> Solve for r
The solution is to apply common sense and look at total net cash flows 😆
Problem #3 : Multiple IRRs
At times IRR gives more than one discount rate. Take a look at the following example
The above example has 2 IRRs. It means that IRR equation can be solved with 2 values. This happens mostly when the cash flows change from positive to negative more than once
The solution lies in Guess which is 2nd input of the IRR function
- Notice when the guess is -30% the result is -19.20%
- When the guess is 50% the result is 72.05%
The inference is that in case the IRR formula encounters multiple IRRs it would return the IRR value which is closer to the guess. I am assuming that you must know what kind of returns are you expecting if investing in a project. Put that expectation as a guess!
Problem #4 : Cash flows not at equal intervals
Take a look at the dates. You can see that the cash flows are not at equal intervals. If you calculate IRR with the IRR function (with irregular cash flow dates) it would still consider the periods to be the equal and would return an incorrect IRR
The solution lies in the using the XIRR function if the cash flows are not at regular gaps
How to use the XIRR Function ?
The XIRR function has 2 (and one optional) input(s)
- Values : These are the cash flows, same as in the IRR function
- Dates : These are dates on which the cash flows occur
- Guess : This is optional and by default excel takes it as 10%
These were the most common problems that I have seen myself or have seen my friends facing them, but there could be more!
The next thing that we are going to discuss is what happens to the IRR when you consolidate quarterly cash flows to annual ?
Annual v/s Quarterly IRR Case
Assume that we have 44.5% XIRR for 16 quarterly cash flows for 4 years.
Lets see what happens when we calculate the XIRR on annual cash flows (consolidated from quarterly cash flows)
Could you think of a reason why is IRR different in both the cases ?
Notice the dates for Quarterly cash flows and Yearly cash flows
- The first 3 positive cash flows are occurring on 1st of April, July and August 2015. Where as their total 195 is assumed to be occurring on 1 Oct 2015
- We discussed earlier that cash today is more valuable than cash tomorrow. That means small cash flows happening earlier are more valuable than total cash flow happening later.
- That is the reason why annual XIRR is less (total cash flows occurring later) than Quarter IRR (where the cash flows are occurring earlier)
The quarterly and annual XIRRs will not be the same because of dates of the cash flows change
Why IRR (and XIRR) is not very robust ?
The IRR (or XIRR) is a very widely accepted metric but has an inherent flaw in it. It assumes that all cash flows are continuously reinvested at the same Rate. I’ll make this more simple to understand with an example
Let’s talk about the first year cash flow of 73 which you will compound at 16.68% for the next 4 years. Do you really think, is that a realistic assumption to let your cash flow gain 16 plus % each year ? I don’t think so! The real return will be less than 16.68%
IRR assumes a surrealistic rate each year, which in the real world is not possible. The MIRR (Modified Internal Rate of Return) comes to rescue in this case, which modifies the IRR to adjust for reinvestment and financing rate.
The MIRR will be our guest in Part 3 of the IRR series. Stay Tuned!!
What problems do you face with IRR ?
Put down your comments and problems that you face while calculating IRR
