Lending Business: DCF Analysis and 3-Statement Model
Recent upgrades: Added a rolling credit facility option, option to define how much interest income/fee revenue is recycled into new originations vs. saved for working capital, and updated equity line items in balance sheet to come in as needed based on negative cash flows per month instead of minimum equity required all up front.
Video Tutorial:
This is a 10-year financial model that was built to help an organization or individual plan out the cash requirements and expected returns of lending out money over time. This Excel template has done a very good job at creating a scalable amortization table for up to 3 loan types. It has always been a struggle to model amortization schedules (interest vs. principal) when you need to account for multiple loan originations per month and varying loan types. Making this dynamic and configurable is one of the most useful pieces of logic I have ever done.
Uses cases include a lending business like a bank and/or a participant within a P2P lender (someone who plans to give loans out on a platform like Lending Club or what have you).
You can see the scale of:
Principal and Interest-only loans
Interest only period followed by Principal and Interest
Interest Only for entire term
The results are pretty cool. The user can configure up to 3 loan types and for each type, the user defines the following:
Start Month
Starting Loan Count
Loans Added per Month (adjustable in each year)
Interest Only Period (months)
Weighted Avg. Interest Rate (Interest Only)
Weighted Avg. Interest Rate (p+i)
Term (years) - p+i
Weighted Avg. Loan Amount
Loan Origination Fees
The P+i and Interest only configurations are similar but the p+i only doesn’t have an interest only period input.
This was setup like a startup operating model. That means assumptions were built around operating expenses (but they can be zeroed out if not necessary) as well as potentially financing the operation with senior debt and/or investors. Any remaining equity requirement that is remaining automatically flows to owner equity.
You also get a 3-statement model that runs off of all the assumptions. An exit multiple also exists as the terminal value of such operations often are based on the value of loans receivable on the termination month. This is all accounted for in an Income Statement, Balance Sheet, and Cash Flow statement as well as an Executive Summary, DCF Analysis, and monthly/annual pro forma details. Plenty of visualizations were put in as well.
If this is a bank, there was scaling logic built to determine the number of sales reps and customer service reps based on ratio logic of new originations that are happening per month.
