Wonky XIRR Return

[Drymr]

Hi there,

I have a long XIRR calculation that keeps returning 0.00% even though the negatives are significantly outweighed by the positives (-2,607,471 and +4,661,616). I tried pasting the calculation below but it just came out as text and isn't formatted correctly. I think there may be something wrong with a setting in excel because this doesn't make sense to me. I've checked and iterative calculation is checked. Can anyone suggest what the problem may be?

Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Jan-25	Feb-25	Mar-25	Apr-25	May-25	Jun-25	Jul-25	Aug-25	Sep-25	Oct-25	Nov-25	Dec-25	Jan-26	Feb-26	Mar-26	Apr-26	May-26	Jun-26	Jul-26	Aug-26	Sep-26	Oct-26	Nov-26	Dec-26	Jan-27	Feb-27	Mar-27	Apr-27	May-27	Jun-27	Jul-27
0	0	0	0	-1,325,289	0	0	25,490	10,646	-58,022	-25,610	-25,587	-25,613	-25,610	-25,587	-25,610	-25,587	-25,613	-25,604	-25,534	-25,610	-33,587	-33,610	-41,927	-41,953	-41,950	-36,927	-63,950	-47,723	-47,750	-47,744	-47,697	-47,747	-47,723	-47,747	-47,723	-47,750	-48,673	-170,411	86,102	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	4,539,379	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
IRR	0.00%

<tbody>
</tbody>

 

[StephenCrump]

I think you'll find the zero result is actually approximately 2.98E-09. It's a little quirk of XIRR that it returns this value if it can't calculate a result.

You haven't posted your formula, but my guess is that you're starting with the August 2017 zero value? You'll need to start with the non-zero December 2017 value, in which case XIRR should return approximately 15.04% p.a., which looks correct to me.

You're probably aware of the limitations of IRR, i.e. if your cashflows flip between positive and negative there may be multiple solutions?

 

[DRSteele]

I'll add my two-cents here to help out. I suspect different versions of Excel interpret IRR with multiple inflection points in different ways. I put the entire dataset into IRR and the function reported 1.1757093%.

Incidentally, XIRR reported 15.0417155% when the dataset begins at Dec , 2017.

 

[Macropod]

Cross-posted at: https://www.excelforum.com/excel-general/1213497-wonky-xirr-return.html

Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[Drymr]

Cross-posted at: https://www.excelforum.com/excel-general/1213497-wonky-xirr-return.html

Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

Sorry about that. I was not aware of the policy. Thanks for pointing it out.

 

[Drymr]

I think you'll find the zero result is actually approximately 2.98E-09. It's a little quirk of XIRR that it returns this value if it can't calculate a result.

You haven't posted your formula, but my guess is that you're starting with the August 2017 zero value? You'll need to start with the non-zero December 2017 value, in which case XIRR should return approximately 15.04% p.a., which looks correct to me.

You're probably aware of the limitations of IRR, i.e. if your cashflows flip between positive and negative there may be multiple solutions?

Thanks for replying. Too bad XIRR isn't able to start with zeros. Oh well.

 

[StephenCrump]

Thanks for replying. Too bad XIRR isn't able to start with zeros. Oh well.

Assuming your header Aug-17 is in cell A1, you could use:

=XIRR(INDEX(A2:DP2,MATCH(TRUE,A$2:DP$2<>0,)):DP2,INDEX(A1:DP1,MATCH(TRUE,A$2:DP$2<>0,)):DP1) array-entered

which should return 15.04% p.a.

 

[StephenCrump]

I put the entire dataset into IRR and the function reported 1.1757093%.

.... per period (month) = (1+1.1757%)^12-1 = 15.06% p.a., i.e. close to XIRR result, but not exactly matching given the IRR assumption that all months are of equal length.

 

[DRSteele]

Stephen, yes I did calulate all that. Thanks tons.

 

[joeu2004]

(1+1.1757%)^12-1 = 15.06% p.a., i.e. close to XIRR result, but not exactly matching given the IRR assumption that all months are of equal length.

That doesn't make it wrong. In fact, that might be the right thing to do. The IRR is used for two typical purposes: for comparing financial options; and for calculating the imputed interest rate. For the former, the exact percentage usually is not the determining factor. For the latter, some jurisdictions require that monthly payments be treated as occurring equally spaced; in fact, in the US, the APR is 12*IRR(...), not the compounded rate.

I, too, had thought of suggesting the use of Excel IRR, if only because it seems to be more stable than Excel XIRR.

"More stable", but not without its foibles. I believe that Excel IRR, like Excel XIRR, uses an approximation of the derivative of the NPV instead of the exact derivative, which is computable. I suspect it seems to have less of adverse impact on Excel IRR because the discount rate is compounded by integers instead of non-integers.