Picture this: you’re a Virginia homeowner sitting on a $380,000 mortgage balance at 7.25%. Two refi quotes land in your inbox on the same afternoon. Quote A comes in at 6.375% with $6,200 in closing costs. Quote B is priced at 6.50% with only $2,800 in costs. On the surface, Quote A looks like the obvious winner — lower rate, done deal, right?
Not so fast. Run the break-even math and the picture flips entirely. Quote A saves you roughly $222 per month but takes 28 months to recoup its closing costs. Quote B saves $191 per month and breaks even in just 15 months. If you’re planning to stay in the home for two years or less, Quote B is the smarter financial move — even though it carries the higher note rate.
That’s exactly the kind of clarity a well-built mortgage comparison spreadsheet delivers. Without a structured framework, most homeowners pick the lower rate and leave money on the table. With one, you see the full picture before you sign anything.
Duane Buziak, NMLS #1110647, walks Virginia homeowners through this kind of analysis every day as the Refi Guy. This guide covers seven field-tested strategies for building a refi comparison spreadsheet that goes well beyond pasting in interest rates — from APR normalization and break-even calculations to PMI removal triggers and cash-out vs. HELOC cost comparisons. Each strategy adds a distinct layer of analytical clarity that the raw quotes from lenders simply don’t give you.
Whether you’re comparing rate-and-term quotes, evaluating a cash-out option, or deciding between a HELOC and a full refinance, these strategies give you a repeatable framework for every scenario.
Table of Contents
1. Normalize Every Quote to APR Before You Enter Anything Else
2. Build a Break-Even Clock Column — Not Just a Break-Even Month
3. Separate Lender Fees from Third-Party Fees in Your Cost Columns
4. Add a PMI Removal Trigger Row for Every Conventional Quote
5. Build a Cash-Out vs. HELOC Side-by-Side Tab
6. Include a 5-Year Total Cost Row — Not Just Monthly Payment
7. Use a Rate Lock Comparison Column to Account for Timing Risk
1. Normalize Every Quote to APR Before You Enter Anything Else
The Challenge It Solves
Note rates are marketing tools. Lenders know that a borrower scanning three quotes will instinctively anchor on the lowest number in the rate column — and that instinct can be exploited through fee structures that inflate the true cost of a loan. Without APR normalization as your first step, you’re comparing apples to oranges before the analysis even starts.
The Strategy Explained
According to the Consumer Financial Protection Bureau, APR includes the interest rate plus lender fees such as origination charges, discount points, and mortgage broker fees — costs that the note rate alone doesn’t capture. That makes APR the only true apples-to-apples comparison metric across competing quotes.
Your spreadsheet’s first column after lender name should be the stated note rate. The second column should be the APR as disclosed on the Loan Estimate. The third column should be your calculated APR spread — the difference between note rate and APR — which immediately flags how fee-heavy each offer is. A large spread signals a lender loading costs into the loan. A tight spread signals a cleaner fee structure.
Here’s how this plays out with real numbers. Take our $380,000 Virginia homeowner from the introduction. Quote A at 6.375% carries $6,200 in lender fees. Quote B at 6.50% carries $2,800 in lender fees. The APR on Quote A, once you factor in those origination charges, may land closer to 6.55% — higher than Quote B’s APR of roughly 6.62%. The gap narrows dramatically. The lower-rate loan is no longer the clear winner it appeared to be.
Implementation Steps
1. Pull the APR directly from Page 3 of each lender’s Loan Estimate — never calculate it yourself from raw inputs, as lender-disclosed APR accounts for fee timing in ways a simple formula won’t match.
2. Create a “APR Spread” column: APR minus Note Rate. Flag any spread above 0.25% for closer scrutiny of the origination fee section.
3. Sort your comparison table by APR, not note rate, before proceeding to any other analysis. This becomes your working order for every subsequent strategy.
Pro Tips
APR is a standardized disclosure, but it still excludes some third-party costs like title insurance and appraisal fees. That means APR normalization is necessary but not sufficient on its own — it’s the foundation that every other strategy in this guide builds on, not the complete picture by itself.
2. Build a Break-Even Clock Column — Not Just a Break-Even Month
The Challenge It Solves
A static break-even month figure sitting in a spreadsheet cell means nothing without context. Knowing that a refinance breaks even in 28 months is only useful if you know whether you plan to stay in the home for 28 months or longer. Without a “planned stay” input that auto-flags whether the math actually works for your timeline, the break-even number is just a number.
The Strategy Explained
The break-even calculation itself is straightforward: total closing costs divided by monthly payment savings equals break-even months. What most homeowners skip is the second half of that equation — comparing break-even months against their realistic planned stay in the property.
Build your spreadsheet with a single input cell at the top: “Planned Stay (Months).” Every quote’s break-even column then auto-compares against that input and returns a simple flag — “Pencils Out” or “Doesn’t Pencil” — based on whether break-even months fall inside or outside your planned stay window. This turns a passive number into an active decision signal.
Using our Virginia homeowner example: Quote A breaks even at month 28 ($6,200 ÷ $222/month savings). Quote B breaks even at month 15 ($2,800 ÷ $191/month savings). Enter a planned stay of 24 months and the spreadsheet flags Quote A as “Doesn’t Pencil” and Quote B as “Pencils Out” — instantly. Change the planned stay to 60 months and both quotes pencil out, but Quote A now wins on total interest saved over the longer horizon.
For Virginia homeowners in Fairfax County, where the FHFA 2026 conforming loan limit for high-cost areas reaches $1,209,750, this dynamic break-even logic is especially important on larger loan balances where closing costs scale up accordingly.
Implementation Steps
1. Create a top-level input cell: “My Planned Stay in Home (Months)” — make this prominent, because everything downstream depends on it.
2. For each quote, calculate: Break-Even Months = Total Closing Costs ÷ Monthly Payment Savings.
3. Add a conditional flag column: IF(Break-Even Months < Planned Stay, “Pencils Out”, “Doesn’t Pencil”) — color-code green and red for fast visual scanning.
Pro Tips
Be honest about your planned stay. Homeowners consistently overestimate how long they’ll stay in a property. If you’re unsure, run the analysis at two scenarios: your conservative estimate and your optimistic one. If a quote only pencils out under the optimistic scenario, treat it with appropriate skepticism.
3. Separate Lender Fees from Third-Party Fees in Your Cost Columns
The Challenge It Solves
Closing costs are not a single negotiable number. Lumping all fees into one “total closing costs” cell is one of the most common spreadsheet mistakes homeowners make — it obscures where you actually have leverage and where you don’t. Negotiating against a number you can’t change is wasted energy. Knowing which fees are moveable is where real savings live.
The Strategy Explained
The CFPB’s Loan Estimate divides closing costs into distinct sections. Section A covers Origination Charges — these are lender-controlled fees including origination points, underwriting fees, and application fees. These are negotiable. Section C covers Services You Can Shop For — title insurance, settlement agent fees, and similar third-party costs that you can independently source and compare. Section B covers Services You Cannot Shop For — appraisal, credit report, flood determination — these are largely market-set and not negotiable with the lender.
Your spreadsheet should mirror this structure exactly. Build three separate cost sub-columns for each lender quote: Section A (Lender Fees), Section B (Non-Shoppable Third-Party), and Section C (Shoppable Third-Party). Total them separately before combining into a grand total.
This structure immediately shows you two things: first, which lender is charging more in Section A fees — and by how much; second, whether a lender’s lower total closing cost is actually a lower Section A (genuine savings) or just a lower Section C (which you could shop independently regardless of which lender you choose).
Implementation Steps
1. Pull Sections A, B, and C figures directly from Page 2 of each Loan Estimate — the CFPB mandates this structure, so every lender’s LE will use the same format.
2. Build three sub-columns per lender: “Lender Fees (A),” “Fixed Third-Party (B),” “Shoppable Third-Party (C).”
3. Add a “Negotiating Leverage” indicator: highlight the lender with the highest Section A fees — that’s your primary negotiation target, not the lender with the highest total closing costs.
Pro Tips
When you identify a lender with strong APR and rate but elevated Section A fees, use the fee breakdown to open a direct conversation: “Your rate is competitive, but your origination fee is $800 higher than a competing quote. Can you match it?” Lenders with flexibility will often move on Section A. Those who won’t are telling you something useful about how they operate.
4. Add a PMI Removal Trigger Row for Every Conventional Quote
The Challenge It Solves
Private mortgage insurance is a monthly cost that many homeowners forget to factor into a refi comparison — especially when a refinance brings their loan-to-value ratio across a key threshold. A refi that eliminates PMI fundamentally changes the payment comparison, and missing that calculation can cause you to undervalue a quote that looks more expensive on rate alone.
The Strategy Explained
Under the Homeowners Protection Act, borrowers can request PMI cancellation when their loan-to-value ratio reaches 80% (20% equity), and lenders must automatically terminate PMI when LTV reaches 78% based on the original amortization schedule. A refinance that establishes a new loan balance at or below 80% LTV can eliminate PMI immediately — a monthly savings that needs its own row in your comparison.
Here’s a real example of how this works. A Virginia homeowner originally purchased at $400,000 with 10% down, starting with a $360,000 loan. After making payments, the balance is now $340,000. The home has appreciated to $430,000. Current LTV: $340,000 ÷ $430,000 = 79.1% — already below 80%, meaning a PMI removal request is valid right now, even without refinancing.
But if that homeowner refinances to $330,000 on a $430,000 appraised value, the new LTV drops to 76.7% — permanently eliminating PMI and locking in the savings for the life of the loan. Depending on the original loan terms, monthly PMI savings can range meaningfully, and that figure needs to be added to the monthly payment savings column before you calculate break-even. Ignoring it understates the true value of the refinance.
Implementation Steps
1. Add a row for each quote: “Current LTV” (current balance ÷ current appraised value) and “Post-Refi LTV” (new loan balance ÷ current appraised value).
2. Add a PMI Trigger flag: IF(Post-Refi LTV <= 0.80, “PMI Eliminated,” “PMI Continues”).
3. When PMI is eliminated, add the monthly PMI savings to the monthly payment savings figure before calculating break-even — this is a separate line item, not buried in the rate comparison.
Pro Tips
This row applies to conventional loans only. FHA loans carry mortgage insurance under different rules — MIP removal on FHA loans originated after June 2013 typically requires a full refinance to a conventional loan, not just reaching an LTV threshold. Flag FHA quotes separately and note that MIP removal requires a loan type change, not just equity accumulation.
5. Build a Cash-Out vs. HELOC Side-by-Side Tab
The Challenge It Solves
Cash-out refinancing and a HELOC are fundamentally different financial instruments — different rate structures, different lien positions, different LTV limits, and different implications for your existing mortgage. Comparing them in the same columns as a rate-and-term refinance creates analytical confusion that leads to bad decisions. They need their own tab.
The Strategy Explained
The structural differences between these two products are significant. A cash-out refinance replaces your entire existing mortgage with a new loan at a new rate, carries higher closing costs, and resets your loan term. A HELOC sits as a second lien behind your existing mortgage, typically carries a variable rate tied to prime, involves lower or no closing costs, and operates with a draw period followed by a repayment period.
On LTV limits: VA cash-out refinancing allows eligible veterans to access up to 100% of their home’s appraised value. Conventional cash-out refinancing is capped at 90% LTV. HELOCs typically max out at 85-90% combined LTV depending on the lender, but your existing mortgage balance counts against that limit.
The key comparison variable your tab needs to surface is the blended rate impact. If your existing mortgage is already at a rate significantly below current market — say you locked in at 3.5% in 2021 — a cash-out refi that replaces that entire balance at today’s rates raises the cost on every dollar of your existing debt, not just the equity you’re accessing. A HELOC at a higher rate on a smaller draw amount may cost less over five years simply because it doesn’t touch your existing low-rate balance.
Your cash-out vs. HELOC tab should calculate 5-year total cost for each option: total interest paid on the full loan balance (cash-out) versus total interest paid on the draw amount only (HELOC) plus the unchanged existing mortgage payments. That comparison number is the honest decision metric.
Implementation Steps
1. Build two columns: “Cash-Out Refi” and “HELOC.” Include rows for: new loan balance, rate, monthly payment, closing costs, existing mortgage impact (eliminated vs. unchanged), and 5-year total interest.
2. Add a “Blended Rate Impact” row for the cash-out column: calculate the effective rate on the full balance post-refi versus the weighted average of your existing mortgage rate plus HELOC rate on the draw amount.
3. Output a single “5-Year Total Cost” figure for each option — total interest paid plus closing costs — as the final comparison row.
Pro Tips
This tab is only relevant when you’re actively evaluating equity access. Don’t add it to a rate-and-term comparison — it introduces variables that don’t apply and clutters the analysis. Keep it as a separate, purpose-built tab that you activate only when cash-out or HELOC is on the table.
6. Include a 5-Year Total Cost Row — Not Just Monthly Payment
The Challenge It Solves
Monthly payment comparisons can be gamed. Extending a loan term from 20 years remaining to a fresh 30-year term will almost always produce a lower monthly payment — but the total interest cost over time increases substantially. A lender who leads with monthly payment and buries the term extension is showing you the most flattering number, not the most honest one.
The Strategy Explained
The 5-year total cost row is your final gut-check before choosing a quote. It captures total interest paid over 60 months plus closing costs — a number that accounts for both the rate differential and the fee load without being distorted by term-length manipulation.
Here’s how to build it. For each quote, calculate: (Monthly P&I payment × 60 months) + Total Closing Costs. Then subtract the principal reduction over those 60 months to isolate the interest-plus-fees figure. The result is a single, comparable number that represents what each offer actually costs you over a realistic time horizon — not an amortization projection stretching 30 years into the future, but a five-year window that matches how most homeowners actually think about their financial planning.
Returning to our Virginia homeowner: Quote A at 6.375% with $6,200 in closing costs produces a monthly P&I of approximately $2,371. Over 60 months, that’s $142,260 in payments. Quote B at 6.50% with $2,800 in costs produces roughly $2,402 per month, or $144,120 over 60 months. Add closing costs: Quote A totals approximately $148,460; Quote B totals approximately $146,920. Over five years, Quote B is marginally cheaper — despite the higher rate — because the closing cost difference more than offsets the rate gap at that time horizon.
The Freddie Mac Primary Mortgage Market Survey provides weekly rate trend data that can help you contextualize whether current rates represent a favorable entry point for a five-year cost comparison — useful context for the header section of your spreadsheet.
Implementation Steps
1. Add a “5-Year Total Payments” row: Monthly P&I × 60.
2. Add a “5-Year Total Cost” row: 5-Year Total Payments + Closing Costs.
3. Highlight the lowest 5-Year Total Cost figure — this is your primary decision metric, weighted more heavily than monthly payment or note rate alone.
Pro Tips
If a lender’s offer looks attractive on monthly payment but loses on 5-year total cost, dig into why. The most common culprits are a term reset (20 years remaining extended back to 30) or closing costs rolled into the loan balance rather than paid upfront. Both inflate the true cost while suppressing the monthly payment number.
7. Use a Rate Lock Comparison Column to Account for Timing Risk
The Challenge It Solves
A 30-day rate lock and a 60-day rate lock on the same quoted rate are not the same offer. Longer locks carry a pricing premium — typically reflected in a slightly higher rate or additional points — and not all lenders offer float-down options if rates drop after you lock. Without a lock-period column in your spreadsheet, you may be comparing a 30-day quote against a 60-day quote without realizing the rate differential is partially a lock-premium, not a genuine lender pricing difference.
The Strategy Explained
Rate lock comparison adds a timing-risk layer to your analysis that most homeowners completely ignore. The relevant variables are: lock period (30, 45, or 60 days), whether a float-down option is available and at what cost, and whether the quoted rate is achievable within the lock window given your transaction timeline.
Build a lock-period column for each quote that captures: stated lock period, lock expiration date based on your estimated closing timeline, and a float-down flag (Yes/No/Cost). Cross-reference the lock expiration against the FHFA PMMS weekly rate trend — if rates have been trending downward, a float-down option has real value. If rates have been stable or rising, the float-down premium may not be worth paying.
The practical risk this column prevents: a homeowner selects Quote A at 6.375% on a 30-day lock, but the refinance takes 45 days to close due to appraisal scheduling or title delays. The lock expires, the rate reprices, and the economics of the deal change. A quote with a 45-day lock at 6.40% might have been the smarter choice for that transaction timeline — but only a lock-period column in your spreadsheet would have surfaced that tradeoff before you committed.
In Virginia markets like Fairfax County and the Richmond metro (Henrico and Chesterfield), where purchase and refinance transaction volumes can affect appraisal turnaround times, building in a realistic closing timeline estimate before selecting a lock period is particularly important.
Implementation Steps
1. Add a “Lock Period” column for each quote: 30, 45, or 60 days as disclosed by the lender.
2. Add a “Lock Expiration Date” column: today’s date plus lock period. Compare against your estimated closing date — flag any quote where the lock expires before your realistic closing window.
3. Add a “Float-Down Available” column (Yes/No) and, if yes, the cost of the float-down option in points or basis points. Factor this cost into your APR calculation if you intend to use it.
Pro Tips
Ask every lender directly: “What happens to my rate if this transaction doesn’t close within the lock period?” The answer tells you a great deal about how that lender manages pipeline risk — and what your exposure is if the timeline slips. Lenders who offer free lock extensions under certain conditions are offering real value that doesn’t show up in the rate column.
Your Implementation Roadmap
Start with strategies 1 and 2 as your non-negotiable foundation. APR normalization and the break-even clock are the two columns that make every other analysis meaningful. Without them, you’re still comparing marketing numbers, not real costs.
Add strategy 3 — the fee separation columns — as your second build step. Knowing which fees are negotiable before you call a lender back is the difference between a productive conversation and a frustrating one.
Layer in strategy 4 (PMI removal trigger) if your current LTV is anywhere in the 78-85% range. That single data point can flip the entire decision, especially when monthly PMI savings get added to the break-even calculation.
Use strategy 5 (cash-out vs. HELOC tab) only when equity access is actively on the table. It’s a purpose-built tool for a specific decision, not a default column in every refi comparison.
Strategy 6 (5-year total cost row) should be your final gut-check before choosing a quote. If a lender’s offer wins on monthly payment but loses on 5-year total cost, that’s a red flag worth investigating before you commit.
Strategy 7 (rate lock comparison) becomes critical when your transaction timeline is uncertain or when rates are trending in a direction that makes lock period selection meaningful. Don’t skip it in a volatile rate environment.
The goal of this spreadsheet isn’t perfection — it’s clarity. A clear, honest picture of what each offer actually costs you over a realistic time horizon is worth more than any single rate comparison tool a lender hands you.
If you’d rather have Duane Buziak, the Refi Guy, run this analysis for you using real quotes from hundreds of wholesale lenders, reach out directly. Call (804) 212-8663 now for your free soft-pull rate analysis — no credit impact, no obligation, no paperwork chase required. A soft-pull pre-qualification is available to get started right now.
2 Responses