Xirr gives zero in Excel

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.