Home › Templates › Mortgage refinance spreadsheet

Mortgage Refinance Spreadsheet

A free mortgage refinance spreadsheet for Excel and Google Sheets: type your current loan and up to three Loan Estimates, and it works out each new payment, the monthly saving, the simple break-even and a stricter interest break-even, the points break-even, whether the new term restarts the clock, total interest and where you stand after the years you expect to keep the loan.

Download Excel template (.xlsx) 356 KB · no macros · Excel 2016+ and Google Sheets · v1.0, 3 Nov 2026

Use it in Google Sheets: choose File → Import → Upload in Sheets and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. No sign-up and no lender links: you type the numbers from your own statement and Loan Estimates.

Preview: the Example Compare and Example Schedule tabs. Fictional loan: $330,400 left at 8.375% with 323 payments to go, compared with three fictional offers; horizon 7 years.
$2,578.68current principal + interest (PMT), $502,512 interest still to pay
16 vs 17 monthsOffer 1: simple vs interest break-even
190 vs 19 monthsOffer 3 (20-year): the simple method misleads
$33,793Offer 3 ahead after 7 years, the best of the three
Example Compare tabCurrent loanLender A: 30-yr, no pointsLender B: 30-yr, 1.5 pointsLender C: 20-yr, 0.75 points
Rate8.375%7.125%6.75%6.875%
Term—30 years30 years20 years
Points—0%1.5%0.75%
Loan amount$330,400.00$330,400.00$340,807.11$330,400.00
Upfront costs—$5,595.00$10,407.11$7,928.00
Paid in cash at closing—$5,595.00$0.00$7,928.00
Monthly P&I$2,578.68$2,225.97$2,210.47$2,536.86
Monthly saving—$352.71$368.21$41.82
Simple break-even (months)—1629190
Interest break-even (months)—172819
Term check—Restarts the clock: 37 more monthsRestarts the clock: 37 more monthsPays off 83 months sooner
Last paymentOct 2053Nov 2056Nov 2056Nov 2046
Total interest left$502,512$470,948$454,962$278,445
Lifetime net advantage—$25,970$37,144$216,139
Net advantage after 7 years—$21,686$20,905$33,793
Points break-even (months)——10050
Example Schedule (fictional): net advantage of each refinance offer against keeping the current loan, month 0 to 360. All three start below zero by their upfront costs ($5,595, $10,407, $7,928) and cross zero in months 17, 28 and 19. After 7 years (month 84) they are ahead by $21,686, $20,905 and $33,793. Over the full term the 20-year offer ends $216,139 ahead; the 30-year offers end $25,970 and $37,144 ahead and fall after month 323, when the current loan would have been paid off.$0$50,000$100,000$150,000$200,000$250,000060120180240300360Months from nowyour horizon: 7 yearscurrent loan paid off (month 323)

Lender A: 30-yr, no points Lender B: 30-yr, 1.5 points Lender C: 20-yr, 0.75 pointsdots: interest break-even month

Net advantage = interest saved so far − upfront costs, month by month from the Example Schedule tab (first 360 of 480 months). The workbook draws the same lines as a native chart on the Compare tab.

What's inside the spreadsheet

TabWhat you typeWhat it calculates
Start hereNothingSteps, how the maths works, where each number sits on the CFPB forms, colour key, limits, disclaimer
CompareCurrent loan: balance, rate, months remaining, next payment date and statement P&I (optional), years you expect to keep the loan. Up to 3 offers: name, rate, term, discount points, rate without points (optional), other lender fees, third-party costs, lender credits, cash or roll-in, cash-outInput checks; loan amount; points in $; upfront costs; cash at closing; new P&I (PMT); monthly saving; simple and interest break-even; payoff months and dates; term check; total interest (CUMIPMT) and total of payments; lifetime net advantage; payments, balance and net advantage at your horizon; points break-even; a one-line verdict; a native chart
ScheduleNothing480 months side by side for the current loan and each offer: date, payment, interest, balance, cumulative interest, net advantage and the break-even month
Example Compare, Example ScheduleAlready filledThe fictional loan and three fictional offers on this page, so every formula can be seen working

This is a comparison tool, not a single-loan schedule. To see every payment of one loan with extra payments and the payoff date, use the loan amortization schedule; to plan paying down several debts, the debt payoff spreadsheet.

