calculate-loan

01 Loan payments

Interest Payment Excel: How to Calculate It

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

The calculator on this page is switched off while it is checked. The formula is below, and the worked example shows how to do the sum by hand.

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.

This calculator works out the monthly payment and total interest for a fixed-rate loan, so you can see exactly how much of each payment goes toward interest. You enter the loan amount, the annual interest rate (APR), and the loan term in years, and you get the monthly payment and the total interest paid over the life of the loan. It is the same math used in most amortization schedules, and it gives you a clear number to plan around.

Formula

The monthly payment for a fixed-rate loan is calculated with the standard amortization formula:

Monthly Payment = P × (r × (1 + r)^n) / ((1 + r)^n – 1)

Where:

  • P = loan amount (principal) in dollars
  • r = monthly interest rate, which is the annual rate divided by 12 (e.g., 8% APR → 0.08/12 = 0.006667)
  • n = total number of monthly payments (loan term in years × 12)

Total interest is then simply the monthly payment multiplied by the number of payments, minus the principal.

Worked Example

Using the default values: loan amount $20,000, APR 8%, term 5 years.

  1. Convert APR to monthly rate: r = 8 / 1200 = 0.0066667
  2. Number of payments: n = 5 × 12 = 60
  3. Calculate (1 + r)^n = (1.0066667)^60 ≈ 1.489846
  4. Payment = 20000 × (0.0066667 × 1.489846) / (1.489846 – 1) = 20000 × (0.0099323) / 0.489846 ≈ 20000 × 0.020276 = 405.52
  5. Total interest = 405.52 × 60 – 20000 = 24,331.20 – 20,000 = 4,331.20

The calculator shows a monthly payment of $405.52 and total interest of $4,331.20, matching this step-by-step result.

Assumptions

  • The interest rate is fixed for the entire loan term.
  • Payments are made at the end of each month (ordinary annuity).
  • Interest compounds monthly at the annual rate divided by 12.
  • The calculation does not include fees, taxes, insurance, or other charges that may appear on a real loan statement.

How to Use This for Your Own Loan

To get a meaningful estimate, you need three accurate inputs. The loan amount is the principal you borrow, not the total cost of the item you are buying. The interest rate should be the APR quoted by the lender, which includes certain fees but not all costs. The term is the number of years you agree to repay. Once you have those, the calculator tells you the fixed monthly payment and the total interest you will pay over the life of the loan.

You can also use it to compare scenarios: a longer term lowers the monthly payment but increases total interest, while a shorter term does the opposite. For example, on a $20,000 loan at 8% APR, a 3-year term gives a payment of about $626.73 and total interest of about $2,562.28, whereas a 7-year term gives a payment of about $311.62 and total interest of about $6,176.08. This trade-off is central to loan planning.

Common Mistakes and Misconceptions

One common mistake is confusing the annual rate with the monthly rate. If you plug 8% directly into the formula as a monthly rate, you will get a wildly wrong payment. Always divide by 12.

Another mistake is using the simple interest formula (principal × rate × time) for an amortized loan. That formula works for a single lump-sum payment, but not for a loan where you pay down the principal gradually. The amortization formula accounts for the declining balance, which is why the interest portion of each payment decreases over time.

A related misconception is that the total interest shown is simply the APR times the principal times the term. In the example above, 8% × $20,000 × 5 = $8,000, but the actual total interest is $4,331.20 because you are not borrowing the full $20,000 for the entire five years—you are paying it back gradually.

Rounding can also cause small discrepancies. The calculator rounds the monthly payment to the nearest cent, and then uses that rounded figure to compute total interest. If you calculate with the unrounded payment, you might get a few cents difference. This is normal and not an error.

Why the Interest Portion Changes Each Month

In a fixed-rate amortized loan, each monthly payment is the same, but the split between interest and principal changes. In the first month, the interest is calculated on the full principal: $20,000 × 0.0066667 = $133.33. The rest of the payment, $405.52 – $133.33 = $272.19, goes toward reducing the principal, leaving a balance of $19,727.81.

In the second month, interest is calculated on the new, lower balance: $19,727.81 × 0.0066667 ≈ $131.52. Now $274.00 goes to principal. Over time, the interest portion shrinks and the principal portion grows, until the final payment is almost entirely principal. This is why the total interest is much less than a simple interest calculation would suggest.

How to Build This in Excel

If you want to replicate this in a spreadsheet, Excel has a built-in PMT function. The syntax is =PMT(rate, nper, pv), where rate is the monthly rate (APR/12), nper is the total number of payments, and pv is the loan amount as a negative number. For the example above, you would enter =PMT(8%/12, 60, -20000), which returns $405.53 (slightly different due to rounding). To get total interest, multiply the payment by the number of payments and subtract the principal: =PMT(8%/12, 60, -20000)*60 - 20000.

You can also create a full amortization schedule using the IPMT and PPMT functions, which give the interest and principal portions for a specific period. For instance, =IPMT(8%/12, 1, 60, -20000) returns the interest for the first payment, and =PPMT(8%/12, 1, 60, -20000) returns the principal. This is useful if you want to see the exact breakdown for each month.

Frequently Asked Questions

How do I calculate the total interest paid on a loan in Excel?

Use the PMT function to get the monthly payment, then multiply by the number of payments and subtract the principal. For example, =PMT(8%/12, 60, -20000)*60 - 20000 gives the total interest for a $20,000 loan at 8% for 5 years.

What is the difference between APR and the interest rate?

The interest rate is the cost of borrowing the principal, expressed as a yearly percentage. APR (Annual Percentage Rate) includes the interest rate plus certain fees and other costs, so it is usually higher. Lenders are required to disclose the APR, and it is the more accurate figure for comparing loans.

Why does the total interest seem lower than I expected?

Because you are paying down the principal each month, you only pay interest on the remaining balance, not on the original amount. This is how amortized loans work, and it is why the total interest is less than a simple interest calculation.

Can I use this calculator for a car loan or mortgage?

Yes, the formula works for any fixed-rate, fully amortizing loan, including auto loans, personal loans, and fixed-rate mortgages. However, mortgages often have additional costs like property taxes and insurance that are not included here. For adjustable-rate loans, the payment can change, so this calculation only applies for the initial fixed period.

What happens if I make extra payments?

Extra payments reduce the principal faster, which lowers the total interest and shortens the loan term. The exact effect depends on when you make the extra payment and whether the lender applies it to the principal. You can model this with a more detailed amortization schedule, but the basic formula here assumes a fixed payment with no prepayments.

This calculator is an educational tool that provides an estimate; the exact figures on your loan will be shown in the lender’s disclosure documents.

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 →