Loan portfolio modelling

daabs

New Member
Joined
Feb 25, 2007
Messages
3
Does anyone know a way to model a portfolio of performing loans made for auto finance? It's easy enough to model repayments for a single loan using NPV but are there any conventions for modelling multiple loans, the number of which changes from month to month owing to new loans, defaults, early payouts and going full term. By way of stabilising some variables, it's all right to assume, for example:

1. average loan size of $20k
2. interest rate 10%
3. term 60 months
4. balloon/residual $5k
5. average early repayment term 32 months

Or does anyone know a good reference that deals with modelling structured finance including securitization?

Thanks
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Does anyone know a way to model a portfolio of performing loans made for auto finance? It's easy enough to model repayments for a single loan using NPV but are there any conventions for modelling multiple loans, the number of which changes from month to month owing to new loans, defaults, early payouts and going full term. By way of stabilising some variables, it's all right to assume, for example:

1. average loan size of $20k
2. interest rate 10%
3. term 60 months
4. balloon/residual $5k
5. average early repayment term 32 months

Or does anyone know a good reference that deals with modelling structured finance including securitization?

Thanks

There are many wasy to do this. You can assume that you have one loan - that seems to be what you suggesting with your average - but I don't think it is a good idea. You can have the database in one worksheet and use vba to either do everything (amortization and prepayment and default analysis) or you can have a seperate worksheet with formulas that does everything and use vba to loop and aggregate each loan into the worksheet.

Gene Klein
 
Upvote 0
Not having a proficiency in VBA I was wondering if there was either an off-the-shelf product you were aware of or a suggested way in excel that will allow me to model the origination of X number of loans every month for say 2 yrs, that have some fixed avg Obal, term, APR, default & prepay rates, etc. I can model an amortization schedule including default and prepayment rates for a single loan or static pool but I'm not sure if simply producing amortization schedules for each individual month and then aggregating them is the best way to represent this structure. I also want to be able to incorporate my cost of capital. In a nut shell, I want to model a lending company scenario. Any advice would be greatly appreciated!
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top