calculate-loan

01 Loan payments

Vehicle Loan Amortization Schedule Excel

Calculator No. 01-23515 · Updated · Formula and assumptions shown below

The calculator on this page is switched off while it is checked. The formula is below, and the worked example shows how to do the sum by hand.

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

This calculator builds a vehicle loan amortization schedule in Excel: enter the loan amount, APR, and term, and it returns the monthly payment and total interest. You can then replicate the schedule row by row in a spreadsheet using the formulas shown below. The result is a full breakdown of principal and interest for every payment.

Formula

The monthly payment for a fixed-rate loan is calculated with the standard amortization formula:

M = P × [ r(1 + r)^n ] / [ (1 + r)^n – 1 ]

Where:

  • M = monthly payment (in $)
  • P = principal loan amount (in $)
  • r = monthly interest rate = APR / 12 / 100 (as a decimal, e.g., 8% APR → 0.08/12 = 0.0066667)
  • n = total number of payments = term in years × 12

To build the amortization schedule, for each payment k (from 1 to n):

  • Interest for month k = remaining balance before payment × r
  • Principal paid in month k = M – interest for month k
  • New balance = previous balance – principal paid

After the last payment, the balance should be $0.00 (up to rounding).

Worked Example

Using the default values: P = $20,000, APR = 8%, term = 5 years.

  1. Monthly rate: r = 8 / 1200 = 0.0066667
  2. Number of payments: n = 5 × 12 = 60
  3. Payment: M = 20000 × (0.0066667 × (1.0066667)^60) / ((1.0066667)^60 – 1)
  4. (1.0066667)^60 ≈ 1.48985
  5. M = 20000 × (0.0066667 × 1.48985) / (1.48985 – 1) = 20000 × (0.0099323) / (0.48985) ≈ 20000 × 0.020276 = 405.52

The calculator gives a monthly payment of $405.52 (rounded to the cent). Total interest = 60 × 405.52 – 20000 = 24331.20 – 20000 = $4,331.20.

Assumptions

  • Fixed interest rate for the entire term (no rate changes).
  • Payments are made at the end of each month (ordinary annuity).
  • Interest compounds monthly at APR/12.
  • No fees, taxes, down payment, trade-in, or rebates are included unless you add them to the loan amount.
  • The payment is rounded to the cent; the schedule uses that rounded payment, so the final balance may be a few cents off.

How to Build the Schedule in Excel

Once you have the monthly payment, you can create the amortization table yourself:

  1. Set up columns: Payment #, Payment, Interest, Principal, Balance.
  2. Row 1: Balance = loan amount (e.g., 20000).
  3. For each row (payment 1 to 60):
    • Interest = previous balance × (APR/1200).
    • Principal = payment – interest.
    • New balance = previous balance – principal.
  4. Copy the formula down for all 60 rows.
  5. Use Excel’s IPMT and PPMT functions as a shortcut: =IPMT(rate, per, nper, pv) and =PPMT(rate, per, nper, pv).

For example, in Excel, with rate in B1 (0.08/12), nper in B2 (60), pv in B3 (20000), the payment formula is =PMT(B1, B2, B3). For the interest in month 1: =IPMT(B1, 1, B2, B3). For principal: =PPMT(B1, 1, B2, B3).

Common Mistakes and Misconceptions

  • Using APR as the monthly rate: Always divide by 12. Using 8% instead of 0.6667% will overstate interest hugely.
  • Forgetting to convert years to months: Term must be in months (years × 12).
  • Ignoring rounding: The payment is rounded to cents, so the final balance may be slightly off; adjust the last payment to zero out.
  • Including fees in the loan amount: If you roll fees into the loan, they are part of the principal and accrue interest. The calculator’s loan amount should be the total financed.
  • Thinking the payment is interest-only: Early payments are mostly interest, but principal is always being paid.

How to Use the Schedule to Save Money

An amortization schedule is useful for planning extra payments. If you pay an extra $50 per month, you can see how much sooner the loan ends and how much interest you save. To model this in Excel, add an extra payment column and subtract it from the balance each month. The schedule will show the loan term shortening. However, check with your lender that there is no prepayment penalty and that extra payments are applied to principal, not to future payments.

Limitations of the Calculator

This calculator assumes a simple fixed-rate loan. Real vehicle loans may have:

  • Variable rates (rare for auto loans, but possible).
  • Fees (origination, documentation) that are not included.
  • Balloon payments or different payment frequencies (biweekly).
  • Early payoff penalties (uncommon but check your contract).

For biweekly payments, you can divide the monthly payment by 2 and pay that every two weeks; this results in 26 half-payments per year, which is equivalent to 13 full payments, reducing the term and interest. The calculator does not model that directly, but you can simulate it by adjusting the payment amount and term.

Frequently Asked Questions

Q: Can I use this calculator for a used car loan?
A: Yes, the calculator works for any fixed-rate loan, new or used. Just enter the loan amount, APR, and term. The age of the car does not affect the math.

Q: How do I include a down payment?
A: Subtract the down payment from the car price to get the loan amount. For example, if the car costs $25,000 and you put $5,000 down, enter $20,000 as the loan amount.

Q: What is the difference between APR and interest rate?
A: APR includes fees and other costs, expressed as a yearly rate. The interest rate is just the cost of borrowing. For amortization, use the APR if it reflects the true cost; otherwise, use the interest rate. Lenders are required to disclose the APR.

Q: How do I calculate the total cost of the loan?
A: Multiply the monthly payment by the number of months. That total includes principal and interest. Add any down payment and fees to get the total cost of the car.

Q: Why does my Excel schedule not match the calculator exactly?
A: Rounding differences. The calculator rounds the payment to cents, and Excel may keep more decimals. Also, check that you are using the same rate (APR/12) and term (months). Minor differences of a few cents are normal.

Q: Can I use this for a lease?
A: No, leases have a residual value and money factor, not a standard amortization. This calculator is for loans where you own the vehicle at the end.

This result is an estimate for educational purposes; your lender’s disclosure will provide the exact payment and total cost.

Related

Formula used
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

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.

More in this section

The whole Loan payments section →