calculate-loan

01 Loan payments

Total Interest Excel Formula: Calculate Loan Interest

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

Monthly payment $405.53
Total interest
$4,331.80
= 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.

In Excel, total loan interest is the sum of all payments minus the amount borrowed: =MonthlyPayment*NumberOfPayments-LoanAmount. For a fixed-rate loan with equal monthly payments, calculate the payment with PMT, then subtract the principal from the total payments; the calculator below estimates both the monthly payment and total interest from the loan amount, APR, and term.

Formula

For a fixed-rate loan repaid through equal monthly payments, total interest is:

Total interest = (Monthly payment × Number of monthly payments) − Loan principal

The monthly payment is calculated using the standard amortizing-loan payment formula:

Payment = P × r / (1 − (1 + r)−n)

  • P is the amount borrowed, in dollars.
  • APR is the annual percentage rate entered as a percentage, such as 8 for 8%.
  • r is the monthly rate as a decimal: APR / 1200. At 8% APR, for example, r is 0.0066667.
  • n is the number of monthly payments: years multiplied by 12.
  • Payment is the monthly amount, rounded to the nearest cent.

In Excel, if A1 contains the loan amount, A2 the annual rate as a decimal (so 8% is entered as 8%), and A3 the term in years, the formulas are:

  • Monthly payment: =-PMT(A2/12,A3*12,A1)
  • Total interest using the rounded payment: =ROUND(-PMT(A2/12,A3*12,A1),2)*A3*12-A1

Excel’s PMT function returns a negative payment when the loan amount is entered as a positive present value, because it treats the payment as money flowing out. The leading minus sign makes the displayed payment positive. Keep the rate and term units consistent: this example divides an annual decimal rate by 12 and multiplies years by 12.

Worked Example

Using the calculator’s defaults—$20,000 borrowed, 8% APR, and a 5-year term—the loan has 60 monthly payments. The monthly rate is 8 / 1200 = 0.0066666667.

  1. Calculate the monthly payment: 20000 × 0.0066666667 / (1 − (1 + 0.0066666667)−60), then round to cents. The result is $405.53 per month.
  2. Multiply the rounded payment by the number of payments: $405.53 × 60 = $24,331.80.
  3. Subtract the borrowed principal: $24,331.80 − $20,000 = $4,331.80.

Therefore, the estimated total interest is $4,331.80. A spreadsheet that retains the unrounded payment for every period may show a slightly different total. The calculator uses the rounded monthly payment for its total, so the displayed payment multiplied by the number of payments reconciles to the displayed total.

Assumptions

  • The interest rate stays fixed for the entire loan term.
  • Payments are equal and made monthly at the end of each period, as assumed by the standard PMT setup.
  • Interest is modelled monthly at APR divided by 12.
  • The estimate excludes fees, taxes, insurance, PMI, escrow, and any extra or missed payments.

When This Formula Applies

This method fits a conventional amortizing loan when the balance is repaid through regular, level payments and the rate does not change. Each payment covers that period’s interest first, with the remainder reducing principal. As the balance falls, the interest portion generally declines and the principal portion rises. Summing every payment and subtracting the original amount borrowed gives the interest cost under those assumptions.

It can also be used for a basic comparison of loan scenarios, provided each scenario’s principal, APR, and term are entered accurately. It does not calculate a credit-card balance with changing charges and payments, a variable-rate loan with future rate changes, or a loan with irregular payment dates. Those cases need a period-by-period balance schedule using the actual terms and cash flows.

Excel Alternatives and a Zero-Rate Case

For a positive fixed rate, the direct PMT-based formula is usually the clearest. You can also place the monthly payment in one cell, the number of payments in another, and use a separate total-interest formula. For example, if B1 is the payment, B2 the number of payments, and B3 the principal, use =B1*B2-B3. This makes it easier to inspect or reuse the assumptions.

At exactly 0% interest, the standard payment expression divides by a rate of zero, so it cannot be used unchanged. The payment is simply principal divided by the number of payments, and total interest is zero. For a spreadsheet that must handle zero-rate loans, use a separate condition, such as =IF(A2=0,A1/(A3*12),-PMT(A2/12,A3*12,A1)) for the payment. The calculator uses a minimum APR of 0.01% to keep its payment expression defined.

Common Excel Mistakes and Limits

  • Mixing percentage formats: If a cell contains 8%, Excel stores 0.08; divide that by 12. If it contains the number 8, first convert it to a decimal or divide by 1200.
  • Using years as months: A 5-year monthly loan has 60 payments, not 5. Use years*12 for the number of periods.
  • Forgetting PMT’s sign convention: A negative result is normal when the principal is entered as positive. Negate the result for a positive payment display.
  • Rounding inconsistently: Rounding only the final total can differ from multiplying a cent-rounded payment by the number of periods. Decide which convention you are using and apply it consistently.
  • Calling all borrowing costs “interest”: This calculation excludes separate charges and does not establish an APR that incorporates fees. Use the lender’s stated loan figures for a complete cost calculation.

The result is a mathematical estimate for a level-payment loan, not a forecast of a changing balance or a substitute for the loan’s payment schedule. A lender may use specific dates, rounding conventions, fees, or other contract terms that this simple formula does not model.

Frequently Asked Questions

What is the simplest Excel formula for total interest?
Use =MonthlyPayment*NumberOfPayments-LoanAmount. For example, if the payment is in B1, payment count in B2, and principal in B3, enter =B1*B2-B3.

How do I calculate total interest with PMT?
For an annual rate stored as a decimal and a term in years, use =ROUND(-PMT(rate/12,years*12,principal),2)*years*12-principal. The minus sign makes the payment positive, and rounding it to cents makes the total consistent with that payment.

Does Excel’s PMT function include total interest?
No. PMT calculates the regular payment, not the total interest by itself. Multiply the payment by the number of periods, then subtract the original principal.

Why does my Excel total differ by a few cents?
Common reasons include rounding the payment each month versus keeping extra decimal places, entering the APR in the wrong format, or using an incorrect number of payment periods. Check the loan’s actual payment schedule for its rounding method.

Can this formula calculate interest on a variable-rate loan?
Not accurately if the rate changes. The formula assumes one fixed rate and equal monthly payments throughout. A variable-rate estimate needs a schedule that applies the rate and payment for each period.

How do I calculate interest if the loan has no interest?
At a 0% rate, divide the principal by the number of payments to get the payment; total interest is $0. The usual PMT-based expression needs a separate zero-rate case because its rate appears in a denominator.

This result is an estimate, and the lender’s disclosure gives the exact figures.

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 | $ | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 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 →