Vehicle Amortization Schedule in Excel: Formula and Example

A vehicle amortization schedule in Excel shows how each loan payment is split between interest and principal, along with the remaining balance after each month. This calculator estimates the monthly payment and total interest from the amount financed, APR, and term; you can use those inputs to build a month-by-month schedule in a spreadsheet.

Formula

The fixed monthly payment for a fully amortizing loan is calculated as:

Payment = P × r ÷ [1 − (1 + r)⁻ⁿ]

• P is the amount financed, in dollars.
• r is the monthly interest rate, calculated as APR ÷ 1,200. APR is entered as a percentage per year.
• n is the number of monthly payments, calculated as years × 12.

Total interest in the calculator is monthly payment × number of payments − amount financed. The payment output is rounded to cents before the total interest is calculated. A spreadsheet may show slightly different totals if its formulas retain extra decimal places internally instead of rounding each payment to cents.

Worked Example

Using the calculator’s default values—$20,000 financed, 8% APR, and a five-year term:

1. Convert the APR to a monthly rate: 8 ÷ 1,200 = 0.0066667.
2. Find the number of payments: 5 × 12 = 60.
3. Substitute into the payment formula: $20,000 × 0.0066667 ÷ [1 − (1.0066667)⁻⁶⁰] = approximately $405.53 per month.
4. Calculate total interest using the displayed, rounded payment: $405.53 × 60 − $20,000 = $4,331.80.

The figures are estimates under the stated assumptions. A lender’s payment schedule can differ if the financed amount, payment dates, rate, fees, or rounding method differs.

Assumptions

• The interest rate remains fixed throughout the term.
• Payments are equal monthly payments, made at the end of each monthly period.
• Interest accrues monthly using APR divided by 12.
• The calculation excludes fees, taxes, insurance, optional products, and other costs unless they are already included in the amount financed.

How to Build the Schedule in Excel

Put the loan amount, annual APR, and term in separate input cells. For example, enter the principal in B1, APR as a percentage in B2, and term in years in B3. Calculate the monthly payment in B4 with a formula such as =B1*(B2/12)/(1-(1+B2/12)^-(B3*12)) if B2 is stored as a decimal such as 8%. If the cell instead contains the number 8, use B2/1200 in place of B2/12.

Create columns for payment number, beginning balance, payment, interest, principal, and ending balance. In the first payment row, the interest is beginning balance × monthly rate. Principal paid is the payment minus that interest. Ending balance is beginning balance minus principal paid; use that ending balance as the next row’s beginning balance.

1. Enter payment number 1 and the original loan amount as its beginning balance.
2. Calculate interest as beginning balance × APR ÷ 12.
3. Calculate principal as the scheduled payment minus interest.
4. Calculate the ending balance as beginning balance minus principal.
5. For each following row, carry the previous ending balance forward and repeat the calculations through the final payment.

Excel’s PMT function can also calculate a payment: =-PMT(B2/12,B3*12,B1) when APR is stored as a decimal. The minus sign makes the result display as a positive payment under the usual cash-flow sign convention. Keep the rate and payment-period units consistent: a monthly payment requires a monthly rate and a count of monthly periods.

Reading the Schedule and Vehicle-Loan Details

At the start of a standard amortizing loan, the balance is largest, so the interest portion of each payment is generally larger. As principal is repaid, later interest charges shrink and more of the same payment goes toward principal. The final balance should be near zero, though rounding can leave a few cents that need adjustment in the last row.

The amount financed is not necessarily the vehicle’s sticker price. It can reflect a down payment, trade-in value, taxes or fees, and any other items included in the loan agreement. To model the payment accurately, use the principal actually financed rather than the vehicle’s advertised price. The lender’s documents are the source for the applicable APR, amount financed, payment amount, and schedule.

A schedule also helps show why an extra payment does not always work like a higher regular payment in a simple estimate. The calculator assumes equal payments and does not model extra principal, skipped or late payments, changing rates, or lender-specific allocation rules. To estimate extra-payment effects, the schedule must include those payments and apply them to the balance according to the loan terms.

Common Spreadsheet Errors and Limits

• Entering APR in the wrong format: If a cell contains 8%, Excel stores it as 0.08. Dividing that by 12 is appropriate; dividing it by 1,200 again would understate the rate. If the cell contains 8, divide by 1,200.
• Mixing years and months: Convert a term in years to months by multiplying by 12. Do not use a monthly rate with a payment count measured in years.
• Rounding every intermediate value: Displaying two decimals does not always mean Excel calculates with only two. Decide whether to preserve full precision or round each payment and schedule row, and understand that the totals may differ slightly.
• Assuming every contract is a standard fixed-rate loan: This method does not represent variable rates, irregular payment dates, balloon payments, fees added over time, or other nonstandard terms.
• Confusing payment with full vehicle cost: The result is principal-and-interest arithmetic only. It is not a complete measure of ownership expenses such as fuel, maintenance, registration, or insurance.

These formulas are useful for understanding a repayment pattern and checking spreadsheet arithmetic. They cannot establish the exact contractual balance or the amount required to pay off a loan on a particular date; those figures can depend on transaction dates and contract details.

Frequently Asked Questions

What is a vehicle amortization schedule?
It is a table showing each scheduled payment, the interest and principal portions, and the remaining loan balance over time.

What Excel formula calculates a vehicle loan payment?
Use =-PMT(monthly_rate, number_of_months, loan_amount). For an APR stored as a decimal, monthly_rate is APR divided by 12; use years multiplied by 12 for the number of months.

Why does the interest portion decrease each month?
For a fixed-rate amortizing loan, interest is calculated on the outstanding balance. As principal is repaid, that balance falls, so less interest accrues in later periods.

Does the schedule include taxes and insurance?
No. The calculator estimates principal and interest only. Costs are reflected only if they have already been included in the amount financed, and ongoing ownership costs are not modeled.

Why does my Excel total differ by a few cents?
Excel may keep more decimal places than the displayed payment, while a lender’s schedule may round payments or interest at particular steps. Compare the underlying assumptions and rounding method.

This result is an estimate; the lender’s disclosure gives the exact figures.

Related

• Marcus Interest Rate? How Marcus Savings Rates Work
• Loan Effective Interest Rate Calculator: Find the APR
• Low Interest Rate? Calculate a Loan Payment

in p | Loan amount | $ | 20000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Loan term | years | 5 | 0.0833 | 50
out pmt | Monthly payment | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2