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
=PPMT(rate, per, nper, pv, [fv], [type])=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
| Argument | Required | Description |
|---|---|---|
rate | Required | The interest rate per period, e.g. 0.09/12 for monthly payments. |
per | Required | The number of the payment whose principal part you want; between 1 and nper. |
nper | Required | The total number of payments. |
pv | Required | The loan amount, positive as money you receive. |
fv | Optional | The amount left after the last payment; leave it out for a loan. |
type | Optional | 0 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.
=PMT($H$3/12,$H$4,$H$2)| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Payment No | Payment | Interest | Principal | Balance | Item | Value | |
| 2 | 1 | -2,052.83 | -90.00 | -1,962.83 | 10,037.17 | Loan Amount | 12,000.00 | |
| 3 | 2 | -2,052.83 | -75.28 | -1,977.55 | 8,059.63 | Annual Rate | 9.00% | |
| 4 | 3 | -2,052.83 | -60.45 | -1,992.38 | 6,067.25 | Term (Months) | 6 | |
| 5 | 4 | -2,052.83 | -45.50 | -2,007.32 | 4,059.92 | |||
| 6 | 5 | -2,052.83 | -30.45 | -2,022.38 | 2,037.55 | |||
| 7 | 6 | -2,052.83 | -15.28 | -2,037.55 | 0.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.
=PPMT($B$3/12,D2,$B$4,$B$2)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Value | Payment No | Principal Part | Balance | |
| 2 | Loan Amount | 300,000.00 | 12 | -457.32 | 294,659.88 | |
| 3 | Annual Rate | 6.00% | 150 | -910.20 | 203,631.41 | |
| 4 | Term (Months) | 300 | 288 | -1,811.56 | 22,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
Add two columns to my repayment schedule showing the principal part of each payment and the remaining balance
Convert to tableBring 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.