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 online calculators only 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 you need a more 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 may 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 you reduce your loan balance every month. To figure this out, use an amortization table, which you can get from a variety of sources. 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 Sheets or Excel, but the process is the same with other amortization tables.

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 today’s date (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.

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 determine 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.

Evaluate Interest Costs

Now, see how much it really costs to refinance. To do so, figure out how much you spend on interest with the old loan and the new loan. Go to each amortization table and 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’re not going to restart the clock and pay interest for 30 more years).

What About Closing Costs?

When you refinance, you may have to pay closing costs. You also have to take these costs into account as you decide what to do. The Google Sheets calculator can assist with that.

You can start with the traditional refinancing breakeven formula: How long will it take you 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 financed the closing costs or “wrapped them into” the new loan (or if you got a loan with no closing costs) you may not need to do anything additional—the charges are already accounted for in your larger loan balance or higher interest rate.

You might also consider the opportunity cost of using those funds: Could you have earned interest on that money or paid down debt? If so, is it still worth it to spend the money on closing costs?