Duane Buziak’s 7 Strategies to Build a Mortgage Comparison Spreadsheet Template That Shows the True Cost of Each Offer

Duane Buziak

Mortgage Maestro | NMLS #1110647 | Coast2Coast Mortgage LLC

Licensed mortgage broker serving Virginia, Florida, Tennessee, Georgia, Washington DC, North Carolina, South Carolina, and Maryland, specializing in VA home loans and first-time homebuyer programs.

A mortgage comparison spreadsheet template works when every offer sits on the same footing: same loan amount, same date, same assumptions, with rate, APR, fees, and break-even time side by side. If one column was quoted on a different credit score or down payment, the sheet is only comparing paperwork. Duane Buziak walks through seven strategies for building a template that exposes the true cost of each offer, so you can line up results from a free broker search against any other quote. The comparison can start with a NoTouch Credit Pull, which is a soft pull, so gathering numbers does not cost you a hard inquiry. Then you can dare any offer to compare.

1. Standardize the Inputs Before You Compare Anything

Start with the mistake. Most lopsided comparisons are not lender differences at all. They come from quotes built on different assumptions, and the sheet makes the gap look like pricing.

Suppose you request two quotes on a $400,000 purchase. One comes back at 80% LTV and the other at 90% LTV. LTV (loan-to-value) is the loan amount divided by the home’s value. At 80%, the loan is $320,000 with $80,000 down. At 90%, the loan is $360,000 with $40,000 down. The second quote will show a larger payment, possibly mortgage insurance, and different pricing, and none of that is the lender’s doing. A shared scenario row at the top of the sheet makes this visible before you draw a conclusion.

Put the scenario in one block that every quote must match:

1. Loan amount, property value, and down payment (which gives you LTV).
2. Credit score band, using the same band for every request.
3. Property type and occupancy (primary residence, second home, or investment).
4. Loan term and loan program.
5. Quote date, since pricing moves daily.

Then request every quote on those exact inputs. Credit is the hardest input to hold steady, because separate hard pulls can return slightly different scores. A NoTouch Credit Pull soft comparison lets you run one consistent credit profile across multiple options without a hard inquiry, so the credit assumption is identical in every column.

Add a check cell beside each quote that reads “matches scenario: yes or no.” If it says no, fix the quote before it earns a column. To measure this, track the share of quotes that match the scenario row exactly. The target is all of them. A sheet with even one mismatched quote needs that column rerun, not adjusted by guesswork.

2. Build Columns for Rate, APR, and Points Separately

A single “rate” column hides the trade-offs that matter. The note rate sets your monthly principal and interest. Discount points are fees paid upfront to lower that rate, and a lender credit works in reverse: the rate goes up and the lender offsets part of your closing costs. APR (annual percentage rate) folds certain fees into a yearly cost figure so loans with different fee structures can be viewed on one scale. Those are four separate things, so they get four separate columns.

Here is a hypothetical illustration. A 6.00% rate with one point and a 6.50% rate with no points are not directly comparable by rate. The first costs more at closing and less each month. The APR on each reflects that mix, and it can come out differently than the headline rate suggests. Seeing rate, points, and APR in adjacent cells lets you ask the right question: what am I paying upfront, and what do I get for it?

How to set it up

1. Add columns for note rate, discount points (as dollars and as a percentage of the loan), lender credit, and APR.
2. Record APR from the Loan Estimate rather than from an advertisement or a verbal quote.
3. Add a net column: points minus credits, so each offer shows one upfront pricing number.
4. Place an APR disclosure note next to the columns: “APR is an annualized estimate based on the quoted fees and full loan term. Your actual cost depends on how long you keep the loan.”

The common mistake is ranking by rate alone, or treating APR as a complete cost measure. APR assumes you hold the loan for its full term and does not capture every cost, so a loan that is paid off or refinanced early can cost differently than its APR implies. Strategy 4 handles that gap.

The measure here is simple: every offer should show rate, APR, and net points or credits on a single row. If any of the three is blank, the offer is not ready to compare.

