Car Loan Amortization Schedule in Google Sheets

Yes. In Google Sheets, enter the amount financed, annual interest rate, and loan term, calculate the monthly payment with PMT, then use each row to split that payment into interest and principal and update the remaining balance. The schedule below uses a sample $25,000, five-year loan at 6.5% APR and shows how to build, check, and adapt the sheet.

What an amortization schedule shows

An amortization schedule lays out a loan payment by payment. For each period, it shows the opening balance, the payment, the amount applied to interest, the amount applied to principal, and the balance left afterward. This makes it easier to see why the loan balance does not fall by the full payment amount: interest is charged first, and only the remainder reduces principal.

For a typical fixed-rate car loan with monthly payments, the required payment is generally level, while the interest and principal portions change over time. Early payments contain more interest because the balance is larger. Later payments contain less interest and more principal. The schedule is a model, however; the lender’s payment allocation, dates, fees, and rounding rules determine the actual account figures.

Set up the loan inputs

In a blank Google Sheets file open in a web browser, add labels in column A and values in column B. Use this layout:

A2: Amount financed; B2: 25000
A3: Annual interest rate; B3: 6.5%
A4: Term in years; B4: 5
A5: Payments per year; B5: 12
A6: First payment due date; B6: enter the date supplied by the lender
A7: Regular payment; B7: formula below

Amount financed means the principal borrowed, not necessarily the car’s sticker price. It may reflect a down payment, trade-in, taxes, fees, or add-ons if those amounts were rolled into the loan. Use the APR and payment frequency that match the loan documents. Format B2 and B7 as currency, B3 as a percentage, and B6 as a date. These options are available from the toolbar or through Format > Number.

With the inputs in B2:B6 as listed, B3 entered as 6.5%, and B7 reserved for the payment, calculate the rounded monthly payment in B7 with =ROUND(-PMT(B3/B5,B4*B5,B2),2). For the example, this returns approximately $488.65 per month. PMT uses the periodic rate, total number of payments, and present loan amount; the minus sign makes the result display as a positive payment. The calculation assumes equal periodic payments and a constant rate.

Build the payment-by-payment schedule

Put these headings in row 11: Period, Due date, Beginning balance, Payment, Interest, Principal, and Ending balance. Enter the first schedule row in row 12. The cell layouts below assume the inputs are in B2:B7 and the schedule columns A:G use those headings.

With A12 reserved for the first payment number, enter =1. In A13, with A12 holding the prior period number, enter =A12+1 and fill downward.
With B12 as the first due-date cell, B6 as the first due date, and A12 as the period number, enter =EDATE($B$6,A12-1). Fill downward to generate monthly dates. EDATE advances by calendar months, so the day of month may shift when a month is shorter.
With C12 as the first beginning balance and B2 as the original amount financed, enter =$B$2.
With E12 as the interest cell, C12 as beginning balance, B3 as annual rate, and B5 as payments per year, enter =ROUND(C12*$B$3/$B$5,2).
With D12 as the payment cell, B7 as the regular payment, C12 as beginning balance, and E12 as interest, enter =MIN($B$7,C12+E12). The MIN prevents the final scheduled payment from exceeding the remaining balance plus that period’s interest.
With F12 as principal paid, D12 as payment, and E12 as interest, enter =D12-E12.
With G12 as ending balance, C12 as beginning balance, and F12 as principal paid, enter =MAX(0,C12-F12).

For the next row, with C13 as the next beginning balance and G12 as the prior ending balance, enter =G12. Copy the formulas for the next payment row down through the number of payments: for this example, 60 payments occupy rows 12 through 71. The formulas adjust their row references as they are copied. In the first period, interest is $135.42 ($25,000 multiplied by 6.5% and divided by 12); the $488.65 payment therefore applies about $353.23 to principal, leaving an ending balance of about $24,646.77. Because interest is rounded to cents each month, the final payment or total can differ slightly from a lender’s figures.

Check the schedule and make it readable

Format the balance, payment, interest, and principal columns as currency, and the due-date column as a date. Freeze the heading row if useful by choosing View > Freeze, then selecting the appropriate rows. A clear layout makes it easier to spot a wrong rate or a balance that fails to decline.

Check several relationships in the table:

Payment should equal interest plus principal for each row.
Ending balance should equal beginning balance minus principal, never less than zero.
The next row’s beginning balance should match the previous row’s ending balance.
For a fully paid fixed-rate loan, the final ending balance should be zero or very close to zero.

In the example, the first row’s payment components add to $488.65, subject to rounding. A useful independent check is to compare the payment from the sheet with the payment shown in the lender’s contract or calculator. A discrepancy can result from a different financed amount, APR, term, payment frequency, first-payment timing, or rounding convention; it is not automatically a spreadsheet error.

Account for extra principal payments

