Interest Loan Excel: Calculate Payments and Total Interest

An interest loan Excel calculator estimates a fixed-rate loan’s monthly payment and total interest from the amount borrowed, annual percentage rate (APR), and term. Enter those three figures to see the estimated payment and the interest paid over the full term, assuming equal monthly payments and no added fees.

Formula

This calculator uses the standard fixed-payment loan formula. It converts the annual APR to a monthly rate, calculates the payment required to repay the balance over the term, rounds that payment to the nearest cent, and estimates total interest by subtracting the original principal from all scheduled payments.

Monthly payment: M = P × r / [1 − (1 + r)⁻ⁿ]

Total interest: I = M × n − P

• P is the loan principal, in dollars.
• APR is the annual percentage rate entered as a percentage, such as 8 for 8%.
• r is the monthly interest rate as a decimal: APR / 1200.
• y is the loan term in years.
• n is the number of monthly payments: 12 × y.
• M is the monthly payment in dollars, rounded to cents.
• I is the estimated total interest in dollars.

The formula assumes the same payment amount each month and a positive, unchanged APR. Rounding the monthly payment to cents before calculating total interest reflects the calculator’s displayed payment; actual payment schedules may handle the final installment differently.

Worked Example

Using the calculator’s default inputs—$20,000 principal, 8% APR, and a five-year term—the monthly rate is 8 / 1200 = 0.0066667, and the number of payments is 5 × 12 = 60.

1. Substitute into the payment formula: M = 20,000 × 0.0066667 / [1 − (1.0066667)⁻⁶⁰].
2. The unrounded payment is approximately $405.527; rounded to cents, the monthly payment is $405.53.
3. Multiply the displayed payment by 60: $405.53 × 60 = $24,331.80 in scheduled payments.
4. Subtract principal: $24,331.80 − $20,000 = $4,331.80 in estimated total interest.

Assumptions

• The APR stays fixed for the full term.
• Payments are equal, monthly, and made at the end of each payment period.
• Interest accrues monthly using the APR divided by 12.
• Fees, taxes, insurance, PMI, escrow, and other charges are not included.

Using the Calculator

Enter the amount borrowed, the APR shown in the lender’s loan disclosure or estimate, and the term in years. The APR field takes a percentage value: enter 8 for 8%, not 0.08. The term can include fractional years; for example, 2.5 years represents 30 monthly payments.

The loan amount is the principal used in the calculation, not necessarily the total cost of a purchase. If an amount includes financed fees or other charges, the calculation treats those dollars as part of the principal. For a precise estimate, use the same principal, APR, and term that apply to the loan being modelled.

The calculator accepts APR values from 0.01% to 100% and terms from 0.0833 to 50 years. Its positive minimum APR avoids division by zero in this version of the payment formula. These input limits are calculator boundaries, not statements about available loan terms or rates.

Reading the Results

The monthly payment is the scheduled principal-and-interest amount under the stated assumptions. It is not necessarily the complete amount due on a real account: separate charges or payment components are outside this calculation. Total interest is the sum of the rounded monthly payment multiplied by the number of payments, less the starting principal.

For a fixed principal and APR, extending the repayment term generally lowers the calculated monthly payment while increasing the number of months over which interest accrues. A shorter term generally raises the scheduled payment and reduces the time interest accrues. These are mathematical effects of the formula, not a recommendation about which term to choose.

Interest is not calculated by multiplying the original loan amount by the APR and the number of years. As principal is repaid, the outstanding balance falls, so the interest portion of a level payment typically declines while the principal portion rises. An amortization schedule shows that changing split payment by payment.

Excel Formula and Common Errors

To reproduce the payment calculation in Excel, use the built-in PMT function with the monthly rate and total number of payments. For example, if the principal is in A1, APR entered as 8% is in A2, and years are in A3, a positive payment display can be calculated with:

=-PMT(A2/12,A3*12,A1)

Excel treats 8% as the decimal 0.08, so divide that cell by 12 to obtain the monthly rate. If instead the cell contains the number 8, use =-PMT(A2/1200,A3*12,A1). Mixing these entry conventions is a common source of wildly incorrect results. For total interest based on the rounded payment, use =ROUND(-PMT(A2/12,A3*12,A1),2)*A3*12-A1 when A2 is stored as a percentage such as 8%.

• Check whether the APR cell contains a percentage (8%) or a whole number (8) before choosing the divisor.
• Convert a term in months to years only if the formula expects years; this calculator expects years and multiplies by 12.
• Keep full precision in intermediate calculations and round the payment only at the final display step.
• Do not mistake the total of payments for total interest: subtract the principal to estimate interest.

Limits and Other Loan Costs

This is a general fixed-rate estimate, not a complete account statement or a personalized financial recommendation. It does not model variable rates, irregular payment dates, skipped or late payments, extra payments, balloon payments, or a different final payment caused by a lender’s rounding and posting practices. A changing rate requires recalculating the schedule as the rate changes.

Fees and other charges can affect the amount financed or the overall cost even when they do not appear in this formula. APR and interest-rate disclosures may also reflect different components depending on the loan and its terms. Use the lender’s disclosed figures and ask how the payment schedule treats fees, payment dates, and rounding if you need to reconcile an estimate with a quoted schedule.

Frequently Asked Questions

How do I calculate loan interest in Excel?
Calculate the monthly payment with PMT using the monthly rate and number of payments, then multiply that payment by the number of payments and subtract the principal. For a rate stored as 8%, use A2/12; if stored as the number 8, use A2/1200.

Is APR the same as the monthly interest rate?
No. APR is expressed annually. This calculator converts it to a monthly rate by dividing the percentage by 12 and then by 100, which is equivalent to APR/1200.

Does the monthly payment include fees and insurance?
No. The result includes only the fixed principal-and-interest payment described by the formula. Fees, insurance, taxes, PMI, and escrow are not inputs and are not included.

Why can my lender’s payment differ from this estimate?
The lender may use different dates, rounding rules, financed charges, rate terms, or payment components. Compare the same principal, APR, term, and payment assumptions, and consult the lender’s disclosed schedule for its figures.

Can I use this for a variable-rate loan?
Only as a snapshot using one assumed APR. The formula holds the rate constant, so it cannot forecast payments or total interest when the rate changes.

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

Related

• Home Loan Refinance Cashback Calculator and Break-Even
• Refinance a Personal Loan for a Lower Rate?
• Refinance a Personal Loan: Compare Payments and Interest

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