PPMT Function
Returns how much of a given loan payment pays down the balance.
Category
Financial
Available in
All versions, Microsoft 365
Returns
The principal part of one payment
Overview
PPMT returns the principal portion of one payment — the part that actually reduces what you owe. It grows each period as the interest portion shrinks.
PPMT + IPMT for the same period always equals the PMT payment.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| rate | Yes | The interest rate per period (annual rate ÷ 12 for monthly payments). |
| per | Yes | Which period you want, from 1 to nper. |
| nper | Yes | The total number of payment periods. |
| pv | Yes | The present value — the loan amount, or what a series of payments is worth today. |
| fv | No | The balance you want left after the last payment. Omitted = 0 (a loan paid off in full). |
| type | No | When payments fall: 0 or omitted = end of each period, 1 = start of each period. |
Examples
Example 1 — Principal paid each month
The same $45,000 van loan. The principal part rises as interest falls.
| A | B | |
|---|---|---|
| 1 | Annual rate | 6.5% |
| 2 | Months | 60 |
| 3 | Loan amount | $45,000 |
| 4 | ||
| 5 | Month | Principal |
| 6 | 1 | -$636.73 |
| 7 | 2 | -$640.18 |
| 8 | 3 | -$643.64 |
| 9 | 60 | -$875.73 |
Month 1: $636.73 principal + $243.75 interest = the $880.48 payment.
Example 2 — Building an amortization schedule
Put periods 1–60 in column A, then use PMT, IPMT and PPMT side by side. The running balance is the loan plus the cumulative principal.
Common mistakes
- Rate and periods in different units. Keep them matched: for monthly payments divide the annual rate by 12 and multiply the years by 12. Mixing an annual rate with monthly periods gives wildly wrong answers.
- Total principal doesn't equal the loan. Check every row uses the same rate and nper with absolute references ($B$1), so they don't shift when filled down.
Notes

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.