Loan Amortization Schedule in Excel: PMT, IPMT and PPMT Formulas, Extra Payments and a Worked Example
Build a loan amortization schedule in Excel: the monthly payment with PMT, the interest and principal split with IPMT and PPMT or simple row formulas, a schedule that handles extra payments and stops at zero, total interest, and a worked $25,000 auto loan example.
Quick answer: a loan amortization schedule shows every payment on a loan, split into interest and principal, with the remaining balance after each payment. In Excel you need three inputs (loan amount, annual rate, number of months) and one formula for the payment:
=PMT(rate/12, months, -amount)
Each row then calculates interest as previous balance × monthly rate, principal as payment − interest, and the new balance as previous balance − principal. For a $25,000 loan at 7.5% APR over 60 months, the payment is $500.95 and total interest is $5,056.92. Adding $100 a month pays it off in 49 months and saves about $1,014 in interest. The figures are an illustration, not a loan offer.
Step 1: Inputs
| Cell | Name | Example |
|---|---|---|
| B2 | Amount | 25,000 |
| B3 | AnnualRate | 7.5% |
| B4 | Months | 60 |
| B5 | StartDate | First payment date |
| B6 | ExtraPayment | 0 (or any extra amount per month) |
| B8 | MonthlyRate | =AnnualRate/12 |
| B9 | Payment | =PMT(MonthlyRate,Months,-Amount) |
The minus sign in front of the amount makes the payment come out as a positive number. Dividing the APR by 12 is the standard convention for US consumer loans and mortgages that compound monthly; if your lender quotes rates differently, use their monthly rate.
Step 2: The schedule rows
Set up columns A to G starting in row 12: Period, Date, Beginning balance, Payment, Interest, Principal, Ending balance.
| Column | First row (row 12) | Following rows (row 13 down) |
|---|---|---|
| A Period | 1 |
=IF(G12>0,A12+1,"") |
| B Date | =StartDate |
=IF(A13="","",EDATE(B12,1)) |
| C Beginning balance | =Amount |
=IF(A13="","",G12) |
| E Interest | =ROUND(C12*MonthlyRate,2) |
same, filled down |
| D Payment | =MIN(Payment+ExtraPayment,C12+E12) |
same, filled down |
| F Principal | =D12-E12 |
same, filled down |
| G Ending balance | =C12-F12 |
same, filled down |
Wrap the formulas in columns D to G with IF($A13="","",…) and fill down for more rows than the loan term (for example 400 rows). The schedule then stops by itself when the balance reaches zero, which matters once you add extra payments. The MIN in the payment column makes the final payment exactly what is left, instead of overpaying.
Rounding interest to cents with ROUND mirrors how lenders post interest. Without it, the final balance can end a few cents away from zero.
Worked example: $25,000 at 7.5% for 60 months
| Period | Beginning balance | Payment | Interest | Principal | Ending balance |
|---|---|---|---|---|---|
| 1 | $25,000.00 | $500.95 | $156.25 | $344.70 | $24,655.30 |
| 2 | $24,655.30 | $500.95 | $154.10 | $346.85 | $24,308.45 |
| 3 | $24,308.45 | $500.95 | $151.93 | $349.02 | $23,959.43 |
| … | … | … | … | … | … |
| 60 | $497.84 | $500.95 | $3.11 | $497.84 | $0.00 |
Notice how the split changes: in month 1, $156.25 of the $500.95 goes to interest; in month 60, only $3.11 does. That is why paying extra early in a loan saves the most interest.
Summary cells:
- Total paid:
=SUM(D12:D411) - Total interest:
=SUM(E12:E411)→ $5,056.92 in this example - Number of payments:
=COUNT(A12:A411) - Payoff date:
=MAX(B12:B411)
Step 3: See the effect of extra payments
Type 100 in ExtraPayment and the schedule recalculates:
| Scenario | Monthly payment | Months to payoff | Total interest |
|---|---|---|---|
| Standard | $500.95 | 60 | $5,056.92 |
| +$100 per month | $600.95 | 49 (last payment $197.77) | $4,043.31 |
| Difference | 11 months sooner | $1,013.61 saved |
Before paying extra, check your loan agreement for prepayment penalties and make sure extra amounts are applied to principal. If you have several debts, a debt snowball spreadsheet helps decide which one to target first.
Shortcut: IPMT and PPMT
For a standard loan without extra payments, Excel can calculate the interest and principal for any period directly:
- Interest in period 12:
=IPMT(MonthlyRate,12,Months,-Amount) - Principal in period 12:
=PPMT(MonthlyRate,12,Months,-Amount) - Total interest over the first year:
=-CUMIPMT(MonthlyRate,Months,Amount,1,12,0) - Balance after 24 payments:
=-FV(MonthlyRate,24,-Payment,Amount)
These functions assume the regular payment schedule, so the row-by-row method is better once extra or irregular payments are involved. Results can differ by a few cents from the rounded schedule.
Useful variations
- Mortgage: same structure with 360 rows; add columns for property tax and insurance if they are paid through escrow, but keep them out of the interest calculation.
- Biweekly payments: 26 half-payments a year equal 13 monthly payments; model it by adding one-twelfth of a payment as an extra each month, or build a biweekly schedule with the rate divided by 26 if your lender actually applies payments every two weeks.
- Interest-only period: for the first months, set principal to zero and payment to interest only, then switch to PMT on the remaining term.
- Chart: a stacked area chart of the Interest and Principal columns shows how the mix shifts over time.
Common mistakes
- Using the annual rate in PMT without dividing by 12.
- Using years instead of months for the number of periods.
- Forgetting the sign convention, which gives negative payments.
- Hard-coding the payment so it doesn’t update when you change the rate.
- Comparing loans by payment only: a longer term lowers the payment but usually raises total interest.
Frequently asked questions
Why does my lender’s schedule differ slightly from mine?
Lenders may compute interest on a daily basis, use the actual payment dates, or round differently. Small differences of a few dollars over the life of the loan are normal; large ones usually mean a different rate, term or fee structure.
Does the schedule include fees?
No. Origination fees and other charges affect the APR and the total cost, not the payment formula itself. Add them to a separate total-cost line if you want to compare offers.
Does it work in Google Sheets?
Yes. PMT, IPMT, PPMT, CUMIPMT, FV, EDATE and MIN all exist in Google Sheets with the same syntax.
To fit the payment into your plans, add it to your monthly budget template. More guides are in Debt payoff & savings.