Collateral Pool Model
Credit Financial Model (Free Excel Download)
Analyze collateral pools using asset balances, eligibility, advance rates, defaults, recoveries, concentration limits, borrowing capacity, and lender loss scenarios.
professionals from Deloitte
Used by professionals from
About this model
This collateral pool model values a warehouse-financed consumer loan portfolio and measures equity returns after credit losses, funding costs, and operating expenses. It models a $140M pool of unsecured consumer loans funded by $98M of warehouse debt at 8.5% (SOFR plus 400 bps) and $42M of equity haircut. The pool generates interest income at a 20% weighted average coupon (WAC), prepayments at 15% annual CPR, and defaults at 7% CDR with 70% loss severity. After funding costs, servicing fees, and net credit losses, the model projects equity IRR, MOIC, and net yield metrics across a 7-year runoff period.
The model includes a pool schedule showing the principal balance roll-forward (opening, scheduled repayments, prepayments, defaults, closing); a credit performance sheet tracking monthly CDR, CPR, delinquency, and recoveries with a 12-month recovery lag; a funding schedule showing warehouse debt drawdown and repayment mechanics; operating cost sections for servicing fees, trustee fees, and G&A; and a three-section cash flow statement separating operating (interest and fees), investing (principal collections and pool acquisition), and financing activities (equity contribution and warehouse movements).
This model is used by alternative credit funds, specialty lenders, and fintech originators evaluating warehouse financing capacity, exit returns, and covenant headroom. It is particularly valuable for teams managing loan-backed securitisations or warehouse facilities where advance rate and OC ratio covenants drive borrowing capacity.
What every model includes
Live formulas, no hardcoded values
Outputs are driven by live formulas, so the workbook updates from its assumptions instead of relying on hardcoded results.
All assumptions in one tab
Inputs are clearly marked in the Assumptions tab and separated from calculations, making it clear what to change and what to leave intact.
Statements always balancing
For integrated-statement models, the balance sheet, cash flow, and supporting schedules tie through properly.
Distinct schedules for clarity
Debt, working capital, taxes, and cash flow can get messy quickly. We group calculations in clear schedules, not across disconnected tabs.
No hidden macros or external links
There are no unexplained external workbook links or macros to undermine auditability or portability.
Changes flow through the model
Update a key driver and see the impact carry through the forecast, financing, and return outputs. We never use hardcoded numbers in formulas.
What's inside the Collateral Pool Model
- Collateral asset inventory with current market values
- Haircut schedules by asset class and market regime
- LTV, coverage ratios, and excess collateral calculations
- Mark-to-market and stress scenario modeling
- Concentration limits and diversification tracking
- Customisable assumptions for your own case
Collateral Pool Model: How It Evaluates Warehouse-Funded Loan Pools
A collateral pool model projects the performance of a pool of consumer or specialty loans financed through a warehouse facility and equity. It tracks pool runoff, credit losses, funding costs, and cash flow waterfalls to estimate equity IRR and MOIC.
This page explains the documented operating drivers, calculation flow, and practical use of the template.
Pool Accounting and Roll-Forward Logic
Pool accounts start at an initial face value and decline each period through scheduled repayments, prepayments, and gross defaults.
- Prepayments apply to the post-default balance, while defaults are not a cash outflow because collections already exclude defaulted principal.
- Recoveries enter as a lagged inflow, with timing controlled by the recovery lag assumption.
- This roll-forward, supported by credit performance, revenue, funding, and operating cost sheets, provides the balances and cash collections that feed the rest of the model.
Credit, Funding, and Operating Cost Drivers
The main credit assumptions are the annual default rate, loss severity, recovery rate, recovery lag, delinquency rate, and annual prepayment rate. Funding costs depend on the warehouse balance, an interest rate, and commitment fees on unused capacity.
- Servicing fees, trustee fees, G&A, and a management fee are also charged against the pool. Under the v4 design, the collateral can be split into prime, near-prime, and sub-prime buckets, each with its own coupon, default, severity, and prepayment assumptions.
- A scenario toggle applies multipliers to default, severity, and prepayment drivers without changing the model structure.
How Cash Flows Build Toward Returns
Cash flow begins with operating inflows such as interest, late fees, prepayment fees, acquisition fees, and recoveries, less servicing, trustee, and G&A outflows. Investing shows the initial pool acquisition and subsequent principal collections.
- Financing covers the equity contribution, warehouse drawdown, repayments, interest, and commitment fees. Netting these sections leaves the equity cash flow stream.
- The model computes equity IRR and MOIC from the equity investor's perspective, and also tracks the OC ratio, leverage, and net yield. All calculations are non-circular.
Covenant Monitoring and Practical Application
Covenant checks validate the advance rate, OC ratio, and default-rate trigger across all periods, and a pool balance reconciliation confirms assets tie out.
- Warehouse balances are floored at zero and must be fully repaid by the final year.
- The template's natural run-off assumption and scenario toggle make it useful for testing how changes in pool mix, credit stress, and prepayment speeds affect coverage and residual equity returns.
- Readers should treat sample figures as illustrative and note that the public download is a values-only preview.
Formatted to IB standards
Named theme colors repaint the whole workbook in one click, on top of an investment-banking structure with clear input, output, and cross-sheet reference styling - brand-ready, institutional-grade, and fully auditable.
Created by ex-finance professionals
Hey, I’m Alex and I created Finamodel.
Over my years in the finance industry I kept building the same models over and over again. Same structure, same assumptions, different logo. So I started building frameworks to turn them into clean, reusable templates.
Every model here is one I’d actually use for a client, and I personally vet each one before it goes up.
I’m not an expert in every industry, but I’ve built enough models to know what belongs in one. And when something is completely foreign to me, I reach out to my network for experts to work on our models with us.
Having a template library on hand cuts a first build from hours to minutes.
Need help finding your model? You’ll find me in the Finamodel app!
Frequently asked
What is a collateral pool model?+
It is a model that tracks the market value, haircuts, and coverage ratios of assets pledged as collateral for a secured lending or securitization facility.
What haircuts should I use?+
Use regulatory haircuts for capital calculations and apply your internal haircuts for risk management. The model supports both simultaneously.
How often should I revalue collateral?+
Daily for liquid securities, weekly for loans, and monthly for real estate. Automating data ingestion reduces manual error and lag.
Can I stress test individual assets?+
Yes. Create stress scenarios by asset class, for example equities down 40 percent or bonds down 20 percent, and recalculate coverage ratios instantly.
Who uses collateral pool models?+
Lenders, risk officers, securitization teams, and collateral managers use them for covenant compliance, securitization structuring, and daily risk monitoring.
Have more financial modelling questions? Contact us
Related templates
Credit Portfolio CDO Model
Model credit default swap portfolio, tranching, and waterfall for CDO securitization.
Mortgage Portfolio Model
Mortgage loan portfolio analysis with prepayment speeds, default rates, and cash flow projections.
Loan Portfolio CDR Model
Credit default risk model for loan portfolios with cumulative default rates and loss severity analysis.
Auto Loan Portfolio Model
Automotive loan securitization and portfolio analysis.
