Car Loan Payment and Amortization Schedules: Calculators and Spreadsheet Templates
How car loan payments and amortization schedules work, how to build one in Excel or Google Sheets, how extra payments and regional loan rules change it, and how to keep it with your service and running-cost records.
Contents
- What is a car loan amortization schedule?
- Payment schedule, repayment schedule and amortization schedule: are they different?
- What columns does a car loan schedule contain?
- How is a car loan payment calculated?
- Worked example: a 25,000 loan at 7.2% over 60 months
- How do you build a car loan amortization schedule in Excel or Google Sheets?
- Steps to build the schedule
- Which spreadsheet functions are useful for car loans?
- Does Google Sheets work differently from Excel?
- What should a car loan payment schedule template include?
- How do extra payments change a car loan schedule?
- Example: adding 50 a month
- What to check before making extra payments
- How do car loan calculators differ by country?
- Car loan amortization schedule in Canada
- Car loan interest calculator in Australia
- Car loan interest calculator in India: EMI, HDFC and SBI
- Car loan interest calculator in Malaysia: flat rate
- Comparison of regional conventions
- How do you connect a loan schedule to a single vehicle record?
- Start with details you already have
- What should you record, and why?
- Columns for a simple service log
- How do you use the record to understand spending and plan upkeep?
- Calculate the true monthly cost
- Work out cost per kilometre
- Plan routine upkeep
- What if the vehicle is leased instead of financed?
- Common mistakes when using car loan calculators and schedules
A car loan amortization schedule is a table that lists every scheduled payment on a vehicle loan. It splits each payment into interest and principal and shows the remaining balance afterwards. You can produce one with an online car loan interest calculator or build it in Excel or Google Sheets using four inputs from your loan agreement:
- the loan principal
- the annual interest rate
- the loan term
- the payment frequency
A loan schedule is more useful when you keep it in the same record as your service dates, fuel, insurance and repair costs. Then you can see what the vehicle really costs each month and plan upkeep before it becomes urgent.
What is a car loan amortization schedule?
Amortization means paying off a debt in regular instalments. Each instalment covers the interest charged for that period, and the rest reduces the amount you owe. A car loan amortization schedule shows this payment by payment. Early payments are mostly interest because the balance is high. Later payments are mostly principal because interest is charged on a smaller balance.
Payment schedule, repayment schedule and amortization schedule: are they different?
The three terms overlap.
- A car loan payment schedule or repayment schedule usually lists the payment dates and amounts.
- An amortization schedule adds the principal and interest breakdown and the remaining balance after each payment.
For managing a vehicle, the amortization version is more useful. It tells you how much you still owe at any date, which matters if you sell, trade in or refinance the car.
What columns does a car loan schedule contain?
| Column | What it shows |
|---|---|
| Payment number | 1 to the last instalment (for example, 1 to 60 on a 5-year monthly loan) |
| Payment date | The due date of each instalment |
| Opening balance | Amount owed before the payment |
| Payment | The scheduled instalment amount |
| Interest | Opening balance × periodic interest rate |
| Principal | Payment minus interest |
| Extra payment | Any additional amount paid that period (optional) |
| Closing balance | Opening balance minus principal and extra payment |
How is a car loan payment calculated?
Most car loans calculate interest on the reducing balance. For these loans, the fixed periodic payment comes from the standard amortization formula:
Payment = P × r × (1 + r)n ÷ ((1 + r)n − 1)
- P is the loan principal, meaning the amount borrowed after any deposit or trade-in.
- r is the interest rate per payment period. For monthly payments, divide the annual rate by 12. For example, 7.2% a year becomes 0.006 a month.
- n is the total number of payments. For example, a 5-year monthly loan has 60 payments.
This is the formula behind most car loan interest calculators and behind the PMT function in spreadsheets.
Worked example: a 25,000 loan at 7.2% over 60 months
A loan of 25,000 (in any currency) at 7.2% a year, repaid monthly over 60 months, gives:
- a monthly payment of about 497.39
- about 29,843 paid in total over the term
- total interest of about 4,843
The first three rows of the schedule look like this:
| No. | Opening balance | Payment | Interest | Principal | Closing balance |
|---|---|---|---|---|---|
| 1 | 25,000.00 | 497.39 | 150.00 | 347.39 | 24,652.61 |
| 2 | 24,652.61 | 497.39 | 147.92 | 349.47 | 24,303.14 |
| 3 | 24,303.14 | 497.39 | 145.82 | 351.57 | 23,951.57 |
The payment stays the same in every row. The interest shrinks slightly each month, and the principal portion grows by the same amount.
How do you build a car loan amortization schedule in Excel or Google Sheets?
You can build the schedule with a few cells for loan details and one row per payment. The same formulas work in Excel and Google Sheets.
Steps to build the schedule
- Enter the loan details in an input area:
- B1: loan principal (for example, 25000)
- B2: annual interest rate (for example, 7.2%)
- B3: number of payments (for example, 60)
- B4: first payment date
- Calculate the payment in B5 with =PMT(B2/12,B3,-B1). The minus sign makes the result positive.
- Add the schedule headings from row 8: No., Date, Opening balance, Payment, Interest, Principal, Extra, Closing balance.
- Fill the first row (row 9):
- No.: 1
- Date: =B4
- Opening balance: =B1
- Payment: =MIN($B$5, C9*(1+$B$2/12))
- Interest: =C9*$B$2/12
- Principal: =D9-E9
- Extra: leave blank or enter an amount
- Closing balance: =C9-F9-G9
- Link the second row to the first:
- No.: =A9+1
- Date: =EDATE(B9,1)
- Opening balance: =H9
Then copy the Payment, Interest, Principal and Closing balance formulas down.
- Fill down until the closing balance reaches zero. The MIN in the payment column stops the last payment from overshooting.
- Add totals under the schedule by summing the Interest column and the Payment column. This shows total interest and total cost.
- Check against the lender. Your payment should match the loan agreement to within a few cents. If it does not, the lender may use a different compounding method, include fees, or count days differently.
Which spreadsheet functions are useful for car loans?
| Function | What it returns | Example (25,000, 7.2%, 60 months) |
|---|---|---|
| PMT | Fixed payment per period | =PMT(7.2%/12,60,-25000) returns about 497.39 |
| IPMT | Interest portion of a specific payment | =IPMT(7.2%/12,1,60,-25000) returns 150.00 |
| PPMT | Principal portion of a specific payment | =PPMT(7.2%/12,1,60,-25000) returns about 347.39 |
| CUMIPMT | Total interest between two payment numbers | =CUMIPMT(7.2%/12,60,25000,1,12,0) returns the first year's interest as a negative number |
| NPER | Number of payments needed at a given payment | Useful to see how quickly a larger payment clears the loan |
| EDATE | Same day a set number of months later | Generates monthly payment dates |
Does Google Sheets work differently from Excel?
For a car loan schedule, Google Sheets works the same way. PMT, IPMT, PPMT, CUMIPMT, NPER and EDATE use the same argument order. A schedule built in one program generally opens and calculates correctly in the other.
Google Sheets is convenient if more than one person in a household records payments or receipts, because the file can be shared. Excel suits people who prefer a file stored on their own computer.
What should a car loan payment schedule template include?
A reusable car loan payment schedule template should have:
- clearly labelled input cells for principal, rate, term, frequency and start date
- a payment calculation from those inputs
- a full schedule long enough for the longest term you might use
- an extra-payment column
- totals for interest and payments
Lock or shade the formula cells so they are not overwritten by accident. Keep the input cells as the only place where you type loan details.
How do extra payments change a car loan schedule?
Extra payments go straight to principal on most reducing-balance loans. Each extra amount lowers the balance that future interest is charged on. As a result, the loan ends sooner and total interest falls.
A car loan interest calculator with extra payments, or the Extra column in your spreadsheet, shows the effect.
Example: adding 50 a month
Take the same 25,000 loan at 7.2% over 60 months. Paying an extra 50 each month from the start, for 547.39 in total, clears the loan in about 54 payments instead of 60. Total interest falls by roughly 540.
A one-off lump sum has a similar effect. The earlier it is paid, the more interest it saves.
What to check before making extra payments
- Prepayment or early settlement charges. Some lenders charge a fee for paying early or for closing the loan before the term ends. The loan agreement states whether this applies.
- How the lender applies the money. Confirm the extra amount reduces principal. Some lenders instead treat it as an early payment of the next instalment.
- Flat-rate loans. If interest was fixed upfront (see Malaysia below), paying early may save less than on a reducing-balance loan. The saving depends on the lender's settlement rules.
How do car loan calculators differ by country?
The basic amortization formula is the same everywhere. Local loan conventions change the inputs, so a calculator or template should match how your lender quotes and charges interest.
Car loan amortization schedule in Canada
Canadian car loans are often offered with weekly or biweekly payments as well as monthly ones. A car loan interest calculator for Canada should therefore let you choose the payment frequency.
- A regular biweekly payment usually equals the annual total of monthly payments divided by 26. With a 497.39 monthly payment, that is about 229.56.
- An accelerated biweekly payment is half the monthly payment, about 248.70. Paid 26 times a year, it adds roughly one extra monthly payment each year and shortens the loan.
For a biweekly schedule, use the annual rate divided by 26 as the periodic rate and 26 payments per year in the term. The financed amount may include sales tax and dealer fees, so take the principal from the loan contract rather than the vehicle's sticker price.
Car loan interest calculator in Australia
Australian car loans are usually advertised with an interest rate and a comparison rate. The comparison rate combines the interest rate with certain standard fees, which helps you compare loans. Your amortization schedule, however, should use the actual interest rate in the contract. Record fees separately.
Many Australian car loans also offer a balloon payment. This is a lump sum left owing at the end of the term, which lowers the regular payment. To model it in a spreadsheet, use =PMT(rate/12,n,-P,balloon). Your schedule will then end at the balloon amount rather than zero.
Car loan interest calculator in India: EMI, HDFC and SBI
In India, the monthly car loan payment is called the EMI (equated monthly instalment). Banks calculate it with the reducing-balance formula above.
Bank EMI calculators, such as those on the HDFC Bank and SBI websites, typically ask for three things:
- the loan amount
- the interest rate
- the tenure in months or years
They then show the EMI, total interest and total payable, and often a principal and interest breakdown.
Example: a loan of ₹8,00,000 at 9% a year over 5 years has an EMI of about ₹16,607 and total interest of about ₹1,96,400. Use the rate actually offered to you, because bank rates change over time and vary by borrower profile. Also note any processing fee separately.
Car loan interest calculator in Malaysia: flat rate
Conventional car hire-purchase loans in Malaysia are commonly quoted as a flat rate. Under a flat rate, interest is calculated on the original principal for the whole term, even as the balance falls.
The calculation has two steps:
- Total interest = principal × flat rate × years
- Monthly instalment = (principal + total interest) ÷ number of months
Example: RM 25,000 at a 3% flat rate over 5 years gives total interest of RM 3,750 and a monthly instalment of about RM 479.17. On a reducing-balance basis, those payments are equivalent to roughly 5.6% a year.
A flat-rate loan therefore costs more than its headline rate suggests. A Malaysian car loan calculator should use the flat-rate method, not the PMT formula, unless the loan states otherwise.
Comparison of regional conventions
| Market | Common interest method | Points to set in a calculator or template |
|---|---|---|
| Canada | Reducing balance | Weekly, biweekly or accelerated payment frequency; financed taxes and fees in principal |
| Australia | Reducing balance | Contract rate (not the comparison rate) for the schedule; optional balloon payment |
| India | Reducing balance (EMI) | Tenure in months; processing fee recorded separately |
| Malaysia | Often flat rate (hire purchase) | Flat-rate interest calculation; check early settlement terms |
How do you connect a loan schedule to a single vehicle record?
A vehicle record works best as one spreadsheet file with separate tabs:
- the loan schedule
- a service log
- a running-costs log
- a monthly summary
Keeping everything in one file means the loan payment sits next to fuel, insurance and maintenance. You can then see the full monthly cost of the car, not just the instalment.
Start with details you already have
Most of the information needed to begin is already in your paperwork.
- Loan agreement: principal, interest rate, term, payment amount, payment frequency, first payment date, and any fees or prepayment terms
- Bank statements: payments already made, to mark them as paid in the schedule
- Purchase documents: purchase date, price, deposit and odometer reading at purchase
- Owner's manual or service booklet: recommended service intervals by distance and time
- Existing receipts: past services, tyres, repairs and registration or insurance renewals
- The odometer today: a current reading to start the log
What should you record, and why?
| Record | Example entries | Why it matters |
|---|---|---|
| Loan payments | Date paid, amount, extra payment, remaining balance | Confirms payments match the schedule; shows what you owe if you sell or refinance |
| Service history | Date, odometer, work done (oil change, brake pads, filters), workshop, cost | Shows when the next service is due; supports warranty claims and resale value |
| Fuel or charging | Date, odometer, litres or kWh, cost | Reveals running cost per kilometre and early signs of a fault if consumption rises |
| Fixed costs | Insurance, registration, road tax, parking permits with renewal dates | Spreads annual bills into a monthly figure; avoids missed renewals |
| Repairs and parts | Tyres, battery, wipers, unexpected repairs | Shows whether repair costs are rising as the car ages |
| Notes | Warning lights, noises, advice from the mechanic | Keeps follow-up items from being forgotten |
Columns for a simple service log
- Date
- Odometer reading (km or miles; use one unit throughout)
- Category: service, repair, tyres, inspection or other
- Description of work
- Cost
- Next due date
- Next due odometer reading
- Notes
Filling in the last two due columns turns the log into a reminder list. Sort by next due date, or compare next due odometer with the current reading, to see what is coming up.
How do you use the record to understand spending and plan upkeep?
A combined record lets you add up all vehicle costs for a month or year and divide them by distance driven.
Calculate the true monthly cost
Add together:
- the loan payment
- average monthly fuel
- monthly insurance
- one-twelfth of annual registration or road tax
- a monthly allowance for maintenance
For example, suppose your figures are:
- loan payment: 497
- fuel: 180
- insurance: 90
- registration spread monthly: 25
- maintenance allowance: 60
The vehicle costs about 852 a month, not 497. These numbers are only an illustration; use your own records.
Work out cost per kilometre
Divide total costs over a period by the distance driven in that period. The difference between odometer readings gives the distance.
If a year's costs total 10,200 and you drove 15,000 km, the cost is 0.68 per km. Tracking this figure over several years shows whether the car is becoming more expensive to run.
Plan routine upkeep
- Use the service log's due columns to schedule the next service before the interval passes.
- Set aside a monthly amount for predictable items such as tyres, brakes and batteries, based on what they cost you last time.
- Check the remaining balance in the loan schedule before a large repair or trade-in decision. Selling a car for less than the remaining balance leaves a debt to settle.
- Note the month the loan ends. After that, the payment amount could go towards future maintenance or the next vehicle's deposit.
What if the vehicle is leased instead of financed?
A lease payment schedule lists fixed payments over the lease term, but it is not an amortization schedule in the loan sense. With a lease, you pay for the vehicle's expected loss in value during the term plus a finance charge. You do not pay down a balance to own the car outright.
For a leased car, the same vehicle record is still useful. Record:
- the payment dates
- the lease end date
- the annual distance allowance and your odometer readings against it
- service history, which leases often require you to keep to the manufacturer's schedule
Common mistakes when using car loan calculators and schedules
- Using the annual rate as the monthly rate. Divide the annual rate by 12 for monthly payments, or by 26 for biweekly payments.
- Using the vehicle price instead of the amount financed. Subtract the deposit and trade-in, and add any financed taxes and fees.
- Mixing flat and reducing-balance rates. A flat rate and a reducing-balance rate with the same number are not equal in cost.
- Ignoring fees. Processing, establishment or documentation fees add to the cost even if they are not in the schedule.
- Never updating the record. Enter payments and receipts when they happen. A brief monthly check keeps the figures reliable.
Related articles
Car Lease-End Charges: Return Fees and How to Dispute Them
Understand car lease-end charges like disposition fees, excess mileage, and wear-and-tear costs. Learn how to dispute unfair charges with evidence, keep organized records, and manage lease returns with confidence.