Weird values for xirr when too close to zero

[isagarzazu]

Hi,

when I run the following example:

dates = [date(2020,1,9),date(2020,2,12),date(2020,3,2),date(2020,3,13),date(2020,5,11),date(2020,5,11),date(2020,5,11),date(2020,5,11),date(2020,11,3),date(2020,12,29),date(2021,3,26),date(2021,7,21),date(2022,6,16),date(2022,7,6)]

cashflow = [-1200,-1050,-400,-800,1500,1100,2000,450,-2000,2850,-1500,2025,-2000,2635]

xirr(dates, cashflow)

I get an xirr of 3.6894338683170713; the same calculation in excel gives me. a value of 0.0000003%. Seems to be an issue with the function for values very close to zero, any suggestions on how to deal with this?

thanks

[isagarzazu]

Here is another example:

dates= [date(2017,11,30),date(2018,1,31),date(2018,2,28),date(2018,3,31),date(2018,4,30),date(2018,5,31),date(2018,7,28),date(2018,7,31),date(2018,8,30),date(2018,8,30),date(2018,8,31),date(2018,8,31),date(2018,10,30),date(2018,10,30),date(2018,11,30),date(2018,12,31),date(2018,12,31),date(2018,12,31),date(2019,2,28),date(2019,3,31),date(2019,5,31),date(2020,1,28),date(2021,3,31),date(2021,10,30),date(2021,10,30),date(2022,2,28),date(2022,2,28),date(2022,4,30),date(2022,6,30),date(2022,10,28),date(2022,11,28),date(2022,11,28),date(2022,11,30)]

cashflow=[-1000,-1050,-1000,-650,-500,-1085,1472.5,1180,1600,7.5,1547.5,132.5,874,100.2,-1500,-1200,-500,1735,-1000,2080,-500,1900,1491.25,3300,1030,-1000,-500,1667.5,-21457.5,1808.71,1043.23,0.01,9153.25]

xirr(dates, cashflow)

xirr with a guess of 0.1 is 0.1798; in excel with the same guess is -0.6488

[Anexen]

Hi @isagarzazu, sorry for the late response.

I've checked the data, pyxirr returns the correct result. This can be proven using the xnpv function. By the definition, xirr is equals to the rate at which xnpv = 0. If you use the result from Excel, xnpv will not be equal to 0.

# must be close to zero
xnpv(xirr(dates, cashflow), dates, cashflow)

Excel sometimes gives the wrong result. Basically xirr is a solution to the minimization problem. I think Excel returns "garbage" if the algorithm does not converge. At the same time, pyxirr has additional checks and returns None if xnpv != 0.

I also checked the calculation in libreoffice and it matched the result of pyxirr.