XIRR returns 0.00% instead of a negative percentage

[kylc3]

Hi,

I'm running a savings scenario where the ending value of my investment ends up being less than the total contributions based on monthly returns during a down market period, but the XIRR is returning a 0.00% instead of a negative percentage. In other periods, when the ending value is greater than the total contributions, the XIRR formula works properly. Can anybody explain/provide a formula that will return a negative XIRR in the attached scenario? Thanks so much!

XIRR Issue.xlsx in link: https://ufile.io/ctp8d

 

[StephenCrump]

Your XIRR is actually returning a very small value, approximately 2.98E-0.09 which is really an error value. XIRR basically is throwing in the towel as it can't cope with the calculation.

Enter the optional third argument, guess, as say -10% and the XIRR will calculate correctly --> approx -6.23% p.a.

 

[kylc3]

Your XIRR is actually returning a very small value, approximately 2.98E-0.09 which is really an error value. XIRR basically is throwing in the towel as it can't cope with the calculation.

Enter the optional third argument, guess, as say -10% and the XIRR will calculate correctly --> approx -6.23% p.a.

Thanks so much! One follow up question... I'm using this formula over rolling periods resulting in various outcomes, both positive and negative. Will this added parameter (-10%) be safe to use and result in the correct XIRR for all of the different periods? Or should I do something like, use an IF statement to say to use the XIRR w/o a parameter if the ending value for the period is higher than the total contributions, which results in a positive return, or use the XIRR w/ -10% parameter if the ending value for the period is lower than the total contributions, which results in a negative return.

Thanks again for the help!

 

[StephenCrump]

I never use XIRR myself, so I can't answer definitively.

In the example you provided, it only takes a very small negative guess, e.g. -0.000001 to send XIRR off looking in the right direction. I think you'll find the converse is also the case, a small negative guess will flummox XIRR if the return is actually positive.

So what you say makes sense to me, and I suggest you just try it out and see if it works.

You may also have problems with XIRR if your cashflows flip between positive and negative, as potentially then there will be multiple solutions and you may get strange results depending on the initial guess.

A quick Google of the limitations of XIRR might be informative.

 

[joeu2004]

Crossposted to https://www.excelforum.com/excel-fo...g-value-is-less-than-total-contributions.html. See responses there.