How to use it

  1. From your latest mortgage statement, type the balance, the interest rate and the months remaining in the yellow cells on Compare. Type 7.5% as 7.5% or 0.075. Optional: the next payment date and the principal + interest on the statement; row 12 says whether it matches PMT within $1.
  2. Type how many years you expect to keep the loan (B10). A move, a sale or another refinance all end the comparison early.
  3. For each Loan Estimate (up to 3), fill one column: rate and term from page 1; points from page 2, Section A; the rest of Section A as other lender fees; the third-party services as third-party costs; lender credits from Section J. Leave escrow deposits and prepaid interest out: you pay those whether you refinance or not.
  4. Pick Cash at closing or Roll into loan, and type any cash-out. If the lender quoted a zero-point rate, type it in "Rate without points" to get the points break-even.
  5. Read the Results: offer check OK, monthly saving, both break-even months, the term check, the lifetime figure and the net advantage at your horizon. Red means behind; amber means the new term ends later than your current loan.
  6. Open Schedule to see the month-by-month path: when each offer crosses zero, and whether it falls back once your current loan would have been paid off.

Worked example: three offers, three different answers

The fictional borrower owes $330,400 at 8.375% with 323 months left. PMT gives $2,578.68 a month, and the statement figure ($2,578.68) matches. Keeping the loan means $502,512.42 more interest. The borrower expects to stay 7 years.

Selected rows of the Example Schedule tab (green: the break-even month of that offer):

MonthDateCurrent balanceCurrent cum. interestOffer 1 cum. interestOffer 1 net adv.Offer 2 cum. interestOffer 2 net adv.Offer 3 cum. interestOffer 3 net adv.
0Nov 2026$330,400.00$0.00$0.00−$5,595.00$0.00−$10,407.11$0.00−$7,928.00
1Dec 2026$330,127.24$2,305.92$1,961.75−$5,250.83$1,917.04−$10,018.23$1,892.92−$7,515.00
2Jan 2027$329,852.58$4,609.93$3,921.93−$4,907.00$3,832.43−$9,629.61$3,782.14−$7,100.21
17Apr 2028$325,494.93$38,932.43$33,129.93$207.50$32,358.77−$3,833.44$31,663.18−$658.75
19Jun 2028$324,878.81$43,473.65$36,995.74$882.91$36,132.32−$3,065.77$35,313.60$232.06
28Mar 2029$321,997.67$63,800.60$54,304.32$3,901.28$53,021.70$371.80$51,535.34$4,337.26
60Nov 2031$310,161.37$134,481.94$114,580.64$14,306.30$111,756.09$12,318.75$106,258.72$20,295.22
84Nov 2033$299,385.33$185,594.13$158,312.99$21,686.14$154,282.40$20,904.62$143,873.58$33,792.55

Two break-even methods, and why the sheet shows both

Simple break-even = upfront costs ÷ (current P&I − new P&I), rounded up. It answers "how many payments until the lower payment has paid back the fees?" It works when the new loan has the same balance and about the same remaining term. It fails when the term changes, when costs are rolled in or when you take cash out, because the payment then mixes a rate change with a change in how fast you repay principal.

Interest break-even = the first month when cumulative interest on the current loan − cumulative interest on the new loan ≥ upfront costs. The Schedule tab runs both loans month by month and records the crossing. This is the same as comparing what you would have paid plus what you would still owe: (payments so far + remaining balance + costs paid in cash − cash-out) for the new loan against (payments so far + remaining balance) for the current one. Interest on a cash-out amount counts against the refinance, which is the cost of borrowing it.

The sheet also shows the net advantage at your horizon (the same quantity at the month you expect to sell or refinance again) and the lifetime net advantage. A new 30-year loan can be ahead at year 7 and behind over its full life, or the other way round; the Schedule shows where the line turns down once the current loan would have been paid off.

Discount points and lender credits

The CFPB's page How should I use lender credits and points says: "Points lower your interest rate, in exchange for paying more at closing. Lender credits lower your closing costs up front, in exchange for a higher interest rate." It adds that "One point equals one percent of the loan amount" and that "Points can be a good choice if you plan to keep your loan for a long time."

The CFPB's own table uses a $180,000 30-year loan at 5.0% with zero points: paying 0.375 points ($675) gets 4.875% and "Pay $14 less each month"; taking a $675 credit means 5.125%. The spreadsheet's formulas give $966.28 and $952.57, a saving of $13.70 (CFPB rounds to $14), so the points break-even is $675 ÷ $13.70 → 50 months. That case is one of the checks run on the file.

The CFPB's tip is the reason the sheet has a horizon: if you are unsure, ask the loan officer "to calculate the total costs over a few different possible timeframes" and "Choose the shortest amount of time, the longest amount of time, and the most likely amount of time you can see yourself keeping the loan." Change B10 to each of those and read the horizon rows.

Where the numbers come from: Loan Estimate and Closing Disclosure

