# The Auto Finance Professional: companion files

Julian R. Sterling

## What is here

- **Auto_Finance_Model.xlsx**: the workbook that rebuilds the Brindlemoor case and reproduces the figures of the book with live formulas. Sheets: Rate card, Application, Dealer, Vintage, Losses, Allowance, Funding, Trust, Collections, Channel, Checks. The Checks sheet puts the figures printed in the book beside the cells that compute them.
- **Auto_Finance_Toolkit.docx**: the 8 check-lists of Appendix B (dealer sign-up and annual review; application and structure check; funding audit check-list; monthly vintage and dealer review; collections and repossession file; allowance committee pack; trust pool and waterfall review; state rules grid: the columns to keep), in editable form.

## Which sheet answers which chapter

- Chapter 4: Dealer (the buy rate, the markup, the reserve and the discount)
- Chapters 5 to 7: Application (the October 2026 file: fails on loan to value as sent, approved at $29,375.00 financed)
- Chapter 8: Rate card (each tier's inputs and its price build in points)
- Chapters 9 and 10: Losses and Vintage (frequency, timing, recoveries; lifetime net losses of $10,190,216 on one $150,000,000 vintage)
- Chapter 11: Collections (the roll rates and one repossession)
- Chapter 12: Allowance (an allowance of $37,185,914 at 31 December 2027 and a 2027 provision of $40,760,864)
- Chapter 13: Funding (a return on equity of 24.5 percent on the vintage, before overhead and taxes)
- Chapter 14: Trust (the $300,000,000 trust, its waterfall and triggers; $43,600,850 released to Brindlemoor in the base case)
- Chapter 16: Channel (Tier A lent direct against the dealer channel)
- Appendix A: 11 exercises, with the answers the workbook returns

## Two inputs to change first

1. On Rate card, raise Tier D's default frequency from 40 to 48 percent: watch its loss curve on Losses, its return on Funding and its day-one allowance on Allowance.
2. On Trust, set the stress at 2.00: watch the trigger trip, the overcollateralization target rise and the cash released to Brindlemoor fall, while the notes lose nothing. Reset the inputs afterwards: the Checks sheet compares the workbook with the figures printed in the book.

Blue type on a light blue background is an input, black type a formula, green type a link to another sheet. No macro, no circular reference, no external link. The Checks sheet should read ALL CHECKS OK on the default inputs.

The model treats every contract in a tier as the tier's average contract, spreads defaults over the months with one timing curve, and gives returns before corporate overhead and income taxes: simplifications for teaching. Brindlemoor Auto Finance, its trust and its dealers are fictional; rates, prices, loss assumptions, funding terms and dates are teaching assumptions, not market data, the terms or results of any real lender or dealer, or a forecast. Provided as is, without warranty. Not legal, compliance, tax, accounting, credit or investment advice.
