Especially when involved in financial modelling, XIRR is commonly used to determine the Internal Rate of Return of a project due to its ability to handle cash flows with various period lengths.
However, the XIRR has a potential issue when the first cash flows it receives are zeros in that the XIRR gives zero as an answer. This might occur when you build a financial model that has a sensitivity around its start date and the first cash flow could be this month or in 12 months time depending on the scenario chosen. The XIRR returns a 0% even when it is obvious there must be another answer and the XNPV formula is generating an answer.
Force Excel to show the XIRR
A couple of solutions to this (depending on whether you want to measure the zero’s as periods or ignore them):
- Use an IF function and create a very small number if the cash flow is a zero i.e. if it is a zero make it a -0.00001. This will have no impact on the overall result but will allow XIRR to work
- Use the OFFSET function to start the XIRR range only where the first cash flow occurs.
Our Financial Modelling course covers the issues around IRR and NPV type calculations.