calculate-loan

01 Loan payments

Interest Payment Excel Formula: How to Use It

Calculator No. 01-24353 · Updated · Formula and assumptions shown below

Monthly payment $405.53
Total interest
$4,331.67
= Monthly payment × loan term × 12 − loan amount
Loan amount
$20,000
Input
Interest rate (APR)
8 %
Input
You enter
Loan amount ($), Interest rate (APR) (%), Loan term (years)
You get
Monthly payment ($), Total interest ($)
Rounding
2 dp

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

To calculate an interest payment for a specific period in Excel, use the IPMT function: =IPMT(rate, per, nper, pv, [fv], [type]). You enter the periodic interest rate, the period you want, the total number of periods, and the present value (loan amount), and Excel returns the interest portion of that period’s payment.

Formula

The IPMT function in Excel follows the standard amortization formula for the interest portion of a level payment. For a loan with principal P, periodic rate r, number of periods n, and the period of interest k, the interest payment is:

Interest for period k = P * r * (1 + r)^(k-1) – PMT * ((1 + r)^(k-1) – 1)

Where:

  • P = present value (loan amount) in dollars
  • r = periodic interest rate (annual rate divided by number of periods per year; for monthly, APR / 12)
  • n = total number of payment periods (years × 12 for monthly)
  • k = the specific period number you want (1 for the first payment, 2 for the second, etc.)
  • PMT = the level payment amount, calculated as P * r / (1 - (1 + r)^-n)

Excel’s IPMT does this calculation internally. The syntax is IPMT(rate, per, nper, pv, [fv], [type]) where rate is the periodic rate, per is the period number (must be between 1 and nper), nper is total periods, pv is the loan amount (enter as a positive number), fv is the future value (usually 0 or omitted), and type is 0 for payments at the end of the period (default) or 1 for the beginning.

Worked Example

Using the calculator defaults: loan amount $20,000, APR 8%, term 5 years. First, find the monthly payment:

  1. Monthly rate r = 8 / 1200 = 0.0066667
  2. Number of periods n = 5 × 12 = 60
  3. Payment = 20000 × 0.0066667 / (1 – (1 + 0.0066667)^-60)
  4. Payment = 20000 × 0.0066667 / (1 – 0.67121) = 133.333 / 0.32879 = 405.53 (rounded to $405.53)

Now calculate the interest for the first period (k=1):

  • Interest = 20000 × 0.0066667 × (1.0066667)^0 – 405.53 × ((1.0066667)^0 – 1)
  • Interest = 133.33 – 405.53 × 0 = $133.33

In Excel, you would enter =IPMT(0.0066667, 1, 60, 20000) and get $133.33. For the second period, =IPMT(0.0066667, 2, 60, 20000) gives $132.43, because the principal balance has dropped slightly. The total interest over the life of the loan is the payment × 60 minus the principal: 405.53 × 60 – 20000 = 24331.80 – 20000 = $4,331.80, which matches the calculator’s output.

Assumptions

  • Fixed interest rate for the entire term.
  • Payments are made at the end of each period (monthly, unless stated otherwise).
  • Interest compounds monthly at APR / 12.
  • No fees, taxes, insurance, or other charges are included in the calculation.

How to Use IPMT in a Real Spreadsheet

To build an amortization schedule, you typically set up columns for period, payment, interest, principal, and balance. In the period column, list 1 through 60. For each row, use IPMT for the interest and PPMT for the principal portion. The principal portion is simply the payment minus the interest, or you can use PPMT(rate, per, nper, pv) directly. The balance after each payment is the previous balance minus the principal paid. This lets you see how much interest you pay each month and how the balance declines.

One common mistake is using the annual rate directly in IPMT without dividing by 12 for monthly periods. Another is entering the loan amount as a negative number, which flips the sign of the result. Also, the per argument must be between 1 and nper; otherwise Excel returns a #NUM! error.

Choosing Between IPMT, PPMT, and PMT

These three functions work together:

  • PMT gives the total periodic payment (principal + interest).
  • IPMT gives the interest portion of that payment for a specific period.
  • PPMT gives the principal portion for that period.

For any period, PMT = IPMT + PPMT. If you only need the total interest over the whole loan, you can also use IPMT for each period and sum, or use PMT * nper - pv. The CUMIPMT function is a shortcut: =CUMIPMT(rate, nper, pv, start_period, end_period, type) returns the cumulative interest between two periods, which is useful for tax or payoff statements.

Common Mistakes and How to Avoid Them

  • Wrong rate period: If payments are monthly, divide the annual rate by 12. If payments are quarterly, divide by 4.
  • Wrong number of periods: For a 5-year monthly loan, nper is 60, not 5.
  • Sign of the present value: Excel treats money you receive as positive and payments you make as negative. If you enter the loan amount as negative, the interest will show as negative. For clarity, enter the loan as positive and the result will be positive.
  • Using the wrong period number: For the first payment, per = 1. For the last, per = nper.
  • Rounding too early: If you round the payment to cents before using it in IPMT, the interest will be slightly off. Let Excel use the full precision internally.

Limitations of the IPMT Formula

The IPMT function assumes a fixed interest rate and a constant payment amount. It does not handle variable-rate loans, interest-only periods, or payments that change over time. For adjustable-rate mortgages or loans with a balloon payment, you would need a more complex model. Also, it assumes payments are made at regular intervals; if payments are irregular, the formula does not apply. Finally, it ignores any fees or costs rolled into the loan, so the actual interest paid could differ if the lender charges origination fees or prepayment penalties.

For a loan with a different compounding period (e.g., daily), the periodic rate should be adjusted accordingly, but the standard IPMT assumes monthly compounding with monthly payments.

Frequently Asked Questions

How do I calculate the interest payment for the first month in Excel?

Use =IPMT(rate/12, 1, years*12, loan_amount). For example, for a $20,000 loan at 8% APR for 5 years, enter =IPMT(0.08/12, 1, 5*12, 20000) and you get $133.33.

What is the difference between IPMT and PPMT?

IPMT returns the interest portion of a payment, while PPMT returns the principal portion. The sum of the two equals the total payment (PMT). For the first payment on the example loan, IPMT = $133.33 and PPMT = $272.20, which together equal $405.53.

Why does my IPMT result show a negative number?

Excel returns a negative number when you enter the loan amount as a negative present value. This is because Excel treats money you receive as positive and payments as negative. To get a positive result, enter the loan amount as a positive number, or multiply the result by -1.

Can I use IPMT for a car loan or mortgage?

Yes, as long as the loan has a fixed interest rate and equal payments. Enter the loan amount, annual rate divided by 12, total months, and the period number. For a 30-year mortgage, nper would be 360.

How do I calculate total interest paid over the life of a loan in Excel?

Use =PMT(rate, nper, pv) * nper - pv for the total interest. For the example loan, that is 405.53 × 60 – 20000 = $4,331.80. Alternatively, use =CUMIPMT(rate, nper, pv, 1, nper, 0).

What if my payments are made at the beginning of the month?

Set the type argument to 1 in the IPMT function. For example, =IPMT(0.08/12, 1, 60, 20000, 0, 1). This shifts the interest calculation because the first payment occurs immediately, reducing the principal sooner.

This calculation is an estimate based on the inputs you provide; the lender’s disclosure statement gives the exact interest figures for your specific loan.

Related

Formula used
in p | Loan amount | $ | 20000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Loan term | years | 5 | 0.0833 | 50
out pmt | Monthly payment | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.

More in this section

The whole Loan payments section →