How can we find the IRR of cash flows in a Dataframe?-CodePudding

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

CodePudding user response：

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.

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)

irr = df.groupby("Building")["Revenue"].apply(npf.irr)

Output:

Building
Casino     0.909400
Stadium    0.679223
Name: Revenue, dtype: float64