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 number. 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!
If you format C3 as Scientific, you will see that XIRR returns about 2.98E-09, not really zero.Originally Posted by kylc3
Although you might think that is close enough to zero, in my experience, that constant represents an error state in XIRR.
In this case, I believe it is an alternative for the #NUM error.
Thus, XIRR might require a "guess" in order to determine the IRR.
And in fact, =XIRR(C6:C126,A6:A126,-6.5%) results in about -6.23%.
I did not pick -6.5% out of thin air. Instead, I determined the inflection point by using the following tables.
FYI, I use SUMPRODUCT instead of XNVP because XNPV does not accept a negative discount rate, for no good reason. It's a defect, at least in Excel 2010 and earlier.
Please Login or Register to view this content.
PS.... Based on only the left-hand table, =XIRR(C6:C126,A6:A126,-5%) would have worked just as well. I drilled down one more level (right-hand table) because the NPV curve for varying discount rates seems to be very steep near the inflection point; and in my experience, often the first level on the left is not sufficient for a steep NPV curve.
Last edited by joeu2004; 10-19-2017 at 08:32 PM. Reason: minor; PS
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 (-XX%) be safe to use and result in the correct XIRR for all of the different periods? Can I just pick a number like -5%?
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/ -XX% (possibly -5%?) 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!
Well, you might get lucky. But in general, no.
It's not that simple; and it has little to do with how the sum of the contributions compares with the ending value.
First, the situations when XIRR will fail in some manner cannot be predicted.
Second, even if they could be, it is difficult to predetermine the "guess" to use.
It would be possible to write a VBA function that implements some form of the method that I demonstrated in my first response. In other words, determine the "guess" dynamically.
However, there are two potential flaws with that.
First, there can be multiple IRRs and even none. That is, in part, due to the inherent nature of the IRR -- the concept, not the Excel function. When there are multiple IRRs, one might seem "more appropriate" than the others. But that is very subjective.
Second, there seems to be flaws in the Excel XIRR implementation. It sometimes produces numerical results which are not the IRR; that is, the returned rate does not cause the NPV to be close to zero. And it sometimes fails (returns #NUM, #DIV/0 or 2.98E-09) even when the "guess" is the actual IRR.
My own VBA implementation of XIRR avoids the latter issue. But there is no workaround for the first issue.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks