Compound Interest Loan Calculator in Excel

A compound interest loan calculator in Excel can estimate a fixed monthly payment, total interest, and payoff schedule from a loan amount, annual percentage rate (APR), and term. The calculator below uses the standard fixed-payment formula, with interest accruing monthly and payments made at the end of each month; enter your loan figures to see the estimates.

Formula

For a fixed-rate loan with monthly payments, the payment is calculated from the present value of a series of equal payments:

PMT = P × r / (1 − (1 + r)−n)

• P is the amount borrowed, in dollars.
• r is the monthly interest rate, calculated as APR ÷ 1,200. APR is entered as a percentage per year.
• n is the number of monthly payments, calculated as loan term in years × 12.
• PMT is the monthly payment, rounded to the nearest cent.

Total interest in this calculator is PMT × n − P. Because the payment is rounded to cents before the total is calculated, the estimate follows the displayed payment rather than an unrounded mathematical payment. Small differences from a lender’s final schedule can result from its rounding and payment conventions.

Worked Example

Using the calculator’s default inputs—$20,000 borrowed, 8% APR, and a five-year term:

1. Convert the APR to a monthly rate: 8 ÷ 1,200 = 0.0066667, or about 0.66667% per month.
2. Convert the term to payments: 5 × 12 = 60 monthly payments.
3. Apply the payment formula: 20,000 × 0.0066667 ÷ (1 − (1 + 0.0066667)−60), which rounds to $405.53 per month.
4. Calculate total interest using the rounded payment: $405.53 × 60 − $20,000 = $4,331.80.

The outputs are an estimated monthly payment of $405.53 and estimated total interest of $4,331.80. The formula’s exact result can differ by a cent or more from a lender’s schedule if payment dates, rounding, or the rate calculation differ.

Assumptions

• The interest rate stays fixed throughout the term.
• Payments are equal, made monthly at the end of each period, and continue for the full term.
• Interest compounds monthly at APR ÷ 12.
• Fees, taxes, insurance, PMI, escrow, late charges, and extra payments are not included.

Build the Calculator in Excel

You can recreate the calculation with ordinary spreadsheet formulas. For example, put the principal in B2, APR as a percentage in B3 (enter 8% rather than 8), and the term in years in B4. Then use these formulas:

• Monthly payment: =ROUND(-PMT(B3/12,B4*12,B2),2)
• Total interest, using the rounded payment in B5: =B5*B4*12-B2

Excel’s PMT function returns a negative payment when the loan amount is entered as a positive present value, because it treats the payment as cash flowing out. The minus sign makes the displayed payment positive. Keep the APR cell formatted as a percentage: Excel interprets 8% as 0.08, so dividing by 12 gives the monthly decimal rate. If you enter 8 as a plain number, the formula must instead divide by 1,200.

For a month-by-month amortization table, create columns for payment number, opening balance, interest, payment, principal repaid, and closing balance. For each month, calculate interest as opening balance × monthly rate; principal repaid as payment − interest; and closing balance as opening balance − principal repaid. The next row’s opening balance equals the prior row’s closing balance. Keep full precision in the balance calculations and format displayed values to cents; rounding every intermediate value can cause a schedule to drift from the calculator’s estimate.

What Compound Interest Means for a Loan

Compound interest means interest is calculated on a balance that can include previously accrued interest. In a standard installment loan with regular payments, each payment first covers interest accrued for that period, then reduces the principal. The next period’s interest is calculated on the remaining balance. As a result, early payments generally contain a larger interest portion than later payments, assuming the rate and payment remain fixed.

This is not the same as simply multiplying the original loan amount by the APR and number of years. That shortcut ignores the changing balance and the monthly payment schedule. The fixed-payment formula accounts for the fact that the balance declines as principal is repaid. It also assumes the stated APR translates directly to a monthly periodic rate; a lender may calculate interest under different contract terms or conventions.

When to Use It and How to Read the Results

Use this estimate to understand the effect of a principal, APR, and term on a regular-payment loan, or to check a quoted payment’s basic arithmetic. The monthly payment covers both principal and interest in this model. The total interest is the estimated interest paid over all scheduled payments, not an additional monthly charge.

Changing the term changes both the payment count and the way the balance is repaid. A longer term spreads repayment across more months, so the monthly payment may be lower while the total interest may be higher, all else equal. A shorter term does the reverse in the mathematical model. These are calculations, not recommendations; use the actual terms supplied by the lender for a meaningful estimate.

To interpret the figures, check that the payment multiplied by the number of months equals the total scheduled payments, then subtract the principal to get interest. The estimate does not show a detailed balance after each payment. An amortization table is useful if you need to see how much principal remains at a particular point or want to model extra payments.

Common Errors and Limits

• Entering APR in the wrong format: In Excel, 8% is stored as 0.08. Dividing that by 12 gives the monthly rate. If the cell contains the number 8, use 8/1,200 instead.
• Mixing months and years: The term here is in years, so the payment count is years × 12. A spreadsheet function that expects months must receive the converted value.
• Confusing interest with APR: APR is an annual rate supplied for the calculation; the result does not separately itemize fees or model every method a lender may use to calculate an effective borrowing cost.
• Rounding too early: Round the monthly payment to cents for the displayed estimate, but avoid rounding each internal balance calculation. A lender may also adjust the final payment slightly to clear a remaining balance.
• Assuming all loans compound monthly: This calculator’s convention is monthly compounding at APR ÷ 12. It may not match a loan with daily interest accrual, variable rates, irregular payment dates, deferred interest, or a changing payment.

It also excludes upfront charges, recurring fees, insurance, taxes, and any payment changes. For a complete payment schedule or payoff figure, use the lender’s disclosure and account details rather than treating this simplified model as a contract calculation.

Frequently Asked Questions

How do I calculate a loan payment in Excel?
Use =ROUND(-PMT(APR/12, years*12, principal),2) when APR is entered as a percentage, such as 8%. The minus sign displays a positive payment.

Does a compound interest loan calculator include principal?
Yes. The monthly payment in this fixed-payment model includes interest for the period and repayment of principal. The balance falls as principal is repaid.

Why is my Excel result different from a lender’s payment?
Differences can come from fees, daily rather than monthly interest accrual, payment dates, a different rate convention, or rounding. Compare the same principal, rate, term, and assumptions.

How do I calculate total interest in Excel?
Multiply the rounded monthly payment by the number of payments, then subtract the amount borrowed: payment × years × 12 − principal.

Can this calculator handle extra payments or a variable rate?
No. It assumes equal monthly payments and a fixed APR. Model extra payments in a month-by-month amortization table, and use the lender’s terms for a variable-rate loan.

This result is an estimate; the lender’s disclosure gives the exact figures.

Related

• Loan Refinance Calculator: Estimate Monthly Savings
• Loan Refinance Comparison Calculator
• Loan Refinance Break-Even Calculator and Formula

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