Modelling Loan Payments - Calculate IRR

Question

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

enter image description here

Stuff I tried now: df_irr2 = pd.DataFrame(data=df_irr, index= 'id', columns= 'funded_amnt_t' 'Expect_NoPayments''installment') df_irr3 = df_irr.groupby(by = df_irr['id']) df_irrT.pivot(index='id', columns='Expect_NoPayments', values='installment') — Wisdom Analytics, Jan 25 '19 at 17:07

score 0 · Accepted Answer · answered Jan 25 '19 at 15:20

0

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.

answered Jan 25 '19 at 15:20

Polkaguy6000

1,150
1
8
15

Nope, not the solution. And I still need to reshape my dataset. – Wisdom Analytics Jan 25 '19 at 17:06
Why is this not the solution? What do you mean by "reshape your dataset"? If you do the math, `IRR` with even payments and `rate` will give you the same result. Can you please be more specific with your question? – Polkaguy6000 Jan 25 '19 at 20:22
It might be useful if you added your desired output for the question. – Polkaguy6000 Jan 25 '19 at 20:41
Dear @Polkaguy6000 I did the best I could to put down an excel file to explaint the desired outcome. – Wisdom Analytics Jan 28 '19 at 16:41
No i have tried this for i in range(0, 60): if i == 0: df_irrT['i'] = df_irr[['funded_amnt_t']] if i <= df_irr[['Expect_NoPayments']]: df_irrT['i'] = df_irr[['installment']] – Wisdom Analytics Jan 28 '19 at 18:37

Modelling Loan Payments - Calculate IRR

1 Answers1