[FIXED] How can we find the IRR of cash flows in a Dataframe?

Issue

I can easily find the NPV if items in a dataframe using the code below. But how can I get the IRR of the same items?

import numpy_financial as npf
import pandas as pd

# Intitialise data of lists
data = [{'Month': '2020-01-01', 'Expense':1000, 'Revenue':5000, 'Building':'Stadium'}, 
       {'Month': '2020-02-01', 'Expense':3000, 'Revenue':4000, 'Building':'Stadium'},
       {'Month': '2020-03-01', 'Expense':7000, 'Revenue':5000, 'Building':'Stadium'}, 
       {'Month': '2020-04-01', 'Expense':3000, 'Revenue':4000, 'Building':'Stadium'},
       {'Month': '2020-01-01', 'Expense':5000, 'Revenue':6000, 'Building':'Casino'}, 
       {'Month': '2020-02-01', 'Expense':5000, 'Revenue':4000, 'Building':'Casino'},
       {'Month': '2020-03-01', 'Expense':5000, 'Revenue':9000, 'Building':'Casino'},
       {'Month': '2020-04-01', 'Expense':6000, 'Revenue':10000, 'Building':'Casino'}]
df = pd.DataFrame(data)
df

df.groupby("Building")["Revenue"].apply(lambda x: npf.npv(rate=0.1, values=x))

Result:

Building
Casino     24587.528174
Stadium    15773.854245

I tried to find the IRR, like this.

df.groupby("Building")["Revenue"].apply(lambda x: npf.irr(values=x))

It calculates only NAN.

Result:

Building
Casino    NaN
Stadium   NaN

Documentation: https://numpy.org/numpy-financial/latest/irr.html

Solution

You could combine apply() with irr().

What you try to find is the interest rate, where the NPV is 0. However, as you only have positive revenues and no initial investment (neg. sign), it can not be. Please check out the formula used in the docs. You might want to also consider the expenses?

I've edited your example to demonstrate a working solution.

Update: I added large initial payments and passed the delta between expenses and revenue to irr().

import numpy_financial as npf
import pandas as pd

data = [{'Month': '2020-01-01', 'Expense':100000, 'Revenue':5000, 'Building':'Stadium'}, 
       {'Month': '2020-02-01', 'Expense':3000, 'Revenue':4000, 'Building':'Stadium'},
       {'Month': '2020-03-01', 'Expense':7000, 'Revenue':5000, 'Building':'Stadium'}, 
       {'Month': '2020-04-01', 'Expense':3000, 'Revenue':4000, 'Building':'Stadium'},
       {'Month': '2020-01-01', 'Expense':500000, 'Revenue':6000, 'Building':'Casino'}, 
       {'Month': '2020-02-01', 'Expense':5000, 'Revenue':4000, 'Building':'Casino'},
       {'Month': '2020-03-01', 'Expense':5000, 'Revenue':9000, 'Building':'Casino'},
       {'Month': '2020-04-01', 'Expense':6000, 'Revenue':10000, 'Building':'Casino'}]
df = pd.DataFrame(data)

irr = df.groupby('Building')[['Revenue','Expense']].apply(lambda x: npf.irr(x['Revenue'] - x['Expense']))

print(irr)

Output:

Building
Casino    -0.786486
Stadium   -0.809623
dtype: float64

Answered By - KarelZe