# XIRR returns 0.00% instead of a negative percentage

#### kylc3

##### New Member
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!

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### StephenCrump

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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.

1,102,476
Messages
5,487,104
Members
407,578
Latest member
jana007