2

I have a Pandas dataframe of cashflows which are of unpredictable length. They range monthly over a period of between a few months and 30+ months and there is a column each month which contains that months cashflow information. I need to compute the IRR for each loan which is defined as follows;

Definition of IRR, where r is "IRR" when NPV is set to 0.

Below is a link to an example of the df where I have "months_remaining", the initial outflow and a range of cashflows over variable periods (between Feb-19 and Dec-29). "S" represents a loan that has settled, i.e. it has reached maturity and had paid back the full amount. "0" represents a loan that has defaulted therefore no further cashflows are expected in perpetuity;

Loans DF

I have tried a range of approaches. (1) Using iterrows to create a numpy array for each row then tried using numpy.irr, however the issue of only calculating IRR for a variable time period still stands (I need to recognise when cashflows stop with an "S" and stop calculating from this point.) (2) Avoiding row by row approaches and trying to perform calculations in Pandas, again the mixture of some "S" loans and some normal cashflows in columns trips this approach up.

Cell which I am currently working in.

Thanks for any help you can provide.

[EDIT] An example of working code using a for loop

 for row in irr_array:
  LoanID=row[1]
  Tier=row[2]
  row=row[2:2000]
  if "S" in row:
    new_array = [i for i in row if i is not "S"]
    irr_results["IRR"]= (pd.Series(round(irr(new_array),3)))
    irr_results["LoanID"]=(pd.Series(LoanID))
    irr_results["Tier"]=(pd.Series(Tier))
    results=irr_df.append(irr_results)
  if 0 in row:
    irr_results["IRR"]= 0
    irr_results["LoanID"]=(pd.Series(LoanID))
    irr_results["Tier"]=(pd.Series(Tier))
    results=irr_df.append(irr_results)
SWBM
  • 21
  • 2
  • Looks similar to https://stackoverflow.com/questions/46203735/calculating-variable-cash-flow-irr-in-python-pandas?rq=1 – shiv_90 Sep 20 '18 at 10:29
  • Hi Shiv_9, I am aware of this example, however it doesn't deal with the key issue I am having which is being able to perform calculation over varied time periods depending on the number of "months_remaining". Any ideas? – SWBM Sep 24 '18 at 08:37
  • 1
    Well your example will require more code since IRR calculation is not really a very simple thing TBH. For loans that have 'S', you can use conditional statements to stop/continue the program but the IRR thing will require some math commands. Unfortunately since I lack a similar dataset in my machine, I won't be able to share a sure answer. – shiv_90 Sep 24 '18 at 13:45
  • 1
    I've managed to get a working piece of code, however it's very slow (using a for loop on each column and numpy.irr to calculate IRR). – SWBM Sep 25 '18 at 15:00
  • Good going! Is it providing you the required output for all loans? Python can be slow for certain actions, but that doesn't mean that the code is inefficient. The primary objective is to make it work first, tweaking it comes later. If you think your solution worked, then you can post it below as a self answered question. – shiv_90 Sep 27 '18 at 07:01

0 Answers0