If you are looking to start a lending business or want to participate as a lender on a p2p lending platform (like Lending Club), this model will help the user figure out capital requirements over time (10 year period) as well as every financial aspect when scaling out up to three loan types to any loan count.
$75.00 USD
- A second version is included that assumes minimum equity is invested up front and that runs off the minimum cash position over the life of the forecast.
- Adjusted the general flow of the model so it is easier to see output reporting and match up the financial statements / distributions.
- Added a module (yes/no selector) that gives the user the option to use a rolling credit facility to fund some portion of loan disbursements (define the percentage). The APR (interest rate) on the credit facility can be defined directly on the monthly detail and you can adjust it in each period or as needed.
- Added an input percentage to define the amount of origination fee / interest revenue that goes towards originating new loans (recycling earnings) vs. how much is used for working capital.
- Adjusted the balance sheet and cash flow statement 'equity line item' so that it flows in as needed instead of all at year 0 based on minimum cash position. This shows a more accurate reflection for the timing of cash flows and gives a better IRR calculation.
- Note, if your financial plan is to fund 100% of the loans with equity then you can select 'No' on the option to use a credit facility.
- I've had questions about being able to have terms that are less than 1 year. The answer is that is completely fine and the model can handle it. Just put a decimal in for the term. For example, if you have 6-month term loans, put 0.5 in for the term, or if you have 9-month term loans, put in 0.75. You can do this for any term (n/12) and it works for all loan types.
- If you want to manually define the loans added per month instead of using the loan scaling assumptions on the 'Loan Configuration' tab, that is fine and simply input the loans added per month on the 'Monthly Detail' tab rows 9, 15, and 21. All the other assumptions about the loans will flow accordingly and this doesn't break anything.
Latest Template Version Updates
More About the Upgrades:
The model is driven by three types of loans (all assumptions are configurable in each of the 10 years except start month/starting loan count) and the user will enter the following for each loan type:
- Interest Only Period following by a Principal + Interest repayment plan.
- Start Month
- Starting Loan Count
- Loans added per Month
- Interest Only Period (months)
- Weighted Average Interest Rate (interest only period)
- Weighted Average Interest Rate (principal + interest)
- Term of principal + interest portion
- Weighted Average Loan Amount
- Loan Origination Fees (defined as a percentage of loan originations)
- Interest Only
- Start Month
- Starting Loan Count
- Loans Added per Month
- Weighted Average Interest Rate
- Term (year and this can be less than 1 year as well by using a decimal)
- Weighted Average Loan Amount
- Loan Origination Fees
- Principal + Interest
- Start Month
- Starting Loan Count
- Loans Added per Month
- Weighted Average Interest Rate
- Term (years)
- Weighted Average Loan Amount
- Loan Origination Fees
A single default rate is applied across all loans and will reduce the amount of principal collected.
The most difficult part of this model was trying to get the proper principal and interest calculations to work within the right month within the context of a 10-year continuous monthly timeline. To get this to work, the logic that was required is somewhat similar to a SaaS model where there are monthly cohorts.
The difference is that the user is 'done' or 'churned' when they have finished repaying the principal balance. The model also needs to account for the fact that on the first loan type, when you have an interest only period followed by a principal and interest repayment schedule, there needs to be an interest calculation per month and then that has to end at the end of the defined assumption for interest only period based on the year of origination.
Also, it then needs to use the payment function in Excel to figure out the right principal and total payment amount when that period is over. The aggregate loan balance is based on loans originated that month multiplied by the average loan amount.
To accomplish the above, it took a lot of tabs with over 800 rows of complex logic, but it was possible and is done correctly and without error. The benefit is that you can now see what it looks like to scale out a lending business over time with any loan count and there is no need to define a bunch of different amortization schedules for every single loan. This scale is very interesting logic and requires some fairly complex structuring, but it does work beautifully.
Beyond the normal monthly/annual detail summaries, I added complete and connected monthly and annual 3-statement financial model that integrates all the main aspects of such a business. The valuation is based on a multiple of the total outstanding loans receivable at the exit month, which will vary per the assumptions.
There is also a standard funding source area on the 'Control' tab that lets the user define start year, debt funding, investor funding, and owner funding if applicable as well as assumptions for how much of the revenues to recycle into new loans vs. use to pay operating expenses / interest expenses. The equity requirement is defined by the biggest negative cumulative cash flow figure. Any net income values are used to fund lending activities and no cash is distributed until the net cash flow from operations is greater than the monthly amount lent out vs. amount collected.
I put in a lot of visuals that focus on overall financial performance and cash flow as well as added ways to see the activity of each loan type over 10 years and the cash flow of each respectively. It is quite interesting to see their shape/values change as the assumptions are changed.
The startup costs and operating expenses work as per usual with a start month for each line item and the monthly cost is defined in each year for each line item. There are two costs that scale directly with loan counts. This includes sales reps and customer service reps and they populate based on total existing loans and total loans settled per month. This is configurable for each loan type and there are up to two sales rep and customer service types for each loan type. The user defines a ratio for how many are needed relative to loan counts and loan originations per month.
A similar, but much more simplified version of this that I have done in the past is the actual operation of a P2P lending platform (that connects lenders and borrowers). That is a bit different than what is going on here though.
General Notes:
- If you want to manually define the credit facility draw each month, you can do that in row 298 on the 'Monthly Detail' tab. You can use row 301 to manually define repayments, otherwise if you select to include an exit value, the repayment will happen on the forecast end month selected.
- Row 299 on the 'Monthly Detail' tab is where you define the annual interest rate if you are using a rolling credit facility to fund loans disbursements.
More Lending / Loan Business Models:
- Loan Tape Analyzer
- Flat Fee Lending Business
- LaaS (Lending-as-a-Service Platform (facilitating interest-only loans for a fee)
- Seller Financing Amortization Template
- P2P Lending Participant
