Lending Business / P2P Lender Participant Financial Model: 10 Year with Scalable Loan Counts

If you are looking to start a lending business or want to participate on a p2p lending platform as a lender (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.

Pricing

The template will be sent to you after purchase via your PayPal/purchasing e-mail. This is part of the Accounting Template Bundle, Lending Bundle as well as the Industry-Specific Template Bundle and a special bundle for just lending models (see at bottom of page).

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:

  1. Interest Only Period following by a Principal + Interest repayment plan.
    1. Start Month
    2. Starting Loan Count
    3. Loans added per Month
    4. Interest Only Period (months)
    5. Weighted Average Interest Rate (interest only period)
    6. Weighted Average Interest Rate (principal + interest)
    7. Term of principal + interest portion
    8. Weighted Average Loan Amount
    9. Loan Origination Fees (defined as a percentage of loan originations)
  2. Interest Only
    1. Start Month
    2. Starting Loan Count
    3. Loans Added per Month
    4. Weighted Average Interest Rate
    5. Term (year and this can be less than 1 year as well by using a decimal)
    6. Weighted Average Loan Amount
    7. Loan Origination Fees
  3. Principal + Interest
    1. Start Month
    2. Starting Loan Count
    3. Loans Added per Month
    4. Weighted Average Interest Rate
    5. Term (years)
    6. Weighted Average Loan Amount
    7. 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 a fully 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. 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.

Lending Model Bundle $320 Value:
Pricing


The making of this financial model:
Part 1:
Part 2:
Part 3:
Part 4:
Part 5:


Screenshots of the model: