Why a Comparison Spreadsheet Helps
When you receive Loan Estimates from three different lenders, comparing them visually on paper or in your head is nearly impossible. Lender A has a lower rate; Lender B has lower closing costs; Lender C is offering a lender credit. Which one actually costs less over the next 5 years? The answer depends on a cascade of calculations that don't resolve without a structured tool.
A comparison spreadsheet serves two purposes. First, it forces you to gather the right data from each Loan Estimate — because you can't fill in the formula columns without the inputs. Second, it shows the results side by side in a format where differences are immediately visible. The best offer is usually obvious once the numbers are properly aligned.
This guide walks through exactly how to build an effective refinance comparison spreadsheet, gives you every formula you need, shows a filled-in example, and explains when the RefinanceUSA calculator is a better tool than a spreadsheet.
Spreadsheet Structure: Rows and Columns
Set up your spreadsheet with lenders as columns and data points as rows. This orientation makes side-by-side comparison easy — you scan across a row to compare the same data point between lenders, and scan down a column to review one lender's full offer.
Section 1: Loan Inputs (from the Loan Estimate)
These rows are entered manually from each lender's Loan Estimate document:
| Row Label | Where to Find It | Notes |
|---|---|---|
| Lender name | Top of Loan Estimate | Include loan officer name and contact |
| Interest rate | Page 1, Loan Terms box | Note rate only — not APR |
| Loan amount | Page 1, Loan Terms box | Confirm it matches your actual balance |
| Loan term (years) | Page 1, Loan Terms box | 30, 20, or 15 years? |
| Loan type | Page 1, Loan Information | Conventional, FHA, VA, fixed/ARM |
| APR | Page 1, Comparisons section | Includes fees amortized over loan life |
| Rate lock period (days) | Page 1, Rate Lock box | 30, 45, or 60 days? |
Section 2: Closing Cost Inputs (from Loan Estimate Page 2)
| Row Label | Loan Estimate Section |
|---|---|
| Origination fee | Section A — Origination Charges |
| Points paid (% of loan) | Section A — Discount Points |
| Lender credits | Section A — Lender Credits (negative number) |
| Appraisal fee | Section B |
| Title + settlement fees | Section C |
| Government recording fees | Section E |
| Total closing costs (A+B+C+E) | Formula (sum of above) |
| Cash needed to close | Page 1, Estimated Cash to Close |
Note: Do NOT include prepaids (Section F) and initial escrow (Section G) in your closing cost comparison. These are advance payments of ongoing expenses you'd make regardless of which lender you choose. Including them inflates one lender's cost artificially if their escrow estimate differs.
Section 3: Your Current Loan (reference column)
Add a "Current Loan" column with your existing mortgage details:
- Current interest rate
- Current remaining balance
- Current remaining term (months)
- Current monthly P&I payment (calculate with PMT formula or look at your statement)
This reference column is what every lender offer gets compared against for monthly savings and break-even calculations.
The 5 Formulas You Need
Once your inputs are filled in, these formulas populate the most important output rows automatically. In these examples, Lender A data is in column B, Lender B in column C, etc. Input rows are labeled with their row numbers (adjust to your actual spreadsheet).
Formula 1: Monthly P&I Payment
The PMT function calculates the monthly principal and interest payment given a rate, number of periods, and loan amount.
Where: B2 = interest rate (decimal, e.g. 0.0625), B4 = term in years, B3 = loan amount
Example: For a $310,000 loan at 6.25% for 30 years: =PMT(0.0625/12, 30*12, -310000) = $1,909/month
Important: PMT returns the payment as a negative number (cash outflow). Wrap in ABS() or negate with a minus sign in front of the loan amount (as shown above) to get a positive result.
Formula 2: Monthly Savings vs. Current Payment
Where CurrentLoanPayment is your current P&I (positive), B_NewMonthlyPayment is from Formula 1
If your current P&I is $2,105/month and Lender A's new payment is $1,909/month, monthly savings = $196/month. A positive number means the refinance saves money; negative means it would cost more per month (which would only make sense if it dramatically shortens your loan term).
Formula 3: Break-Even Point (months)
CEILING rounds up to the nearest whole month
Example: $7,200 closing costs ÷ $196/month savings = 36.7 → 37 months break-even. This tells you: you need to stay at least 37 months after closing to come out ahead. Compare this number directly to how long you expect to stay in the home — that's the core decision.
Formula 4: Total Interest Over Loan Life
Example: ($1,909 × 30 × 12) − $310,000 = $687,240 − $310,000 = $377,240 in total interest. This number is most useful when comparing a 30-year vs. 15-year refinance option — the interest savings on a 15-year are enormous. It's less useful for comparing two 30-year offers from different lenders at similar rates.
Formula 5: Net Savings Over Your Planned Hold Period
Example: ($196 × 84 months) − $7,200 = $16,464 − $7,200 = $9,264 net savings if you stay 7 years. This is the most important output row — it tells you in dollars how much better off you are by refinancing with this specific lender over your actual expected time horizon. Compare this number across all lenders, not just the break-even point.
Worked Example: Three Lenders, One Decision
Here's a complete filled-in comparison for a $310,000 balance, current rate 7.0%, current monthly P&I: $2,063, planned stay: 7 years (84 months).
| Data Point | Lender A | Lender B | Lender C |
|---|---|---|---|
| Interest Rate | 6.25% | 6.10% | 6.50% |
| Loan Amount | $310,000 | $310,000 | $310,000 |
| Term | 30 yr | 30 yr | 30 yr |
| APR | 6.41% | 6.52% | 6.51% |
| Rate Lock | 45 days | 45 days | 30 days |
| CLOSING COSTS | |||
| Origination fee | $1,550 | $3,100 | $775 |
| Points paid | None | $3,100 (1 pt) | None |
| Lender credits | None | None | None |
| Appraisal | $550 | Waived | $550 |
| Title + settlement | $1,720 | $1,720 | $1,720 |
| Government recording | $210 | $210 | $210 |
| Total closing costs | $4,030 | $8,130 | $3,255 |
| CALCULATED OUTPUTS | |||
| Monthly P&I (new) | $1,909 | $1,881 | $1,961 |
| Monthly savings | $154 | $182 | $102 |
| Break-even | 27 months | 45 months | 32 months |
| Net savings at 7 years | $8,906 | $7,178 | $5,313 |
Analysis: Lender B has the lowest rate and highest monthly savings ($182/month) — but 1 discount point inflated closing costs to $8,130, pushing break-even to 45 months. If you planned to stay only 4 years, Lender B would be the worst choice. Over 7 years, Lender A generates the most net savings ($8,906) despite a higher rate than Lender B, because its closing costs are $4,100 lower. Lender C's lender credit keeps upfront costs lowest, but its higher rate (6.50%) leaves less monthly savings, and net savings trail Lender A by $3,593 over 7 years.
Lesson: Rate alone doesn't determine the best offer. The 6.25% loan from Lender A beats the 6.10% loan from Lender B over 7 years — entirely because of closing cost differences. This is only visible in a side-by-side comparison.
Common Mistakes in Refinance Comparison Spreadsheets
Even well-structured spreadsheets produce wrong answers when the inputs or formulas have these common errors:
Mistake 1: Comparing Offers With Different Point Levels
If Lender A quotes 6.0% with zero points and Lender B quotes 5.75% with 1 point, you cannot compare them by rate alone. Lender B's lower rate cost $3,100 on a $310,000 loan. Your spreadsheet must account for this via break-even — divide the extra cost of the points by the additional monthly savings they generate. If Lender B's 0.25% lower rate saves $48/month more than Lender A, and the point cost was $3,100, the point break-even is $3,100 ÷ $48 = 65 months. Add this to the base closing cost break-even. Use the Points Calculator to model this directly.
Mistake 2: Including Prepaids in Closing Cost Comparison
Prepaids (Section F: prepaid interest, insurance) and initial escrow (Section G) appear on the Loan Estimate and are often included in the "Estimated Cash to Close" total. But they represent advance payments of ongoing expenses — you'd pay these regardless of which lender you chose. Including them in your closing cost comparison makes one lender look more expensive simply because they collected more months of taxes or insurance upfront. Only compare Sections A+B+C+E.
Mistake 3: Comparing P&I to PITI
Your current monthly statement likely shows your PITI (Principal, Interest, Taxes, Insurance). When you use PMT() to calculate the new payment, it gives you P&I only. Comparing them directly will overstate the refinance savings by the amount of taxes and insurance in your current payment. Strip taxes and insurance from your current payment before computing savings, or make sure you're comparing P&I to P&I consistently.
Mistake 4: Using the Full Loan Term for Net Savings Instead of Your Hold Period
Calculating "lifetime savings" as monthly savings × 360 months (30 years) is misleading — most homeowners refinance again or sell well before that. Use your realistic hold period (5, 7, or 10 years) in the net savings formula. This is the calculation that actually determines which offer makes more financial sense for you.
Mistake 5: Forgetting PMI in the Monthly Savings Calculation
If the refinance eliminates PMI (by reaching 80% LTV via appreciation), the monthly savings include both the rate savings and the PMI savings. These must both be included in your "monthly savings" input — otherwise the break-even looks longer than it actually is. Check the PMI Removal Calculator to confirm your new LTV and whether PMI applies to the new loan.
When to Use the Online Calculator Instead of a Spreadsheet
A spreadsheet built from this guide will work well. But for most homeowners, the Mortgage Savings Calculator and Break-Even Calculator at RefinanceUSA accomplish the same goal faster, with less risk of formula error, and with some important features that a spreadsheet can't easily replicate.
What the Online Calculator Does That a Spreadsheet Can't
- Interactive hold-period slider: Drag a slider to see how net savings change as you plan for 3, 5, 7, or 10 years. A spreadsheet requires you to manually re-enter the hold period for each scenario.
- Visual break-even chart: The calculator shows a cumulative savings graph — you can see exactly when each refinance becomes profitable, month by month. This is difficult to build in a spreadsheet without advanced charting skills.
- All fee categories pre-structured: The Closing Cost Calculator breaks down costs by origination, appraisal, title, and government fees, matching the Loan Estimate structure exactly. Less room for miscategorization.
- No formula errors: Spreadsheet PMT formulas are easy to misconfigure (wrong sign, wrong rate decimal format, wrong period count). The calculator handles this automatically.
When a Spreadsheet Is Still Worth Building
A spreadsheet is valuable when you want to run many scenarios simultaneously — multiple loan amounts, multiple rate assumptions, or sensitivity analysis ("what if rates drop another 0.25% before I lock?"). Spreadsheets also let you share the comparison with a spouse, financial advisor, or accountant in a format they can edit and annotate. For standard 3-lender comparisons, the online calculator is faster. For complex multi-scenario planning, a spreadsheet gives you more flexibility.
The Hybrid Approach
Many borrowers use both: the online calculator for quick daily checks as they shop rates, and a spreadsheet for a final formal comparison once Loan Estimates are in hand. The Loan Estimate format is so standardized that plugging its numbers into either tool takes under 5 minutes per offer.
Understanding APR vs. Rate in Your Spreadsheet
Your spreadsheet should include both the interest rate (for payment calculations) and APR (for cost comparison). The APR — Annual Percentage Rate — incorporates most lender fees into the rate calculation, amortized across the full loan term. It's always higher than the note rate.
APR is most useful as a quick sanity check when two offers have the same rate but different fees. The offer with higher fees will have a higher APR. In your spreadsheet, a wide spread between rate and APR (e.g., 6.25% rate but 6.65% APR) signals high lender fees — investigate what's driving the gap before accepting the offer.
The APR blind spot: it assumes you hold the loan for the full 30-year term. If you refinance again or sell in 5 years, the APR understates the real cost of high-fee loans (because the fees are never fully amortized). This is why the break-even and net-savings-over-your-hold-period calculations are more useful for actual decisions than APR alone. Use the APR Calculator to compare two offers' true annual cost side by side.
Run Your Numbers — No Spreadsheet Required
Frequently Asked Questions
What columns should I include in a refinance comparison spreadsheet?
The essential columns are: interest rate, loan amount, loan term, monthly P&I payment, APR, total closing costs (Sections A+B+C+E only), origination fee, points paid, lender credits, rate lock period, cash needed to close, monthly savings vs. current payment, break-even in months, and net savings over your planned hold period. Derived rows (monthly payment, savings, break-even, net savings) should be formula-driven, not entered manually.
How do I calculate break-even in a spreadsheet?
Break-even in months = CEILING(TotalClosingCosts / MonthlySavings, 1). In Excel: if closing costs are in D2 and monthly savings are in E2, use =CEILING(D2/E2,1). Make sure monthly savings only includes P&I differences (not escrow). If you're eliminating PMI, add PMI savings to the monthly savings figure.
What is the PMT function and how do I use it for refinance comparison?
PMT(rate, nper, pv) calculates the periodic payment for a loan. For monthly payments: =PMT(AnnualRate/12, TermYears*12, -LoanAmount). The negative sign on the loan amount makes the result positive. Example for $310,000 at 6.25% for 30 years: =PMT(0.0625/12, 360, -310000) = $1,909/month.
What is the biggest mistake when comparing refinance offers in a spreadsheet?
Comparing offers at different point levels without adjusting for the break-even on those points. A lower-rate offer with 1 discount point looks better in a simple rate comparison, but the point cost may take 60+ months to recoup. Always compare offers at the same point level, or use break-even to account for the difference. Comparing PITI to P&I (mixing total payment vs. principal + interest) is the second most common error.