Analyzing mortgage refinancing

I recently had to analyze whether I should refinance our mortgage, and did the same thing that most of use would do: start searching for articles about what to consider, how to calculate it, and started testing the various online calculators to help understand the math behind the decision. Each calculator has the same inputs, the same analysis, the same links to lenders. But something felt off, like there was an aspect to the decision that wasn’t clear, and slightly different results as well. Perhaps I’m just too accustomed to being able to see the actual calculations behind things - as a reminder, I build financial models for startups - so I had to build it out myself, and once complete, thought it could be good to share the results, adding one more to the cacophony of mortgage amortization tools.

View, download or copy in Google Sheets, or Copy directly to your Google Drive, or use a version of this in Causal

What I felt the online calculators left out is the concept of equity in the house, and how your future payments will accrue towards that. All of them focus on two or three things:

Reducing your monthly payments
How long does it take to reach breakeven on your closing costs (for example, let’s say you pay $5k in closing costs on refinancing your mortgage, and you save $150 a month in a lower mortgage payment, so it takes 5,000 / 150, or 33 months to breakeven the refinancing)
Total interest costs over the term of the mortgage, and how lower rates reduce this cost

But that seems to overly focus on the concept that you’ll pay the loan off, instead of selling the house before the loan is paid off. Given the likely scenario that I’ll move in less than 20-30 years, I care about the total cost over the timeframe that I expect to live in the house, and the equity that I build in the house over that time; home equity is a “forced savings account”, and represents the portion of the house I actually own, instead of what I’m borrowing to own.

While breakeven is a good concept for understanding whether the financing makes sense on a total cost basis to the borrower, it obscures where those costs are going to. Depending on the terms of the refinancing, you may be building equity faster, or more of your monthly payments might be going towards interest, and thus building equity slower.

So that’s what I designed the tool to to. Given an original loan, and terms for refinancing, the sheets show the amortization table and totals for the original loan, the refinanced loan, and the current remaining loan, so you can compare a broader set of decision points.

A couple caveats:

I am not a personal finance expert, so please don’t use this as the sole basis for your decision. Financial advisors are valuable for making key financial decisions like this, and can be great patners for your future.
I did not build in the effect of tax deductions from mortgage interest into the analysis.
I did not build in any returns you may earn from investing any monthly savings from refinancing. Meaning, if you are using the monthly savings to invest, the value of those savings and investments can have an impact on the decision.
I have not built in analysis for cashing out, paying points to reduce the interest rate, recasting, PMI, adjustable rate mortgages (ARM), or other factors that may complicate your analysis.
I’m American, so this is only built around the concept for mortgage loans in the USA.

As all of the articles and advisors say, refinancing is a decision that has to be based on your particular situation, so please make your decision carefully and do not use this as the basis for your decision.

Questions, ask anytime.