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 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 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.