Financial Model, Financial Services, Loans & Lending Businesses, Microfinance, Peer to Peer Lending (P2P)

10-year Financial Projections, 5-year Financial Projections, Accounting, Amortization, Cash Flow Analysis, Cash Flow Projections, Dashboard, Database, DCF Model, Debt Amortization, Economic Indicators, Excel, financial, Financial Analysis, Financial Debt, Financial Due Diligence, Financial Planning, Financial Projections, Financial Ratios, Forecast, Forecasting, Hedge Fund, KPIs (Key Performance Indicators), Loans, Mortgage, NPV (Net Present Value), Private Equity, Stress Test

Video Overview:

Here I’ve provided a database structure that any loan portfolio can be dropped into. The output reports are displayed in two views. One is a monthly look at the loan portfolio performance over 5 years (if you want to show more months, simply drag the last column over as far as you want). The other view is a current dashboard snapshot.

The input columns are simple and include things like:

• LoanID
• Risk Rating
• Loan Type
• Interest Rate
• Loan Amount
• Term (in months)
• Remaining Term (in months)
• Remaining Balance
• Paid Off Date
• Default Date

The monthly view and visualization output metrics include:

• Total Loans Originated per Month (and by risk rating)
• Total Amount of Loans Originated per Month (and by risk rating)
• Count of Defaults per Month (and by risk rating)
• Weighted Average Interest Rate of Loans Settled per Month
• Weighted Average Interest Rate of Ongoing Portfolio (and by risk rating)
• Default Rate by Risk Rating
• Amount of Principal Defaulted per Month (and by risk rating)
• Ongoing Comparison of Total Originated vs Total Defaulted and the Percentage
• Cumulative Amount Originated (and by risk rating)

The above metrics are also shown as a ‘total per date’ view on a dashboard and that is also broken down by risk rating. By using this loan portfolio analysis tool, it gives internal and external stakeholders a good idea of what a given lending business has done over time based on their actual settled loans. It shows what kind of risks were taken and the level of originations that had been completed at a given risk rating. This is also helpful in order to see the average interest rate of the entire portfolio over time.

Expected Future Principal and Interest Collected

An additional feature I built out was the automated future principal and interest that is expected to be collected of all existing loans. This is driven off the remaining term (in months), interest rate, original loan amount, and initial loan term. The output report shows a monthly and annual total of both principal and interest the loans will be expected to produce. Defaulted loans and paid off loans are not counted as this is supposed to be an analysis of the future performance as of all current statuses. This also has a discounted cash flow analysis attached to it so the user can figure out the present value of all future payments expected.

I think one of the most useful metrics is the ongoing total of originations vs defaults. This gives the user a true idea of the average expected chance that a given amount of principal will be repaid or not when looking at all historical loan data.

Over the years, I’ve built 100s of financial models, tools, and trackers. You can access all of them in one large bundle here: https://www.efinancialmodels.com/downloads/all-my-financial-models-spreadsheets-templates-and-tools-120-421052/