Mortgage Refinance Calculator in Google Sheets: Break-Even Guide

Photo by Nick Chong on Unsplash

What this calculator does and does not cover

This guide builds a fixed-rate mortgage refinance savings and break-even calculator. You enter the details of your existing loan and the proposed replacement loan, add the one-time refinancing costs, choose a comparison horizon, and the model returns the monthly payment saving, cumulative interest saving over that horizon, and the number of months before the saving exceeds the closing costs.

What is excluded: adjustable-rate mortgages after the fixed period ends, cash-out refinancing where the new principal is larger than the payoff amount, tax deductibility of mortgage interest, private mortgage insurance changes, and escrow or impound account adjustments. Refinancing costs vary by lender, loan type, and jurisdiction. Always confirm figures with your lender before making a financial decision. For more context on how spreadsheet-based financial calculators work, see the Google Sheets PMT function documentation.

Key terms explained

• Remaining balance: The amount you still owe on your existing mortgage today, not the original loan amount.
• Annual interest rate: The fixed yearly percentage the lender charges on the outstanding balance. In this calculator you enter it as a plain number such as 6.5, meaning 6.5%.
• Loan term in months: The total repayment period expressed in months. A 30-year mortgage is 360 months; a 15-year mortgage is 180 months.
• Monthly payment: The fixed amount paid each month covering both principal and interest, calculated with the standard amortisation formula.
• Refinancing costs: One-time fees paid when the new loan closes — origination fees, appraisal, title insurance, and any other lender charges. Also called closing costs.
• Break-even point: The number of months it takes for cumulative monthly savings to equal the total refinancing costs. If you sell or refinance again before this point, the transaction costs you money.
• Comparison period: The number of months over which you measure total interest paid on both loans side by side. Choose a horizon that matches how long you realistically plan to keep the loan.
• PMT function: A spreadsheet function that returns the fixed periodic payment for a loan given a periodic interest rate, number of payment periods, and present value (loan balance). It returns a negative number by convention; the formulas below negate it to show a positive payment.
• Amortisation: The process of paying down a loan through scheduled payments, each of which covers interest accrued in that period plus a portion of the principal.
• Default value (column E): The value pre-loaded into the web form when a visitor opens the calculator. Used for testing before publishing.
• Runtime value (column G): The value submitted by the visitor when they click Calculate. All result formulas must reference column G, not column E.

Prerequisites

• A Google account with access to Google Sheets.
• A GSheetPress account with a Calculator entitlement. Sign up at gsheetpress.com/admin/signup.html. The Google account email you use to open this spreadsheet must match the email registered with GSheetPress.
• The GSheetPress add-on installed from the Google Workspace Marketplace. After installation, open a spreadsheet and confirm you see Extensions → GS2Web SaaS in the menu bar.
• Review your plan at gsheetpress.com/pricing.html to confirm Calculator publishing is included.
• A dedicated sample workbook created only for this calculator. Do not use a spreadsheet that contains private financial data, because the sharing step later makes the sheet editable by anyone who holds the link.

Build the refinance calculator in Google Sheets

Open your dedicated sample workbook. Click the + tab icon at the bottom to insert a new sheet. Double-click the tab and rename it RefinanceCalc. Every step below takes place on this tab.

Step 1 — Create a new calculator

1. Click Extensions → GS2Web SaaS → Calculator → New Calculator.
2. When prompted for a name, type Mortgage Refinance Calculator and confirm.
3. When asked for the number of inputs, enter 7. Count only the input rows, not the button.
4. When asked whether to include graphs, choose No.
5. Click OK or Create.

The add-on writes the header row A1:H1 automatically with these column labels: Labels, Input Type, Required, Data, Default, ToolTips, Inputs, Rules. Do not edit row 1. With seven inputs the layout is:

Step 2 — Configure each input row

Fill in columns A, B, C, D, E, F, and H for each row. Column G is the runtime column: the add-on overwrites G2:G8 with values submitted by web visitors each time they click Calculate. Never place result formulas in column G. Column E holds defaults that pre-populate the form and are used for local testing.

Row 2 — Existing loan balance

• A2 — Label: Existing loan balance ($)
• B2 — Input Type: Number
• C2 — Required: Check the checkbox (tick it on)
• D2 — Data: Leave blank
• E2 — Default: 280000
• F2 — ToolTips: Enter the outstanding balance on your current mortgage in dollars.
• H2 — Rules: Leave blank

