LinkBack URL
About LinkBacks
I can't get a simple XIRR to work when the total cash outlaw is negative.
I can't get a simple XIRR to work when the total cash outlaw is negative.
Welcome to the forum.
What is a cash outlaw? For some reason, I have the image of a bandit in my head ...
How about a sample workbook? Instructions at the top of the page.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
If you mean all your cashflows are negative, there can't be an IRR. IRR requires at least one sign change in a set of cashflows. If you mean the sum of nominal cashflows is negative, that actually means very little with respect to IRRs. Consider -1 at time 0 and +10 at time 1, the IRR is +900%. OTOH, +1 at time 0 and -10 at time 1 also has an IRR of +900%.
Details will definitely help.
I am not knowledgeable about financial models and such, so I don't understand all of the mathematical and financial ins and outs of the XIRR() (and other financial functions). I know that in other discussions, we have talked about some numeric instabilities in Excel's XIRR() implementation. A value near 0 (something like 2.98E-9 seems to be the common value returned) is often the equivalent of an error value. User joeu2004 discusses this issue here: https://www.excelforum.com/excel-gen...ml#post5191942 I observed during the same discussion that other spreadsheets seem to have more stable implementations of XIRR() and don't return the same "error" value.
Debugging steps I would take at this point:
1) Check the actual XIRR() output and see if it is 2.98E-9 or similar to indicate an error condition.
2) Check the result by calculating XNPV(). If the interest rate is correct, the XNPV() will be 0 or nearly 0. If the XNPV() is not 0, then you know the XIRR() returned an incorrect value. Note joeu2004's observation that Excel will not allow a negative interest rate in the XNPV() function, so you may need some adjustment (or use a different spreadsheet that is not limited in this way).
3) Try different values for the "guess" parameter in the XIRR() function.
4) Calculate XNPV() at different interest rates, then create a scatter chart for XNPV() vs. interest rate. Remember that XIRR() is determining the interest rate that makes XNPV()=0, so you can then look at the chart and determine approximately where XNPV() would be 0.
That should give you some things to read and try while debugging your XIRR() function.
Originally Posted by shg
Please confirm your range of dates and particularly your values.
If they are all negative or all positive then you won;t get a result. One of the numbers needs to have a different sign
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
I had the same problem until I used a guess of -.5. Then it correctly calculated the negative IRR.
C182
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks