The XIRR function in Excel is a great adaptation of the normal IRR function in that it helps to match values with dates giving you more flexibility. However, like the IRR function, you can get a XIRR #num error message and it is sometimes difficult to know why (by the way our IRR Calculator may help you get your answers).
The most common reason we have found for receiving a XIRR #num error message is that the person forgot to include at least one number with an opposite sign to the others, typically a negative cash flow in the beginning.
As stated in the help files “XIRR expects at least one positive cash flow and one negative cash flow; otherwise, XIRR returns the #NUM! error value”
Some other reasons for errors messages with XIRR include
- the dates must be valid excel dates (dates that excel recognises)
- the number of dates in the range is more or less than the number of cashflows i.e. you highlight 4 cells for the date range but 5 cells for the number range.
Typically IRR and XIRR are used in financial models. We cover these topics in our Financial Modelling courses.