Row 3 — Existing annual interest rate

• A3 — Label: Existing rate (% per year)
• B3 — Input Type: Number
• C3 — Required: Check the checkbox
• D3 — Data: Leave blank
• E3 — Default: 7.25
• F3 — ToolTips: Enter your current mortgage interest rate as a plain number, e.g. 7.25 for 7.25%.
• H3 — Rules: Leave blank

Row 4 — Remaining term on existing loan

• A4 — Label: Existing remaining term (months)
• B4 — Input Type: Number
• C4 — Required: Check the checkbox
• D4 — Data: Leave blank
• E4 — Default: 300
• F4 — ToolTips: How many monthly payments remain on your current loan? A 25-year remaining term is 300 months.
• H4 — Rules: Leave blank

Row 5 — New loan annual interest rate

• A5 — Label: New rate (% per year)
• B5 — Input Type: Number
• C5 — Required: Check the checkbox
• D5 — Data: Leave blank
• E5 — Default: 5.75
• F5 — ToolTips: Enter the interest rate offered on the refinanced loan as a plain number, e.g. 5.75 for 5.75%.
• H5 — Rules: Leave blank

Row 6 — New loan term

• A6 — Label: New loan term (months)
• B6 — Input Type: Number
• C6 — Required: Check the checkbox
• D6 — Data: Leave blank
• E6 — Default: 360
• F6 — ToolTips: Total number of monthly payments on the new loan. A 30-year refinance is 360 months.
• H6 — Rules: Leave blank

Row 7 — Total refinancing costs

• A7 — Label: Total refinancing costs ($)
• B7 — Input Type: Number
• C7 — Required: Check the checkbox
• D7 — Data: Leave blank
• E7 — Default: 6500
• F7 — ToolTips: Enter all one-time closing costs in dollars: origination fees, appraisal, title insurance, and any other lender charges.
• H7 — Rules: Leave blank

Row 8 — Comparison period

• A8 — Label: Comparison period (months)
• B8 — Input Type: Number
• C8 — Required: Check the checkbox
• D8 — Data: Leave blank
• E8 — Default: 60
• F8 — ToolTips: How many months do you plan to keep this loan? Interest totals are compared over this period.
• H8 — Rules: Leave blank

Step 3 — Check the generated button row

Row 9 is the button row created automatically by the add-on. Verify it contains:

• A9: Calculate (you may edit this display label)
• B9: Button
• D9: main
• E9: Reset

Do not move or delete row 9. Row 10 is the merged Result Fields separator. Row 11 holds result column headings. Your result content begins at row 12.

Step 4 — Copy defaults to column G for local testing

Before entering formulas, populate the runtime column so you can verify results without publishing:

1. Select E2:E8.
2. Copy (Ctrl+C or Cmd+C).
3. Click G2.
4. Choose Edit → Paste special → Values only.

Column G now contains the same numbers as column E. Formulas will calculate correctly. When a visitor submits the web form, G2:G8 are overwritten with their values automatically.

Step 5 — Enter result labels and formulas

Result labels go in column A starting at A12. Result values and formulas go in column B starting at B12. Click each cell, type or paste the content shown, and press Enter. Every formula starts with =.

The seven runtime inputs are:

Result row 12 — Existing monthly payment

Click A12 and type:

Existing monthly payment ($)

Click B12, paste the formula below, and press Enter. PMT returns a negative value by convention so the leading minus sign makes it positive. The annual rate in G3 is divided by 12 to get a monthly rate, and divided by 100 because the visitor enters a plain percentage number.

=IFERROR(IF(OR(G2<=0,G3<=0,G4<=0),"Check inputs — values must be greater than zero",-PMT(G3/100/12,G4,G2)),"Check inputs")

Result row 13 — New monthly payment

Click A13 and type:

New monthly payment ($)

Click B13 and enter:

=IFERROR(IF(OR(G2<=0,G5<=0,G6<=0),"Check inputs — values must be greater than zero",-PMT(G5/100/12,G6,G2)),"Check inputs")

Note: the new loan balance is the same as the existing remaining balance (G2). If your lender is rolling closing costs into the new loan principal, add G7 to G2 in this formula — but confirm that with your lender first, as it changes the break-even logic.

Result row 14 — Monthly saving

Click A14 and type:

