Appendix
Collateral analytics checklist
Collateral analytics checklist
This checklist covers every step of loan tape analysis, from initial data validation through performance metric calculation and red flag identification. Use it as your standard workflow when evaluating a new pool or running monthly surveillance on an existing facility.
The checklist is organized by workflow stage. Complete each section in order for new pools. For ongoing surveillance, you can skip directly to the performance and trend sections.
Pre-analysis setup
Before opening the file, confirm you have what you need.
- Data dictionary obtained — field definitions, code values (e.g., “CU” = current), date formats, null conventions
- Tape date confirmed — as-of date is within 30 days for diligence, 15 days for closing
- File format verified — CSV encoding (UTF-8), delimiter, header row present, no merged cells
- Eligibility criteria documented — which loans are intended for the facility vs. excluded
If the originator cannot provide a data dictionary, flag this as a data quality concern before proceeding.
Data quality checks
Field completeness
Calculate the percentage of populated fields for each column.
| Field Category | Fields | Maximum Missing |
|---|---|---|
| Identity | Loan ID, Borrower ID | 0% |
| Balance | Original balance, Current balance | 0% |
| Terms | Interest rate, Original term, Origination date, Maturity date | <1% |
| Performance | Loan status, Days past due, Last payment date | <1% |
| Borrower | Credit score, Income (if underwritten) | <5% |
| Collateral | Property/asset details (if secured) | <5% |
- Critical fields have 0% missing — Loan ID, balances, rate, status
- Core fields have <2% missing — FICO, term, geography
- Supplementary fields have <5% missing — income, employment, collateral value
- Any field >5% missing documented — document the field, count, and root cause
Logical consistency
Run these validation checks on every tape.
- Maturity date > origination date — for all loans
- Current balance <= original balance — for amortizing loans (neg-am and fee capitalization are exceptions)
- Remaining term <= original term — for all loans
- Days past due consistent with status code — a loan coded “Current” should have 0 DPD
- Interest rate within product bounds — typically 0.01-36% for consumer, 4-25% for commercial
- No negative values where impossible — balances, rates, terms
- Origination date not in the future — relative to tape date
- No charge-off date before origination date
Format validation
- Dates in consistent format — YYYY-MM-DD preferred; document if different
- Numerics clean — no $, %, or commas in numeric fields
- State codes standardized — 2-letter abbreviations only
- Boolean fields consistent — single convention (Y/N, 1/0, True/False), not mixed
- Loan status codes match data dictionary — no undocumented values
Duplicate detection
- No duplicate loan IDs — should be impossible; indicates data error
- No exact duplicate rows — check for accidental row duplication
- No suspicious borrower duplicates — same borrower with nearly identical loans (possible entry error)
Exception documentation
Document all findings before proceeding.
Exception Report: [Pool Name] ([Tape Date])
============================================
Critical Issues (must resolve before analysis):
- [X] loans ([X]% of pool) missing [field]
- [X] loans with [impossible value]
- [X] duplicate loan IDs
Warnings (review with originator):
- [X]% of loans show [anomaly]
- [X] loans with [unusual but possible value]
Data Quality Assessment: [ACCEPTABLE / MODERATE / POOR]
Recommendation: [Proceed / Request corrections / Decline]First five checks (30-minute screen)
These checks take 30 minutes and tell you whether the tape is worth deeper analysis.
1. Data completeness summary
- Critical field check passed — 0% missing on identity, balance, rate, status
- Overall data quality acceptable — no single required field >5% missing
2. Eligibility screening
Apply the facility eligibility criteria and count passing loans.
| Criterion | Pass Count | Pass Rate |
|---|---|---|
| Current balance >$0 | ||
| Remaining term >0 | ||
| FICO >= [minimum] | ||
| Not 60+ DPD | ||
| Originated within [X] months | ||
| Total Eligible |
Illustrative pricing. See pricing disclaimer.
- Eligibility pass rate documented — if <80%, clarify with originator which loans are intended for the facility
- Ineligible loans identified — document reasons for ineligibility
3. Balance distribution
| Metric | Value |
|---|---|
| Total UPB | |
| Loan count | |
| Average balance | |
| Median balance | |
| Min balance | |
| Max balance | |
| Top 10 loans % of pool |
- Skew assessed — large gap between average and median indicates concentration in large loans
- Concentration noted — if top 10 >15%, document for concentration analysis
4. Delinquency snapshot
| DPD Bucket | Loan Count | UPB | % of Pool |
|---|---|---|---|
| Current (0) | |||
| 1-29 DPD | |||
| 30-59 DPD | |||
| 60-89 DPD | |||
| 90+ DPD | |||
| Total |
- 30+ DPD rate calculated — this is your headline delinquency number
- Benchmarked against asset class — prime consumer 2-4%, subprime 6-10%, prime auto 1-3%
- Anomalies flagged — DQ rate significantly above or below benchmark
5. Age distribution
| Origination Period | Loan Count | UPB | % of Pool | Avg Seasoning (mo) |
|---|---|---|---|---|
| [Current year] | ||||
| [Prior year] | ||||
| [2 years ago] | ||||
| [3+ years ago] |
- Weighted average seasoning calculated — WAL in months
- Vintage concentration assessed — >30% in last 3 months is a yellow flag for unproven loans
- Seasoning documented — affects loss curve projection
Stratification analyses
Run these strats on every pool. Add asset-class-specific strats as applicable.
Balance bands
| Current Balance | Count | UPB ($M) | % Pool | Avg Rate |
|---|---|---|---|---|
| $0-10,000 | ||||
| $10,001-25,000 | ||||
| $25,001-50,000 | ||||
| $50,001+ | ||||
| Total |
Illustrative pricing. See pricing disclaimer.
- Distribution documented — note any concentration in single band
- Rate-balance correlation noted — larger loans typically have lower rates
Rate bands (WAC analysis)
| Rate Band | Count | UPB ($M) | % Pool | Avg FICO |
|---|---|---|---|---|
| 6-8% | ||||
| 8-10% | ||||
| 10-12% | ||||
| 12%+ | ||||
| Total/WAC |
- Weighted average coupon (WAC) calculated
- Rate-credit correlation verified — higher rates should correlate with lower FICO
Credit score distribution
| FICO Band | Count | UPB ($M) | % Pool | Avg Rate |
|---|---|---|---|---|
| 750+ | ||||
| 700-749 | ||||
| 660-699 | ||||
| 620-659 | ||||
| <620 | ||||
| Total/WA FICO |
Illustrative pricing. See pricing disclaimer.
- Weighted average FICO calculated
- Credit tier identified — prime (700+), near-prime (660-699), subprime (<660)
- Credit-rate alignment confirmed — higher risk = higher rate
Geographic distribution
| State | Count | UPB ($M) | % Pool |
|---|---|---|---|
| [Top 1] | |||
| [Top 2] | |||
| [Top 3] | |||
| [Top 4] | |||
| Other | |||
| Total |
- Single-state concentration calculated — typical limit 15-25%
- Excess concentration documented — if any state >20%, note for haircut calculation
Asset-class-specific strats
Auto loans:
- New/used/CPO distribution
- LTV bands
- Vehicle type (sedan, truck, SUV)
- Vehicle age at origination
Consumer unsecured:
- Loan purpose distribution
- Income verification method
- DTI bands
- Employment status
Commercial/SMB:
- NAICS code distribution (top 5 industries)
- Years in business bands
- Revenue bands
- Collateral type (if secured)
Mortgage:
- Property type (SFR, condo, multi-family)
- Occupancy (primary, second home, investment)
- Documentation type (full doc, bank statement, DSCR)
- LTV bands
Performance metrics calculation
CDR (constant default rate)
Monthly Default Rate (MDR) = Defaults This Month / Beginning UPB
CDR = 1 - (1 - MDR)^12
| Period | Beginning UPB | Defaults | MDR | CDR (annualized) |
|---|---|---|---|---|
| [Month 1] | ||||
| [Month 2] | ||||
| [Month 3] | ||||
| Average |
- CDR calculated for last 3-6 months
- Trend assessed — stable, improving, deteriorating
- Benchmarked — see asset class benchmarks below
CPR (constant prepayment rate)
Single Monthly Mortality (SMM) = Prepayments / (Beginning UPB - Defaults)
CPR = 1 - (1 - SMM)^12
| Period | Beginning UPB | Prepayments | SMM | CPR (annualized) |
|---|---|---|---|---|
| [Month 1] | ||||
| [Month 2] | ||||
| [Month 3] | ||||
| Average |
- CPR calculated for last 3-6 months
- Impact on WAL assessed — high CPR shortens duration
CNL (cumulative net loss)
CNL = (Cumulative Gross Losses - Cumulative Recoveries) / Original Pool Balance
| Vintage | Original Balance | Cumulative Gross Loss | Cumulative Recovery | Net Loss | CNL |
|---|---|---|---|---|---|
| [Cohort 1] | |||||
| [Cohort 2] | |||||
| [Cohort 3] |
- CNL calculated by vintage
- Terminal loss projected — using loss curve shape from mature vintages
Loss severity
Severity = (Principal at Default + Accrued Interest + Expenses - Recoveries) / Principal at Default
| Period | Defaults | Gross Claim | Recoveries | Net Loss | Severity |
|---|---|---|---|---|---|
| [Period 1] | |||||
| [Period 2] | |||||
| [Period 3] | |||||
| Average |
- Severity calculated — note if recovery data is available
- Compared to benchmark — severity can exceed 100% with accrued interest
Static pool and vintage analysis
Cohort construction
- Cohorts defined — by origination month or quarter
- Minimum cohort size verified — cohorts with <$5M or <200 loans may be unreliable
- Cohort tracking established — monthly or quarterly updates
Cumulative loss curves
| Months on Book | Q1 20XX CNL | Q2 20XX CNL | Q3 20XX CNL | Q4 20XX CNL |
|---|---|---|---|---|
| 6 | ||||
| 12 | ||||
| 18 | ||||
| 24 | ||||
| 30 | ||||
| 36 |
- Loss curves plotted by vintage
- Curve shape assessed — front-loaded (consumer) vs. back-loaded (mortgage)
- Terminal loss identified — where mature curves flatten
Vintage comparison
- Same-age comparison completed — compare vintages at equal months on book
- Trend identified — improving, stable, or deteriorating vintages
- Root cause documented — if deterioration, identify potential causes (credit loosening, macro, channel)
Seasoning adjustment
For newer vintages, project terminal loss:
Projected Terminal CNL = Current CNL / (% of terminal loss reached at current age)
- Seasoning factor applied — using loss timing from mature vintages
- Projection documented — with assumptions stated
Roll rate analysis
Transition matrix
Build a month-over-month state transition matrix.
| From \ To | Current | 30 DPD | 60 DPD | 90 DPD | Default | Paid Off |
|---|---|---|---|---|---|---|
| Current | ||||||
| 30 DPD | ||||||
| 60 DPD | ||||||
| 90 DPD |
- Transition matrix built — using 3-6 months of data
- Cure rates calculated — % returning to current from each DPD bucket
- Roll rates calculated — % advancing to next DPD bucket
Default projection
Use roll rates to project future defaults from current delinquency.
| DPD Bucket | Current UPB | Roll Rate | Expected Next Bucket |
|---|---|---|---|
| 30 DPD | x | ||
| 60 DPD | x | ||
| 90 DPD | x | ||
| Expected Defaults |
Illustrative pricing. See pricing disclaimer.
- Forward default projection completed
- Pipeline quantified — $ of expected defaults from current DQ stack
Concentration analysis
Obligor concentration
| Metric | Value | Limit | Status |
|---|---|---|---|
| Top 1 obligor % | 1-2% | ||
| Top 10 obligors % | 10-15% |
- Single obligor concentration checked — flag if any >2%
- Top 10 concentration documented
Geographic concentration
| State | % of Pool | Limit | Excess |
|---|---|---|---|
| [Top 1] | 20% | ||
| [Top 2] | 20% | ||
| [Top 3] | 20% |
- State concentration calculated
- Excess concentration quantified — dollars above limit
Vintage concentration
| Vintage Period | % of Pool | Limit | Status |
|---|---|---|---|
| [Most recent quarter] | 30% | ||
| [Most recent month] | 10% |
- Vintage concentration assessed
- Seasoning concerns flagged — high concentration in recent originations
HHI calculation
HHI = Sum of (each loan’s % of pool)^2 x 10,000
| Metric | Value | Interpretation |
|---|---|---|
| HHI | <100 = diversified, 100-1000 = moderate, >1000 = concentrated |
- HHI calculated — useful for comparing pools
Excess concentration haircut
For pools exceeding concentration limits:
| Concentration Type | Excess % | Excess $ | Haircut Rate | Haircut Amount |
|---|---|---|---|---|
| [State X] | 50% | |||
| [Obligor Y] | 100% | |||
| Total Haircut |
Illustrative pricing. See pricing disclaimer.
- Haircut impact calculated — reduction in borrowing base
Red flags checklist
Data quality red flags
- >5% missing on any critical field
- Systematic logical inconsistencies (>2% of loans)
- No data dictionary provided
- Tape >60 days stale for diligence
- Tape >30 days stale for closing
- Evidence of data imputation without disclosure
- Multiple data sources with conflicting values
Credit quality red flags
- Newer vintages performing worse than older at same age
- CDR >50% above benchmark for stated credit tier
- Rising roll rates (30-to-60, 60-to-90) over last 6 months
- Severity above asset class norms (unsecured approaching 100%+)
- 30+ DPD rate increasing month over month
- Credit-rate alignment breaks down (low FICO loans at low rates)
Concentration red flags
- Single state >25% of pool
- Single obligor >2% of pool
- Single vintage month >10% of pool
- Single vintage quarter >30% of pool
- HHI >1,000
- Single origination channel >80%
Operational red flags
- Payment history gaps or anomalies
- System migration artifacts affecting data continuity
- Inconsistent field definitions across vintages
- Servicer comments indicating collection issues
- High volume of loan modifications without documentation
Asset class benchmarks
Use these ranges to contextualize your findings.
Auto loans
| Metric | Prime | Subprime |
|---|---|---|
| CDR | 1-2% | 8-15% |
| CPR | 10-18% | 12-22% |
| Severity | 35-50% | 50-70% |
| 30+ DPD | 1-3% | 8-15% |
Consumer unsecured
| Metric | Prime | Near-Prime | Subprime |
|---|---|---|---|
| CDR | 3-5% | 6-10% | 10-18% |
| CPR | 15-25% | 20-35% | 25-40% |
| Severity | 80-95% | 85-100% | 90-100%+ |
| 30+ DPD | 2-4% | 4-7% | 6-12% |
Equipment finance
| Metric | Prime | Subprime |
|---|---|---|
| CDR | 1-3% | 4-8% |
| CPR | 5-12% | 8-15% |
| Severity (titled) | 40-60% | 55-75% |
| Severity (soft) | 70-90% | 80-100% |
Commercial/SMB
| Metric | Secured | Unsecured |
|---|---|---|
| CDR | 2-5% | 4-10% |
| Severity | 40-70% | 75-100% |
These ranges are indicative. Actual performance varies by originator, underwriting criteria, economic conditions, and product specifics.
Capital provider diligence alignment
What capital providers request
Initial screening (provided upfront):
- Current loan tape
- Data dictionary
- Basic strat tables
- Summary of eligibility criteria
Full diligence (provided after term sheet):
- 24-36 months of monthly tapes
- Static pool data by vintage (CNL, DQ, prepay curves)
- Roll rate history (6-12 months)
- Recovery data by vintage
- Servicing policies and procedures
- Sample loan files (typically 25-50 loans)
IC presentation package
Structure your findings for investment committee consumption.
Executive summary (1 page):
- Pool size, loan count, WAC, WA FICO, WAL
- Current 30+ DQ rate with benchmark comparison
- Key findings (3-5 bullets)
- Red flags identified
- Recommendation with conditions
Formatted strat tables:
- All required strats with commentary
- Trend vs. prior tape (if available)
- Concentration limit compliance
Performance analysis:
- CDR/CPR calculations
- Static pool curves
- Vintage comparison
Red flag documentation:
- Each flag with supporting data
- Proposed mitigation (haircut, limit, exception)
Quick reference checklist
Print this page for rapid assessment.
Pre-analysis
- Data dictionary obtained
- Tape date within acceptable range
- File format verified
Data quality (must pass)
- 0% missing on Loan ID, balance, rate, status
- <5% missing on FICO, term, geography
- No systematic logical inconsistencies
- No duplicate loan IDs
First five checks
- Data completeness: PASS / FAIL
- Eligibility pass rate: ____%
- Total UPB: $_____M
- 30+ DPD rate: ____% (benchmark: ____%)
- WA seasoning: ____ months
Key strats completed
- Balance bands
- Rate bands (WAC: ____%)
- Credit distribution (WA FICO: ____)
- Geographic (top state: ____%)
- Vintage (% in last 6 months: ____%)
Performance metrics
- CDR: ____% (benchmark: ____%)
- CPR: ____%
- Severity: ____%
- Projected terminal CNL: ____%
Concentration check
- Single state: ____% vs ___% limit
- Top 10 obligors: ____%
- HHI: ____
Red flags identified
- ________________________________
- ________________________________
- ________________________________
Recommendation
- Proceed to full diligence
- Request tape corrections first
- Proceed with conditions: _______________
- Decline
Related pages
- Collateral analysis covers the full methodology for tape analysis, static pool construction, and performance metrics
- Your loan tape: what lenders actually look at explains the originator perspective on tape preparation and common data quality issues