Rate and Term Refinance Closing Cost Recoup Calculator Excel: Why 19.5 Months Becomes 37

The lender's quote for your duplex says it plainly: rate drops from 8.25 to 6.75 percent, payment falls from $2,404.05 to $2,023.35, cash to close $7,408.59, break-even in 19.5 months. A rate and term refinance closing cost recoup calculator in Excel exists to check that last line before you sign, because on a rental financed with a DSCR loan the quote leaves out the largest cost of the whole transaction, counts money that is not a cost at all, and answers a question you did not ask. You do not need the month the fees come back. You need to know which loan is cheapest for the number of years you will actually own the building.

This article builds that calculator on one example: a duplex bought with a $320,000 DSCR loan at 8.25 percent, originated in December 2023, with a five-year step-down prepayment penalty. The rates, fees and timeline are example figures. The rules on penalties, escrow refunds and points come from federal regulations and IRS Publication 527, cited where they are used.

The 19.5 months on the quote, rebuilt

The lender divides cash to close by the monthly payment saving: $7,408.59 divided by $380.71 is 19.5 months. Three things are wrong with that division, and they push in opposite directions.

The penalty is missing because it is not a closing cost. It sits in the payoff letter from your current servicer, which the new lender wires money against without printing it on the page you read. On this loan it is almost twice the fees. Counted properly, the refinance needs 37 months to pay for itself, and if you plan to sell the duplex in two or three years, the quote that looked like a 19-month decision is a loss.

Sort the closing table into costs and cash that moves

Before anything goes into the spreadsheet, split the closing statement into money you spend and money that only changes accounts. If your lender issues a Closing Disclosure, the federal format already separates them: loan costs sit under Origination Charges, Services Borrower Did Not Shop For and Services Borrower Did Shop For, while Prepaids and Initial Escrow Payment at Closing are separate subheadings under other costs (12 CFR 1026.38(f) and (g)). The rule that requires that form covers consumer loans, so a lender on a rental refinance may hand you its own settlement statement instead; sort the lines yourself.