3. Transcribe the Loan Estimate Line by Line

Bottom-line cash to close is the number most people compare, and it is the least informative one. It blends lender fees, third-party fees, tax and insurance prepaids, and credits into a single figure. Two offers can land within a few hundred dollars of each other while being built completely differently.

Consider an illustration. Offer one has higher origination charges but lower third-party fees. Offer two is the reverse, with similar totals. The difference matters because lender-controlled charges are the ones you can ask about and negotiate, while third-party charges such as appraisal and title tend to follow outside pricing. Identical totals, different leverage.

The CFPB Loan Estimate explainer describes the standardized layout every lender must use, which is what makes line-by-line transcription possible. Verify your sheet’s rows against it.

1. Create rows for Section A (origination charges), Section B (services you cannot shop for), Section C (services you can shop for), Section E (taxes and government fees), Section F (prepaids), and Section G (initial escrow).
2. Subtotal lender-controlled fees separately from third-party fees.
3. Compare Section A first, because it reflects what the lender or broker charges for the loan itself.
4. Enter credits on their own row so they do not disappear into a net total.
5. Check that each Loan Estimate’s totals match your row sums, which catches transcription errors.

The mistake to avoid is comparing only the cash-to-close line. It hides the difference between a fee and a prepaid, and between a credit and a lower price. Prepaids like interest and escrow deposits are not costs of the loan in the same sense that origination charges are.

Measure the variance in Section A and in total lender-controlled fees between offers. When Section A differs materially and the rate does not, you have found the negotiating target.

4. Add a Break-Even Calculator for Points and Credits

Paying points is a bet on time. You spend money now to save money monthly, and the bet only pays if you keep the loan long enough. The break-even calculator turns that into a number you can compare with your plans.

The formula is upfront cost difference divided by monthly payment difference. Here is a worked, hypothetical example on a $400,000 30-year fixed loan:

• Offer A: 6.50% with no points. Principal and interest is about $2,528.27 per month.
• Offer B: 6.00% with one point, which is $4,000 upfront. Principal and interest is about $2,398.20 per month.
• Monthly savings: $2,528.27 minus $2,398.20 equals $130.07.
• Break-even: $4,000 divided by $130.07 equals about 30.8 months, or roughly 31 months.

If you stay in the home and keep the loan for five years (60 months), Offer B saves about $3,800 after recovering the point (29 months of savings at $130.07 is about $3,772). If you sell or refinance in two years, you lose money on the point. The numbers are illustrations, not quotes, but the logic carries to any real offer.

Putting it in the sheet

1. Add a cell for the upfront cost difference between each offer and your baseline offer.
2. Add a cell for the monthly principal and interest difference.
3. Divide to get break-even months.
4. Add an input for expected years in the home or before refinancing, and convert it to months.
5. Add a flag that turns on when break-even exceeds that horizon.

This also works in reverse for lender credits: a higher rate with a credit costs you monthly, so the question becomes how many months of higher payments the credit covers.

The classic mistake is paying points without estimating how long you will hold the loan. Be honest in the horizon cell, since people move and refinance more often than they plan to. Measure break-even months against your realistic time in the home. If the gap is thin, skip the points.

5. Model the Full Monthly Payment, Not Just Principal and Interest

Two offers at the same rate can produce different monthly payments, and the difference is often mortgage insurance. Imagine two offers on the same scenario at the same rate, where one carries a monthly mortgage insurance charge that is noticeably higher than the other’s. Principal and interest match, but the total payment does not, and a sheet that stops at P&I would call them equal.

PITI stands for principal, interest, taxes, and insurance, and it is the baseline for what you will pay each month. Add mortgage insurance and any HOA dues, and you have the number your budget actually feels.

1. Add columns for principal and interest, property taxes, homeowners insurance, mortgage insurance, and HOA dues, then a total.
2. Use one tax estimate and one homeowners insurance estimate across every row. These belong in the scenario block from Strategy 1, not in each quote.
3. Record mortgage insurance as quoted, and note whether it is monthly, upfront, or both.
4. Confirm the loan type before comparing. For 2026, FHFA sets the conforming loan limit at $832,750 for a one-unit property in most of the country, with a ceiling of $1,249,125 in high-cost areas. Limits vary by county, so check your county on the FHFA site. A loan above the limit is a jumbo, which can carry different pricing and underwriting.

