Financial Model, Financial Services, Loans & Lending Businesses

10-year Financial Projections, 5-year Financial Projections, Accounting, Accounts Receivable, Amortization, Budget, Budgeting, Business Valuation, Cash Flow Analysis, Cash Flow Projections, Cash on Cash Yield, Cash-on-Cash Multiple, CFO, Controlling, Dashboard, DCF Model, Debt Amortization, Debt Service Coverage, Discount Rate, Dividends, Excel, Financial Analysis, Financial Debt, Financial Feasibility, Financial Model, Financial Modeling, Financial Planning, Financial Projections, Financial Ratios, Financial Reporting, Financial Statements, Financing, Forecast, Forecasting, Income Statement, Investment Multiple, Investor Cash Flows, Investors, IRR (Internal Rate of Return), Know-How, Loans, Mortgage, NPV (Net Present Value), Pro-Forma, Profitability Analysis, Return on Investment (ROI), Revenue Projections, Scenario Analysis, Startup Financial Models, Terminal Value, Three Statement Model | Three Financial Statements, Valuation

Video Tutorial:

Update: Improved cash flow analysis reporting, the way contributions are defined, and deprecated dividend distributions logic as it was redundant.

Update: Added the option to use a credit facility to fund a percentage of the lending activity as well as a percentage that defines how much of the interest / fee revenue is recycled into new loans vs. how much is kept for working capital needs (to fund interest expenses / opex).

Flat out, this was one of the most complex financial models I have ever gotten into. The difficulty is scaling the origination of loans by month on the same 10-year timeline. Each set of loans added per month acts like its own cohort, which is a little bit like a SaaS model. The difference is that there is no churn, but rather loans get paid off eventually, depending on the terms. Also, the principal and interest need to be tracked, as well as if there is an interest-only period in the beginning.

Essentially, to build this accurately, I had to go with a bunch of different matrix tabs, which created a scalable amortization aggregation infrastructure. The final result is that you can plan out how many loans you think will be settled over time based on 3 loan-type configurations. Based on that, the model will tell you the monthly cash required for loan disbursements, principal collections, origination fees, and interest earned.

This model is also very useful for anyone that wants to participate in a p2p lender as a lending source. You can clear out all the OpEx assumptions and simply enter assumptions about the loan terms over time and see what kind of capital is required and the expected return as well as when you can start reinvesting profits by keeping the distributable cash flow close to 0.

The three loan types include:

1. Interest Only followed by principal and interest repayment amortization
2. Interest Only followed by full principal repayment at the end of the term
3. Standard Principal and interest repayment amortization

For each of those loan types, the user can define the month each one starts, the starting loan count, and then adjust the following loan terms/attributes across 10 years of time:

1. Loans Added per Month
2. Interest Only Period (months)
3. Weighted Avg. Interest Rate (Interest Only)
4. Weighted Avg. Interest Rate (p+i)
5. Term (years) – p+i
6. Weighted Avg. Loan Amount
7. Loan Origination Fees

The model assumes payments always start the month after origination.

The other aspect of this model is building a monthly and annual 3-statement model that plugs right into the resulting lending activity. The reason I did this was to see what a balance sheet may look like for such activity, as I have never balanced this type of transaction. The result was satisfying, and it turns out the valuation of such a lending business is simply based on a multiple of the net assets at exit (meaning the value of loans receivable less any debt owed in this case).

The user would get a full DCF analysis for the project as a whole and on an investor / owner-operator basis if funding sources included investors. Lots of visuals were included, and they make it really easy to see how changing various loan assumptions over time changes the overall financial picture over time.

There is also an input for the default rate, which will apply to principal collections.

If this is the lending branch of a bank or just a lending business in general that needs customer service reps (CS) and/or sales reps (SR), those OpEx costs are defined based on a ratio (customer service reps based on cumulative active loans and sales reps based on added loans per month). There are two types of CS and Salespeople for each loan type configuration. User will define their fully loaded salaries as well as improvement in how much a given CS/SR can handle.

Latest Version: