calculate-loan

01 Loan payments

Total Interest Calculator in Excel: Formula and Example

Calculator No. 01-14984 · 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.

A total interest calculator in Excel estimates how much interest a fixed-rate, fully amortizing loan will cost over its term. Enter the loan amount, annual percentage rate (APR), and term to calculate the monthly payment and total interest; this calculator uses the same standard monthly-payment method.

Formula

For a fixed-rate loan with equal monthly payments, first calculate the payment, rounded to cents, then subtract the original principal from the sum of those payments.

  • Monthly payment: M = round(P × r / (1 − (1 + r)−n), 2)
  • Total interest: I = M × n − P
  • Monthly rate: r = APR / 1,200
  • Number of payments: n = years × 12

P is the loan principal in dollars, M is the monthly payment in dollars, I is total interest in dollars, and APR is the annual percentage rate entered as a percentage number (for example, 8 for 8%). The division by 1,200 converts that percentage to a monthly decimal rate. The term is entered in years. The calculator assumes a payment at the end of each month.

Worked Example

Using the calculator’s defaults—$20,000 principal, 8% APR, and a 5-year term—the loan has 60 monthly payments. The monthly rate is 8 ÷ 1,200 = 0.0066667.

  1. Calculate the unrounded payment: 20,000 × 0.0066667 ÷ [1 − (1 + 0.0066667)−60] ≈ $405.53.
  2. Round the monthly payment to cents: M = $405.53.
  3. Multiply that rounded payment by 60: $405.53 × 60 = $24,331.80.
  4. Subtract the amount borrowed: $24,331.80 − $20,000 = $4,331.80 total interest.

The result reflects the rounded monthly payment, so it can differ slightly from a calculation that keeps full precision until the final step. A lender may adjust the final payment by a few cents to bring the account balance exactly to zero.

Assumptions

  • The interest rate remains fixed for the entire modeled term.
  • Payments are equal, made monthly, and occur at the end of each payment period.
  • Interest compounds monthly using APR divided by 12.
  • The estimate excludes fees, taxes, insurance, PMI, escrow, and any extra or missed payments.

Build the Calculation in Excel

To create a basic spreadsheet, place the principal in B1, APR as a percentage in B2, and term in years in B3. In B4, enter a payment formula that rounds to cents:

=ROUND(-PMT(B2/12,B3*12,B1),2)

Excel’s PMT function uses a sign convention: a loan amount received and payments made have opposite signs. The minus sign makes the displayed payment positive. If B2 contains 8% as an actual Excel percentage, use B2/12. If it contains the number 8, use B2/1200 instead; mixing these formats is a common source of errors.

Then calculate total interest in B5 with =B4*B3*12-B1. Because the payment is rounded in B4, this formula follows the calculator’s approach. Alternatively, Excel’s CUMIPMT function can sum scheduled interest across periods, but its period numbers and sign conventions need careful handling. For a transparent estimate, the rounded payment multiplied by the number of payments, less principal, is easy to audit.

How to Read the Result

Total interest is the modeled amount paid above the original principal over the full term. It is not the monthly payment, the total amount paid, or an annual interest rate. In the example, total payments are $24,331.80, of which $20,000 repays principal and $4,331.80 represents interest under the assumptions.

In a standard amortizing loan, each payment includes interest on the outstanding balance and a portion that reduces principal. Early payments generally contain a larger interest share because the balance is higher; later payments direct more toward principal. The total-interest calculation does not show this schedule. An amortization table can display each period’s starting balance, interest, principal reduction, and ending balance.

Common Errors and Limitations

  • Entering APR in the wrong format: Use either 8 with a formula dividing by 1,200, or 8% with a formula dividing by 12—not both conversions together.
  • Using years as months: Multiply the entered years by 12 to get the number of monthly payments.
  • Rounding inconsistently: Rounding the payment to cents before multiplying can produce a different total from retaining full precision. This calculator explicitly uses the rounded payment.
  • Assuming every loan amortizes this way: Variable rates, interest-only periods, balloon payments, irregular payment dates, and changing balances require a different model.
  • Leaving out costs or payment changes: Fees and extra payments can change the amount paid, but they are not inputs here.

APR is an input, not a prediction of a rate someone will receive. For an accurate estimate, enter the rate and repayment term stated by the lender. A lender’s payment schedule may use conventions or adjustments not represented by this simplified monthly model.

Frequently Asked Questions

How do I calculate total interest in Excel?
Calculate the monthly payment, round it to cents, multiply by the number of monthly payments, then subtract the principal. For example, if payment is in B4, years in B3, and principal in B1, use =B4*B3*12-B1.

Can I use Excel’s PMT function?
Yes. For an APR stored as 8%, use =-PMT(B2/12,B3*12,B1) and wrap it in ROUND to match cent-rounded payments. If the APR is stored as the number 8, use B2/1200 as the monthly rate.

Is total interest the same as the total amount repaid?
No. Total amount repaid includes principal and interest. Total interest is the amount repaid minus the amount borrowed, excluding any costs not modeled.

Why does my Excel answer differ by a few cents?
Your sheet may retain more decimal places in each payment, while this method rounds every monthly payment to cents before totaling. A lender may also adjust the final installment or calculate interest using different payment-date conventions.

Does this work for credit cards or variable-rate loans?
Not as a complete estimate. Credit cards may use daily balances, changing payments, and new purchases; variable-rate loans can change their rates or payment amounts. This formula assumes one fixed APR and equal monthly payments.

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 →