To calculate total interest on a loan in Excel, use the formula =CUMIPMT(rate, nper, pv, start_period, end_period, type) for a fixed-rate loan, or simply multiply the monthly payment by the number of payments and subtract the principal. This article explains both methods, shows a built-in calculator, and walks through a full example so you can replicate the math in your own spreadsheet.
Formula
The total interest on a fixed-rate loan is the difference between the total amount you pay over the life of the loan and the original principal. The payment itself is calculated with the standard amortization formula:
Monthly payment (PMT) = P × r / (1 − (1 + r)^(−n))
- P = loan principal (the amount borrowed, in dollars)
- r = monthly interest rate = annual percentage rate (APR) divided by 12, expressed as a decimal (e.g., 8% APR → 0.08/12 = 0.0066667)
- n = total number of monthly payments = loan term in years × 12
- PMT = the level monthly payment, rounded to the nearest cent
Then, Total interest = PMT × n − P. This works for any fixed-rate, fully amortizing loan where the payment is constant and the rate never changes.
Worked Example
Using the calculator defaults: a $20,000 loan at 8% APR for 5 years.
- Convert APR to a monthly rate: r = 8 / 1200 = 0.0066667
- Total payments: n = 5 × 12 = 60
- Compute the monthly payment: PMT = 20000 × 0.0066667 / (1 − (1 + 0.0066667)^(−60))
- (1 + 0.0066667)^(−60) ≈ 0.671216, so the denominator is 1 − 0.671216 = 0.328784
- PMT = 20000 × 0.0066667 / 0.328784 ≈ 133.333 / 0.328784 ≈ 405.53 (rounded to the cent)
- Total paid = 405.53 × 60 = 24,331.80
- Total interest = 24,331.80 − 20,000 = 4,331.80
The calculator above gives the same result: monthly payment $405.53 and total interest $4,331.80.
Assumptions
- The interest rate is fixed for the entire term (APR does not change).
- Payments are made at the end of each month, starting one month after the loan is taken out.
- Interest is compounded monthly at a rate of APR/12.
- No fees, taxes, insurance, PMI, or escrow amounts are included. Those would add to the total cost but are not part of the pure interest calculation.
How to Do It in Excel
Excel offers two reliable ways to get total interest. The simplest is to use the CUMIPMT function, which is designed exactly for this:
- CUMIPMT(rate, nper, pv, start_period, end_period, type) – returns the cumulative interest paid between two periods. For total interest over the whole loan, set start_period = 1 and end_period = nper. The result is negative because it represents money paid out; multiply by −1 to get a positive number.
- Example:
=CUMIPMT(8%/12, 60, 20000, 1, 60, 0)gives approximately −4,331.80.
Alternatively, you can compute it step by step:
- In cell A1, enter the loan amount (e.g., 20000).
- In cell A2, enter the APR as a percentage (e.g., 8%).
- In cell A3, enter the term in years (e.g., 5).
- In cell A4, enter the monthly payment formula:
=A1*(A2/1200)/(1-(1+A2/1200)^(-A3*12)) - In cell A5, enter total interest:
=A4*A3*12 - A1
This manual method is transparent and lets you see each component. If you want to build an amortization schedule, you can use IPMT for each period’s interest and sum the results.
Common Mistakes and Pitfalls
- Using the APR directly as a monthly rate – Always divide by 12. Using 8% instead of 0.6667% per month will massively overstate the payment.
- Forgetting to round the payment – In real loans, the payment is rounded to the cent, and the total interest is based on that rounded payment. If you keep many decimals, the total interest will be slightly off (usually by a few cents).
- Mixing up years and months – The term must be converted to months (years × 12) in the exponent. Using 5 instead of 60 gives a wrong payment.
- Ignoring the sign convention in Excel –
CUMIPMTreturns a negative value. If you forget to take the absolute value, you might misread the result. - Assuming a simple interest calculation – Some people multiply principal × rate × time (simple interest). That gives $8,000 for the example above (20000 × 0.08 × 5), but the actual interest is $4,331.80 because the principal is paid down over time, so interest is only charged on the remaining balance.
When to Use This Calculation
This calculation is useful for any fixed-rate installment loan: auto loans, personal loans, student loans, or mortgages (though mortgages often have additional costs). Knowing the total interest helps you compare loan offers with different rates and terms. For example, a longer term lowers the monthly payment but increases total interest, because you pay interest for more months. This formula does not apply to credit cards (which have revolving balances and variable payments) or to loans with variable rates, where the interest changes over time.
Limitations and What to Watch For
The formula assumes the loan is fully amortizing, meaning each payment covers the interest due and reduces the principal. It also assumes you make exactly the scheduled payment every month. If you pay extra, miss payments, or refinance, the total interest will differ. Additionally, the APR used should reflect the true annual cost, but if the loan has origination fees or other upfront charges, the actual interest paid may be higher than what this calculation shows. Always check the lender’s amortization schedule for the exact figures.
Frequently Asked Questions
How do I calculate total interest on a loan in Excel without CUMIPMT?
You can use the formula: total interest = (PMT × n) − P, where PMT is the monthly payment from the PMT function. For example, =PMT(8%/12, 60, 20000) gives the payment, then multiply by 60 and subtract 20000. This is the simplest method and works for any fixed-rate loan.
Why does the total interest seem lower than simple interest?
Because the loan is amortized: you pay down the principal each month, so the interest is charged on a decreasing balance. Simple interest assumes the full principal is outstanding for the entire term, which is not the case for installment loans. The difference is significant for long terms.
Does this formula work for a mortgage?
Yes, for a fixed-rate mortgage with no prepayment, the same formula applies. However, mortgages often include property taxes, insurance, and PMI in the monthly payment, which are not part of the interest calculation. Use the formula for the loan amount only, and add those costs separately if needed.
What if I make extra payments?
The total interest will be lower than the formula predicts, because extra payments reduce the principal faster, and future interest is calculated on a smaller balance. To model extra payments, you would need an amortization schedule that recalculates each month.
Can I use this for a variable-rate loan?
No, this formula assumes a fixed rate. For variable rates, you would need to project future rates, which is uncertain. In that case, the lender’s disclosure will show the maximum possible interest, but the actual amount depends on rate changes.
The result from this calculator is an estimate; the lender’s official disclosure will give the exact total interest for your specific loan.
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
- QNB Car Loan Payment Calculator and Guide$405.53
- Car Loan Payment Calculator and Formula$405.53
- VA Down Payment Assistance Calculator and Guide$15,000.00
- Low Down Payment Home Loans: Costs and Trade-Offs$405.53
- Down Payment vs Advance Payment: Key Differences
- DIB Bank Car Loan Calculator and Payment Guide$405.53