The CFPB's What is a Loan Estimate? says it is "a three-page form that you receive after applying for a mortgage", that "The lender must provide you a Loan Estimate within three business days of receiving your application", and that "All lenders are required to use the same standard Loan Estimate form." The CFPB's Loan Estimate explainer walks through each line, including "Services You Cannot Shop For" and "Services You Can Shop For". Because the form is standard, the same cells on the Compare tab fit every lender's estimate.

Before closing, the Closing Disclosure is "a five-page form that provides final details about the mortgage loan you have selected", and "The lender is required to give you the Closing Disclosure at least three business days before you close on the mortgage loan." Type its final figures over the estimate's to re-check the break-even; the Closing Disclosure explainer shows where each section sits.

Market rates. Freddie Mac's Primary Mortgage Market Survey reported: "The 30-year fixed-rate mortgage averaged 7.24% as of October 29, 2026" and "The 15-year fixed-rate mortgage averaged 6.60%" (retrieved 3 Nov 2026). Freddie Mac says the results "are based on data from our Loan Product Advisor (LPA): mortgage rates collected from thousands of loan applications". It is a national average of purchase applications, not a quote for your loan; the rates in the example are fictional. Compare your own Loan Estimates.

Points and taxes on a refinance

IRS Publication 936 (2025) says: "Generally, points you pay to refinance a mortgage aren't deductible in full in the year you pay them. This is true even if the new mortgage is secured by your main home." If part of the proceeds substantially improves your main home and you meet the tests listed there, that part can be deducted in the year paid, and "You can deduct the rest of the points over the life of the loan." Its Example 1 spreads $2,000 of points over a 15-year loan: "You deduct $67 [($2,000 ÷ 180 months) × 6 payments]". The spreadsheet leaves taxes out; any deduction would make points slightly cheaper than the sheet shows, and only if you itemize.

The formulas, explained

Exact formulas from the blank Compare and Schedule tabs. On Compare, B5–B10 hold the current loan, C15–E24 the offers (C16 rate, C17 term, C18 points, C20–C22 fees, third-party costs and credits, C23 the cost choice, C24 cash-out) and row 25 the offer check. On Schedule, row 7 is month 0 and row 8 month 1.

FieldFormula
Compare: current P&I (B33)=IF($B$11<>"OK","",PMT($B$6/12,$B$7,-$B$5))
Compare: loan amount, Offer 1 (C28)=IF(C$25<>"OK","",IF(C23="Roll into loan",($B$5+N(C24)+N(C20)+N(C21)-N(C22))/(1-N(C18)),$B$5+N(C24)))
Compare: points $ (C29)=IF(C$25<>"OK","",N(C18)*C28)
Compare: upfront costs (C30)=IF(C$25<>"OK","",C29+N(C20)+N(C21)-N(C22))
Compare: new P&I (C33)=IF(C$25<>"OK","",PMT(C16/12,C17*12,-C28))
Compare: simple break-even (C35)=IF(C$25<>"OK","",IF(C30<=0,0,IF(C34<=0,"No monthly saving",ROUNDUP(C30/C34,0))))
Compare: interest break-even (C36)=IF(C$25<>"OK","",IF(C30<=0,0,IF(COUNT(Schedule!$L$8:$L$487)=0,"Not reached",MIN(Schedule!$L$8:$L$487))))
Compare: term check (C39)=IF(C$25<>"OK","",IF(C38>0,"Restarts the clock: "&C38&" more months",IF(C38<0,"Pays off "&-C38&" months sooner","Same payoff month")))
Compare: total interest left, current (B41)=IF($B$11<>"OK","",-CUMIPMT($B$6/12,$B$7,$B$5,1,$B$7,0))
Compare: lifetime net advantage (C43)=IF(C$25<>"OK","",$B$41-C41-C30)
Compare: net advantage at horizon (C47)=IF(OR(C$25<>"OK",$B$44=""),"",INDEX(Schedule!$K$8:$K$487,$B$44))
Compare: points break-even (C49)=IF(C48="","",IF(C48-C33<=0,"No saving from points",ROUNDUP(C29/(C48-C33),0)))
Schedule: Offer 1 interest, month 1 (H8)=IF(I7="","",IF(I7<=0.005,0,I7*Compare!C$16/12))
Schedule: Offer 1 payment (G8)=IF(I7="","",IF(I7<=0.005,0,MIN(Compare!C$33,I7+H8)))
Schedule: Offer 1 balance (I8)=IF(I7="","",IF(I7<=0.005,0,I7+H8-G8))
Schedule: Offer 1 net advantage (K8)=IF(OR(I8="",F8=""),"",F8-J8-Compare!C$30)
Schedule: Offer 1 break-even month flag (L8)=IF(K8="","",IF(AND(K8>=0,K7<0),A8,""))

