Excel Auto Loan Calculator
Most auto loan calculators hand you a payment and leave you to rebuild the deal in a spreadsheet yourself. This one works the other way round: the inputs are laid out as worksheet cells B1 to B5, the math is Excel’s PMT function, and the results panel gives you the finished =-PMT formula with your numbers filled in, ready to paste into Excel or Google Sheets. It also does two things plain PMT walkthroughs skip: rebates and taxes and fees are folded into the amount financed, and every result comes with a 36 to 84 month term comparison.
Quick Answer
A $32,000 car with $4,000 down and a $3,000 trade-in finances $25,000.00. At 6.9% over 5 years the monthly payment is $493.85, the total of 60 payments is $29,631.08 and total interest is $4,631.08. The spreadsheet formula that produces that payment is =-PMT(6.9%/12,5*12,-25000), and this calculator writes it for you with your own numbers in it.
Key takeaways
- The amount financed is the number that matters, and it is rarely the sticker price: rebates pull it down, financed taxes and fees push it up.
- PMT needs the rate per month and the term in months. Annual rate divided by 12 and years times 12 is the whole conversion.
- At 6.9%, stretching $25,000 from 36 to 84 months cuts the payment from $770.78 to $376.10 and raises interest from $2,748.26 to $6,592.07.
- Copy the generated formula into your own sheet and the comparison work happens there, where you can line up several offers at once.
- Quick Answer
- How to use this Excel auto loan calculator
- The PMT formula behind the payment
- Worked example: $32,000 at 6.9% over 5 years
- Rebates, taxes and fees: the honest amount financed
- Term comparison: what the extra years really cost
- Build a comparison sheet for several dealer offers
- What competing calculators and guides miss
- What PMT does not price
- Frequently asked questions
- Add This Calculator to Your Website
- References
How to use this Excel auto loan calculator
Fill in the cells, click Calculate payment, and read the payment, the formula and the term table. Results stay hidden until you calculate.
- Price, down payment and trade-in (B1 to B3): the three figures every dealer quote starts with. Blank down payment or trade-in fields count as zero.
- Rebates and taxes and fees (optional): enter them if the deal has them. They are part of the amount financed on a real contract, so they belong in the calculation rather than in a footnote.
- Rate and term (B4, B5): the annual rate as a percent and the term in years. Half-year terms such as 3.5 are fine.
- Copy the formula: the results panel shows the finished
=-PMTformula with your numbers in it, plus a button that copies it, so the same calculation lives in your own workbook.
| Cell | Input | Worked example |
|---|---|---|
| B1 | Vehicle price | $32,000 |
| B2 | Down payment | $4,000 |
| B3 | Trade-in value | $3,000 |
| B4 | Annual rate | 6.9% |
| B5 | Term in years | 5 |
| B6 | Amount financed (calculated) | $25,000.00 |
| B7 | Monthly payment (calculated) | $493.85 |
The PMT formula behind the payment
PMT(rate, nper, pv) returns the fixed payment that pays a loan off over a set number of periods at a constant rate. Microsoft’s PMT documentation stresses one point above the rest: the units for rate and nper must match, so a monthly loan needs a monthly rate and a count of months.
rate = B4/12 (annual percent to monthly decimal) · nper = B5*12 (years to months) · pv = B6 (amount financed)
The two minus signs are Excel’s cash-flow sign convention, not a typo. Money you receive is positive and money you pay out is negative, so a loan drawn as a positive amount produces negative payments unless you negate the present value inside PMT and put a minus in front of the function, as this page does. Under the function, PMT evaluates the standard amortization formula M = P × r(1+r)n ÷ ((1+r)n − 1), which is exactly what this calculator computes in JavaScript, so the web result and your spreadsheet agree to the cent. If you later want the split of a single payment, the auto loan principal payment calculator isolates the principal portion, which PMT alone does not show.
Two PMT arguments are deliberately left at their defaults here: fv (future value) and type (payment timing). A standard dealer loan is fully amortizing with payments at the end of each month, so fv is 0 and type is 0. Balloon loans and beginning-of-month collection are real, but they belong to a different setup, covered by the Excel car payment calculator, which exposes both arguments.
Worked example: $32,000 at 6.9% over 5 years
A $32,000 car, $4,000 down, a $3,000 trade-in, 6.9% APR and a 5 year term produce a $493.85 payment. Here is the chain, exactly as the tool runs it:
| Check run | Financed | Payment | Total interest |
|---|---|---|---|
| Worked example (no rebate, no fees) | $25,000.00 | $493.85 | $4,631.08 |
| Same car with $1,000 rebate and $1,500 fees | $25,500.00 | $503.73 | $4,723.70 |
| 0% deal: $24,000 price, $4,000 down, 4 years | $20,000.00 | $416.67 | $0.00 |
Each row is a real run of this calculator, so you can retype any of them and get the same figures back. The middle row is the one most web calculators cannot reproduce, because they have nowhere to put the rebate or the fees.
Rebates, taxes and fees: the honest amount financed
The amount financed is price plus financed taxes and fees, minus down payment, trade-in and rebates. Get that one number right and the payment follows; get it wrong and every figure downstream is fiction.
That build-up mirrors the structure of the CFPB auto loan shopping sheet, which totals the price, add-ons, taxes and fees first, then subtracts down payment and trade-in to reach the amount to finance. One caution from the same consumer guidance: only finance fees you actually owe. Optional add-ons such as service contracts and protection packages ride inside the fees figure if you accept them, and they raise the payment every month for the full term.
Term comparison: what the extra years really cost
On the worked example, moving from 36 to 84 months drops the payment by $394.68 a month and adds $3,843.81 in interest. The table in your results runs the same comparison on your own numbers.
| Term | Monthly payment | Total interest | Reading |
|---|---|---|---|
| 36 months | $770.78 | $2,748.26 | Cheapest overall |
| 48 months | $597.50 | $3,679.85 | Still lean |
| 60 months | $493.85 | $4,631.08 | The worked example |
| 72 months | $425.03 | $5,601.85 | Interest climbs |
| 84 months | $376.10 | $6,592.07 | 2.4x the 36 month interest |
The pattern never reverses: a longer term always lowers the payment and always raises total interest. Guidance from the CFPB on comparing auto loan offers adds the second risk of long terms: owing more than the car is worth for longer, because the balance falls slowly while the car depreciates. A credit union pre-approval is worth running through the same table before you negotiate; the BECU auto loan calculator prices that lender’s own terms if you are comparing a dealer quote against one.
Build a comparison sheet for several dealer offers
One formula becomes a shopping tool when you copy it down a column of offers. Set the sheet up once and every quote a dealer gives you prices itself.
- One row per offer: price, down payment, trade-in, rebate, fees, rate and years across columns B to H.
- Amount financed in column I:
=B2+E2-C2-D2-F2if your columns run price, down, trade, rebate, fees in that order; adjust the letters to your own layout. - Payment in column J:
=-PMT(G2/12,H2*12,-I2), with the rate in G and the years in H. - Total interest in column K:
=J2*H2*12-I2. Sort or scan that column: the cheapest offer is the one with the lowest total interest that still leaves a payment you can carry.
Keep the term in years in its own column rather than typing months into the formula. Two offers quoted at different lengths are the oldest trick in the showroom, and a sheet that normalizes every offer to payment plus total interest makes the trick visible at a glance.
What competing calculators and guides miss
Most Excel auto loan content teaches a clean version of the deal and stops before the deal gets real. The gaps are consistent across the guides and tools checked for this page:
- Clean-price examples (UMA Technology, How-To Geek, ExcelTactics): solid PMT walkthroughs, but the financed amount is price minus down payment and that is it. Rebates, sales tax and dealer fees, the items that move a real contract by hundreds or thousands, never enter the formula.
- Amortization-first guides (TheBricks and several UMA follow-ups): they move straight from PMT to a full schedule. Useful, but a schedule is a second job; a buyer comparing three offers on a Saturday needs the payment and total interest per offer first, which is what this page and its term table produce.
- Generic web calculators: they price the loan and keep the math. None hands you a copyable
=-PMTformula with your numbers already substituted, so the work cannot move into the workbook where comparisons actually happen. - The gap this page fills: the spreadsheet workflow itself, with the honest amount financed (rebates in, fees in) and a five-term payment-versus-interest table attached to every result.
What PMT does not price
PMT prices principal and interest only. Microsoft’s own documentation notes the returned payment includes no taxes, reserve payments or fees, which is why this calculator handles taxes and fees one step earlier, inside the amount financed, instead of pretending PMT covers them.
Two more boundaries worth knowing. First, the rate you enter is treated as the loan’s interest rate; a contract’s APR can sit slightly higher because it folds in certain fees, a distinction the CFPB explains in its interest rate versus APR guidance. Comparing offers on APR is still the fairest basis, then run the winning rate here for the payment. Second, this tool does not build a payment-by-payment amortization schedule and does not model balloon payments or beginning-of-month collection; those are separate jobs with their own pages on this site.
Disclaimer: this calculator is for education and comparison only and is not financial advice. Confirm the APR, the fees and the total amount financed in writing with your lender before you sign.
Frequently asked questions
PMT returns the fixed monthly payment for a loan with a constant rate. For a car loan the form used here is =-PMT(rate/12, years*12, -amount financed): the annual rate divided by 12, the term in months, and the amount financed negated so the payment prints as a positive number.
Because they move the amount financed in opposite directions. Rebates and incentives reduce what you borrow; sales tax, title, registration and dealer fees increase it when you do not pay them in cash. The tool adds fees and subtracts rebates inside cell B6, so the payment, the total interest and the copied formula all use the honest amount financed.
It generates the finished PMT formula with your own numbers filled in, for example =-PMT(6.9%/12,5*12,-25000) for the worked example given earlier. Paste it into any cell in Excel or Google Sheets and it returns the same payment the calculator shows, and it stays live when you change the rate, term or amount in your own sheet.
Enter 0 as the annual rate. With no interest, the payment is simply the amount financed divided by the number of months, and both this calculator and PMT in Excel return that figure instead of an error. On $20,000 financed over 48 months, the payment is $416.67 and total interest is $0.00.
No. The table re-runs your amount financed and your rate at 36, 48, 60, 72 and 84 months and shows the payment and total interest for each length, marking the term you entered. It is a reading of your own numbers at other lengths, so you can see what a shorter term would cost per month and save in interest before you commit to one.
No, and that is deliberate. This page prices the loan and hands you the PMT formula plus a term comparison; it does not list every payment with its interest and principal split. If you need the full schedule in a spreadsheet, that is the job of a loan amortization sheet built with IPMT and PPMT, which return the interest and principal portions of each period.
The term can be up to 10 years, entered in half-year steps if you like, and the annual rate can be from 0% up to 100%. Entering a rate above 100% returns the message that the rate looks too high, which catches the common mistake of typing a decimal such as 0.069 into a percent field, or a monthly rate into an annual one.
Total interest, with the payment as a budget check. A longer term almost always lowers the monthly payment while raising the total cost, and the Consumer Financial Protection Bureau makes the same point in its guidance on comparing auto loan offers: look at the amount, the APR and the length together, not the payment alone.
Add This Calculator to Your Website
Embed this calculator on your own site with the code below. Pick a size to preview it live, then copy the code.
Powered by Calculator Zilo
References
- Microsoft Support. PMT function. Accessed October 21, 2026.
- Google Docs Editors Help. PMT function in Google Sheets. Accessed October 21, 2026.
- The Document Foundation. Financial Functions Part Two: PMT (LibreOffice Calc help). Accessed October 21, 2026.
- Consumer Financial Protection Bureau. What is the difference between a loan interest rate and the APR? Accessed October 21, 2026.
- Consumer Financial Protection Bureau. How do I compare auto loan offers? Accessed October 21, 2026.
- Consumer Financial Protection Bureau. What things can I negotiate when shopping for a car or auto loan? Accessed October 21, 2026.
- Consumer Financial Protection Bureau. Auto Loan Shopping Sheet. Accessed October 21, 2026.
- Consumer Financial Protection Bureau. Your Money, Your Goals: Comparing Auto Loans tool. Accessed October 21, 2026.
- Board of Governors of the Federal Reserve System. The Nuts and Bolts of Today’s Auto Finance Market (Consumer and Community Context, November 2023). Accessed October 21, 2026.
- Microsoft Support. PMT function (Excel). Accessed October 21, 2026.