Excel like a finance pro.
The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.
Amortization schedule (PMT · IPMT · PPMT)
FinanceBuild a full loan payoff schedule — principal vs. interest, month by month.
Difficulty
1What is it?
An amortization schedule breaks a fixed-rate loan into its payments and splits each one into interest and principal. PMT gives the level payment; IPMT returns the interest portion of a given payment; PPMT returns the principal portion. Chain them down the months and the balance walks to zero — so you can see total interest, your payoff date, and how much an extra payment saves. QuickBooks tracks the loan balance but never shows you this split; Excel does.
2What it looks like
PMT(rate, nper, pv) · IPMT(rate, per, nper, pv) · PPMT(rate, per, nper, pv)- rate
- The rate per period — annual rate ÷ 12 for a monthly schedule.
- nper
- Total number of payments (e.g. 60 for a 5-year monthly loan).
- per
- Which payment you're splitting — 1 for the first month, 2 for the next… (IPMT / PPMT only).
- pv
- The loan amount / present value — enter it negative so the results come back positive.
3When you use it
- See how much of each payment is interest vs. principal.
- Total the interest you'll pay over the life of a loan.
- Test how an extra monthly payment shortens the term and cuts interest.
- Drop a debt schedule into a 3-statement model.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Payment / mo
$3,114
level payment
Total interest
$36,825
Total paid
$186,825
Paid off in
5 yr
60 payments
Payment schedule
| # | Payment | Interest | Principal | Balance |
|---|---|---|---|---|
| 1 | $3,114 | $1,125 | $1,989 | $148,011 |
| 2 | $3,114 | $1,110 | $2,004 | $146,008 |
| 3 | $3,114 | $1,095 | $2,019 | $143,989 |
| 4 | $3,114 | $1,080 | $2,034 | $141,955 |
| 5 | $3,114 | $1,065 | $2,049 | $139,906 |
| 6 | $3,114 | $1,049 | $2,064 | $137,841 |
| 7 | $3,114 | $1,034 | $2,080 | $135,762 |
| 8 | $3,114 | $1,018 | $2,096 | $133,666 |
| 9 | $3,114 | $1,002 | $2,111 | $131,555 |
| 10 | $3,114 | $987 | $2,127 | $129,428 |
| 11 | $3,114 | $971 | $2,143 | $127,285 |
| 12 | $3,114 | $955 | $2,159 | $125,125 |
| 13 | $3,114 | $938 | $2,175 | $122,950 |
| 14 | $3,114 | $922 | $2,192 | $120,759 |
| 15 | $3,114 | $906 | $2,208 | $118,550 |
| 16 | $3,114 | $889 | $2,225 | $116,326 |
| 17 | $3,114 | $872 | $2,241 | $114,085 |
| 18 | $3,114 | $856 | $2,258 | $111,826 |
| 19 | $3,114 | $839 | $2,275 | $109,551 |
| 20 | $3,114 | $822 | $2,292 | $107,259 |
| 21 | $3,114 | $804 | $2,309 | $104,950 |
| 22 | $3,114 | $787 | $2,327 | $102,623 |
| 23 | $3,114 | $770 | $2,344 | $100,279 |
| 24 | $3,114 | $752 | $2,362 | $97,918 |
| 25 | $3,114 | $734 | $2,379 | $95,538 |
| 26 | $3,114 | $717 | $2,397 | $93,141 |
| 27 | $3,114 | $699 | $2,415 | $90,726 |
| 28 | $3,114 | $680 | $2,433 | $88,292 |
| 29 | $3,114 | $662 | $2,452 | $85,841 |
| 30 | $3,114 | $644 | $2,470 | $83,371 |
| 31 | $3,114 | $625 | $2,488 | $80,883 |
| 32 | $3,114 | $607 | $2,507 | $78,375 |
| 33 | $3,114 | $588 | $2,526 | $75,849 |
| 34 | $3,114 | $569 | $2,545 | $73,305 |
| 35 | $3,114 | $550 | $2,564 | $70,741 |
| 36 | $3,114 | $531 | $2,583 | $68,157 |
| 37 | $3,114 | $511 | $2,603 | $65,555 |
| 38 | $3,114 | $492 | $2,622 | $62,933 |
| 39 | $3,114 | $472 | $2,642 | $60,291 |
| 40 | $3,114 | $452 | $2,662 | $57,629 |
| 41 | $3,114 | $432 | $2,682 | $54,948 |
| 42 | $3,114 | $412 | $2,702 | $52,246 |
| 43 | $3,114 | $392 | $2,722 | $49,524 |
| 44 | $3,114 | $371 | $2,742 | $46,782 |
| 45 | $3,114 | $351 | $2,763 | $44,019 |
| 46 | $3,114 | $330 | $2,784 | $41,236 |
| 47 | $3,114 | $309 | $2,804 | $38,431 |
| 48 | $3,114 | $288 | $2,826 | $35,605 |
| 49 | $3,114 | $267 | $2,847 | $32,759 |
| 50 | $3,114 | $246 | $2,868 | $29,891 |
| 51 | $3,114 | $224 | $2,890 | $27,001 |
| 52 | $3,114 | $203 | $2,911 | $24,090 |
| 53 | $3,114 | $181 | $2,933 | $21,157 |
| 54 | $3,114 | $159 | $2,955 | $18,202 |
| 55 | $3,114 | $137 | $2,977 | $15,225 |
| 56 | $3,114 | $114 | $3,000 | $12,225 |
| 57 | $3,114 | $92 | $3,022 | $9,203 |
| 58 | $3,114 | $69 | $3,045 | $6,158 |
| 59 | $3,114 | $46 | $3,068 | $3,091 |
| 60 | $3,114 | $23 | $3,091 | $0 |
Early payments are mostly interest; the split flips toward principal as the balance falls. Interest is the cost of the loan — principal is what actually pays it down.
5Common errors
Wrong signpv was entered positive.Fix: Enter the loan as negative (or negate the result) so payment, interest, and principal come back positive.
Doesn't foot to zerorate and per/nper mix period lengths.Fix: Keep it all monthly: rate = annual ÷ 12, and per and nper counted in months.
#NUM!per is 0 or larger than nper.Fix: per runs from 1 to nper — one row per payment.
6Better functions & alternatives
- PMT — Just the level payment, without the interest/principal split.
- CUMIPMT / CUMPRINC — Total the interest or principal between two payment numbers in one call.
- XNPV / XIRR — For valuing uneven, dated cash flows rather than a level loan.
Want Amortization schedule (PMT · IPMT · PPMT) already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Get my free report →