When you're evaluating the performance of loan tapes (data sets containing detailed information on a pool of loans) over time, you can use cohort analysis to group loans by certain shared characteristics (like the month or year of origination) and then measure their performance over time. Here's a breakdown of how to approach this analysis and what metrics/data points you might consider:
Check out the various lending models and templates I've built here.
Note, the term "performance" just means the repayments over time of principal and interest as well as default rates.
Metrics to Consider:
- Origination Volume: The total number or dollar amount of loans originated in a given period (by risk rating, interest rate range, term length, type (if you offer different loan products) and/or in aggregate).
- Default Rate: The percentage of loans that have defaulted within each cohort. This is a hugely important metric that any investor wants to understand thoroughly if they are looking to buy a loan portfolio.
- Prepayment Rate: The percentage of loans that have been paid off ahead of schedule.
- Delinquency Rate: The percentage of loans that are behind on payments by a certain number of days (e.g., 30, 60, 90 days).
- Loss Given Default (LGD): The amount of money lost when a loan defaults, usually expressed as a percentage of the loan amount.
- Recovery Rate: The percentage of defaulted loan amounts that have been recovered.
- Net Loss Rate: The total amount of losses minus recoveries, divided by the total original balance for the cohort.
- Current Balance: The remaining balance of loans in a cohort over time.
Cohort Analysis Steps:
- Data Segregation: Group loans by origination date, typically by month or quarter, creating different cohorts.
- Timeframe Selection: Decide on the evaluation timeframe. Are you looking at 12-month performance? 24-month performance? And so on.
- Performance Tracking: For each cohort, track performance metrics month-by-month to analyze how the cohort's performance changes over time.
- Visual Representation: Use line or bar graphs to visualize how each cohort's performance changes over time.
- You can also segment the cohorts by risk rating, interest rate range, loan type, term type and in aggregate.
Required Data Points:
- Loan ID: Unique identifier for each loan.
- Origination Date: The date the loan was originated. If you can get this as its own column, it will make the cohort analysis much easier. Meaning, each row of repayment / event will include the original origination date for a given Loan ID. If you have 100s of thousands of rows, this will save you.
- Original Loan Amount: The amount of money borrowed initially.
- Current Balance: The outstanding balance on the loan.
- Payment History: Record of each payment, showing amounts and dates.
- Default Status: Indicator if a loan has defaulted.
- Recovery Amounts: Any amounts recovered on defaulted loans.
- Prepayment Status: Indicator if a loan has been prepaid.
- Delinquency Status: How many days a loan is late, if any.
- Loan Term: The original term or duration of the loan.
Modeling in Excel:
- Raw Data Sheet: A tab containing all raw loan data.
- Cohort Summary Sheet: Group loans by origination date (using pivot tables or grouping functions) and calculate monthly metrics for each cohort.
- Graphs/Charts: Use line or bar charts to visualize each cohort's performance over time.
- Conditional Formatting: This can help highlight loans or cohorts that are underperforming relative to benchmarks or historical averages.
- Filters & Slicers: Allow for easy data manipulation and to dive deeper into specific cohorts or loan categories.
- Scenario Analysis Tools: Using tools like data tables or the "What-If Analysis" functions can help you explore various scenarios and their potential impact on loan performance.
Remember, the exact metrics and data points you need might vary depending on the specific loan type (e.g., mortgages vs. personal loans) and the goals of your analysis. But the steps and general principles above provide a solid foundation for most loan tape analyses.
