Working with loan data. I have a dataframe with the columns:
df_irr = df1[['id', 'funded_amnt_t', 'Expect_NoPayments','installment']]
ID of the Loan | Funded Amount | Expected Number of Payments | fixed instalment of the annuity.
I have estimated the number of payments with regression analysis. the loans have 36 or 60 months maturity.
Now I am trying to calculate the expected irr (internal rate of return).
But I am stuck
I was planning to use numpy.irr
However, I never had the chance to use it - as my date is not in the right format?
I have tried pandas pivot and reshape functions. No Luck.
Time series of cash flows: - Columns: Months 0 , ...., 60 - Rows: ID for each loan - Values in Month 0 = - funded_amount - Values in Month 0-60: installment if expected_number_of_payments > months
My old Stata code was:
keep id installment funded_amnt expectednumberofpayments
sort id
expand 61, generate(expand)
bysort id : gen month = _n
gen cf = 0
replace cf = installment if (expectednumberofpayments+1)>=month
replace cf = funded_amnt*-1 if month==1
numpy.irr is the wrong formula to use. That formula is for irregular payments (e.g. $100 in month 1, $0 in month 2, and $400 in month 3). Instead, you want to use numpy.rate. I'm making some assumptions about your data for this solution:
import numpy as np
df_irr['rate'] = np.rate(nper=df_irr['Expect_NoPayments'],
pmt=df_irr['installment'],
pv=df_irr['funded_amnt_t'])
More information can be found here numpy documentation.