What is on the loan amortization schedule
- Loan amount, annual rate, term
- The three inputs. Rate is the annual percentage; the sheet divides by 12 for the monthly rate. Term is in months (48 for four years).
- Payment
- The fixed monthly instalment, calculated with the standard annuity formula (Excel's PMT). The same every month on a fixed-rate loan.
- Interest
- Last month's balance times the monthly rate. High at the start, shrinking every month.
- Principal
- Payment minus interest: the part that reduces the debt. Low at the start, growing every month.
- Balance
- Previous balance minus principal. Zero at the end of the term if every payment is made on time.
- Extra payment
- Anything paid above the scheduled amount. It goes entirely to principal, so every following month's interest is lower and the loan ends early.
- Total paid, total interest and fees (comparison)
- Payment times months plus fees, and that total minus the amount borrowed. The honest cost of each offer.
How amortization works
A fixed-rate instalment loan (car loan, personal loan, mortgage, most student loans) is repaid in equal payments. Each payment first covers the interest that accrued on the outstanding balance since the last payment; whatever is left reduces the balance. Because the balance is largest at the start, early payments are mostly interest and late payments are mostly principal. On a $18,500 car loan at 6.9% over 48 months, the payment is about $442; the first payment is $106 of interest and $336 of principal, the last is about $2.50 of interest. Total interest over the loan is a little over $2,700.
Seeing this on a schedule changes how people think about extra payments. $300 paid in month 3 removes $300 of principal, which removes the interest that principal would have generated every remaining month. On the loan above, seven extra payments of $300 in the first two years cut roughly four months off the term and several hundred dollars off the interest. The extra-payments design shows this row by row.
Which sheet to use
- Monthly schedule: one loan, see the plan. Print it and tick payments off, or keep the Excel sheet and watch the balance line.
- With extra payments: the same loan when you intend to pay it down early. Enter extras as you make them.
- Compare three offers: before you sign. Monthly payment is what the salesperson quotes; total interest and fees is what you actually pay.
How to use the Excel version
- Enter the loan amount, the annual rate as a percentage (6.9, not 0.069) and the term in months in the boxes at the top.
- The payment, every row and the totals calculate. Rows after the payoff stay blank.
- For a loan longer than 24 months, copy the sheet to a new tab and type the previous page's closing balance into the first row's opening balance (or extend the table by copying the last row down).
- On the extra-payment sheet, type extras in the Extra column as you pay them. The balance and the following months update.
- Check your lender's statement against the schedule once or twice a year. If the lender's balance is higher than yours, ask why (fees, a late payment, or a payment applied to interest only).
Mistakes that cost money on instalment loans
- Choosing the offer with the lowest monthly payment. A longer term lowers the payment and raises the total interest; the comparison sheet shows both.
- Ignoring fees. An origination fee of 2% on a four-year loan can cancel out a half-point lower rate.
- Making extra payments without telling the lender to apply them to principal. Some lenders apply them to next month's instalment instead, which saves nothing.
- Prepayment penalties. Check before paying extra.
- Not keeping your own schedule, so an error in the lender's statement goes unnoticed.
Reading an APR
In the US, lenders must quote the APR (annual percentage rate) under the Truth in Lending Act. For a simple instalment loan with no fees the APR equals the interest rate; with fees, the APR is higher because it spreads the fees across the term. The sheets here use the interest rate for the payment calculation and add fees separately, which is how the totals line up with the lender's disclosure. For credit cards, which are not amortizing loans, use the credit card payoff planner instead; for several debts at once, the debt payoff tracker.
The Word and PDF versions
The PDF is a fillable printable schedule: fill it from your lender's amortization table or from the Excel sheet, then tick each payment as it is made. It does not calculate (PDF forms cannot run PMT reliably across readers). The Word version is the same table for anyone who wants to paste figures into a document. The ODS file carries the same formulas as the Excel file for LibreOffice users.
Questions
How is the monthly payment calculated?
With the annuity formula: payment = P × r / (1 − (1 + r)^−n), where P is the amount, r the monthly rate (annual rate ÷ 12) and n the number of months. In Excel that is PMT(rate/12, months, amount). The sheets do this for you.
Why is so much of my early payment interest?
Because interest is charged on the outstanding balance, which is highest at the start. As the balance falls, the interest share falls and the principal share rises, even though the payment stays the same.
Do extra payments really save much?
On a 48-month car loan at 6.9%, an extra $100 a month saves several hundred dollars of interest and ends the loan about half a year early. On a 30-year mortgage the effect is far larger. The extra-payment sheet shows your own numbers.
Does the PDF calculate?
No. The PDF is a fillable printable schedule. The Excel and ODS files calculate every row from the amount, rate and term.
Can I use this for a mortgage?
Yes, for a fixed-rate mortgage. Enter the term in months (360 for 30 years) and extend the table across sheets. Adjustable-rate loans change rate over time, so recalculate from the new rate at each reset.
