*This post is a part of a series of posts we have written. To start from the beginning, click [HERE].
Modeling asset-backed credit investments is different from most other financial modeling exercises.
The reason is that rather than underwriting growth assumptions and EBITDA multiples, you are trying to figure out what percentage of the underlying assets can default before the deal suffers a loss of income and a loss of principal.
Credit investors are so focused on the “downside” because unlike in an equity investment, the upside is capped — i.e., the best you can do is earn your interest rate. (Sure, some debt facilities come with penny warrants in the borrower’s business, but this upside is often excluded from the underwriting.)
One of the most common assets within an asset-backed lending (ABL) facility are loans. Let’s assume we are putting together an ABL facility backed by unsecured consumer loans. In order to model out the investment we are going to need the following inputs, split out by asset-level assumptions and facility-level assumptions:
Asset-level assumptions:
- Interest rate or fee charged as a percentage of initial capital outlay to the borrower
- Underwritten duration
- Payment frequency (daily, monthly, etc.)
- Payment type (interest-only, principal and interest, etc.)
- Prepayments and refinancings
- Default rate (we like to use constant default rate or “CDR” which we will describe in a moment)
- Facility-level assumptions:
- Interest rate
- Advance rate (percentage of the asset value that the lender will lend against)
- Payment type (interest-only, fully amortizing, tranched payments, etc.)
We have built out a [sample model] for illustrative purposes to help describe a hypothetical credit facility secured by consumer loans. In the model, we are trying to identify the default rate at which we would lose our income or our principal on the facility.
The underlying loans have the following characteristics (see the “Assumptions” tab):
- Duration:
- 2-year loans (assumptions below)
- 80% of the loans are 2 years in duration
- 15% of the loans will prepay at month 6
- 35% of the loans will fully amortize over the 2 year underwritten term
- 50% of the loans will be refinanced into new 2 year loans in month 6, effectively extending duration to 2.5 years
- 5-month loans (assumptions below)
- 20% of the loans are 5 months in duration
- 100% of the loans will fully amortize per the underwritten term
Interest Rate:
- 2-year loans: 25%
- 5-month loans: 20%
Payment Frequency:
- Monthly payments, fully amortizing
Default Rate:
- 2-year loans: 7.50% CDR (meaning that 7.50% of the loans default per year)
- 5-month loans: 3.75% CDR (meaning that 3.75% of the loans default per year)
With all of the assumptions in hand, we can begin to build out the projected cash flows associated with our portfolio. We have built out different tabs for the different types of repayment schedules and then consolidated them into one tab (“Complete Schedule”).
Within each tab we are able to create an expected payment schedule using the PMT function in Google Sheet or Excel. We can then layer in our CDR assumptions to arrive at a performing loan balance and the interest earned in a given period (remember that the defaulting loans are not earning interest).
We chose to use CDR in this model because we do not have historical default data to base our default curves on. If we had specific data that were driving our assumptions for defaults, we could create default curves to mimic historical trends. For example, there could be a 5% first-period payment default and then de minimis defaults thereafter, and we would incorporate defaults into the model accordingly. Having such information would make the model more precise, but without that information, CDR is a fairly reliable way of accounting for defaulting loans.
The refinanced loans have a special payment in month 6, when we are assuming the refinancing will occur (this can be seen cell E9 of the “2Y — Refinance Schedule” tab). Essentially, all of the principal returned in the first 5 periods are lent back out, net of the payments received in period 6. For the prepaid loans, we assume that all remaining principal balance on the loans are prepaid in month 6 (see “2Y — Prepay Schedule”). Again, if we had historical data we could make the repayment schedule more precise, but for illustrative purposes we will use these assumptions.
After consolidating all of the cash flows into one master cash flow schedule, we can begin to analyze the lender’s IRR. In order to understand how the assets perform relative to the lender’s expectation, we now need to weave in the facility assumptions:
- Amount Lent: $15,000,000
- Facility Interest Rate: 15%
- Advance Rate (also known as Loan-to-Value): 95%
- Repayment: Fully amortizing with the exception of the refinanced assets
Using the facility assumptions we can build out the lender’s projected cash flow section in the model (see columns P — U in the “Complete Schedule” tab). All principal that is paid is used to amortize the loan and available interest from the loans is used to pay the facility interest.
You may notice the amount lent is less than the $15,000,000 facility size. This is a result of the 95% advance rate (also known as loan-to-value, or the “cents on the dollar” that the lender is lending against a given asset value), which gives the lender “subordination” — meaning that there is effectively a 5% equity contribution from the borrower (because it only received 95% of every $1 of collateral). More plainly put, the borrower is contributing money to the facility to absorb potential losses. In this case, the facility is lending $15M against ~$15.79M of receivables (e.g., $15M / 95%).
With the asset and lender cash flow schedules complete, we can now begin to run our default scenarios analysis. Typically we build in a CDR multiplier which allows us to stress test the CDR being used (see cell C20 in the “Assumptions” tab). We can then build a sensitivity analysis table (using “Data Table” in Excel — but unfortunately Google Sheets does not support his function, so it is hardcoded in our model) projecting the lender’s IRR at various multiples of the base case CDR (columns G — H of the “Assumptions” tab). The way to read the table is as follows: our income will not be impacted as long as the IRR of the lender’s cash flows is equal to the interest rate. Once it dips below the interest rate, our income is impacted; and once it dips below 0%, our principal is impacted.
The metric we use to understand the relative impact required to cross those loss thresholds is a loss coverage ratio. The loss coverage ratio is the multiple on CDR that we can sustain before losing income or principal. In this model, we can sustain 2.13x our base case default assumption before loss of income, and 4.16x our base case default assumption before loss of principal.
We also like to look at this number on absolute default terms. A 2.13x increase would be a CDR of ~14.6% and a 4.16x increase would be a CDR of ~28.5%. If there is historical data on the asset class, specifically within a certain borrower demographic, we can use it to see what the realistic possibility is that defaults reach that level. If available, 2007–2009 data is the best for stress-testing as it is the most recent example of macroeconomic distress.
Depending on the newness of the asset class, the availability of historical asset default rates and the overall correlation to broader financial markets, the loss coverage ratio we are comfortable with varies. Investment grade asset-backed securities typically have loss coverage ratios at 2x or above, which is typically the minimum we will have for losses before income is impacted.
*******
So much of content on investing is written about “making an investment.” But monitoring the investment/portfolio is nearly as important as doing a good deal.
Continue to this post to read about: [Monitoring a Deal Post-Investment].
For the Full Series of Posts, Please See Below:
(1) Part 1: An Intro to Online Lending (LINK)
(2) Part 2: An Intro on How To Source Deals [LINK]
(3) Part 3: Initial Diligence [LINK]
(4) Part 4: Deeper Diligence [LINK]
(5) Part 5: Structuring The Deal [LINK]
(6) Part 6: Building a Credit Model [LINK]
(7) Part 7: Monitoring Your Investment [LINK]
(8) Part 8: Conclusion [LINK]
