Loan Portfolio Analysis Template

Description
If you are involved in the lending industry as a buyer of loan portfolios, investor in loans, and/or are looking to clean up your in-house loan tape reporting, this template is a great place to start. I've included only the most relevant columns of data points for reporting on the most important metrics of a loan tape.


The focus is on risk rating cohorts and the performance therein. KPIs include:
 - Loans settled by risk rating
 - Loan amounts settled by risk rating
 - Ongoing default rate and total default rate by risk rating
 - Weighted average interest rate by risk rating
 - Total amount of loans settled vs total amount of defaults


The monthly summary displays data for up to 5 years, but you can easily expand that to 10 or 15 years or however much is needed by dragging the formulas over from the last column. Then, simply adjust the start month and the whole analysis will update based on the data in the loan tape tab.


This also works as a forecasting model by displaying the expected future principal and interest of the existing loans. You will be able to enter the number of remaining months, which will drive the remaining expected payments along with the total loan term, interest rate, and initial loan amount.


The visualizations are going to display cumulative data by month for all the above metrics.



This template is also included in three bundles:
- All Models Bundle
- Accounting Tools
- Lending Models