Auto Loan Amortization Schedule
Create a loan payment schedule using this free Excel template. Unlike other amortization spreadsheets, this one takes rounding into account, and still makes the end balance zero. You just enter the loan amount, the interest rate, the term of the loan, date of first payment, and the payment frequency. Investigate how making extra payments or balloon payments will affect when you can pay off the loan and the total interest paid. Apply to consumer loans, car loans, and home mortgages. This spreadsheet creates a payment schedule for a fixed-rate loan, with optional extra payments. The payment frequency can be annual, semi-annual, quarterly, bi-monthly, monthly, bi-weekly, or weekly. Values are rounded to the nearest cent. The last payment is adjusted to bring the balance to zero. (No macros. Requires the Analysis ToolPak add-in.) Note: This amortization schedule spreadsheet is meant for educational purposes only. We believe the calculations to be correct, but do not guarantee the results. Please consult your financial advisor or lending institution before making any final financial decisions.
Rounding
Actual payments must be rounded to the nearest cent, so this can cause rounding problems in amortization schedules. The spreadsheet rounds the monthly payment and the interest payment to the nearest cent.
Zero Balance
One of the challenges of creating a spreadsheet for amortization that accounts for rounding and extra payments is adjusting the final payment to bring the balance to zero. In this spreadsheet, the formula in the Payment Due column checks the last balance to see if a payment adjustment is needed to zero out the balance.
Additional vs. Actual Payments
One way to account for extra payments is to record any additional payments made, but this spreadsheet includes another worksheet that allows you to record the actual payment instead. (Just in case you find that to be more convenient.) For example, if the monthly payment is $300, but you pay $425, you can either record this as an additional $125, or an actual payment of $425.