Consumer Loan Model Template

Consumer Loan Model Template

The Consumer Loan model breaks down the financing structure of a bundle of consumer loans absorbed by a lender. This calculates Lender cash flows before and after Senior Debt is paid.

In the top left of the Consumer Loan Model Template in the gray area, there is the assumptions section that drives the model. Information can be entered in this section, including Default Rate and Default Recovery, Prepayment Rate, Advance on Senior Loan and Senior Loan Rate. There are also calculations for IRR before and after Senior loan debt is paid. The first table to the left breaks down the amortization of the collection of loans with principal paid, interest paid and number of defaults based on the assumption. This calculates the total collected by the lender, including any portion of defaults that are recovered.

This flows into the next table of the Consumer Loan Model Template where the lender cash flows are calculated. These breakdowns the total collections into interest and principal repaid to the lender. This provides the Lender cash flows for each payment period that is used to calculate the IRR of the consumer loan. This now flows into the last table where cash flow is calculated after any senior debt, if present. Since senior debt takes priority, this will reduce cash flow to the lender in this situation. This table is used to calculate the IRR after Senior Debt.

All cells in blue font are input cells where custom information can be entered. All cells in black font are formulas set to streamline the model.