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

Taught hands-on in Financial Modeling · Financial Functions Toolkit module

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

=PPMT(rate, per, nper, pv, [fv], [type])

Arguments

ArgumentRequired?What it does
rateYesThe interest rate per period (annual rate ÷ 12 for monthly payments).
perYesWhich period you want, from 1 to nper.
nperYesThe total number of payment periods.
pvYesThe present value — the loan amount, or what a series of payments is worth today.
fvNoThe balance you want left after the last payment. Omitted = 0 (a loan paid off in full).
typeNoWhen 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.

B6fx=PPMT($B$1/12,A6,$B$2,$B$3)
AB
1Annual rate6.5%
2Months60
3Loan amount$45,000
4
5MonthPrincipal
61-$636.73
72-$640.18
83-$643.64
960-$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.

=$B$3+SUM($C$6:C6) Explain
CUMPRINC gives the same running total without a helper column.

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

Kyle Stott

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.