Auto Loan Amortization Schedule in Google Sheets

Yes—Google Sheets can create an auto loan amortization schedule that shows each monthly payment’s interest, principal, and remaining balance. Enter the loan amount, annual interest rate, and number of monthly payments, calculate the payment with PMT, then fill a small set of formulas down the schedule.

What an auto loan amortization schedule shows

An amortization schedule breaks a fixed-payment loan into a row for each payment period. It distinguishes the amount going to interest from the amount reducing the loan principal, and tracks the balance after each payment. This makes it easier to see why the balance falls slowly at first: when the balance is larger, more of each payment goes toward interest.

A basic schedule is an estimate based on regular monthly payments and a constant rate. It is useful for understanding the loan’s structure, comparing hypothetical terms, and checking how an extra principal payment might affect the balance. It does not replace the lender’s official payment schedule, payoff quote, or account history.

Set up the loan inputs

In a blank Google Sheets file, put the labels in column A and the corresponding values in column B. Use this simple layout:

A2: Loan amount; B2: 25000
A3: Annual interest rate; B3: 6%
A4: Number of monthly payments; B4: 60
A5: Regular monthly payment; B5: formula

Enter the rate as a percentage, such as 6%, rather than 6. Enter the term as a count of months: a five-year term is 60 payments. Format B2 and B5 as currency and B3 as a percentage using Format > Number. In Google Sheets in a web browser, select the cells and use the toolbar’s number-format controls or that menu path.

With the labels above and the amount, annual rate, and payment count in B2:B4, calculate the regular payment in B5 with =-PMT(B3/12,B4,B2). For the illustrative $25,000 loan at 6% over 60 months, this returns about $483.32 per month. The minus sign changes the function’s cash-flow sign so the displayed payment is positive.

Build the monthly schedule

Start the schedule in row 9. Add these headers: A9 Period, B9 Starting balance, C9 Payment, D9 Interest, E9 Principal, and F9 Ending balance. Row 10 will be the first payment. For a 60-payment example, the schedule runs through row 69, one row per month. You can bold the header row or freeze it with View > Freeze so the labels remain visible while scrolling.

Enter 1 in A10 as a value. With the period number in A10, enter =A10+1 in A11 and fill it down through A69; the first result is 2 and the last is 60. With B2 holding the original principal, enter =$B$2 in B10; it returns $25,000. In B11, enter =F10 and fill down; each subsequent starting balance then links to the prior row’s ending balance.

With the starting balance in B10 and the annual rate in B3, enter =B10*$B$3/12 in D10 and fill down the Interest column. The first month’s interest is $125: $25,000 multiplied by 6% and divided by 12. This standard monthly calculation assumes the lender applies one-twelfth of the annual rate each period.

With the scheduled payment in B5, the starting balance in B10, and the current-period interest in D10, enter =MIN($B$5,B10+D10) in C10 and fill down. The first payment is about $483.32. The MIN function makes the last payment no larger than the remaining balance plus that period’s interest, rather than charging a full payment after the loan is paid off.

With the payment in C10 and interest in D10, enter =C10-D10 in E10 and fill down. The first payment reduces principal by about $358.32. With the starting balance in B10 and principal paid in E10, enter =MAX(0,B10-E10) in F10 and fill down. The first ending balance is about $24,641.68. Format the money columns as currency with two decimal places; formatting changes what is displayed, not the underlying calculation.

Worked example and totals

For a $25,000 balance, a 6% annual rate, and 60 monthly payments, the payment formula gives approximately $483.32. In month one, the schedule allocates about $125.00 to interest and $358.32 to principal, leaving about $24,641.68. In later months, interest is calculated on the lower starting balance, so more of the same regular payment goes to principal.

The unrounded payment is approximately $483.32006. Across 60 payments, the estimated total paid is about $28,999.20, and estimated interest is about $3,999.20. These are illustrative results, not a lender quote. If your schedule contains 60 rows of payments in C10:C69, enter =SUM(C10:C69) in a separate total cell to total payments; it returns about $28,999.20 for this example. With interest amounts in D10:D69, =SUM(D10:D69) returns about $3,999.20. Keep totals outside the schedule rows so they are not accidentally included in the sum.

Make the sheet easier to read and check