Monthly saving ($)

Click B14 and enter:

=IFERROR(IF(B12="Check inputs","Check inputs",B12-B13),"Check inputs")

A positive result means the new loan has a lower monthly payment. A negative result means the new payment is higher — refinancing to a longer term can do this even when the rate drops.

Result row 15 — Break-even point in months

Click A15 and type:

Break-even point (months)

Click B15 and enter:

=IFERROR(IF(B14<=0,"No saving — new payment is not lower",CEILING(G7/B14,1)),"Check inputs")

CEILING rounds up to the next whole month because you need to have completed that month's payment to have recovered the cost. If G7 is zero (no closing costs) the result is 0, meaning you break even immediately.

Result row 16 — Total interest on existing loan over comparison period

Click A16 and type:

Total interest paid (existing, over comparison period) ($)

Click B16 and enter:

=IFERROR(IF(OR(B12="Check inputs",G8<=0),"Check inputs",(B12*G8)-(G2-(-PV(G3/100/12,G4-G8,B12)))),"Check inputs")

This formula multiplies the existing monthly payment by the number of comparison months, then subtracts the principal reduction over that period (the difference between today's balance and the balance remaining after G8 payments). The result is the interest portion of all payments during the comparison window.

An alternative simpler approximation for interest paid is B12*G8 - (G2 - remaining_balance). The formula above computes remaining balance directly using PV so you do not need a separate amortisation table.

Result row 17 — Total interest on new loan over comparison period

Click A17 and type:

Total interest paid (new loan, over comparison period) ($)

Click B17 and enter:

=IFERROR(IF(OR(B13="Check inputs",G8<=0),"Check inputs",(B13*G8)-(G2-(-PV(G5/100/12,G6-G8,B13)))),"Check inputs")

Result row 18 — Interest saving over comparison period

Click A18 and type:

Interest saving over comparison period ($)

Click B18 and enter:

=IFERROR(IF(OR(B16="Check inputs",B17="Check inputs"),"Check inputs",B16-B17),"Check inputs")

Result row 19 — Net saving after refinancing costs

Click A19 and type:

Net saving after refinancing costs ($)

Click B19 and enter:

=IFERROR(IF(B18="Check inputs","Check inputs",B18-G7),"Check inputs")

A positive value means the interest saving over your chosen comparison period exceeds what you paid to refinance. A negative value means you have not yet broken even within that window.

Result row 20 — Refinancing verdict

Click A20 and type:

Verdict

Click B20 and enter:

=IFERROR(IF(B15="No saving — new payment is not lower","Refinancing does not reduce monthly payments",IF(B15="Check inputs","Check inputs",IF(B15<=G8,"Break-even reached within comparison period — refinancing looks favourable","Break-even not reached within comparison period — consider staying with current loan"))),"Check inputs")

Step 6 — Format result cells

Select B12:B19, then choose Format → Number → Currency (or Accounting) to display dollar amounts with two decimal places. Leave B15 and B20 as plain text format because they may display month counts or verdict strings.

Worked example with expected results

Using the default values pre-loaded in column E (and copied to G), here is what each result cell should display:

The break-even of 20 months means that if you keep the new loan for at least 20 months, the $332.54 monthly saving will have covered the $6,500 in closing costs (20 × $332.54 = $6,650.80, just over $6,500). The net saving of roughly $12,947 shows how much further ahead you are after a full 60 months.

Alternative scenario

Change G5 (new rate) to 6.9 and G7 (closing costs) to 8000. The monthly saving drops to about $54, the break-even extends to 149 months (over 12 years), and the 60-month net saving is negative because the interest advantage is too small to recover costs within 5 years. The verdict changes to reflect that break-even is not reached within the comparison period — a clear signal to reconsider refinancing at that rate.

Frequently asked questions

Why does the new monthly payment sometimes go up even when the new rate is lower?

Refinancing restarts the amortisation clock. If your existing loan has 25 years left (300 months) and you refinance into a new 30-year (360-month) loan, you are spreading the same balance over more payments. A smaller rate reduction may not fully offset the cost of those extra years. To avoid this, compare new loans with a term equal to or shorter than your existing remaining term and check the monthly saving result in B14 — it will show a negative number if the payment rises.

Can I include rolled-in closing costs in the new loan balance?

Yes. If your lender is adding closing costs to the new loan principal rather than requiring cash at closing, change the G2 reference in B13's PMT formula to G2+G7. Also set G7 to 0 in the refinancing costs input, because the cost is now embedded in the balance rather than paid upfront. Be aware that rolling costs into the balance means you pay interest on them for the full loan term, which increases total interest paid and shifts the effective break-even.

Why does the interest calculation use PV instead of a full amortisation table?

A full amortisation table would require 300 or more rows and complex summing logic. The PV function calculates the present value of the remaining payments after G8 months, which equals the outstanding balance at that point. Subtracting that from today's balance gives the total principal repaid during the comparison period. Total payments minus principal repaid equals interest paid — without needing a row-by-row table. The result matches a full amortisation schedule for fixed-rate loans.

Troubleshooting

B12 or B13 shows "Check inputs" instead of a dollar amount

One or more of G2, G3, G4, G5, or G6 is zero, blank, or contains text. If you are testing locally, confirm you copied E2:E8 to G2:G8 using Paste special → Values only. If values are present but the cell still shows the error message, check that the cells are formatted as numbers, not as text.

B15 shows "No saving — new payment is not lower"

The new monthly payment in B13 is equal to or higher than the existing payment in B12. This happens when the new rate is close to or higher than the existing rate, or when the new term is much longer. Adjust G5 to a rate clearly below G3, or shorten the new term in G6.

B16 or B17 shows a surprisingly large number

The PV formula inside B16 uses G4-G8 as the number of remaining periods after the comparison window. If G8 is larger than G4 (you entered a comparison period longer than the remaining loan term), the formula produces an unreliable result. Keep G8 less than or equal to G4 and less than or equal to G6.

The add-on menu does not appear after installation

Close and reopen the spreadsheet. If the menu still does not appear, confirm the add-on is installed under Extensions → Add-ons → Manage add-ons and that your signed-in Google account matches your GSheetPress registration email. Visit gsheetpress.com/support if the problem persists.

Formula results differ from the worked example

Double-check that G2:G8 contains the exact default values shown in Step 4. A common mistake is having G3 contain 0.0725 (decimal form) instead of 7.25 (percentage form). The formulas divide the rate by 100 internally, so entering a decimal rate will produce a result close to zero. Also verify there are no leading spaces in the cells.

For additional help building and configuring calculators, see the GSheetPress calculator documentation and the table configuration guide.

Publish and test your web calculator

Step 1 — Share the spreadsheet

1. Click the Share button in the top-right corner of the spreadsheet.
2. Under General access, open the dropdown and choose Anyone with the link.
3. Set the role to Editor.
4. Click Done.

Important: Setting access to Editor means anyone who obtains the spreadsheet URL can modify it directly. This is why you must use only a dedicated sample workbook for this calculator, never a file containing real personal or financial data.

Step 2 — Publish the calculator

1. Make sure the RefinanceCalc tab is selected (click its tab at the bottom).
2. Click Extensions → GS2Web SaaS → Calculator → Publish Calculator.
3. Wait for the confirmation message that the calculator has been published successfully.

Step 3 — Open the published calculator

1. Click Extensions → GS2Web SaaS → Calculator → Get Links (or the equivalent option in your add-on version). This refreshes the links sheet.
2. A sheet named something like Calculator Links or Published Calculators will open or become active. Locate the row whose Sheet Name column matches RefinanceCalc.
3. In that row, find column C — Link (the public calculator link). Click it to open the live calculator in your browser. Do not use column B (Edit Link), which opens the backend editor.

Step 4 — Test the published calculator

1. In the public calculator, enter the seven default values: 280000, 7.25, 300, 5.75, 360, 6500, 60.
2. Click Calculate.
3. Verify that the results match the worked example table above: existing payment approximately $1,966.71, new payment approximately $1,634.17, break-even 20 months, and a favourable verdict.
4. Try the alternative scenario: change the new rate to 6.9 and costs to 8000, then click Calculate again. Confirm the verdict changes to indicate break-even is not reached within 60 months.

Step 5 — Embed the calculator on a website (optional)

Return to the Calculator Links sheet. In the same row for RefinanceCalc, column D contains an Iframe code snippet. Copy that snippet and paste it into any HTML block on your website to embed the calculator inline. Republishing the same calculator in future retains the same public link and iframe URL — no need to update the embed code after changes.

To learn more about what GSheetPress calculators can display, visit the calculator details page.