To model additional principal, add an Extra principal column, such as column H, and enter the extra amount planned for each period. Do not simply increase the regular-payment input: separating extra principal makes the assumptions visible. With H12 holding the extra amount, C12 the beginning balance, D12 the scheduled payment, E12 interest, and F12 scheduled principal, the ending-balance calculation can subtract the extra amount as well. Cap the extra amount so it cannot exceed the balance remaining after the scheduled payment, or the model could show a negative balance. For example, calculate an allowed extra amount as the smaller of H12 and the balance left after scheduled principal; the ending balance then subtracts that allowed amount.

Extra payments can shorten the modeled payoff period and reduce modeled interest, but the real effect depends on how the lender applies them. Some lenders require instructions to apply additional money to principal, and some loans have terms or payment-processing rules that affect timing. Confirm the procedure, any applicable fee, and whether the lender recalculates the required payment or simply shortens the payoff period. Do not treat a spreadsheet projection as a promise of savings.

Why the sheet may not match the lender

A basic PMT schedule assumes a constant periodic rate and regular payments. Real auto loans may calculate interest using actual days between payments, a daily rate, or another method described in the contract. A first payment that falls unusually soon or late, a payment posted after its due date, or a changed due date can therefore shift interest from the simple monthly estimate.

Other common causes of differences include a financed service contract or other product omitted from the principal, a down payment or trade-in entered incorrectly, payment rounding at a different stage, and late fees or past-due amounts. The APR is also not always interchangeable with every rate figure displayed in loan paperwork. Use the lender’s stated loan amount, rate, term, payment schedule, and official payoff quote when reconciling the account. For an exact payoff amount, request it from the lender because accrued interest and fees can change by date.

Google Sheets versus a lender calculator

A lender’s calculator is convenient for a quick estimate and may reflect that lender’s product assumptions, but it may not expose every period’s calculations. A Google Sheets schedule is easier to inspect and customize: you can change the financed amount, vary extra payments by month, or compare scenarios side by side. The trade-off is that the sheet only knows the assumptions entered. It does not retrieve account activity or automatically know how the lender posts payments.

For routine fixed monthly payments, the PMT-based schedule is an appropriate planning model. For variable rates, irregular payments, payment holidays, or a loan with a special interest method, a basic schedule needs more detailed rules and may not reproduce the contract. Google Sheets uses PMT and EDATE in the formulas above; Excel has similarly named functions, but its menus and interface differ. In Sheets, use the web application’s menus rather than Excel ribbon instructions. Formula argument separators can vary with spreadsheet locale; the examples use commas.

Common setup mistakes

Entering 6.5 instead of 6.5% in B3 treats the annual rate as 650% rather than 6.5%.
Using the annual rate directly in the monthly interest formula omits division by 12.
Using the car’s price instead of the amount actually financed can overstate or understate every balance.
Entering the term as 60 in years while also multiplying by 12 produces 720 payments. For this layout, enter 5 years and let the payment formula calculate 60 monthly periods.
Copying only some formulas down can leave gaps or cause later rows to reference the wrong balance.
Rounding the interest to cents each period is practical for a readable estimate, but a lender may use a different rounding process.

Google Sheets recalculates formulas when their inputs change, so a scenario can be tested by editing an input and then restoring it. Keep a copy of the original assumptions if you want to preserve the first estimate.

Frequently Asked Questions

What formula calculates a car loan payment in Google Sheets?

Use PMT with the periodic interest rate, total number of payment periods, and amount financed. For monthly payments, divide the annual rate by 12 and multiply years by 12. The example rounds the result to cents and makes it positive for display.

How do I calculate the interest for each car payment?

For a simple monthly estimate, multiply that row’s beginning balance by the annual rate divided by 12, then round to cents. The interest portion usually declines as the balance falls. A lender using daily interest may produce different figures.

Can Google Sheets show when the car loan will be paid off?

Yes. Extend the schedule for the full payment count and look for the row where ending balance reaches zero. If you model extra principal payments, the balance can reach zero before the original final payment date, subject to the assumptions and lender rules.

Why is my Google Sheets payment different from the lender’s payment?

Check the amount financed, APR, term, payment frequency, and first due date first. The lender may also use a different interest accrual or rounding method, or include fees or products in the amount borrowed. Compare the sheet with the signed loan terms, not just the vehicle price.

Does an amortization schedule include insurance or maintenance?

No. The schedule describes loan payments and interest, not the full cost of owning a car. Insurance, fuel, maintenance, registration, and other expenses are separate budget items unless a specific amount was financed into the loan principal.

This spreadsheet is for illustration and organization, not financial, tax, or legal advice. Verify payment details, extra-payment treatment, fees, and payoff figures with the lender; consult a qualified professional for advice specific to your circumstances.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.