To calculate total interest paid in Excel for a fixed-rate loan with equal monthly payments, use =PMT(rate/12, years*12, -loan_amount)*(years*12)-loan_amount. For a $20,000 loan at 8% APR over five years, the result is about $4,332.86; the calculation assumes the monthly payment is not rounded to cents between periods.
Calculator
Formula
The calculator uses the standard fixed-payment loan formula. Let P be the amount borrowed in dollars, a the annual percentage rate (APR) as a percentage, y the term in years, and n = 12y the number of monthly payments. The monthly rate in decimal form is r = a/1200.
- Monthly payment: M = P × r ÷ [1 − (1 + r)−n], rounded to cents.
- Total paid: M × n.
- Total interest: M × n − P.
The Excel formula in the opening paragraph uses PMT to calculate the payment. Excel treats a loan received as a cash flow with the opposite sign from payments, so the principal is entered as a negative value inside PMT; that makes the returned payment positive. The calculator block instead displays the payment and interest using the standard mathematical expression.
Worked Example
Using the calculator’s defaults: principal P = $20,000, APR a = 8%, and term y = 5 years.
- Convert the APR to a monthly decimal rate: r = 8 ÷ 1200 = 0.0066667.
- Find the number of monthly payments: n = 5 × 12 = 60.
- Substitute into the payment formula: M = 20,000 × 0.0066667 ÷ [1 − (1.0066667)−60] ≈ $405.53.
- Multiply the displayed, cent-rounded payment by the number of payments: $405.53 × 60 = $24,331.80.
- Subtract the principal: $24,331.80 − $20,000 = $4,331.80.
The calculator’s second output is $4,331.80 because it uses the payment rounded to cents before multiplying, as specified in the calculator formula. Excel’s unrounded PMT result gives approximately $4,332.86 when multiplied by 60 and reduced by $20,000. The difference is a rounding convention, not a different loan rate or term. To reproduce a real amortization schedule, calculate each period’s interest and principal using the lender’s actual payment and rounding rules.
Using the Excel Formula
Put the loan amount, annual APR, and term in separate cells. For example, suppose A2 contains 20000, B2 contains 8%, and C2 contains 5. Because B2 is entered as a percentage, Excel stores it as 0.08. The following formula returns total interest without first displaying the monthly payment:
=PMT(B2/12,C2*12,-A2)*(C2*12)-A2
Alternatively, put the payment in D2 using =PMT(B2/12,C2*12,-A2), then calculate interest with =D2*C2*12-A2. This layout makes each step visible and easier to check. Format the result as currency, but avoid using Excel’s display formatting as a substitute for rounding in the underlying calculation.
Excel’s IPMT function can calculate the interest portion for a specific payment period. To add the interest for all periods in a five-year monthly schedule, use =-CUMIPMT(B2/12,C2*12,A2,1,C2*12,0). The leading minus sign changes the function’s cash-flow sign to show interest as a positive amount. The final argument 0 means payments occur at the end of each period; use the correct timing for the loan being modelled.
When This Calculation Applies
This method is for a loan repaid through equal, regular payments, with a constant rate and monthly periods. It is useful for estimating interest over the full scheduled term and for checking a basic amortizing-loan calculation. If you change the principal, APR, or term, the payment and total interest both change; holding the other values fixed, a longer term generally means more scheduled payments and more interest overall.
APR is entered as an annual percentage, not a monthly percentage. Dividing by 12 converts the annual rate to a monthly rate under this formula. For example, enter 8% as 8% in a cell or as 0.08 if you are using a raw decimal rate. Do not enter 8 as a decimal rate: that represents 800% in Excel.
Common Errors and Limitations
- Wrong sign in PMT: If the principal is positive, Excel commonly returns a negative payment. Enter the principal as negative inside
PMT, or negate the result deliberately. - Mixing years and months: The number of periods must match the monthly rate. For a term in years, use
years*12; for a term already in months, do not multiply by 12 again. - Rounding inconsistency: The formula shown in the calculator rounds the payment to cents before computing the total. A lender’s schedule may round each interest amount or payment differently, so its final total can vary slightly.
- Non-monthly or changing terms: A variable rate, irregular payments, fees added to the balance, extra payments, or a different payment frequency needs a schedule or a formula that models those details.
Total interest is not the same as total borrowing cost. This calculation subtracts the original principal from scheduled payments; it does not add fees or other charges. It also assumes the loan remains outstanding for the full term. Paying early or making additional principal payments changes the schedule and the interest actually paid.
Assumptions
- The interest rate stays fixed for the full loan term.
- Payments are equal, monthly, and made at the end of each period.
- Interest compounds monthly using APR divided by 12.
- Fees, taxes, insurance, PMI, escrow, and extra payments are not included unless entered separately in a different model.
Frequently Asked Questions
What is the Excel formula for total interest on a loan?
Use =PMT(rate/12,years*12,-principal)*(years*12)-principal for a fixed-rate loan with monthly payments. This version uses the unrounded payment returned by Excel.
How do I calculate total interest using CUMIPMT?
Use =-CUMIPMT(rate/12,years*12,principal,1,years*12,0) to sum interest from the first through final monthly period. Check the payment-timing argument and the sign convention for your spreadsheet.
Why does Excel show a negative payment?
Excel’s financial functions use cash-flow signs. Money received and money paid out have opposite signs, so entering a positive loan amount may make the payment negative. Reverse the sign of one cash flow to display a positive payment.
Does total interest include the principal?
No. Total interest is the amount paid above the original amount borrowed. Total payments include both principal and interest; subtract the principal to isolate interest.
Why does my result differ from a lender’s figure?
Possible reasons include payment rounding, fees, a different rate or payment schedule, extra payments, or the lender’s precise accrual and posting rules. Use the loan disclosure and payment schedule for the contractual figures.
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 | $ | 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