# Auto Loan Model Build an auto loan portfolio model with vintage tracking, prepayment speeds, default probability curves, and net interest margin analysis. Designed for lenders, credit funds, and securitisation teams. - Canonical: https://finamodel.com/templates/auto-loan-model - Excel download: https://finamodel.com/templates/auto-loan.xlsx - Category: Credit - Model type: Underwriting - Difficulty: Intermediate - Audiences: Credit & risk, Bankers & advisors, Credit analysts, Securitization teams, Finance companies, Investors - Tags: auto lending, securitization, credit analysis, portfolio, CPD ## Overview 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's included - 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) - Loss severity and recovery timing by vehicle type - Pool composition by original term, rate, and credit score - Portfolio performance and loss development tracking ## 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. ## Built for credit portfolio analysis Use this model when default curves, prepayment behaviour, and yield spreads are central to the lending decision. ## Designed for lenders and credit funds A useful auto loan model separates portfolio performance by vintage and tranche so credit quality is visible over time. ## Better than a basic amortisation sheet This gives you institutional-grade portfolio logic instead of a simple loan schedule that ignores defaults, recoveries, and cost of funds. ## Built for credit portfolio analysis Use this model when default curves, prepayment behaviour, and yield spreads are central to the lending decision. ## Designed for lenders and credit funds A useful auto loan model separates portfolio performance by vintage and tranche so credit quality is visible over time. ## Better than a basic amortisation sheet This gives you institutional-grade portfolio logic instead of a simple loan schedule that ignores defaults, recoveries, and cost of funds. ## Features - **Credit tier segmentation:** Model prime, near-prime, and subprime loans separately with realistic CPD and loss rates for each tier. - **Vehicle age and prepayment dynamics:** Capture refinancing incentives and payoff behavior as vehicles age and rates change. - **Warranty and insurance linkage:** Model gap insurance coverage and how warranty expiration affects loss severity. ## Use cases - **Loan securitization and deal structuring:** Forecast pool performance under base and loss stress scenarios to size tranches and pricing. - **Portfolio underwriting:** Evaluate new loan origination terms and credit policy changes for profitability and risk. - **Reserve and capital adequacy:** Calculate ALLL and regulatory capital requirements based on loss forecasts. ## Frequently asked questions ### 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. ## Related templates - [Mortgage Portfolio Model](https://finamodel.com/templates/mortgage-portfolio-model) - [Student Loan Portfolio](https://finamodel.com/templates/student-loan-model) - [Credit Portfolio CDO Model](https://finamodel.com/templates/credit-portfolio-cdo-model)