The Consumer Loan model Template helps breaks down the financing structure of a bundle of consumer loans absorbed by a lender. This template allows the calculation of the Lender cash flows before and after Senior Debt is paid.
In the top left of the model 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 the principal paid, interest paid, and amount 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 where the lender cash flows are calculated. This 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.