Use consistent formats: currency for balances and payment amounts, percentage for the annual rate, and whole numbers for period numbers. A clear label for the rate is important because the formulas expect an annual rate in B3 and divide it by 12. Do not enter a monthly rate there unless you also change the interest and payment formulas.

Check that the first row’s payment equals interest plus principal, allowing for displayed rounding, and that each row’s starting balance matches the prior row’s ending balance. The final balance should be zero or close enough to display as $0.00. A tiny residual can arise from floating-point precision or from displaying only two decimals. Do not mistake a rounded display for a lender-confirmed payoff amount.

If you want to print or share the schedule, add a descriptive title above the table, keep the input assumptions visible, and use File > Print to review page breaks. You can also use Format > Conditional formatting to highlight balances or a final payment row, but conditional formatting is optional and does not change the calculations.

Extra payments and changes to the loan

A standard schedule assumes the same payment each month and no additional principal payments. To model extra payments, add an Extra principal column and decide how the extra amount affects that month’s principal and ending balance. Do not simply add extra cash to the scheduled payment without checking the last row: the loan may be paid off early, so a later regular payment should not be shown as still due.

Extra-payment rules vary. Some lenders apply additional money to principal only when it is designated or processed in a particular way; others may treat it as an advance payment. A spreadsheet scenario can illustrate the arithmetic, but it cannot establish how a lender will apply funds or whether the loan has a prepayment charge. Confirm those details in the loan documents or directly with the lender before relying on a scenario.

You can also change the input rate or term to compare hypothetical payments, while keeping the original loan data in a separate copy or clearly labeled section. A longer term generally lowers the scheduled monthly payment but can increase total interest when the rate and principal are otherwise comparable. That comparison is a calculation, not a recommendation about which loan to choose.

Why a spreadsheet may differ from the lender

The formulas above model a conventional fixed-rate loan with equal monthly periods. A lender’s actual calculation may use daily interest accrual, a different day-count convention, a first period that is longer or shorter than a month, payment-date rules, or rounding to cents on every transaction. Fees, add-on products, late payments, deferments, and prior extra payments can also change the account balance.

For that reason, a schedule built from the original amount, APR, and term may not match a current statement or payoff quote exactly. If the lender gives you a required payment, use that figure for budgeting and check what the contract says about interest and payment application. For a payoff on a particular date, request an official payoff amount; a sheet’s balance is not a substitute for it. For questions about your specific contract, check with the lender or a qualified professional. This explanation is educational and is not financial, tax, investment, or legal advice.

Google Sheets compared with Excel

Google Sheets in a browser supports the PMT function and the formulas used here. The basic cell formulas are also compatible with current Microsoft Excel, although menu locations, sharing, and some interface details differ between the programs. In Sheets, menu paths such as Format > Number and File > Print refer to the browser interface; Excel uses its own ribbon and print controls.

For a straightforward fixed-rate loan, you do not need an add-on or a special amortization template. Building the schedule with ordinary cells makes it easier to see the assumptions and inspect each calculation. If you use a downloaded template, verify its rate convention, payment frequency, and treatment of extra payments before relying on its totals.

Frequently Asked Questions

What formula calculates an auto loan payment in Google Sheets? Use =-PMT(annual_rate/12,number_of_months,loan_amount) when the annual rate is entered as a percentage and the loan has monthly payments. For the example layout in this article, the equivalent formula is =-PMT(B3/12,B4,B2).

How do I calculate the interest for each month? Multiply that month’s starting balance by the monthly rate. With the starting balance in B10 and annual rate in B3, the formula is =B10*$B$3/12. The example’s first month of interest is $125.00.

Why does the balance barely go down at the beginning? Interest is calculated on the outstanding balance, which is largest at the start. In the example, $125 of the first roughly $483.32 payment goes to interest and about $358.32 reduces principal. As the balance declines, the interest portion usually declines too.

Should I round every formula to two decimal places? Usually, format the result to display two decimal places without rounding each intermediate calculation. Rounding every row can create a small difference in the ending balance. Lenders may round transactions according to their own servicing rules, so their records can still differ.

Can this sheet tell me my exact payoff amount? No. It estimates a scheduled balance under the assumptions entered. Daily interest, payment timing, fees, and lender-specific rules can affect the amount due on a particular date. Ask the lender for an official payoff quote.

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.