Loan Securitization Model: A Template for Multi-Tranche Structures, Coverage Metrics, and Sensitivity Analysis

Annualized or periodic default assumptions

Choice of proportional principal payment or Excel PPMT-based repayment

Senior, Mezzanine, and Junior notes

Equity tranche receives residual cash flows

Supports time horizons up to 240 months

Overcollateralization coverage (loan pool balance vs. note balance)

Interest coverage (portfolio interest vs. note interest)

Automatic red-flag highlighting when thresholds are breached

Shows IRRs and equity multiples for each note tranche and the equity piece

User-defined distribution frequency (monthly, quarterly, or annually)

Graphs for loan portfolio balance, cash flows, and coverage metrics

Weighted average interest rate (pool-level yield)

Weighted average remaining term (WAM)

Total remaining loan balance

Prepayment defined via annual percentage (converted to monthly)

Defaults defined as annual rates or total annual pool default percentages

Recoveries determined by a percentage and a recovery lag in months

Fixed periodic amount or percentage of the initial principal balance

Define up to three tranches (Senior, Mezz, Junior) with fixed or floating rates

LIBOR curve entry for floating-rate notes

Option for unpaid interest to compound

Waterfall prioritizes principal repayment: Senior → Mezz → Junior → Equity

Collateral interest nets out servicing fees, then pays note interest; excess goes to equity

Waterfall tab with overcollateralization coverage and interest coverage by period

Highlights periods in red if coverage thresholds are not met

Built-in 3×3 matrix to see how default rate and weighted average interest rate changes affect equity IRR

Can be removed to improve model speed if desired

Time-series graphs of portfolio balance and note amortization

Coverage metric charts (overcollateralization and interest coverage)

Return summaries (IRRs and equity multiples) for each tranche and equity

Offers a powerful, user-friendly platform for modeling loan securitizations without loan-level detail

Enables quick scenario testing and granular coverage analysis for professional securitization assessments