XIRR returns 0.00% instead of a negative percentage

kylc3

New Member
Joined
Jun 16, 2017
Messages
14
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,768
Office Version
365
Platform
Windows
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
Joined
Jun 16, 2017
Messages
14
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
Joined
Sep 18, 2013
Messages
3,768
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,886
Messages
5,471,313
Members
406,755
Latest member
CalJake

This Week's Hot Topics

Top