Calculate How a Refinance Will Work

Does it make sense? This example (with template) shows you.

a close up of a mortgage refinance loan application
••• Courtney Keating/E+/Getty Images

Before you refinance, it’s crucial to understand the costs and benefits. Most basic online calculators tell you your breakeven period based on cash flow: They show how long it will take to recoup any closing costs after accounting for a new (lower) monthly payment.

That information is helpful, but it's not sufficient to make the best choice. You need to include a thorough review of how interest costs change when you refinance. To see how refinancing truly affects your finances, follow the steps below. You’ll learn everything you need to know about your existing loan and the potential replacement.

Follow along with a Google Sheets template pre-filled with the example below (and which you can edit for your own loans).

Calculate Your Original Loan

You already know what your monthly payment is and how much you still owe. But you also need to know how much of each payment goes toward your interest cost, and how much of each payment reduces your loan balance every month. To get those numbers, use an amortization table, which you can get from several places. In addition to the Google Sheet above, you can use Excel to calculate amortization, an online calculator, or any other spreadsheet software.

This example shows how to calculate your refinancing options using Google Sheets or Excel, but the process is the same with any amortization table.

Assume the following loan details:

  • Original loan amount: $180,000
  • Original loan date: Nine years ago
  • Interest rate on the loan: 5.4%
  • Loan term: 30 years

To obtain the details of your original loan, enter them into your amortization calculator. Use your original loan amount—not the amount you currently owe.

See Where You Stand Now

Take note of where you stand with your current loan. Scroll down to your current loan balance (at month 108 or year 9), and see how much you still owe on the loan. In our example, it’s $152,160.64. Your numbers may vary due to rounding depending on the software you use and how precise it is.

Calculate the Replacement Loan

Figure out what your new loan would look like if you refinance. For this example, assume the following:

  • Loan amount: $152,160.64 (copied from above)
  • Loan start date: today
  • Interest rate on new loan: 4.25%
  • Loan term: 30 years

Notice that your monthly payment would drop to $748.54 if you refinance (vs. $1,010.76 for the original loan). That’s appealing, but it’s no surprise since your new loan is smaller and it comes with a lower interest rate.

Saving on the monthly payment may be important to you, but it’s just one of several important factors.

If can afford to pay more than the $748.54 minimum payment, consider a mortgage term of 20 years, 15 years, or 10 years. Shorter terms often have the lowest rates. The shorter the mortgage period, the faster you build equity in your home, and the sooner you pay off your mortgage.

Alternatively, you can get a 30-year loan and just pay extra each month. That approach allows you to maximize flexibility. You pay down the mortgage faster (when you have the money), but your required monthly payment remains low in case you fall on hard times.

Make Assumptions About How Long You’ll Keep the Loan

Unfortunately, there’s rarely a definitive answer when you’re deciding whether or not to refinance. You need to project what you believe will happen and make a decision based on your assumptions. So, try to estimate how long you’ll keep the new loan. Will you stay in the same house for the next seven years? Will you stay there for the full 30 years? It’s okay if you don’t know—you can do several “what-if” analyses using your amortization tables.

Evaluate Interest Costs

Figure out how much you will spend on interest with the old loan and the new loan. With each amortization table, tally up the total amount in the “Interest” column. Start with the current month, and continue down until you think you’ll get rid of the loan (seven years from now, when it’s paid off, or whatever else you choose).

This is easiest if you calculate each loan with a spreadsheet, or if you can copy and paste your amortization table into a spreadsheet. See an example of how to quickly add numbers here, or use the SUM function in OpenOffice, Google Docs, or Excel. In our example, there are some interesting differences:

  • If you keep the existing loan until it’s paid off, you’ll spend $103,236 in interest from today until the end of the loan’s term.
  • If you refinance and keep the loan until it’s paid off, you’ll pay $117,313 in interest from today until the end of the loan’s term.

Is it worth roughly $14,000 over the next 30 years to get a lower monthly payment? Maybe it is, and maybe it isn’t. But what if you’ll only keep the loan (or stay in the home) for 10 years?

  • If you keep the existing loan, you’ll spend roughly $69,565 in interest over the next 10 years.
  • If you refinance, you’ll spend about $58,545 in interest over the next 10 years.

In that case, refinancing looks more attractive. Not only do you get the benefit of a lower payment, but you also save on interest costs (because you don't just restart the clock and pay interest for the full 30 years).

What About Closing Costs?

When you refinance, you may have to pay closing costs. You also have to evaluate those costs as you explore refinancing. The Google Sheets calculator can assist with that.

You can start with the traditional refinancing breakeven formula, which tells you how long it takes to recoup the money you spend, (assuming your monthly payment decreases). Divide the monthly savings by your total closing costs to figure out how many months it takes. Will you stay in the house long enough to recover those costs?

If you finance the closing costs or “wrap them into” the new loan (or if you get a loan with no closing costs) you may not need to do anything additional—the charges are already accounted for in your larger starting loan balance (or a higher interest rate).

Consider the opportunity cost of any money that might go toward closing costs: Can you earn more on that money or save more money by paying down debt? If so, it might not be worth paying closing costs.

Article Sources

  1. Federal Reserve Board. "A Consumer's Guide to Mortgage Refinancings." Accessed Nov. 30, 2020.

  2. Provident Credit Union. "Mortgage Rates." Accessed Nov. 30, 2020.