IPMT vs PPMT: Interest and Principal in Excel

IPMT vs PPMT comes down to which half of a loan payment you want. IPMT returns the interest part of one payment, and PPMT returns the principal part, the money that actually reduces what you owe. Add them together and you get the full payment from PMT. This guide compares both on the same loan, so you can build an amortization schedule or answer a finance test question with confidence.

IPMT vs PPMT at a glance#

Both functions split the same fixed payment. They differ only in which part they return.

The rule that ties them together: IPMT + PPMT = PMT for every period.

The arguments, explained once#

IPMT and PPMT use the same arguments as PMT, plus one extra: per, the number of the payment you're asking about.

• rate: the interest rate per period. For monthly payments on a 6% annual rate, use 6%/12.
• per: which payment, from 1 to nper. Payment 1 is the first month.
• nper: the total number of payments. A 3-year monthly loan has 36.
• pv: the loan amount today (present value), such as 20,000.
• fv (optional): a balance left after the last payment, such as a balloon payment. Leave it out for 0.
• type (optional): 0 or left out means payments at the end of each period; 1 means at the start.

If PMT is new to you, the PMT function guide explains the shared arguments and why the result is negative.

The same loan solved both ways#

Here is a loan of 20,000 at 6% a year, repaid monthly over 36 months:

The full monthly payment is =PMT(B2/12,B3,B1), which returns −608.44. Now split it for month 1:

=IPMT(B2/12,1,B3,B1)
=PPMT(B2/12,1,B3,B1)

IPMT returns −100.00 and PPMT returns −508.44. The interest is easy to check by hand: 20,000 × 6% ÷ 12 = 100. Everything else in the payment, 508.44, goes to the balance.

Change per to see how the split moves over time:

If the same loan were repaid yearly over 3 years, you'd use the annual rate and the number of years: =IPMT(B2,1,3,B1) returns −1,200.00 and =PPMT(B2,1,3,B1) returns −6,282.20, which add up to the yearly payment of −7,482.20. The rule is simple: the rate and nper must use the same period as the payments.

The payment never changes, but the mix does. Each month the balance is a little lower, so less interest is due, and more of the same payment goes to principal. Over the whole loan you pay about 1,903.79 in interest.

When to use IPMT#

Use IPMT when the question is about the cost of borrowing. It fits when:

• You need the interest for one period, such as "How much interest is in the 12th payment?"
• You report interest expense. Businesses often record interest and principal separately, because interest is a cost and principal only reduces a debt.
• You build the interest column of a schedule. Put period numbers in column A and use =IPMT($B$2/12,A6,$B$3,$B$1), filled down.
• You need a yearly total. For the sum of several periods, CUMIPMT is shorter: =CUMIPMT(B2/12,B3,B1,1,12,0) returns about −1,029.39 of interest in year 1.

When to use PPMT#

Use PPMT when the question is about paying down the loan. It fits when:

• You need the principal for one period, such as "How much does payment 24 reduce the balance?"
• You track the remaining balance. Subtract each PPMT from the previous balance, or use CUMPRINC for a range of periods: =CUMPRINC(B2/12,B3,B1,1,12,0) returns about −6,271.88 of principal repaid in year 1.
• You compare loans and want to see how fast each one builds equity.
• You build the principal column of a schedule, next to the IPMT column.

In practice you often use both together, as in a full loan amortization schedule.

Building a schedule with absolute references#

In a schedule, every row uses the same rate, term and loan amount, and only the period changes. That makes $ signs essential. With period numbers in A6:A41:

=-IPMT($B$2/12,A6,$B$3,$B$1)
=-PPMT($B$2/12,A6,$B$3,$B$1)

$B$2, $B$3 and $B$1 stay fixed as you fill down, while A6 becomes A7, A8 and so on. Press F4 (Mac: ⌘T) while the cursor is on a reference to add the $ signs. The minus sign in front turns the results positive, which reads better in a report.

If you round each value, for example =ROUND(-IPMT($B$2/12,A6,$B$3,$B$1),2), the rounded interest and principal may not add up to the rounded payment by a cent in some rows. That's normal. Keep the unrounded values for totals, or let the last payment absorb the difference. The ROUND function guide covers the options.

Common mistakes with IPMT and PPMT#

These cause most wrong answers:

1. Annual rate with monthly periods. =IPMT(6%,1,36,20000) treats 6% as a monthly rate and returns −1,200. Divide by 12.
2. Years instead of payments. A 3-year monthly loan has an nper of 36, not 3.
3. A period outside the term. per must be between 1 and nper, or Excel returns #NUM!.
4. Mixing up the two. If your "interest" grows each month, you've used PPMT. Interest should shrink on a normal loan.
5. Forgetting the sign. Both return negatives for a positive loan. Don't add a negative IPMT to a positive balance by mistake; decide on one sign convention for the whole sheet.
6. Using type = 1 by accident. With payments at the start of each period, IPMT for period 1 is 0, because no time has passed for interest to build.

The same rate and period rules apply to the other time-value functions, such as FV for savings growth and compound interest.

Our recommendation#

Use both, side by side, whenever you split loan payments. Use IPMT for the interest column and interest expense, and PPMT for the principal column and the falling balance. After you build them, add a check column with =IPMT(...)+PPMT(...) and compare it to PMT; if they match on every row, your arguments are right. For totals over many periods, switch to CUMIPMT and CUMPRINC instead of adding dozens of single-period results.

If a test asks only for "the interest in payment n", IPMT alone is the fastest answer. If it asks how much the balance falls, reach for PPMT.

Two quick sanity checks work for any loan. First, the IPMT for period 1 should equal the loan amount times the periodic rate: 20,000 × 0.5% = 100. If it doesn't, the rate or nper is in the wrong unit. Second, the PPMT values for every period should add up to the full loan amount, because by the end of the term all of the principal has been repaid.

Practise it#

IPMT and PPMT schedules depend on two core skills: locking the inputs and rounding money correctly. Practise relative vs absolute references in a live spreadsheet to fill a formula down without breaking it, then round money to two decimals in the rounding lesson. Each answer is checked instantly. For timed practice, try the finance Excel test.