A refinance calculator in Excel can estimate a replacement loan’s monthly payment and total interest from its principal, APR, and term. The calculator below uses the same standard fixed-payment formula and shows a worked example; an Excel sheet can reproduce it, but this simple model does not determine whether refinancing is financially suitable.
Formula
For a fixed-rate loan with monthly payments, the monthly payment is:
M = round(P × r / (1 − (1 + r)−n), 2)
Here, P is the amount borrowed in dollars, r is the monthly interest rate as a decimal, and n is the number of monthly payments. Convert the entered annual percentage rate to a monthly decimal with r = APR / 1200; convert years to months with n = years × 12. The calculator rounds the payment to cents before calculating total interest.
Total interest over the full schedule is calculated as I = M × n − P, where I is dollars of interest and M is the rounded monthly payment. The calculation assumes every scheduled payment is made and does not account for a final-payment adjustment that a lender’s exact amortization schedule may show because of rounding.
Worked Example
Using the calculator’s default values—$20,000 borrowed, 8% APR, and a five-year term:
- Monthly rate:
8 / 1200 = 0.0066666667. - Number of payments:
5 × 12 = 60. - Unrounded payment:
20,000 × 0.0066666667 / (1 − (1 + 0.0066666667)−60), approximately $405.53. - Payment rounded to cents: $405.53 per month.
- Total of scheduled payments:
$405.53 × 60 = $24,331.80. - Total interest:
$24,331.80 − $20,000 = $4,331.80.
The payment is an estimate based on the inputs and formula. Use the loan’s stated payment schedule for an exact contractual figure.
Assumptions
- The APR is fixed for the full term.
- Payments are equal, made monthly, and paid at the end of each monthly period.
- Interest is modelled monthly at APR divided by 12.
- The calculation excludes closing costs, other fees, taxes, insurance, mortgage insurance, and escrow.
Build the Calculation in Excel
Enter the new loan amount, annual APR, and term in separate cells. For example, put principal in B2, APR as a percentage such as 8% in B3, and years in B4. Then use a formula equivalent to:
=ROUND(B2*(B3/12)/(1-(1+B3/12)^(-B4*12)),2)
This version expects B3 to contain a percentage entered as 8%, not the number 8. If you enter 8 as a plain number instead, use B3/1200 for the monthly rate. Confusing those formats can produce a dramatically incorrect payment. To calculate interest using the rounded payment in B5, use =B5*B4*12-B2.
For a fuller spreadsheet, create an amortization table with columns for payment number, starting balance, payment, interest, principal repaid, and ending balance. For each month, calculate interest as starting balance multiplied by the monthly rate; subtract that interest from the payment to get principal repaid; then subtract principal repaid from the starting balance. The final balance should approach zero, subject to rounding. Do not treat the loan payment as total housing or vehicle cost: recurring insurance, taxes, and other expenses are outside this model.
What a Refinance Estimate Leaves Out
A refinance replaces an existing debt with a new loan. To assess the arithmetic of a particular offer, the new principal should reflect the amount actually financed. That may differ from the old loan’s original amount: the payoff figure can include accrued interest or charges, and some borrowers may finance transaction costs or change the amount borrowed. Those figures must come from the relevant loan documents or lender; this calculator cannot infer them.
The calculator reports the new loan’s payment and interest over its entire stated term. It does not compare those amounts with the remaining payments on the current loan, include costs paid upfront, model a changing rate, or calculate a break-even point. A lower monthly payment alone does not establish lower total borrowing cost: extending repayment can reduce the payment while increasing the time interest accrues. Conversely, a shorter term can raise the payment while reducing the number of payments. Compare complete schedules and include costs consistently if making a numerical comparison.
Common Excel Problems and Limits
- APR entered in the wrong format: use 8% with the formula’s monthly conversion, or enter 8 and divide by 1200. Do not mix the two conventions.
- Years confused with months: the formula multiplies years by 12. If your term is already in months, use that number directly for
n. - Rounding too early: round the monthly payment to cents for the displayed payment, then use that rounded amount for the shown total-interest estimate. Small rounding differences can accumulate across many payments.
- Fees omitted: a payment calculated only from principal, APR, and term will not include costs unless they are added to the amount financed or modelled separately.
- Rate or payment structure differs: this formula is for a fixed APR and equal monthly payments. It does not model variable rates, irregular payment dates, interest-only periods, or a balloon payment.
Excel’s PMT function is another way to calculate a level payment: =-PMT(B3/12,B4*12,B2) when B3 contains an APR such as 8%. The minus sign displays the payment as a positive cash outflow; Excel’s function does not by itself add costs or compare loans.
Frequently Asked Questions
Can I use Excel as a refinance calculator?
Yes. Enter the new principal, annual APR, and term, convert the APR to a monthly rate, and apply the fixed-payment formula. The result is an estimate, not a lender’s official schedule.
What Excel formula calculates a refinance payment?
Use =ROUND(P*(APR/12)/(1-(1+APR/12)^(-years*12)),2) if APR is entered as a percentage such as 8%. Alternatively, use Excel’s PMT function with the monthly rate and total number of payments.
Does the refinance calculator include closing costs?
No. The calculator includes only the loan amount, APR, and term. Obtain any applicable costs from the lender and model them separately or include financed costs in the principal only when that matches the actual loan terms.
Why can a refinance payment be lower while total interest is higher?
A longer repayment term spreads payments over more months. That can lower each scheduled payment while interest accrues for a longer period. Compare the full repayment schedules and relevant costs, not just the monthly payment.
Is APR the same as the interest rate used in this formula?
This simplified calculation treats the entered APR as a fixed annual rate and divides it by 12. Actual loan disclosures may incorporate costs or calculation conventions that this basic formula does not model, so use the lender’s stated terms for exact figures.
This result is an estimate; the lender’s disclosure gives the exact figures.
Related
Formula used
in p | New 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
- 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