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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top