invalid calculation for xirr

[lucas03]

I am getting invalid result for this input. (I know it's not reasonable data, but xirr should deal with those anyway). Here it is:

>>> short = [(datetime.date(2000, 6, 9), 2500.0), (datetime.date(2000, 6, 9), -2500.0)]
>>> xirr(short)
0.100055

Google spreadsheet returns error and libre office as well. Why does your calculation return that number?

[lucas03]

Hm, that number is a guess(0.1 in my case) plus some small number.

[lucas03]

Ok, I noticed there is a warning:

/usr/lib/python2.7/dist-packages/scipy/optimize/zeros.py:150: RuntimeWarning: Tolerance of 0.00011 reached
  warnings.warn(msg, RuntimeWarning)

So I made a quick dirty code to fix it - my xirr function.

def xirr(cashflows,guess=0.1):
    """
    import warnings
    with warnings.catch_warnings(record=True) as w:
        result = optimize.newton(lambda r: xnpv(r,cashflows),guess)
        if w:
            return None
    return result

[peliot]

thanks for flagging this. This is the first case I have seen where my function returns a different value than libreoffice calc. It is so close to the seed value (0.1) that something must be going on in the first iteration. I will take a look. On Jun 9, 2015 5:54 PM, "Lukas Vojt" notifications@github.com wrote:

Ok, I noticed there is a warning:

/usr/lib/python2.7/dist-packages/scipy/optimize/zeros.py:150: RuntimeWarning: Tolerance of 0.00011 reached warnings.warn(msg, RuntimeWarning)

So I made a quick dirty code to fix it - my xirr function.

def xirr(cashflows,guess=0.1): """ import warnings with warnings.catch_warnings(record=True) as w: result = optimize.newton(lambda r: xnpv(r,cashflows),guess) if w: return None return result

— Reply to this email directly or view it on GitHub https://github.com/peliot/XIRR-and-XNPV/issues/5#issuecomment-110515747.

[lucas03]

Ok, thanks. Catching warnings worked ok yesterday, but today I do not see any warnings and it gives me above mentioned number. // it appears warnings are raised randomly.

[lucas03]

@peliot any update on this issue?

[manonfire86]

Hi,

Was this ever solved? I am encountering the same issue

[lucas03]

probably not

[peliot]

It's been awhile since I looked at this code (I am no longer using it in production). When I wrote it, I was testing against MSFT Excel and never found a case where the secant method version disagreed with Excel (which I believe uses the same method). The scipy Newton's method optimizer should use the same method, though it handles errors better. I don't know what numerical solver method Calc uses, but I assume it is using a slightly different approach which is causing the error (I don't have Calc installed here to test against). I don't spend as much time with the intricacies of IRR calculations as I used to, but I would caution that whatever the platform (Excel, Calc, Sheets, etc), XIRR is a tricky function to work with because it always requires a numerical solver, and numerical solvers are prone to error if the function is not well formed. There are always going to be corner cases that cause errors or in the worst case, cause the solver to produce an incorrect answer (due to starting at a stationary point or landing on a local minimum instead of the global minimum, etc). Regardless of platform, it's important to have some way of sanity checking the answer (I consider Excel the gold standard for XIRR, and I have had real-life cash flow examples that caused it to produce an incorrect value, much like this example with Calc). If you are embedding this code in something else where you can't visually inspect the answer, perhaps you could run it multiple times with different seed values and take the most commonly returned result. The default seed is 0.1 (same as Excel and Calc), but that fails to produce an answer in some cases where the IRR is negative, in which case a negative seed is required to get the solver to converge on the right answer (in Excel and in this code). All that is to say, I don't view this error as a limitation of the code, but rather a limitation of XIRR solvers in general, which can never be guaranteed to produce a correct answer 100% of the time, due to the necessity of using numerical solvers. My goal was to match behavior with Excel (which I believe it does). If other spreadsheet programs implement the numerical solver differently, there are going to be discrepancies when you hit those corner cases.

Regards, Philip

On Fri, Nov 20, 2020 at 4:33 PM Lukas Vojt notifications@github.com wrote:

probably not

— You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub https://github.com/peliot/XIRR-and-XNPV/issues/5#issuecomment-731418170, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABOXRMPZVSIGQQTZA5IQTF3SQ3OBTANCNFSM4BHWV6XA .

[peliot]

Just to add the specific answer to @lucas03 's original question - the definition of IRR is the discount rate at which the NPV of the cash flows is zero. So, what we are doing to find IRR is looking at NPV as a function of discount rate and finding the zero values. For the original example, where there is an in flow and out flow of the same magnitude on the same day, the NPV is zero, regardless of the discount rate. The plot of NPV as a function of discount rate is a flat line at y=0. Thus the first discount rate we try (the guess) yields a zero for NPV and is returned as the answer. That answer is not incorrect - it is a discount rate for which the NPV is zero, which is the definition of IRR, but it is not the only answer.

[manonfire86]

Hi,

Secant method is better for error handling. I just updated my code to utilize that method instead.

On Sat, Nov 21, 2020 at 9:57 PM peliot notifications@github.com wrote:

Just to add the specific answer to @lucas03 https://github.com/lucas03 's original question - the definition of IRR is the discount rate at which the NPV of the cash flows is zero. So, what we are doing to find IRR is looking at NPV as a function of discount rate and finding the zero values. For the original example, where there is an in flow and out flow of the same magnitude on the same day, the NPV is zero, regardless of the discount rate. The plot of NPV as a function of discount rate is a flat line at y=0. Thus the first discount rate we try (the guess) yields a zero for NPV and is returned as the answer. That answer is not incorrect - it is a discount rate for which the NPV is zero, which is the definition of IRR, but it is not the only answer.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/peliot/XIRR-and-XNPV/issues/5#issuecomment-731684088, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHIKYR7QA3R6HOLVZFNVA5TSRB423ANCNFSM4BHWV6XA .