An amortization schedule shows how each loan payment splits between interest and principal, and how the balance falls to zero. IPMT gives the interest part of any payment and PPMT the principal — build the whole table by filling them down.
The example
The first months of a $25,000 loan at 6% over 5 years (payment ≈ $483.32).
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Pmt # | Interest | Principal | Balance |
| 2 | 1 | $125.00 | $358.32 | $24,641.68 |
| 3 | 2 | $123.21 | $360.11 | $24,281.57 |
| 4 | 3 | $121.41 | $361.91 | $23,919.66 |
The formula
Interest and principal for payment number A2:
How it works
Each payment is fixed, but the split shifts over time:
- Both functions use the monthly rate (
B1/12) and total periods (B2*12), just like PMT. - The 2nd argument is the payment number (
A2) — fill A2 down 1, 2, 3… for each row. IPMTreturns that payment’s interest (high early, since the balance is large);PPMTreturns its principal (low early, growing each month).- Interest + principal always equals the fixed PMT; subtract each month’s principal from the balance to track the payoff.
Running balance: start with the loan amount, then each row is previous balance + PPMT(…) (PPMT is negative, so it reduces the balance). The final row lands on exactly $0.
Try it: interactive demo
$25,000 loan, 6%, 5 yrs. Pick a payment number to see its split.
Variations
Total interest paid
Sum all interest, or use CUMIPMT:
Total principal over a range
CUMPRINC for payments 1–12:
The fixed payment
The PMT every row sums to:
Pitfalls & errors
Rate/period mismatch. Monthly schedules need rate/12 and years*12 in every function — PMT, IPMT, and PPMT must all agree.
Signs. With a positive loan amount, IPMT/PPMT return negative values (cash out). Negate them or enter the loan as negative for positive figures.
Period must be valid. The payment number has to be between 1 and the total periods, or you get an error.
Practice workbook
Frequently asked questions
How do I build a loan amortization schedule in Excel?
How do I find total interest paid over a loan?
Why are IPMT and PPMT negative?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class