calculate-loan

01 Loan payments

Loan Rate Comparison Excel: Compare Payments and Interest

Calculator No. 01-19522 · Updated · Formula and assumptions shown below

Monthly payment $405.53
Total interest
$4,331.80
= Monthly payment × loan term × 12 − loan amount
Loan amount
$20,000
Input
Interest rate (APR)
8 %
Input
You enter
Loan amount ($), Interest rate (APR) (%), Loan term (years)
You get
Monthly payment ($), Total interest ($)
Rounding
2 dp

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

A loan rate comparison Excel sheet can compare monthly payments and total interest for two loans with different APRs, terms, or amounts. This calculator estimates one loan’s monthly payment and total interest from its principal, APR, and term; enter the figures from a lender’s quote to see the result.

Formula

The calculator uses the standard fixed-payment amortizing-loan formula. It first calculates the monthly payment, rounds that payment to the nearest cent, then multiplies the rounded payment by the number of months to find total payments. Total interest is total payments minus the amount borrowed.

  • p is the loan principal, in dollars.
  • apr is the annual percentage rate entered as a percentage, such as 8 for 8% per year.
  • r is the monthly rate as a decimal: apr ÷ 1,200.
  • n is the term in months: years × 12.
  • pmt is the monthly payment, in dollars, rounded to cents.
  • int is total interest over the modeled term, in dollars.

In symbols, the payment is pmt = round[p × r ÷ (1 − (1 + r)−n), 2]. Total interest is int = (pmt × n) − p. The APR is treated as a nominal annual rate divided into 12 monthly periods; it is not independently converted to an effective annual yield.

Worked Example

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

  1. Convert the APR to a monthly decimal rate: 8 ÷ 1,200 = 0.0066667.
  2. Convert the term to months: 5 × 12 = 60.
  3. Substitute into the payment formula: $20,000 × 0.0066667 ÷ [1 − (1.0066667)−60] = $405.53 per month after rounding to cents.
  4. Calculate total payments using that rounded amount: $405.53 × 60 = $24,331.80.
  5. Subtract the principal: $24,331.80 − $20,000 = $4,331.80 total interest.

The calculation uses the rounded payment for the interest total, as the calculator does. Actual statements may differ slightly because lenders can use their own payment schedules, rounding procedures, or day-count methods.

Assumptions

  • The interest rate is fixed for the full term.
  • Payments are equal, made monthly, with the first payment one month after borrowing.
  • Interest accrues monthly at APR ÷ 12.
  • Fees, taxes, insurance, mortgage insurance, escrow, late charges, and changes to payment timing are not included.

How to Compare Loan Rates in Excel

Excel can compare several loan scenarios by placing each offer in a separate column or row and applying the same calculation to each. A comparison is meaningful only when the inputs and included costs are aligned. For example, comparing one loan’s payment over five years with another’s over seven years does not isolate the effect of APR: the longer term usually spreads repayment over more months and changes both the payment and total interest.

For a basic spreadsheet, create columns for principal, APR, term in years, monthly payment, and total interest. If principal is in A2, APR as a percentage in B2, and years in C2, Excel’s payment formula can be entered as =ROUND(-PMT(B2/12,C2*12,A2),2). The minus sign makes the payment display as a positive amount under Excel’s cash-flow sign convention. If the resulting payment is in D2, total interest using the rounded payment is =D2*C2*12-A2.

Excel’s PMT function expects the periodic rate and number of periods, so divide an APR entered as 8 for 8% by 12, not by 1,200. If the APR cell instead contains 8% as a true percentage value (stored as 0.08), the formula should use B2/12 as well. Do not mix these conventions: a value of 8 and a value of 8% represent different stored numbers.

What Makes a Comparison Fair

To compare two offers, use the actual principal being financed, the quoted APR, and the number of monthly payments for each. Keep the assumptions visible and check whether the quoted figures include charges that affect the cost. This calculator does not estimate fees or adjust APR for them, so a lender’s disclosed APR and any separate charges should be recorded distinctly rather than silently added to the spreadsheet.

  • Compare the same borrowing amount when assessing rate differences.
  • Enter each offer’s actual term; do not assume matching terms.
  • Record whether a rate is fixed or variable. This calculator models only a fixed rate.
  • Track upfront and recurring charges separately if they are not included in the inputs.
  • Compare both monthly payment and total interest. A lower payment alone can reflect a longer term, not a lower borrowing cost.

For instance, if two loans have the same amount and APR but different terms, the longer term generally produces a lower scheduled monthly payment and more monthly periods over which interest can accrue. That is a mathematical consequence of the payment schedule, not evidence about which offer fits an individual situation. Use lender-provided disclosures for the complete cost details.

Common Spreadsheet Mistakes and Limits

A common misconception is that the interest rate alone determines the total cost. Principal, term, payment frequency, fees, and rate changes also matter. Another error is multiplying an unrounded payment by the number of months when the calculator rounds each scheduled payment to cents. That can create a small difference in the displayed total; this tool consistently uses the rounded payment.

The formula assumes a fully amortizing loan with equal monthly payments and no skipped or extra payments. It does not calculate an adjustable-rate schedule, a payoff date after extra payments, a balloon balance, or an APR reconstructed from fees. It also cannot establish which quote is available to a borrower. Enter rates and terms supplied by the lender, and consult the lender’s disclosures for items that the simple calculation omits.

Frequently Asked Questions

How do I compare loan rates in Excel?
Put each loan’s principal, APR, and term in its own row or column, calculate the monthly payment with PMT, then calculate total interest as rounded payment × number of months − principal. Keep fees and other costs in separate fields.

Does a lower monthly payment mean a cheaper loan?
Not necessarily. A lower payment can result from a longer repayment term, which may increase total interest. Compare the term and total cost figures as well as the payment.

Should I enter APR as 8 or 8% in Excel?
Use one convention consistently. If the cell contains the number 8, treat it as 8 percentage points and divide by 12 in a PMT formula. If it contains 8% (stored as 0.08), divide that cell by 12.

Does this calculator include loan fees?
No. It estimates payments and interest from principal, APR, and term only. Fees, taxes, insurance, and other charges must be reviewed separately in the lender’s disclosure.

Can I use this for a variable-rate loan?
Not as a full schedule. The calculation assumes one fixed APR for the entire term; a variable rate can change payment or interest over time, so use the lender’s rate-change terms and schedule for that comparison.

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 | $ | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 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

The whole Loan payments section →