Skip to content

SheetRoutine

Spreadsheet tutorials for Excel, Google Sheets and LibreOffice. Each one ends with a working file, and every number in it was checked.

Build a loan amortization schedule in Excel with PMT, IPMT and PPMT

Three inputs, three functions, and a 60-row schedule that ends at zero. The loan in this tutorial is $25,000 at 6.5% annual interest over 5 years, paid monthly. Change any input and the whole schedule updates.

Checked
AppsExcel, Google Sheets, LibreOffice
VersionsLibreOffice 24.2.7.2 (recalculation) · Python 3.12.3 · openpyxl 3.1.2
Read time8 minutes

Measured result — $25,000 at 6.5% over 5 years

Monthly payment$489.15
Total paid over 60 payments$29,349.22
Total interest$4,349.22
Balance after the last payment$0.00
Rows matching the independent computation (within $0.01)60 of 60

Download the spreadsheet: loan-amortization-schedule.xlsx (7,234 bytes)

The file that was recalculated and checked. It contains the inputs, the formulas below, the 60-row schedule, and three check cells at the bottom.

1What you need

2Enter the three inputs

Put the loan terms in cells B2 to B4. Everything else in the workbook reads from these three cells, so changing the loan means changing one number, not sixty formulas.

CellLabelValue
B2Loan amount$25,000.00
B3Annual interest rate6.50%
B4Term (years)5

3The monthly payment, with PMT

In B5, enter the payment formula. PMT function, Microsoft Support defines PMT as the payment for a loan based on constant payments and a constant interest rate, with the syntax PMT(rate, nper, pv, [fv], [type]).

=PMT(B3/12, B4*12, -B2)

The rate and the number of periods have to use the same unit: the annual rate is divided by 12 for a monthly rate, and the term in years is multiplied by 12 for the number of monthly payments. The principal is negated (-B2) so the payment comes out positive instead of negative; PMT returns cash paid out as a negative number by default. Result: $489.15 per month.

4Totals from the payment

Two more cells, both reading from B5:

B6  =B5*B4*12     Total paid: $29,349.22
B7  =B6-B2        Total interest: $4,349.22

Microsoft's PMT page notes the same rule: multiply the payment by the number of periods to find the total paid over the life of the loan. PMT covers principal and interest only — no taxes, reserve payments, or fees.

5The schedule headers

In row 9, enter five headers: Payment, Payment, Interest, Principal, Balance. Rows 10 to 69 hold payments 1 to 60.

6Row 1: split the first payment with IPMT and PPMT

IPMT returns the interest part of a payment for a given period; PPMT returns the principal part. Both take the same arguments as PMT plus the period number. In row 10:

A10  1
B10  =$B$5
C10  =IPMT($B$3/12, $A10, $B$4*12, -$B$2)
D10  =PPMT($B$3/12, $A10, $B$4*12, -$B$2)
E10  =$B$2-D10

Payment 1 works out as interest $135.42, principal $353.74, balance $24,646.26. The balance formula for the first row subtracts the principal from the loan amount; every row after that subtracts from the previous balance.

7Fill the schedule down to payment 60

Copy row 10 down to row 69, changing only the balance formula: from row 11 on, the balance is the previous row's balance minus this row's principal.

E11  =E10-D11

Because the payment, rate and term cells are locked with $ and only the period number ($A10) moves, the copied formulas stay correct. Three rows from the checked schedule:

PaymentPaymentInterestPrincipalBalance
1$489.15$135.42$353.74$24,646.26
30$489.15$75.42$413.73$13,510.67
60$489.15$2.64$486.52$0.00

Interest falls and principal rises as the balance shrinks. By payment 30 the interest is down to $75.42; the last payment is almost all principal.

8Three checks at the bottom

Below the schedule, three cells prove the schedule balances:

Sum of principal   =SUM(D10:D69)   $25,000.00
Sum of interest    =SUM(C10:C69)   $4,349.22
Final balance      =E69            $0.00

If the sum of principal is not the loan amount, or the final balance is not zero, a formula was copied wrong. In the checked file the final balance is $0.00 to the cent; the unrounded value is 0.0000000000207, a floating-point remainder, not a real amount.

9Change the inputs and watch it update

Try $30,000 at 7.5% over 4 years: the payment cell updates to $725.37 (checked the same way — Python computation and LibreOffice recalculation agree). But the schedule still has 60 rows for a 48-payment loan, and rows 49 to 60 show errors: IPMT and PPMT only accept a period number from 1 to the number of payments, so period 49 of 48 is out of range. That is expected. For a different term, rebuild the schedule with the new number of rows.

Traps

Rate and periods must match. An annual rate with a term in years gives a yearly payment, not a monthly one. Divide the rate by 12 and multiply the years by 12, every time.

The sign. PMT, IPMT and PPMT return negative numbers when the principal is positive. Negate the principal in the formula, or put a minus sign in front of the whole formula — but do it once, consistently, or the balance column runs the wrong way.

A lender's statement will differ by cents. These functions work on unrounded amounts. A lender rounds each payment and each interest charge to the cent and adjusts the final payment, so a statement can differ from this schedule by a few cents. That is a rounding difference, not an error in the formulas.

How this was checked

The workbook was built with openpyxl 3.1.2 (formulas only, no typed-in results), recalculated by LibreOffice 24.2.7.2 in headless mode, and read back with openpyxl in data-only mode. The expected values were computed independently in Python 3.12.3 from the same three inputs, without using the spreadsheet formulas. All 60 rows matched within $0.01; the sum of principal was exactly $25,000.00 and the final balance $0.00.

Sources