How can calculate the special cash flow's irr with excel or python accurately?

Question

It's simple to calculate the following cash flow's irr:

cash flow     date
-150000     2023-03-30
575         2023-04-30
594.17      2023-05-30
575         2023-06-30
594.17      2023-07-30
594.17      2023-08-30
575         2023-09-30
594.17      2023-10-30
575         2023-11-30
594.17      2023-12-30
575         2024-01-30
594.17      2024-02-30
150575      2024-03-30

With python:

#install numpy_financial first
#pip install numpy_financial
import numpy_financial as npf
cash =[-150000,575,594.17,575,594.17,594.17,575,594.17,575,594.17,575,594.17,150575]
npf.irr(cash)
0.003897273466228812
(1+npf.irr(cash))**12-1
0.047782876205111346

So the irr per month for the cash flow is 0.39%,the irr per month for the cash flow is 4.78%.

With excel irr(a1:a13) can get the same result for irr per month.

Now let's make the cash flow more special:

cash flow     date  
-150000    2023-03-30
421.67     2023-04-20
575        2023-05-20
594.17     2023-06-20
575        2023-07-20
594.17     2023-08-20
594.17     2023-09-20
575        2023-10-20
594.17     2023-11-20
575        2023-12-20
594.17     2024-01-20
594.17     2024-02-20
555.83     2024-03-20
150191.67  2024-03-30

How can calculate the irr accurately with python or excel then?

Answer 1

In Excel you can use the XIRR function to calculate the IRR with irregular investment dates. I do not see a numpy function, but there are separate xirr and pyxirr packages that seems to be relatively widely used.

Answer 2

Your issue seems to be that the time periods are irregular. It would aid reading the question to make that explicit.

Besides using the packages D Stanley mentions, options are to create an array with regular time periods (for example, create an array with cash flows for each day, with zeros filled in for each day without a cash flow), or to write a function that calculate the NPV from the IRR, and then use any goal-seek meta-function to find the IRR(s) that give a NPV of zero.

Note that I included a possible plural on IRR. You may already be aware of this, but I think it should be included as a caveat in discussions of IRR calculations: The equation for IRR given a list of N cash flows is an Nth order polynomial, so there will be N roots (although it's theoretically possible for some of them to be multiple roots). Usually all but one will be complex, but it is possible for a cash flow to be consistent with more than one real value of IRR.

Answer 3

To calculate the irr from the data i provided here.Please install python library with pip install pyxirr,save the data which contains cash flow and date in cash.csv.

from datetime import date
from pyxirr import xirr
import pandas as pd
df = pd.read_csv('cash.csv',header=None,sep='\s+')
df.columns = ['amount','date']
df['date'] = df['date'].astype('datetime64[ns]')
irr = xirr(df['date'],df['amount'])

The irr is annulized by default.

Answer 4

Here is a minimal coding solution written in Mathematica using only basic functions so easily transcribed to your language of choice without need of financial or solver libraries.

values = {-150000, 421.67, 575, 594.17, 575,
   594.17, 594.17, 575, 594.17, 575, 594.17,
   594.17, 555.83, 150191.67};

dates = {{2023,3,30}, {2023,4,20}, {2023,5,20},
   {2023,6,20}, {2023,7,20}, {2023,8,20}, {2023,9,20},
   {2023,10,20}, {2023,11,20}, {2023,12,20},
   {2024,1,20}, {2024,2,20}, {2024,3,20}, {2024,3,30}};

start = First[dates];
days = Function[date, First[DateDifference[start, date]]] /@ dates;
tdays = Last[days];
fractions = days/tdays;
Clear[r]
len = Length[dates];
terms = List[];
For[i = 1, i <= len, i++,
  AppendTo[terms, values[[i]]/(1 + r)^fractions[[i]]]];

dfractions = (tdays + days)/tdays;
dterms = List[];
For[i = 1, i <= len, i++,
  AppendTo[dterms, -values[[i]]*fractions[[i]]/(1 + r)^dfractions[[i]]]];

tolerance = 0.001;
r = 1234;
return = 0.01;(* initial guess *)
While[Abs[r - return] > tolerance,
  r = return;
  y = Total[terms];
  yprime = Total[dterms];
  return = r - y/yprime]
annualised = (return + 1)^(365/tdays) - 1

0.047788337

Check in Excel

The while loop is based on this Newton-Raphson algorithm and loops 3 times in this example.

If one were to use a solver only the code as far as the terms would be required:-

...
Clear[r]
terms = MapThread[#1/(1 + r)^#2 &, {values, fractions}];
return = Last[r /. NSolve[Total[terms] == 0, r, Reals]];
annualised = (return + 1)^(365/tdays) - 1

0.047788337

For transcription, below are the main lines with their outputs.