A car loan calculator Excel file needs one function to get the monthly payment: =PMT(rate/12, months, -amount). This free template does that, then adds the parts most downloads skip: sales tax with or without a trade-in credit, dealer fees rolled into what you borrow, a 96-row amortization schedule and an extra principal column. Open it with the defaults and it shows the same $662 a month and $6,291 of total interest as the car loan calculator by state, because both start from the same inputs. No macros, no sign-up, and it opens in Google Sheets too.
Download the free Excel workbook (.xlsx, two sheets)
What Is in the Free Microsoft Excel Template
The workbook has two tabs. Yellow cells are inputs; every other cell is a formula, and column C on the first tab prints each one with FORMULATEXT so you can see exactly what it does.
- Inputs: vehicle price (B5), cash down (B6), trade-in value (B7), whether the trade-in lowers the taxable price (B8), sales tax rate (B9), doc and title fees (B10), whether tax and fees are rolled in (B11), annual rate (B12), term in months (B13) and extra principal (B14).
- Results: amount financed, monthly payment, interest over the term, all-in cost, cash due at signing, the first-year interest and principal split, and what extra principal saves.
- Amortization: a loan amortization schedule with up to 96 rows, driven entirely by the Inputs tab. Rows past the payoff month stay blank.
Defaults match the site’s no-state baseline: $35,000 new vehicle, $4,000 down, no trade, a 5.75% typical sales tax rate, $440 of doc and title fees ($400 plus $40), a 7.00% rate for prime credit and a 60-month loan term. That makes the loan amount $33,452.50.
The PMT Formula for a Car Payment, Step by Step
PMT is the function that returns a fixed installment for a fully amortizing debt. Microsoft documents it as PMT(rate, nper, pv, [fv], [type]) and warns that rate and nper must use the same units, so an annual interest rate gets divided by 12 when you pay monthly (Microsoft Support, PMT function).
- Taxable price:
=MAX(0,B5-IF(B8="Yes",B7,0)). Leave B8 at Yes if your state taxes the price minus the trade-in value; switch it to No if it does not. The car loan trade-in calculator shows which states give the credit. - Sales tax:
=B17*B9, or $2,012.50 at the defaults. - Amount financed:
=B5-B6-B7+IF(B11="Yes",B18+B10,0). - Monthly payment:
=PMT(B12/12,B13,-B19), which returns $662.40. - Interest over the term:
=B20*B13-B19, which returns $6,291.48.
The minus sign in front of B19 is not a typo. Cash paid out counts as negative, so flipping the sign of the principal keeps every output positive without wrapping the formula in ABS.
Behind PMT sits the standard amortizing loan formula: payment = P × r ÷ (1 – (1 + r)^-n), where P is the amount financed, r is the annual rate divided by 12 and n is the number of payments. With P = 33,452.50, r = 0.07 ÷ 12 (about 0.0058333) and n = 60, it works out to $662.40, the same number the spreadsheet returns. Typing that by hand as =B19*(B12/12)/(1-(1+B12/12)^-B13) is a useful check when a downloaded template gives a payment that looks off.
Of the $39,744 you hand over in five years, $6,291 is the cost of borrowing; the rest repays the amount financed. Rolling the $2,452.50 of tax and fees into the balance adds about $461 of that cost. Set B11 to No and the installment drops to $614 with $5,830 of interest, though $6,452.50 is then due at signing. The car loan calculator with taxes and fees breaks that trade-off down by state.
How Much Interest Is in Each Month: IPMT, PPMT and CUMIPMT
IPMT returns the interest part of one payment and PPMT the principal part; the two always add up to the PMT result. For the default deal:
=IPMT(B12/12,1,B13,-B19)returns $195.14 of interest in month 1.=PPMT(B12/12,1,B13,-B19)returns $467.26 of principal in month 1.=-CUMIPMT(B12/12,B13,B19,1,12,0)returns $2,158.24, the total amount of interest in the first 12 months.=-CUMPRINC(B12/12,B13,B19,1,12,0)returns $5,790.56 of principal for the same year.
Those year-one figures match what the online calculator prints as “Year 1: principal $5,791, interest $2,158”. One gotcha: CUMIPMT and CUMPRINC return a #NUM! error when the rate is zero, so a 0% promotional deal breaks them. The template wraps both in IFERROR; at 0% the year-one interest is simply $0.
Build the Amortization Schedule in a Spreadsheet
The schedule lists every month with its interest, principal and remaining balance. The second tab builds each row from five formulas, shown here for row 6 (month 1):
- Starting balance (B):
=IF(Inputs!$B$19>0,Inputs!$B$19,"")in the first row, then=IF(N(G6)<=0.005,"",G6)below it. - Interest (E):
=IF(OR(A6>Inputs!$B$13,N(B6)<=0.005),"",B6*Inputs!$B$12/12). - Scheduled payment (C):
=IF(E6="","",MIN(Inputs!$B$20,B6+E6)). - Principal (F):
=IF(E6="","",C6+D6-E6). - Ending balance (G):
=IF(E6="","",B6-F6).
The MIN in column C caps the last installment at whatever is left, so the final row closes at $0.00 instead of a few cents below zero. The 0.005 test stops the schedule once the balance rounds to nothing, which is why a 60-month term leaves rows 61 to 96 blank. The loan amortization calculator shows the same rows next to what the car is worth.
Add an Extra Payment and Watch the Payoff Move
Column D holds the extra principal: =IF(E6="","",MIN(Inputs!$B$14,B6+E6-C6)). Type $100 in B14 and the balance reaches zero after 51 months instead of 60, and interest falls from $6,291 to $5,301, a $990 saving. You can confirm the count without the schedule: =ROUNDUP(NPER(B12/12,-(B20+B14),B19),0) also returns 51.
| Extra principal in B14 | Payments needed | Interest paid | Interest saved |
|---|---|---|---|
| $0 | 60 | $6,291 | None |
| $50 | 56 | $5,753 | $538 |
| $100 | 51 | $5,301 | $990 |
| $200 | 45 | $4,585 | $1,707 |
Watch for one mistake in downloaded templates: a schedule that re-runs PMT on the remaining balance every row. With that design, extra money lowers next month’s bill instead of shortening the repayment. The CFPB describes simple interest as far more common on auto loans than the precomputed kind, and on those contracts extra payments go to principal (CFPB). Keep the scheduled payment fixed, as this sheet does. The payoff calculator runs the same $100 test and also reports 51 months and $990 saved.
Compare 48, 60, 72 and 84 Months on One Sheet
Changing B13 one term at a time works, but a four-row side table shows the trade-off at a glance. On a blank tab, type 48, 60, 72 and 84 in A2:A5, then enter =PMT(Inputs!$B$12/12,A2,-Inputs!$B$19) in B2 and =B2*A2-Inputs!$B$19 in C2, and fill both down. The dollar signs lock the rate and the amount financed so only the term changes.
| Term | Monthly payment | Total interest | Total of payments |
|---|---|---|---|
| 48 months | $801.06 | $4,998 | $38,451 |
| 60 months | $662.40 | $6,291 | $39,744 |
| 72 months | $570.33 | $7,611 | $41,064 |
| 84 months | $504.89 | $8,958 | $42,411 |
Stretching from 60 to 84 months cuts the payment by about $158 but adds $2,667 of interest, and the balance stays above the car’s value for longer. If the 84-month row is the only one that fits the budget, the price is usually the problem, not the term.
Open the Template in Google Sheets or Build It From Scratch
Using the car loan calculator Excel file in Google Sheets
Upload the .xlsx to Google Drive and open it with Google Sheets, or use File > Import in a new sheet. Every function in the workbook (PMT, IPMT, PPMT, CUMIPMT, CUMPRINC, NPER, IFERROR and FORMULATEXT) exists in Sheets, and cross-tab references like Inputs!$B$19 use the same syntax, so the results match Excel. One catch applies in both programs: if your locale uses a comma as the decimal mark, formulas take semicolons between arguments, so =PMT(B12/12,B13,-B19) becomes =PMT(B12/12;B13;-B19) when you type it yourself.
The four-cell version
For a quick check without the template, put the loan amount in A1, the annual rate in A2 (as 7% or 0.07), the number of payments in A3, and =PMT(A2/12,A3,-A1) in A4. That is all a basic car payment needs. What it leaves out is everything that makes the real number bigger: sales tax, fees and whether they are financed.
Start by downloading the car loan calculator Excel template and typing in the rate from your best lender quote, not the dealer’s estimate. Then read the total interest line next to the monthly figure for each term; the loan term calculator runs the same comparison online. A lower interest rate shrinks every row at once, so a second quote is worth getting, and the car loan calculator with credit score shows the typical rate for your tier before you ask.
Car Loan Calculator Excel: Frequently Asked Questions
How can you pay off a 7-year car loan in 3 years?
Pay the 36-month amount instead of the 84-month one. On $33,452.50 at 7%, 84 months costs $504.89 a month and 36 months costs $1,032.92, so an extra $528.03 a month clears it in 36 months and cuts interest from $8,958 to $3,732. Ask the lender to apply the extra to principal, and check the contract for prepayment penalties first.
Is this template free?
Yes. It is a standard .xlsx workbook with no macros, no sign-up and no locked cells, built for personal finance use. It works as a loan calculator with amortization schedule for any fixed-rate installment debt up to 96 months.
Where is the loan calculator in Excel?
Excel has no loan calculator button; the payment comes from the PMT function, listed under Formulas > Financial. Microsoft also offers ready-made loan amortization templates under File > New if you search for “loan”. Those are generic, so they skip sales tax, dealer fees and trade-in credit, which is what this workbook adds.
Why does my spreadsheet not match my lender’s numbers?
A spreadsheet assumes every installment lands on its due date. Simple interest is charged on the outstanding balance, so payments that post early or late shift the split between principal and interest. Other culprits: an annual rate left undivided, a term typed in years, tax and fees financed in one tool and paid at signing in the other, or a rounded APR. At the defaults, the workbook and the online auto loan calculator agree to the dollar: $33,453 financed, $662 a month, $6,291 of interest and a $43,744 all-in cost.
Estimates only, not a quote or an offer of credit. Template defaults are September 2026 planning figures; the lender’s contract sets what you actually pay. Last reviewed: November 2026.