PMT, IPMT and CUMIPMT. Microsoft's PMT page says it "calculates the payment for a loan based on constant payments and a constant interest rate" and that "The payment returned by PMT includes principal and interest but no taxes, reserve payments, or fees sometimes associated with loans." It also warns to be consistent with units ("use 12%/12 for rate"). The Schedule's interest column is what IPMT returns for each period, written as balance × rate/12 so each row is visible; total interest uses CUMIPMT, which "Returns the cumulative interest paid on a loan between start_period and end_period" and gives #NUM! when the rate is 0 or less, which is why the offer check requires a rate above 0.

Rolled-in points without a circular reference. Points are a percentage of the final loan, and the final loan includes the points. Solving L = balance + cash-out + points × L + fees + third-party − credits gives L = (balance + cash-out + fees + third-party − credits) ÷ (1 − points), which is what C28 does.

Unrounded payments. PMT is kept unrounded so each loan reaches a zero balance exactly on its last month. Your servicer rounds to the cent, so real balances can differ by a few cents.

Common mistakes

Limits

FAQ

How do you calculate the break-even point on a mortgage refinance?

The simple method divides the upfront costs by the monthly payment saving: in the example, $5,595 ÷ $352.71 = 15.9, so 16 months. The spreadsheet also gives an interest break-even: the first month when the interest you have saved so far is at least the upfront costs. For the same offer that is month 17. Use the interest version when the term, the loan amount or cash-out changes, because the payment alone does not show those.

Why can the simple refinance break-even be misleading?

Because a payment change mixes interest with principal. A shorter term raises the principal part of each payment, so the payment barely drops even though far less interest is paid. In the example, the 20-year offer saves only $41.82 a month, so the simple break-even is 190 months, yet its interest break-even is month 19 and it is $33,793 ahead after 7 years. The reverse happens with a new 30-year term: the payment drops partly because the loan is stretched.

Are discount points worth it when refinancing?

Only if you keep the loan longer than the points take to pay back. The CFPB says "Points can be a good choice if you plan to keep your loan for a long time" and that "One point equals one percent of the loan amount." Ask the lender for the rate without points and type it in: the sheet divides the points cost by the monthly P&I they save. In the example, 1.5 points cost $5,112 and save $51.17 a month, a points break-even of 100 months, longer than the 7-year horizon.

Does refinancing into a new 30-year loan cost more in the long run?

It can. If 27 years are left and you take a new 30-year loan, you add years of payments. The Term check row flags this ("Restarts the clock: 37 more months" in the example) and the lifetime net advantage row shows the total effect. A positive lifetime figure means the lower rate still outweighs the extra years; a negative one means the lower payment is bought with more total interest.

Should I roll closing costs into the new loan or pay them in cash?

Rolling them in keeps cash in your pocket but you borrow the costs and pay interest on them for the whole term. The sheet solves the bigger loan amount exactly: (balance + fees + third-party costs − credits) ÷ (1 − points), because points are a percentage of the final loan. In the example, Offer 2's loan grows from $330,400 to $340,807. Both choices start the net advantage at minus the same upfront costs; the rolled-in loan then pays interest on them.

Can I deduct points paid on a refinance?

IRS Publication 936 (2025) says: "Generally, points you pay to refinance a mortgage aren't deductible in full in the year you pay them. This is true even if the new mortgage is secured by your main home." It explains that you generally deduct them over the life of the loan, with an exception for the part of the proceeds used to substantially improve your main home. The spreadsheet does not model taxes; check the publication or a tax professional.

Where do I find the numbers for each refinance offer?

On the Loan Estimate. The CFPB says lenders must provide it "within three business days of receiving your application" and that "Points are listed on your Loan Estimate and on your Closing Disclosure on page 2, Section A." Lender credits are on page 2, Section J. Before closing, the Closing Disclosure arrives "at least three business days before you close" so you can compare the final numbers with the estimate.

Can I use this refinance spreadsheet in Google Sheets?

Yes. In Google Sheets choose File → Import → Upload and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. The formulas use PMT, CUMIPMT, EDATE, SUMIFS, INDEX, MIN, MAX, ROUND, ROUNDUP, TEXT and simple IF checks, which exist in Excel 2016+, Google Sheets and LibreOffice. No macros, no dynamic arrays.

Sources

All retrieved 3 November 2026.

Related templates

Educational template only, not financial, tax or legal advice and not a recommendation to refinance or to choose any lender or loan. Example data is fictional: the loan, rates, fees and offers are made up to show the formulas. Real offers depend on your credit, property, loan amount and the market; check every figure against your Loan Estimate and Closing Disclosure. CFPB, Freddie Mac, IRS and Microsoft material was retrieved on 3 November 2026.