Python IRR Function giving different result than Excel XIRR

Question

I am using the following functions to perform IRR-Calculations with Python:

from scipy.optimize import newton

def xnpv(rate, values, dates):
    
    if rate <= -1.0:
        return float('inf')
    min_date = min(dates)
    return sum([
        value / (1 + rate)**((date - min_date).days / 365)
        for value, date
        in zip(values, dates)
     ])


def xirr(values, dates):
    return newton(lambda r: xnpv(r, values, dates), 0)

Source for functions: https://2018.pycon.co/talks/personal-pynance/personal-pynance.pdf

For months this functions worked perfectly with all kind of different cash flows & dates and I got the same result as with Excel's XIRR function. However, suddently with the below list of cash flows & dates it stopped working and I get a different result than with Excel's IRR Formula (which is the correct& expected one):

import pandas as pd
import datetime
import numpy as np
from decimal import *

# Input Data
dates = [datetime.date(2020, 8, 31), datetime.date(2020, 5, 5), datetime.date(2020, 2, 28), datetime.date(2020, 8, 31),datetime.date(2018, 6, 30)]
values = [50289.0, -75000.0, 0.0, 0.0, 0.0]

# Create Dataframe from Input Data
test = pd.DataFrame({"dates" : dates, "values" : values})

# Filter all rows with 0 cashflows
test = test[test['values'] != 0]

# Sort dataframe by date
test = test.sort_values('dates', ascending=True)
test['values'] = test['values'].astype('float')

# Create separate lists for values and dates
test_values = list(test['values'])
test_dates = list(test['dates'])

# Calculate IRR
xirr(test_values, test_dates)

The result I get in Python is 0.0001 whereas in Excel I get -0.71 and I have no clue what I am missing here. Maybe someone has an idea?!??!

score 3 · Accepted Answer · answered Sep 08 '20 at 16:36

3

Scipy optimization functions are fallable to local minima. Change optimization method to something diferent, e.g. anderson, and get what you expect to.

Proof

from scipy.optimize import anderson

def xnpv(rate, values, dates):
    
    if rate <= -1.0:
        return float('inf')
    min_date = min(dates)
    return sum([
        value / (1 + rate)**((date - min_date).days / 365)
        for value, date
        in zip(values, dates)
     ])


def xirr(values, dates):
    return anderson(lambda r: xnpv(r, values, dates), 0)

import datetime
from decimal import *

# Input Data
dates = [datetime.date(2020, 8, 31), datetime.date(2020, 5, 5), datetime.date(2020, 2, 28), datetime.date(2020, 8, 31),datetime.date(2018, 6, 30)]
values = [50289.0, -75000.0, 0.0, 0.0, 0.0]

# Create Dataframe from Input Data
test = pd.DataFrame({"dates" : dates, "values" : values})

# Filter all rows with 0 cashflows
test = test[test['values'] != 0]

# Sort dataframe by date
test = test.sort_values('dates', ascending=True)
test['values'] = test['values'].astype('float')

# Create separate lists for values and dates
test_values = list(test['values'])
test_dates = list(test['dates'])

# Calculate IRR
xirr(test_values, test_dates)
array(-0.70956212)

answered Sep 08 '20 at 16:36

Sergey Bushmanov

23,310
7
53
72

Yes, thanks a lot you saved my day!! I changed the function to anderson and get the expected result. However, now some other cash flows are not calculated correctly, that worked fine till now with the newton method. Nevertheless, thanks to you now I know the root cause of this issue and will try to find a solution by myself... – Daniel Sep 09 '20 at 09:13
1

Try providing your rough guess for what the value should be. I think this should work in most situations. – Sergey Bushmanov Sep 09 '20 at 09:21
2

I am writing a library which translates Excel formulas into Python and I have just written support for XIRR using Sergey Bushmanov's code. The library is [xlcalculator][1] and the code for XIRR can be found in the financial function definitions. [Microsoft help][2] states they use the newton optimization in the XIRR calculation. I support the suggestion for supplying a guess. [1]: https://github.com/bradbase/xlcalculator [2]: https://learn.microsoft.com/en-us/office/troubleshoot/excel/algorithm-of-xirr-funcation – bradbase Nov 24 '20 at 10:21
1

@bradbase Very nice endeavour! While translating Excel formulas it could be especially helpful if operators like LOOKUP, which do not have exact counterparts in Python, can be applied to a csv (with possibly specifying a range) – Sergey Bushmanov Nov 24 '20 at 10:38
Cheers. I'm proud of it. The xlcalculator library can be initiated from a dictionary (link below). I think this is the only library of it's kind which can take a dict. I am (/the project is) yet to implement LOOKUP, but it's on the cards. the competing libraries (pycel, formulas and koala) have implemented LOOKUP so we will need to support it. Currently nobody has raised an issue to support LOOKUP, neither has anyone offered a PR ;D . https://github.com/bradbase/xlcalculator/blob/master/tests/test_model.py:275 – bradbase Nov 24 '20 at 11:14
https://github.com/bradbase/xlcalculator#lookup-and-reference – bradbase Nov 24 '20 at 11:14
crap.. I broke my link. xlcalculator can use a dict. Can see how it's done in the tests.. Line 275 in the below unittest file. https://github.com/bradbase/xlcalculator/blob/master/tests/test_model.py It kinda needs to be a dict because xlcalculator needs to know which sheet and cell a given value is on. Then (when LOOKUP is supported), you can get the function evaluated, set new values on the cells, and evaluate again... etc.. – bradbase Nov 24 '20 at 11:37

Python IRR Function giving different result than Excel XIRR

1 Answers1