Free Excel templates

A free used car dealer gross per unit and floor plan calculator in Excel

Type your average purchase price, auction fees and transport, reconditioning and retail price, the documentation fee, F&I per financed deal and the financed share, commission and other costs per sale, the wholesale loss, sales a month, days in stock, the floor plan terms and your fixed costs: the sheet returns front-end and total gross, the carrying cost a day, what one sale leaves, monthly EBITDA and the break-even, with three checks.

The worked example is the book's fictional independent lot on its FY2026 averages: a car bought for $11,770 with $632 of fees and transport and $1,242 of reconditioning, sold for $16,136, earns $2,492 of front-end gross and $3,611.36 of total gross with the documentation fee and F&I. At $2.67 a day of floor plan cost over 38.2 days, and after commission, other costs and its share of wholesale losses, it leaves $2,089.37. Against $57,647 of fixed costs a month the lot breaks even at 27.6 sales a month and earns $14,436 a month at 34.5.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 158 free models.

Used_Car_Dealer_Gross_per_Unit_and_Floor_Plan_Calculator.xlsx (12 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This model goes with The Used Car Dealership Owner, which explains the method behind it, chapter by chapter.

Get the PDF edition → See the book

Want every model at once?

The Financial Model Library: all 159 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 24 paperbacks by Julian R. Sterling on insurance and retirement, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

3 sheets. Inputs can be changed; everything else is a formula, and the last sheet checks the figures against the book.

Inputs
Average purchase price, buyer fee and transport, reconditioning, retail price, documentation fee, F&I income per financed contract and the financed share, commission, other variable costs and wholesale loss per sale, sales a month, days in stock, floor plan rate, fee and share of stock cost financed, fixed costs, and the what-if days and reconditioning.
Results
Front-end gross, total gross, carrying cost a day, net per sale, monthly EBITDA, break-even sales a month, and what ten more days in stock and $100 more reconditioning cost a month.
Checks
Three tests on net per sale above zero, shares between 0 and 100 percent, and a price above cost, and an All checks cell.

What the worked case returns

MeasureValue
Front-end gross per sale$2,492
Total gross per sale, documentation fee and F&I included$3,611.36
Floor plan carrying cost a day$2.67
Net per sale after carrying cost, fee, commission, other costs and wholesale loss$2,089.37
Monthly EBITDA at the sales entered$14,436
Break-even retail sales a month27.6
Interest a month of ten more days in stock$921
Cost a month of $100 more reconditioning a car, commission held flat$3,450

Every figure is a teaching assumption of the book's fictional case, not a market norm or the terms of a real lender, auction or state. The calculator uses averages and a flat commission; in the book's model the commission is 25 percent of gross after the pack, and the model's own break-even, with its cost classes, is 28.3 sales a month.

How to use it on your own case

  1. Type your average purchase price, buyer fee and transport, reconditioning and retail price from last year's deal recaps.
  2. Type the documentation fee, F&I income per financed contract and the share of sales financed, then commission, other variable costs and the wholesale loss per retail sale.
  3. Enter sales a month, average days in stock, the floor plan rate, fee and share of cost it advances, and your fixed costs a month.
  4. Read net per sale, monthly EBITDA, the break-even, the two what-ifs and the checks.

Questions people ask about it

How many cars does a used car lot need to sell to break even?

Fixed costs a month divided by what one sale leaves after its own costs. On the book's averages that is 27.6 retail sales a month with this calculator, and 28.3 with the model's own cost classes.

How much does a day in stock cost a used car dealer?

The floor plan interest on the part of the car's cost the line advances. In the case, $2.67 a car a day: ten more days in stock across 34.5 sales a month cost $921 a month.

Why does the calculator hold the commission flat?

To keep one sale on one line. In the book's model the salesperson earns 25 percent of gross after the pack, so a quarter of any change in gross is shared; change the commission when you change the price or the reconditioning.

Is this calculator really free?

Yes. You enter an email address, the download opens at once, and you can leave the reader list in one click.

Go further

The free workbooks of The Used Car Dealership Owner go further: the full companion set, blank models and the worked cases of the book.

See the book and its free files →

Other free templates

This template goes with The Used Car Dealership Owner. See the book.