Loan amortization in Excel: PMT, month-by-month interest, and extra payments

The four inputs and one formula

Amount borrowed, annual rate, term in years, first payment date. The monthly payment is Excel’s PMT with the rate and the term converted to months:

=-PMT(B4/12, B5*12, B3)      ← rate per month, number of months, amount

PMT returns a negative number (money leaving you); the minus sign in front flips it. For $250,000 at 5.9% over 25 years that’s $1,595.51. Total interest over the scheduled life is payment × months − amount.

One row of the schedule

Five columns: payment number, date, payment, interest, principal, balance. The first row reads the loan amount as its “previous balance”; every row after reads the row above.

A12  #:          =IF($B$3<=0, "", 1)
B12  Date:       =IF(A12="", "", EDATE($B$6, 0))
F12  Interest:   =IF(A12="", 0, ROUND($B$3 * $B$4/12, 2))
C12  Payment:    =IF(A12="", 0, MIN($E$3, $B$3 + F12))
D12  Principal:  =IF(A12="", 0, C12 - F12)
G12  Balance:    =IF(A12="", 0, ROUND($B$3 - D12, 2))

A13:  =IF(G12<=0, "", 2)
B13:  =IF(A13="", "", EDATE($B$6, 1))
F13:  =IF(A13="", 0, ROUND(G12 * $B$4/12, 2))
C13:  =IF(A13="", 0, MIN($E$3, G12 + F13))
D13:  =IF(A13="", 0, C13 - F13)
G13:  =IF(A13="", 0, ROUND(G12 - D13, 2))

Three details carry the whole thing:

• Interest is previous balance × rate ÷ 12, rounded to cents. That’s how a lender computes it. IPMT would give an unrounded theoretical figure that drifts from the statement by a few cents.
• The payment is MIN(scheduled, balance + interest). On the last row the balance plus interest is less than a full payment; MIN takes the smaller, so the balance lands on exactly 0.00 instead of −3 cents.
• The row number appears only while the previous balance is above zero. That’s how the schedule stops itself. Fill the formulas down 480 rows (40 years) and forget about them.

Zero, not blank

The money columns return 0 on rows past the end, and the number format #,##0.00;-#,##0.00;;@ hides zeros. If they returned "", SUM over the interest column would still work in Excel — but (previous balance − principal) in the next row would not, and neither would a recalculation check. Keep numeric columns numeric.

Extra payments

Add a column, Extra, that you type into on any month, and a cell for a monthly extra in the inputs. The payment formula becomes:

=IF(A13="", 0, MIN($E$3 + $B$7 + N(D13),  G12 + F13))
                        ↑ scheduled  ↑ monthly extra  ↑ one-off this row     ↑ never more than owed

N(D13) turns an empty Extra cell into 0. Nothing else changes: interest is still on the previous balance, principal is still payment minus interest, and the schedule still stops at zero — just sooner. Three summary formulas tell you what the extras bought:

Payments made:   =COUNTIF(G12:G491, ">0") + 1
Total interest:  =SUM(F12:F491)
Interest saved:  =scheduled total interest − that
Paid off:        =INDEX(B12:B491, payments made)

Comparing rates and terms

Once PMT is in one cell, a grid is cheap: rates down the side, terms across the top, and =-PMT($A5/12, B$4*12, $B$3) in each cell with mixed references. A second grid of payment × months − amount shows total interest. Highlight the row matching your rate with a conditional-format rule =ABS($A5-$B$4)<0.0001.

Fortnightly or weekly

Change two numbers: the rate divisor (26 or 52 instead of 12) and the period count (years × 26), and use $B$6 + 14 × (n−1) instead of EDATE for the dates. The schedule’s logic is the same.

Principal is this schedule built out to 480 rows with the extras column, the comparison grids and a sheet explaining each formula; the free version is the plain schedule.