Formuladrill

Financial Updated November 2026

IPMT and PPMT in Excel

IPMT(rate, per, nper, pv) returns the interest part of one loan payment and PPMT(rate, per, nper, pv) returns the principal part. Together they add up to PMT for every period. Early payments are mostly interest and late payments mostly principal, and per says which payment you mean.

Syntax

IPMT(rate, per, nper, pv, [fv], [type])
PartWhat it is
rateThe interest rate per period. Yearly rate divided by 12 for monthly payments.
perThe payment number, from 1 to nper.
nperThe total number of payments.
pvThe loan amount.
fvOptional. The balance left after the last payment. Default 0.
typeOptional. 0 for payments at the end of each period (default), 1 for the beginning.

Examples

=IPMT(0.06/12, 1, 60, 20000)

Result: -100.00

Interest in the first month of a 20,000 loan at 6% over 60 months: 20,000 x 0.5%.

=PPMT(0.06/12, 1, 60, 20000)

Result: -286.66

The rest of the first payment of 386.66 reduces the balance.

=IPMT(0.06/12, 60, 60, 20000)

Result: -1.92

The last payment is almost all principal.

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

Result: The interest for the period in A5, as a positive number

Lock the loan terms with $ and fill down a column of period numbers to build a schedule.

Common mistakes

  • #NUM! means per is below 1 or above nper.
  • Using the yearly rate with monthly periods overstates the interest twelve times. Divide by 12.
  • The results are negative because they are money paid out. Put a minus sign in front, or enter pv as a negative number.
  • Forgetting to lock rate, nper and pv with $ before filling down a schedule.

Practice IPMT and PPMT

These drills check your answer on live data, free, no account needed.

  • Interest and principal in one paymentSplit the monthly payment with the number in B4 into its interest part (B5) and its principal part (B6). The rate and the term are yearly, payments are monthly, and both answers must be positive.
  • Total interest on a loanA loan is repaid monthly over the years in B3 at the yearly rate in B2. Calculate the total interest paid over the life of the loan, as a positive number, in B4.
  • Loan installmentCalculate the monthly installment with PMT. Rates and terms are yearly, payments are monthly. For this exercise, round the installment up to a whole amount. Put the answer in B4 as a positive number.

Questions

How do I calculate the interest paid in one month of a loan?

=IPMT(yearly rate/12, month number, total months, loan amount). The result is negative; add a minus sign in front for a positive number.

Do IPMT and PPMT add up to PMT?

Yes, for every period. PMT is the fixed payment, IPMT the interest in it and PPMT the principal.

How do I get the total interest over several payments?

=CUMIPMT(rate, nper, pv, first period, last period, 0), or build a schedule with IPMT and sum the column.

Related

  • PMTHow the Excel PMT function calculates the payment on a loan or the deposit for a savings goal, with mortgage and car loan examples and the monthly rate rule.
  • NPERHow NPER works in Excel: the number of periods needed to pay off a loan or reach a savings goal at a fixed rate and payment, with monthly examples.
  • RATEHow RATE works in Excel: find the interest rate per period from the number of payments, the payment and the amount, then turn it into a yearly rate.
  • NPVHow the Excel NPV function discounts future cash flows at a rate, why the initial investment is added outside the function, with a worked example and XNPV.

Previous: RATENext: XNPV