• Cost: points, origination and underwriting fees, appraisal, credit report, lender's title policy, settlement fee, recording and transfer taxes, and the prepayment penalty on the loan being paid off.
• Not a cost: prepaid interest (interest for the days you hold the new loan, at a lower rate than the old loan's payoff interest for the same days), the insurance premium if the policy renewal was due anyway, and the escrow deposit.
• Cash timing to track: the refund of the old escrow balance. On a consumer mortgage the servicer must return it within 20 business days of payoff (12 CFR 1024.34(b)). A rental loan is business-purpose credit under Regulation Z (comment 3(a)-4: credit to acquire, improve or maintain a rental that is not owner-occupied is deemed business purpose), and Regulation X exempts business-purpose loans (12 CFR 1024.5(b)(2)), so that clock may not bind your servicer. Put the $2,380 on a follow-up list with a date.

The skipped payment that loan officers mention belongs on the same list. Interest is paid in arrears, so the month without a payment was already covered by the payoff interest on the old loan and the prepaid interest on the new one. If your escrow is running short at reset time, the rental property escrow analysis tracker shows how the new servicer will size the deposit.

The DSCR prepayment penalty decides the timing

On an owner-occupied qualified mortgage, a prepayment penalty is capped by Regulation Z at three years and at 2 percent of the balance prepaid in years one and two, 1 percent in year three (12 CFR 1026.43(g)(2)). That cap covers consumer loans. A DSCR loan on a non-owner-occupied rental is business-purpose credit, so it sits outside the cap, and state law and the note itself become the only limits.

Lenders use that room. In a loan tape filed with the SEC on August 13, 2026 (EFMT Depositor LLC, Form ABS-15G, Exhibit 99.11), all 44 DSCR loans carried a prepayment penalty, 36 of them the five-year 5%, 4%, 3%, 2%, 1% step-down, and all were marked exempt from the ability-to-repay rule. The one bank-statement loan in the same tape had none. If your rental loan came from a DSCR lender, assume the penalty exists until you have read the note.

The step-down creates a cheap move. The example loan was originated in December 2023, so a November 2026 closing pays it off in month 35, which is year three of the schedule: 3 percent of $311,956.69, or $9,358.70. Close in January instead, month 37, and the rate drops to 2 percent of $311,436.20, or $6,228.72. That saves $3,129.98 in penalty. The two extra months at 8.25 percent cost $779.62 more interest than the new loan would have charged, and the point is a few dollars smaller on the lower balance. Net, waiting adds $2,346 to option A on a two-year hold and $2,311 on a five-year hold. Across every option and hold period in the decision table below, the January closing comes out between $2,032 and $2,546 ahead.

Read the note for two details before trusting that arithmetic: whether the penalty year runs from the note date or the first payment date, and whether the percentage applies to the full balance or only to prepayment above an annual free amount. The model below assumes the full balance, which is the expensive case.

Payment savings are the wrong denominator

The quote divides by the payment drop, and a payment drop mixes two things: interest you stop paying and principal you stop repaying. When the new loan restarts a 30-year term, part of the lower payment is simply slower paydown, which you hand back at sale as a larger payoff.

On the duplex the two are close, because the old loan is only three years old: the payment falls by $380.71 and the first month's interest falls by $389.95. On a seasoned loan the gap is large. Take a hypothetical $200,000 portfolio loan at 7.0 percent that is 15 years in, with $148,037.73 left, refinanced into a new 30-year loan at 6.5 percent with $3,000 in costs.

The payment shortcut says the $3,000 comes back in 7.6 months. Counted on interest, the cumulative saving peaks at $931.49 in month 29 and then shrinks, so the costs never come back at all. The lower payment is real cash flow, and for an investor short on monthly cash it may still be worth buying, but it is borrowed from your own equity and the spreadsheet should say so.

The fix is to compare total interest over the holding period, plus costs and penalties. For a rate and term refinance where the new loan equals the old payoff, that is exactly equal to comparing payments made plus the balance owed at sale, so nothing is lost by switching the denominator. The rental property mortgage payoff calculator covers the balance side of the same arithmetic.

Build the recoup calculator in Excel

One sheet, inputs in column B, the old loan's penalty schedule in D2:D6 and the new loan's in E2:E6 (leave E blank for a loan without one). The figures shown are option A closed in November with a sale in 24 months.

B16 is the line most calculators miss. If you sell inside the old loan's penalty window, keeping the loan does not avoid the penalty, it only shrinks it. Selling 24 months from a November closing lands in month 59 of the old loan, where the keep path pays 1 percent, $3,052.14. Selling 12 months out lands in month 47 and the keep path pays 2 percent, $6,174.48. The refinance's penalty is only the difference.

B17 is the line that protects you from repeating the mistake. A new DSCR loan restarts its own step-down, and a sale in year two of a 5% to 1% schedule on the new loan costs 4 percent of its balance.

For the recoup month, put months 1 to 120 in F2:F121 and copy the B14 to B18 logic into G2:G121 with F2 in place of B10. Then =MATCH(TRUE,INDEX(G2:G121>=0,0),0) returns the first month at which selling would leave you ahead of keeping the old loan. For option A closed in November, that is month 37, the same month the corrected division gave. Format G with a red fill below zero so a hold period that loses money is visible without reading a number.

Pick the loan by the year you will sell

The quote compares one loan to doing nothing. In practice you choose among several offers, and the right one changes with your exit. Three example offers on the same $311,956.69 payoff, all 30-year fixed:

• Option A: conventional at 6.75 percent with 0.5 point and $3,255 of other loan costs, no prepayment penalty. Payment $2,023.35.
• Option B: no-cost at 7.125 percent, the lender credit covers every loan cost, no penalty. Payment $2,101.71.
• Option C: DSCR at 6.5 percent with 1 point, the same $3,255 of other costs and a new 5%, 4%, 3%, 2%, 1% penalty. Payment $1,971.78.

Net advantage over keeping the 8.25 percent loan, with each refinance closed in January (month 37), by months from today to the sale. Undiscounted, pre-tax.

Three readings follow. The lowest rate on the sheet is the worst choice for anyone selling within four years: at a one-year sale it loses $17,323 against keeping the old loan, because its penalty and point land on exactly the sale you are planning. The no-cost loan wins every sale date through month 51, and it also wins the case the table cannot show: if rates fall another point, you can refinance B again without a sunk point or a penalty. A takes over from month 52, and C from month 63, the first sale date after its own five-year penalty has expired.

Before you put a number in the sale column, check the other forces on it: a loan maturity or balloon elsewhere in the portfolio (the rental portfolio interest rate stress test lists them by year), a planned 1031 exchange, a partner's exit date. If you cannot name the year, run the table and pick the option that loses least in the worst row you consider plausible.

What taxes change, and what they do not

Build the comparison pre-tax, then adjust. Mortgage interest on a rental is a Schedule E expense, so interest you stop paying also stops reducing your taxable rental income: at a 24 percent marginal rate, the first month's $389.95 of interest saved is $296.36 after tax.

Points follow their own schedule. IRS Publication 527 (2025) treats points on a rental mortgage as prepaid interest deducted over the loan term under the original issue discount rules. When the points are under the de minimis threshold, 0.25 percent of the loan times its full years, you may deduct them straight line: here $1,559.78 of points against a threshold of $23,396.75, so $51.99 a year. When the loan ends early through a refinancing or a prepayment, such as a sale, the remaining points may be deductible that year, except that a refinance with the same lender carries them over the new loan instead. Publication 527 also lists mortgage commissions, abstract fees and recording fees as capital expenses that become part of the property's basis. Put those rules in one row per option if two options sit within a few hundred dollars of each other, as A and B do at 48 months ($310 apart), and have your preparer confirm the treatment of the prepayment penalty on your return.

The decision, in order

• Get the payoff letter from your current servicer and the note. Write down the penalty schedule, how the year is counted, and the exact month the next step-down begins.
• Strip prepaids and escrow from the lender's cash to close and add the penalty. Put only real costs in B8 and B13.
• Run B18 for every offer at every sale year you consider possible, including the new loan's own penalty in B17.
• If a step-down falls within the next three months, price the wait: on this loan, two months is worth over $2,000 on every option.
• If the new conventional loan exceeds 80 percent of value, mortgage insurance becomes a monthly cost on the new side; the PMI removal breakeven calculator covers when it ends on a rental. On a DSCR refinance, recheck coverage at the new payment with the DSCR calculator.

For this duplex, close in January. If the plan is to sell or recycle the equity within four years, take the no-cost loan at 7.125 percent: it is $3,165 ahead of keeping the old loan at a two-year sale. If the duplex is a long hold, the DSCR loan at 6.5 percent wins from month 63, and only because you have accepted that a sale before year five pays its new penalty.

The SheetCraft Rental Property Analyzer holds the rest of this decision. Its mortgage analysis tab shows the amortization schedule, total interest paid and your equity position at any point, and compares up to three loan scenarios side by side, so you can enter the old loan, option B and option C on the same property and compare total interest and equity at the year you plan to sell. Its deal summary then shows what the winning payment does to monthly cash flow and cash-on-cash return. It does not price a prepayment penalty or find the recoup month: build the sheet above next to it, and carry the winning loan's rate, payment and costs into the scenario you compare.