# XIRR near 0 when should be negative

1. ## XIRR near 0 when should be negative

I can't get a simple XIRR to work when the total cash outlaw is negative.  Register To Reply

2. ## Re: XIRR near 0 when should be 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.  Register To Reply

3. ## Re: XIRR near 0 when should be negative

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%.  Register To Reply

4. ## Re: XIRR near 0 when should be negative

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.  Register To Reply

5. ## Re: XIRR near 0 when should be negative

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  Register To Reply

6. ## Re: XIRR near 0 when should be negative

I had the same problem until I used a guess of -.5. Then it correctly calculated the negative IRR.
C182  Register To Reply