what is different in excel and pyxirr when calculating xirr？

[dytttf]

Hello， I encountered some problems while calculating xirr. Could you please help me check if it is a bug。

This is data file： 3547的irr.xlsx

Firstly, when I use Excel to calculate xirr, I can get the results：

But，when I use pyxirr to calculate xirr, I can not get the results, it return None.

code in here: pyxirr==0.10.3

import openpyxl
from pyxirr import xirr
from decimal import Decimal

wb = openpyxl.load_workbook("3547的irr.xlsx", read_only=True)
sheet = wb["3547的irr"]

header = []

dates = []
cashs = []
values = []

for idx, row in enumerate(sheet.rows):
    value = [x.value for x in row]
    if idx <= 0:
        header = value
        continue
    values.append(dict(zip(header, value)))

values.sort(key=lambda x: x["report_date"])

for v in values:
    if v["dim_id"] != "3547##0":
        continue
    v["report_date"] = v["report_date"].date()
    v["market_value"] = Decimal(v["market_value"])
    dates.append(v["report_date"])
    cashs.append(v["market_value"])
    if not v["between_day"]:
        continue
    if v["between_day"] <= 1:
        dates.append(v["report_date"])
        v["market_value2"] = Decimal(v["market_value2"])
        cashs.append(v["market_value2"])
        # 0
        # step = -4682
        step = 0
        irr = xirr(dates[step:], cashs[step:], guess=-0.9)
        print(irr)

wb.close()

In line 4681 of the file, there is a significant negative value, which may be the reason why it cannot be calculated：

But Excel can calculate it, so I want to know where the difference is?

[Anexen]

Hello @dytttf,

Both pyxirr and Excel utilize iterative algorithms for finding IRR. However, pyxirr additionally checks if the xnpv is close to zero with a precision of 0.001. If it is not, pyxirr returns None. In your case, it's impossible to represent IRR as a double (float64) number that is small enough for xnpv to be close to zero. The minimum possible xnpv value is 32 (you can verify this in your spreadsheet).

There is one trick that can help when working with large numbers: scaling. You can divide each amount by some constant value, for example, by the maximum amount:

scale_factor = max(map(abs, cashs[step:]))
# you can also simply divide by 1000 or 1000000 depending on your data
amounts = [x/scale_factor for x in cashs[step:]]
irr = xirr(dates[step:], amounts)

[dytttf]

Thank you，It works.