Auto Loan Model
Credit Financial Model (Free Excel Download)
Forecast auto-loan originations, prepayments, defaults, recoveries, funding costs, and net interest margin across vintages for lender and credit-fund analysis.
professionals from Deloitte
Used by professionals from
About this model
An auto loan portfolio model projects the cash flow performance of securitised auto loans by modelling loan-level amortisation, prepayment speed (CPR) by vehicle age and rate environment, default rates (CPD) by credit tier, loss severity and recovery timing to determine expected losses and credit losses at each tranche level. The model answers what cash flows and loss timeline lenders and credit-risk holders should expect under base case and stress scenarios.
The pool begins with detailed loan characteristics: original term (36–72 months), current rate by credit tier (A: 5%, B: 7%, C: 10%), and credit score distribution. Prepayment and default assumptions vary by vehicle age and credit profile: newer vehicles and strong-credit borrowers prepay faster; older vehicles and subprime borrowers default more frequently. Recovery occurs with a lag (typically 3–6 months) as vehicles are repossessed and auctioned. Loss severity varies by vehicle type (luxury vehicles have lower recovery, used vehicles have higher loss rates). The model projects principal and interest cash flows to each tranche, applies charge-offs and recovery receipts, and calculates expected loss at each subordination level.
Auto ABS investors, rating agencies, and portfolio managers use auto loan models to compare pool composition and expected losses across securitisation deals, stress test for economic downturn (unemployment spike → default surge), and confirm that subordination levels and DSCR are adequate for the credit rating.
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 Auto Loan Model
- Loan origination and vintage tracking
- Dynamic amortisation with prepayment speeds (CPR)
- Default probability curves (CDR) and severity assumptions
- Recovery lag and repossession cost mechanics
- Yield spread and net interest margin analysis
- Loan-level amortization with varying terms and rates by credit tier
- Prepayment modeling by vehicle age and interest rate environment
- Default and delinquency assumptions with CPD (conditional prepayment default)
What the Auto Loan Model Template Captures Across Vintages, Credit and Funding
This auto loan model template is a seven-year annual business planning template for auto finance portfolios. It combines a legacy book with annual origination vintages, FICO-tier assumptions, allowance and provision rollforward, warehouse and ABS funding, and integrated financial statements.
The design supports examining growth, loan yield, losses, funding needs and profitability through connected schedules rather than a single static calculation.
Key Operating Drivers and Case Settings
The template's behaviour is shaped by a case selector and a structured set of assumptions rather than by a single growth rate. Case inputs choose between bear, base and bull outlooks, and those selections drive origination growth, tier APRs, warehouse advance and funding rate.
- Additional multipliers adjust APR, growth, charge-offs, advance rate and funding cost. Because both layers operate together, meaningful case comparisons should review the combined effect rather than isolating one switch.
- Illustrative starting values include a 150 million dollar legacy book and 200 million dollars of Year 1 originations, treated as example setup rather than market data.
How Vintages and the Portfolio Roll Forward
Each annual origination cohort is aged using age-specific prepayment and default speeds, with scheduled principal reduction and half-year runoff in its origination year. The legacy book runs off separately using an average-term repayment fraction, base prepayment speed and tier-blended losses.
- Closing balance combines legacy runoff with all vintages, and average balance is the simple average of opening and closing positions. Gross charge-offs less recoveries produce net charge-offs.
- One documented limitation is that new-cohort closing balances are not floored at zero, so extreme combined runoff assumptions can produce invalid negative balances.
Credit Staging, Allowance and Provision Flow
Delinquency buckets are generated by applying successive roll percentages to closing receivables, producing current, 30, 60 and 90-plus allocations. The required allowance sums each bucket balance multiplied by its loss factor.
- Provision then bridges opening allowance to required allowance after accounting for net charge-offs. The design notes that this is a same-period percentage allocation, not a full migration and cure simulation, and the allowance uses assumed bucket loss factors rather than a validated CECL or IFRS 9 implementation.
- Analysts should treat credit outputs as planning estimates driven by the selected assumptions.
Funding, Revenue and Financial Statement Links
Funding sizes warehouse debt against the loan book, transfers excess above a threshold into ABS, and applies average-balance interest.
- Revenue combines loan interest with origination and late fees, while costs include funding, dealer commissions, provision, ABS fees and book-based operating expenses.
- Portfolio aggregates feed credit, funding and revenue schedules, which in turn feed the income statement, cash flow statement, balance sheet and ratios.
- Repayments and charge-offs appear as negatives in the portfolio rollforward, while recoveries, provisions and statement costs are positive; cash flow originations are negative and collections positive.
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 an auto loan portfolio model?+
It is a model that tracks loan originations, amortisation, defaults, recoveries, and yield across a portfolio of auto loans.
Who uses auto loan models?+
Auto lenders, credit funds, securitisation analysts, and risk management teams use them for portfolio valuation and credit facility sizing.
What should an auto loan model include?+
It should include origination tracking, amortisation, default curves (CDR), recovery assumptions, prepayment speeds (CPR), and net interest margin.
Does it support prime and subprime tranches?+
Yes. You can stratify the portfolio by credit tier and assign different default rates, loan terms, and interest rates to each segment.
Can I use this for securitisation analysis?+
Yes. The model supports cash flow waterfall logic suitable for ABS transaction modelling and investor reporting.
Have more financial modelling questions? Contact us
Related templates
Mortgage Portfolio Model
Mortgage loan portfolio analysis with prepayment speeds, default rates, and cash flow projections.
Student Loan Portfolio
Model a portfolio of student loans with origination, repayment schedules, and default assumptions.
Credit Portfolio CDO Model
Model credit default swap portfolio, tranching, and waterfall for CDO securitization.
Buy Now Pay Later Model
BNPL fintech platform financial projections.
