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.
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.
| Example Compare tab | Current loan | Lender A: 30-yr, no points | Lender B: 30-yr, 1.5 points | Lender C: 20-yr, 0.75 points |
|---|---|---|---|---|
| Rate | 8.375% | 7.125% | 6.75% | 6.875% |
| Term | — | 30 years | 30 years | 20 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) | — | 16 | 29 | 190 |
| Interest break-even (months) | — | 17 | 28 | 19 |
| Term check | — | Restarts the clock: 37 more months | Restarts the clock: 37 more months | Pays off 83 months sooner |
| Last payment | Oct 2053 | Nov 2056 | Nov 2056 | Nov 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) | — | — | 100 | 50 |
Lender A: 30-yr, no points Lender B: 30-yr, 1.5 points Lender C: 20-yr, 0.75 pointsdots: interest break-even month
What's inside the spreadsheet
| Tab | What you type | What it calculates |
|---|---|---|
| Start here | Nothing | Steps, how the maths works, where each number sits on the CFPB forms, colour key, limits, disclaimer |
| Compare | Current 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-out | Input 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 |
| Schedule | Nothing | 480 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 Schedule | Already filled | The 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
- 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%or0.075. Optional: the next payment date and the principal + interest on the statement; row 12 says whether it matches PMT within $1. - Type how many years you expect to keep the loan (B10). A move, a sale or another refinance all end the comparison early.
- 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.
- 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.
- 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.
- 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.
- Lender A: 30-yr, no points at 7.125%: $5,595 of fees paid in cash. The payment falls to $2,225.97, a saving of $352.71. Simple break-even: $5,595 ÷ $352.71 → 16 months; interest break-even: month 17. It restarts the clock (37 more months) but still ends $25,970 ahead over its full life, and $21,686 ahead after 7 years.
- Lender B: 30-yr, 1.5 points at 6.75%, costs rolled in: the loan becomes $340,807.11 because points are charged on the bigger loan ($5,112.11 of points). It has the lowest payment ($2,210.47) and a bigger lifetime figure than Offer 1 ($37,144), but after 7 years it is only $20,905 ahead, less than Offer 1. The reason is the points: against the same lender's 7.125% zero-point rate they save $51.17 a month, a points break-even of 100 months, longer than the 84-month horizon.
- Lender C: 20-yr, 0.75 points at 6.875%: the payment drops just $41.82, so the simple method says 190 months to break even. The interest method says month 19: most of each payment now goes to principal, and the loan ends 83 months sooner. It is $33,793 ahead after 7 years and $216,139 over its life, but it needs the higher monthly payment to be affordable.
Selected rows of the Example Schedule tab (green: the break-even month of that offer):
| Month | Date | Current balance | Current cum. interest | Offer 1 cum. interest | Offer 1 net adv. | Offer 2 cum. interest | Offer 2 net adv. | Offer 3 cum. interest | Offer 3 net adv. |
|---|---|---|---|---|---|---|---|---|---|
| 0 | Nov 2026 | $330,400.00 | $0.00 | $0.00 | −$5,595.00 | $0.00 | −$10,407.11 | $0.00 | −$7,928.00 |
| 1 | Dec 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 |
| 2 | Jan 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 |
| 17 | Apr 2028 | $325,494.93 | $38,932.43 | $33,129.93 | $207.50 | $32,358.77 | −$3,833.44 | $31,663.18 | −$658.75 |
| 19 | Jun 2028 | $324,878.81 | $43,473.65 | $36,995.74 | $882.91 | $36,132.32 | −$3,065.77 | $35,313.60 | $232.06 |
| 28 | Mar 2029 | $321,997.67 | $63,800.60 | $54,304.32 | $3,901.28 | $53,021.70 | $371.80 | $51,535.34 | $4,337.26 |
| 60 | Nov 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 |
| 84 | Nov 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.
| Field | Formula |
|---|---|
| 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
- Using the original loan amount or term. Use today's balance and the months actually left; otherwise the current payment and interest are wrong. The statement check catches most of this.
- Comparing payments that include escrow. Taxes and insurance stay the same whichever loan you have. Compare principal and interest only.
- Counting escrow deposits and prepaid interest as refinance costs. They appear on the Loan Estimate, but they fund your own tax and insurance bills and interest you owe either way, so they do not change the comparison.
- Reading a lower payment as a saving. A new 30-year term lowers the payment partly by adding years. Look at the term check and the lifetime row.
- Paying points without a horizon. Points only pay back if you keep the loan past the points break-even.
- Comparing offers with different points. The CFPB suggests: "When comparing offers from different lenders, ask for the same amount of points or credits from each lender."
Limits
- Fixed-rate loans with monthly payments only. Adjustable-rate loans, interest-only periods and balloon payments are not modelled.
- Principal and interest only. Property tax, homeowner's insurance, mortgage insurance and escrow are left out.
- No time value of money: a dollar saved in year 20 counts the same as one saved today, and cash you keep (or take out) earns nothing in the sheet.
- No taxes, no extra principal payments, no prepayment penalties. Up to 3 offers and 480 months per file.
- Recalculation was tested in LibreOffice; the file uses only functions that also exist in Excel 2016+ and Google Sheets.
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
- CFPB, How should I use lender credits and points (also called discount points)? (last reviewed 19 Oct 2023)
- CFPB, What is a Loan Estimate? and the Loan Estimate explainer
- CFPB, What is a Closing Disclosure? and the Closing Disclosure explainer
- Freddie Mac, Primary Mortgage Market Survey (weekly averages as of 29 Oct 2026)
- IRS, Publication 936 (2025), Home Mortgage Interest Deduction
- Microsoft Support: PMT function, IPMT function, CUMIPMT function
Related templates
- Loan amortization scheduleOne loan, every payment, extra payments
- Debt payoff spreadsheetPlan paying down several debts
- Rental property spreadsheetCash flow and returns on a rental
- Budget spreadsheetWhere a lower payment goes
- Net worth trackerHome equity and debts over time
- All templatesFree spreadsheets
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.