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 and 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 following 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 will 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 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.