The mistake that skews more sheets than any other is using different tax or insurance estimates in different columns. A lender’s quote may use a conservative tax figure and another may use a low one, and the total payment then reflects estimating habits rather than loan cost. Overwrite every estimate with your shared numbers.

Measure the spread in total monthly payment across offers using identical estimates. If the spread is small, the decision comes down to upfront cost and break-even. If it is large, look at mortgage insurance and loan structure first.

6. Track Rate Lock Terms and Quote Expiration

An unlocked quote is a snapshot, not a promise. A rate lock holds your rate and points for a set number of days while the loan is processed. A float-down option lets you take a lower rate if the market improves during the lock, usually for a fee or under conditions. Without lock terms in the sheet, you are comparing offers with different levels of certainty.

Suppose your purchase contract closes in 45 days and an offer includes a 30-day lock. That leaves 15 days uncovered, and a 60-day lock would close the gap, possibly at a different price. The cheaper-looking offer may not be cheaper once you add the longer lock or risk the exposure.

1. Add columns for lock length in days, lock fee, extension cost per day or per period, float-down (yes or no, with cost), and quote expiration date.
2. Enter your expected closing date and compute the days of cushion between lock expiry and closing.
3. Highlight any row where cushion is zero or negative.
4. Get lock terms confirmed in writing, since verbal assurances do not survive a delayed closing.

Compare quote dates as well. A quote from ten days ago reflects market conditions that no longer exist, so the quote-date column from your scenario block should flag stale rows.

The common mistake is comparing unlocked quotes as if they were guaranteed. A lower rate that is not locked can move before you lock it, while a slightly higher rate that is locked cannot. Measure lock coverage against your expected closing date in days of cushion. A cushion of at least a week or two is a reasonable target, because closings slip for reasons outside your control.

7. Add a Lender-Type Tab: Broker Access vs. a Single Shelf

The final tab compares where offers come from. A single lender can only offer its own products and pricing. A broker, which is what Duane Buziak is, can source loans from hundreds of wholesale lenders, so the same scenario can be shopped against many shelves instead of one. That structural difference is real, but it is not a guarantee of a lower price on any particular loan, so the sheet should test it rather than assume it.

Add a tab with a short reference table, then add a “source type” column to the main sheet.

Now run the test. Enter a direct-lender quote and a broker-sourced quote on the same scenario row, then compare APR, Section A fees, and break-even. You can generate the broker side by running Free Mortgage Search by Duane Buziak and entering the results next to any outside offer. The search starts with a NoTouch Credit Pull, so your credit profile stays consistent and no hard inquiry is triggered. That is the Dare to Compare approach: put any offer next to the others and let the numbers decide.

Two mistakes to avoid. First, comparing sources on different scenarios or dates, which undoes everything in Strategy 1. Second, treating the structural advantage of broker access as a promise. Sometimes a single lender wins on a given loan, and the sheet should be allowed to say so.

Measure total cost over your expected holding period for each source type: upfront costs plus monthly payments times months held, using the same scenario. That one number settles most comparisons.

Where to Start, and How the Pieces Fit Together

Build Strategies 1 through 3 first. A scenario block, separate rate, APR, and points columns, and a Loan Estimate transcription give you clean inputs, and nothing after them works without that. Then add the break-even calculator and the full payment model, which turn the clean numbers into decisions about points, credits, and affordability. Add lock tracking as your closing date firms up. Finish with a soft-pull NoTouch Credit Pull search and a Dare to Compare check, so you are testing outside quotes against broker-sourced offers on identical terms.

Your dream home is within reach. Start comparing personalized mortgage rates from trusted lenders today and discover the loan solution that fits your budget and goals. Compare rates now and take the first step toward securing your home with expert guidance every step of the way.