Skip to content

Excel function · Financial

Excel PPMT function

PPMT tells you how much a given payment on an equal-payment loan reduces the debt, that is, its principal part. Together with the interest part it equals the payment; it is used to track the remaining balance.

In Turkish Excel: ANA_PARA_ÖDEMESİ

What it does

The principal part of a payment is the amount taken off the debt. It is small at the start of the loan and large at the end: interest is charged on the remaining balance, so as the balance falls, more of each payment goes to principal.

The arguments are the same as for IPMT. For the same payment, IPMT + PPMT = PMT always holds, which is a good check when building a schedule. By the sign rule, a positive loan amount gives a negative principal part.

For the total principal paid up to a given payment, use CUMPRINC; the remaining balance is the loan amount plus that (negative) total. For the payment itself use PMT, and for the present value PV.

Syntax

English Excel=PPMT(rate, per, nper, pv, [fv], [type])
Turkish Excel=ANA_PARA_ÖDEMESİ(oran; dönem; dönem_sayısı; bd; [gd]; [tür])

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
rateRequiredThe interest rate per period, e.g. 0.09/12 for monthly payments.
perRequiredThe number of the payment whose principal part you want; between 1 and nper.
nperRequiredThe total number of payments.
pvRequiredThe loan amount, positive as money you receive.
fvOptionalThe amount left after the last payment; leave it out for a loan.
typeOptional0 or omitted: payments at the end of each period. 1: at the start.

Examples

1A six-payment schedule with the remaining balance

The loan details are in column H. Columns B, C and D give the payment, interest and principal; column E adds the (negative) principal paid so far to the loan amount to get the remaining balance.

B2fx=PMT($H$3/12,$H$4,$H$2)
A six-payment schedule with the remaining balance: example table
ABCDEFGH
1Payment NoPaymentInterestPrincipalBalanceItemValue
21-2,052.83-90.00-1,962.8310,037.17Loan Amount12,000.00
32-2,052.83-75.28-1,977.558,059.63Annual Rate9.00%
43-2,052.83-60.45-1,992.386,067.25Term (Months)6
54-2,052.83-45.50-2,007.324,059.92
65-2,052.83-30.45-2,022.382,037.55
76-2,052.83-15.28-2,037.550.00
  • B2 (filled down to B7)=PMT($H$3/12,$H$4,$H$2)
  • C2 (filled down to C7)=IPMT($H$3/12,A2,$H$4,$H$2)
  • D2 (filled down to D7)=PPMT($H$3/12,A2,$H$4,$H$2)
  • E2 (filled down to E7)=$H$2+SUM($D$2:D2)

On every row, interest plus principal equals the payment. The principal part starts at -1,962.83 and ends at -2,037.55; after the last payment the balance is 0.00.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2The balance left after given payments on a long loan

Column E gives the principal part of the chosen payment. Column F adds the total principal paid up to that payment to the loan amount with CUMPRINC.

E2fx=PPMT($B$3/12,D2,$B$4,$B$2)
The balance left after given payments on a long loan: example table
ABCDEF
1ItemValuePayment NoPrincipal PartBalance
2Loan Amount300,000.0012-457.32294,659.88
3Annual Rate6.00%150-910.20203,631.41
4Term (Months)300288-1,811.5622,458.28
  • E2 (filled down to E4)=PPMT($B$3/12,D2,$B$4,$B$2)
  • F2 (filled down to F4)=$B$2+CUMPRINC($B$3/12,$B$4,$B$2,1,D2,0)

Only -457.32 of payment 12 reduces the debt, and after a year the balance is still 294,659.88. Halfway through, after payment 150, the balance is 203,631.41: in the early years most of each payment goes to interest.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#NUM!

Cause: per is less than 1 or greater than the number of payments.

Fix: Give a payment number between 1 and nper.

Totals do not match

Cause: The interest and principal columns point at different rate or term cells, so they do not add up to the payment.

Fix: Use the same rate, term and amount cells, locked with $, in all three formulas.

Negative result

Cause: The loan amount was positive, so the principal part comes back negative.

Fix: Put a minus sign in front of the formula to see it as positive.

Good to know

  • The remaining balance in a schedule: =$H$2+SUM($D$2:D2); the principal parts are negative, so adding them to the loan amount brings the balance down.
  • To get the balance after a given payment in one formula: =B2+CUMPRINC(B3/12,B4,B2,1,D2,0).

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add two columns to my repayment schedule showing the principal part of each payment and the remaining balance

Convert to table

Bring your schedule into the tool and type this. Nexell adds both columns with real Excel formulas and shows them in the preview; in the downloaded file, the whole plan recalculates when the loan amount changes.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I calculate the remaining balance on a loan?

The balance after payment n is the loan amount plus the (negative) total of principal paid so far: =B2+CUMPRINC(B3/12,B4,B2,1,n,0). =-FV(rate,n,payment,loan_amount) gives the same result, with the payment entered as a negative number.

Related functions

All financial functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free