Common errors when calculating IRR
Until now, you must’ve found the IRR function to be a piece of cake. However, some conceptual issues may arise with the IRR function.
These can cost you the wrong IRR or an error from Excel. Look at each of them below.
1. Blank Cells in the Value argument
1. If your cashflows look like this:
The image has a series of cashflows starting from Year 1 to Year 5. However, the cashflows for Years 2 and 4 are zero. So, the relevant cells are left empty.
2. Now, if you apply the IRR function as follows:
Replace all empty cells with zeros and reapply the IRR function as below.
IRRs for both datasets show a dramatic difference. 20.79% to 13.25%.
This is because the IRR function doesn’t count in empty cells.
So, if your cashflows show zero cash flow for any year, make sure it is zero and not left blank.
2. Uneven Intervals
What if you get monthly cash flows from your project after three months, then after six months, then after one year, and so on?
With such a dataset, if you apply the IRR function, the results would not be accurate.
This is because the IRR function assumes that all cash flows occur at regular intervals.
If you have cash flows that occur at different intervals, as shown above, you may use the XIRR function.