Amortization schedule with extra payments in Excel

Step 1: the scheduled payment and the extra

Start with the payment the lender requires. With the loan in B1, the annual rate in D1 and the term in months in F1:

=PMT(D1/12,F1,-B1) returns $674.99 a month.

Divide the annual rate by 12 because the payments are monthly, and put a minus sign in front of the loan so that PMT returns a positive payment. Then put the extra payment in its own input cell (D2, $200) and add the two in F2: $874.99 paid each month. Keeping the extra in one cell lets you test $50, $200 or $500 without touching the schedule. If you need a refresher on the plain schedule, the loan amortization schedule guide builds one without extra payments.

Step 2: one row per month, with the last payment capped

Each row has five formulas, copied down from row 4 to row 27:

• Opening balance: =$B$1 in the first row, then =F4 (last month's closing balance).
• Interest: =B4*$D$1/12, always on the balance still owed.
• Payment: =MIN($F$2,B4+D4), the planned payment, but never more than what is left plus this month's interest.
• Principal: =C4-D4. The whole extra goes here.
• Closing balance: =B4-E4.

The MIN is what makes a schedule with extra payments work. Without it, month 19 would pay the full $874.99 against a balance of only $165.05 plus $1.03 interest, the closing balance would drop to −$708.91 and every later row would keep going negative. With the cap, the last payment is exactly $166.08 and all rows after it stay at zero.

Worked example: $15,000 loan with $200 extra a month

Inputs are yellow (B1, D1, F1, D2). Row 4 is copied down to row 27. The payment is capped with MIN, so every row after the payoff in month 19 stays at zero.

Worked example: $15,000 loan with $200 extra a month

	A	B	C	D	E	F
1	Loan amount	15,000.00	Annual rate	7.50%	Term (months)	24
2	Scheduled payment	674.99	Extra per month	200.00	Paid per month	874.99
3	Month	Opening balance	Payment	Interest	Principal	Closing balance
4	1	15,000.00	874.99	93.75	781.24	14,218.76
5	2	14,218.76	874.99	88.87	786.13	13,432.63
6	3	13,432.63	874.99	83.95	791.04	12,641.59
7	4	12,641.59	874.99	79.01	795.98	11,845.61
8	5	11,845.61	874.99	74.04	800.96	11,044.65
9	6	11,044.65	874.99	69.03	805.96	10,238.68
10	7	10,238.68	874.99	63.99	811.00	9,427.68
11	8	9,427.68	874.99	58.92	816.07	8,611.61
12	9	8,611.61	874.99	53.82	821.17	7,790.44
13	10	7,790.44	874.99	48.69	826.30	6,964.13
14	11	6,964.13	874.99	43.53	831.47	6,132.67
15	12	6,132.67	874.99	38.33	836.66	5,296.00
16	13	5,296.00	874.99	33.10	841.89	4,454.11
17	14	4,454.11	874.99	27.84	847.16	3,606.95
18	15	3,606.95	874.99	22.54	852.45	2,754.50
19	16	2,754.50	874.99	17.22	857.78	1,896.72
20	17	1,896.72	874.99	11.85	863.14	1,033.58
21	18	1,033.58	874.99	6.46	868.53	165.05
22	19	165.05	166.08	1.03	165.05	0.00
23	20	0.00	0.00	0.00	0.00	0.00
24	21	0.00	0.00	0.00	0.00	0.00
25	22	0.00	0.00	0.00	0.00	0.00
26	23	0.00	0.00	0.00	0.00	0.00
27	24	0.00	0.00	0.00	0.00	0.00
28	Payoff month (payments made)					19
29	Months saved					5
30	Total interest with extra payments					915.97
31	Total interest without extra payments					1,199.85
32	Interest saved					283.88

Fictional loan, illustrative rate. Educational only, not financial advice. Check your own loan agreement for prepayment terms and fees.

Step 3: payoff month, months saved and interest saved

Three summary formulas answer the questions every borrower asks:

• Payoff month: =COUNTIF(C4:C27,">0") counts the payments that were actually made: 19.
• Months saved: term − payoff month = 24 − 19 = 5 months.
• Interest saved: the interest without extra payments (payment × term − loan = $674.99 × 24 − $15,000 = $1,199.85) minus the interest with them (=SUM(D4:D27) = $915.97) = $283.88.

You can cross-check the payoff month without the table: =ROUNDUP(NPER(D1/12,-F2,B1),0) also returns 19. NPER gives about 18.2 payments, and rounding up turns the small final payment into month 19.

Why extra payments save so much interest

Interest is charged on the balance you still owe. An extra payment does not buy anything this month: it lowers the balance, so every later month charges interest on a smaller amount. That is why the saving grows with three things: the interest rate, the time left on the loan and how early the extra starts. On a two-year car loan the effect is a few hundred dollars. On a 30-year mortgage the same logic can cut years off the term, because the early payments are mostly interest.

Monthly extra, a lump sum or a recast

The same schedule handles other prepayment patterns with one change in the payment column. A one-off lump sum, for example a $3,000 bonus in month 12, becomes =MIN($F$2+IF(A15=12,3000,0),B15+D15). Keeping the normal payment afterwards shortens the loan. Some lenders instead recast the loan: they re-amortise the lower balance over the remaining term, so the payment falls but the end date stays. A recast is a new PMT on the balance after the lump sum.

Before relying on any of this, check two things in the loan agreement: whether there is a prepayment fee, and that the lender applies the extra to principal rather than to next month's payment.

Prepaying versus keeping the cash

Paying extra is a guaranteed return equal to the loan rate, here 7.5% a year. Whether that beats other uses of the money depends on your emergency savings, other debts with higher rates and what the money would earn elsewhere. The schedule gives you the numbers to compare, not the decision. If you are comparing a whole new loan instead, see the refinance break-even guide.

Common mistakes

• Letting the balance go negative. Without the MIN cap the last payment overshoots and the schedule shows interest on money the borrower does not owe.
• Calculating interest on the original loan amount. Interest must use the opening balance of each row, otherwise the extra payments seem to save nothing.
• Using the annual rate in a monthly row. Divide the rate by 12 in both PMT and the interest column.
• Typing the extra into each row by hand. Keep it in one input cell so you can test other amounts and the summary updates automatically.
• Counting the months saved from the last row of the table. The table always has 24 rows. Count the payments that are greater than zero.
• Assuming the lender applies the extra to principal. Some apply it to future payments or charge a prepayment fee. Check before you rely on the savings.