#num error in XIRR

[ratika07]

Hi All,

I'm getting #Num error on using XIRR. My cash-flows are both +ve and -ve with the first period having +ve cash-flow. when I'm making 1st period cash-flow as -ve, I'm getting the result. Is it possible to compute XIRR with the original cash-flows having +ve cah-flow in 1st period?

TIA

 

[joeu2004]

I'm getting #Num error on using XIRR. My cash-flows are both +ve and -ve with the first period having +ve cash-flow. when I'm making 1st period cash-flow as -ve, I'm getting the result. Is it possible to compute XIRR with the original cash-flows having +ve cah-flow in 1st period?

Yes, here is a simple example: =XIRR({1000,-500,-600},{42661,43026,43391}) returns about 6.394102871418%.

If you need some assistance, please post the relevant values and dates.

Generally, XIRR returns #NUM when it is unable to find an acceptable solution within the limits of the algorithm (#iterations, tolerance, etc). You might need to provide a reasonable "guess" (third parameter).

Simply changing the sign of the first value (present value!) radically alters the expected IRR. So it is no surprise that it might correct the #NUM error. But the resulting IRR has no relationship to the IRR for the original values.

However, the Excel XIRR implementation is prone to erroneous results: bogus #NUM results in some cases; invalid numerical results in others.

So the devil is in the details that you need to provide.