This is a financial analysis tool that lets the user enter various inputs and assumptions in regards to loan securitization. In general, this means a bank offloads many loans in a security offering that is run by a special purpose vehicle (separate from the bank). Investors are offered notes with various terms (coupon rates / rules) that are used to pay for the initial loan collateral. The performance of the loans (collateral) drives note repayment.
$75.00 USD
After purchase, the model will be immediately available to download. It is also included in the Lending Business bundle, the Industry-specific bundle, Accounting bundle, and the Super Smart Bundle.
Template Features:
- Optionality for how defaults work and how scheduled principal works.
- Up to 3 loan offerings (senior, mezzanine, junior)
- Includes an equity tranche for remaining cash flows.
- Model up to 240 months.
- Overcollateralization coverage and interest coverage metrics per period.
- Output shows final IRR and equity multiple for each loan tranche and the equity leg.
- Define if investor distributions happen monthly, quarterly, or annually.
- Visualizations for the loan portfolio balance over time / investor cash flows / coverage metrics.
Primary Inputs and Assumptions
This model was built to model as closely as possible the performance of an underlying loan pool without having to input any individual loans. Instead, the model looks at the attributes of the pool of loans in order to estimate interest, prepayments, defaults, recoveries, and scheduled principal payments. This methodology gets you closer to actual cash flows if the underlying loans have widely varying interest rates, remaining months left, and default expectations / prepayment expectations.
Loan Pool Inputs (performing collateral)
- Weighted average interest rate.
- Weighted average remaining term (WAM)
- Total remaining balance.
- Amortization Profile:
- Option 1 - Simple average % of beginning balance = scheduled principal payment.
- Option 2 - Use 'WAM', interest rate, and the PPMT Excel function to define this payment.
- Prepayment is a defined annual percentage that is converted into a monthly compounded rate.
- Defaults:
- Option 1 - Define an annualized default rate that is converted into a monthly compounded rate and multiplied by the beginning monthly principal balance.
- Option 2 - Define the total % of the initial loan pool balance that will default per year. This is converted into a periodic rate per year.
- Recovery is based on a defined % of total defaulted amounts that get recovered and a defined number of months it takes to recover that amount. This flows as cash available to distribute to principal balances for each loan tranche.
- Define servicing fees as a fixed periodic amount or a % of initial principal balance.
Security Offering Inputs (Notes)
- Define terms for up to three tranches of notes (Senior, Mezz., Junior)
- Terms include fixed or floating rate and if floating then the LIBOR + rate.
- Define a LIBOR curve.
- Option to compound / accrue unpaid interest.
- By default, 100% of principal cash flows go to the senior note first. When that note is fully repaid, the mezzanine note principal balance starts to get repaid, and so on to the junior note.
- Interest (collateral interest less servicing fees) is paid to all notes each period as available.
- Any remaining available cash flow (principal + interest) that is left after paying the note obligations each period will flow to a final 'equity' tranche.
I included a calculation to show the estimated remaining loan balances each period compared to the remaining loan balances of the notes used in the security offering (called overcollateralization coverage) as well as the total interest produced by the loan portfolio compared to the interest due to the notes (interest coverage). If it falls below a defined threshold, the period will highlight in red under those two calculated columns on the 'waterfall' tab.
Sensitivity Table
In order to easily see how the loan portfolio performs with varying changes to key variables, I put in a sensitivity table that shows the final equity IRR if 3 different default rates and 3 different weighted average interest rates. This does slow the model down a bit so if you don't want to see this, just remove the entire table by highlighting it and right clicking > selecting 'clear contents'.
You also get plenty of visualizations and final return summaries for IRR / Equity Multiple on each tranche and the equity leg.
Similar Financial Models
