Total interest in Excel is the total of all loan payments minus the amount borrowed. Use this calculator to estimate it: enter the loan amount, annual APR, and term to get the monthly payment and total interest, using a fixed-rate, monthly-payment formula.
Formula
The standard fixed-payment formula calculates a monthly payment first, then subtracts the principal from the sum of all scheduled payments to find total interest:
- Monthly payment: M = P × r ÷ [1 − (1 + r)−n]
- Total interest: I = M × n − P
Here, P is the starting loan principal in dollars, APR is the annual percentage rate in percent per year, r = APR ÷ 1200 is the monthly rate as a decimal, n = years × 12 is the number of monthly payments, M is the monthly payment in dollars, and I is total interest in dollars. The calculator’s payment formula uses APR ÷ 1200 directly and rounds the displayed payment to cents. Total interest uses that rounded payment.
Worked Example
Using the default inputs—$20,000 principal, 8% APR, and a five-year term:
- Convert the APR to a monthly decimal rate: r = 8 ÷ 1200 = 0.0066667.
- Convert the term to monthly payments: n = 5 × 12 = 60.
- Calculate the payment: M = 20,000 × 0.0066667 ÷ [1 − (1.0066667)−60] = $405.53 per month, rounded to cents.
- Calculate the scheduled payment total: $405.53 × 60 = $24,331.80.
- Subtract the principal: $24,331.80 − $20,000 = $4,331.80 total interest.
The displayed result follows the calculator expression and rounds the payment before multiplying by the number of months. A lender’s final payment can differ slightly because of its precise payment schedule, rounding, or other contract details.
Assumptions
- The interest rate stays fixed throughout the term.
- Payments are equal, made monthly, and assumed to occur at the end of each monthly period.
- Interest compounds monthly at APR ÷ 12.
- The calculation excludes fees, taxes, insurance, PMI, escrow, and any extra or missed payments.
How to Calculate Total Interest in Excel
To reproduce the calculation in a spreadsheet, put the principal in cell A1, APR as a percentage in A2, and the term in years in A3. For the default example, enter 20000, 8%, and 5. If the APR is entered as the number 8 rather than the percentage value 8%, divide it by 1200 when calculating the monthly rate.
A direct Excel formula for total interest, with the payment rounded to cents as in this calculator, is:
=ROUND(PMT(A2/12,A3*12,-A1),2)*A3*12-A1
Excel’s PMT function returns the regular payment for a constant rate and number of periods. The minus sign before A1 gives the result a positive payment sign under Excel’s cash-flow convention. If the payment is calculated in B1 instead, use =B1*A3*12-A1 for total interest. The formula assumes A2 contains a true percentage, such as 8%, not the whole number 8. If A2 contains 8, use =ROUND(PMT(A2/1200,A3*12,-A1),2)*A3*12-A1.
Excel’s built-in CUMIPMT function can also sum interest portions across a selected range of payment periods. For a full-term total, the periods are 1 through the total number of payments. Because function arguments and sign conventions can vary by spreadsheet configuration, check the result against the payment-based calculation and confirm that the rate is monthly and the period count is in months.
When This Estimate Is Useful
This calculation can help explain the relationship between the amount borrowed, the rate, and the repayment term. It is also useful for checking a spreadsheet or comparing hypothetical scenarios using figures supplied by a lender. Keep all other inputs consistent when examining how a changed term or APR changes the arithmetic; this is a calculation comparison, not a recommendation about borrowing.
Total interest is not the same as the monthly payment or the total amount repaid. The monthly payment is the regular scheduled amount in this model. Total amount repaid is payment multiplied by the number of periods, and total interest is that amount minus the principal. For the example, those figures are $405.53, $24,331.80, and $4,331.80, respectively.
Common Excel Errors and Limitations
- Mixing annual and monthly units: PMT needs a monthly rate and a count of monthly payments here. Use APR divided by 12 when APR is entered as a percentage, and years multiplied by 12 for the period count.
- Entering 8 instead of 8%: A cell containing 8 is not the same as a cell containing 8%. Format the input as a percentage or use the appropriate conversion in the formula.
- Ignoring payment rounding: Rounding a payment before multiplying it by all months can cause a small difference from calculations that use unrounded values. The calculator rounds the regular payment to cents first.
- Assuming every loan has equal payments: The formula does not represent a variable rate, interest-only period, irregular payment dates, balloon payment, or changing payment amount.
- Confusing interest with borrowing cost: Fees and other charges are not included, so this interest-only estimate is not necessarily the same as a lender’s full cost disclosure.
For a changing balance or extra payments, calculate interest period by period from the amortization schedule: each period’s interest depends on the balance at that point and the applicable rate. The simple total-payment formula cannot capture those changes unless the payment pattern still matches its assumptions.
Frequently Asked Questions
What is the Excel formula for total loan interest?
Calculate the monthly payment, multiply it by the number of monthly payments, and subtract the principal. For example, use =ROUND(PMT(APR/12,Years*12,-Principal),2)*Years*12-Principal when APR is stored as a percentage.
Does Excel PMT show total interest?
No. PMT returns the regular payment. Multiply that payment by the number of payments and subtract the original principal to estimate total interest under the same fixed-rate assumptions.
Why does my Excel answer differ by a few cents?
Possible causes include whether the payment was rounded before multiplication, whether spreadsheet cells store APR as 8% or 8, and differences in payment timing or the final payment amount.
Does total interest include fees?
No. The calculation covers interest implied by the principal, APR, payment count, and fixed-payment formula. Fees and other costs require separate inputs and may be treated differently in lender disclosures.
Can this formula calculate interest on a credit card?
Not reliably for a changing credit-card balance. This formula assumes a fixed principal and equal payments; revolving balances, new transactions, and changing payments require a period-by-period calculation.
This result is an estimate; 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 | $ | 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
- Closing Costs on a House Loan: What to Expect$405.53
- Secured Loan for Home Refinancing: Payment Calculator$405.53
- Closing Cost House Calculator: Estimate Buyer Costs$8,500.00
- Closing Cost House Estimate Calculator
- Loan Repayment Extension Request Letter: What to Include$405.53
- Refinance Calculator With PMI and Loan Term