Originally published on EarlyPayoff in February 2016. Fully rewritten and updated in October 2026.
To calculate an early mortgage payoff, you need three numbers: your current principal balance, your interest rate and your regular principal-and-interest payment. Plug them into the NPER formula with your payment plus the extra amount, and you get the number of months left; reverse the question with the PMT formula and you get the extra payment needed to finish by a target date. Any spreadsheet can do both in a few cells.
This guide shows the three formulas, walks through two real examples, and explains why the balance on your statement is not the same as the amount it takes to close the loan.
Key takeaways
- Months left = NPER(monthly rate, payment + extra, current balance). The spreadsheet functions PMT, FV, NPER and CUMIPMT cover every step.
- A $300,000 loan at 3.09% taken out five years ago, with an extra $300 a month from now on, ends in May 2045 instead of October 2051 and saves $32,582.25 in interest.
- To finish a new $350,000, 7.40% loan in 15 years instead of 30, you need an extra $801.36 a month; that saves $291,955.10 in interest.
- Use the principal balance and rate printed on your monthly statement. To close the loan, request a payoff statement; it adds daily interest and any fees.
Step 1: Gather the right numbers
Your calculation is only as good as its inputs. Pull these from documents, not memory:
- Outstanding principal balance and current interest rate. Federal rules (Regulation Z, section 1026.41) require most servicers to print both on your monthly periodic statement, along with the date the rate can next change and whether a prepayment penalty may apply. Small servicers and some fixed-rate loans paid with coupon books are exempt, so you may need to call or log in instead.
- Principal-and-interest payment. The statement breaks your payment into principal, interest and escrow. Use only principal plus interest; taxes and insurance don’t reduce the loan.
- Original terms (loan amount, term, first payment date) are on your promissory note and Closing Disclosure. You need them only if you want to rebuild the full schedule.
Your annual Form 1098 also shows a balance, but the IRS instructions for that form say Box 2 reports outstanding principal as of January 1. By October it is ten months stale, so prefer the latest statement.
Step 2: Learn the three formulas
All three use the monthly rate, r = annual rate ÷ 12. At 7.40%, r is 0.074 ÷ 12, or about 0.006167.
Monthly payment
M = P × r ÷ (1 − (1 + r)^−n), where P is the amount borrowed and n is the number of monthly payments. For a $350,000, 30-year loan at 7.40%, the Freddie Mac weekly average for 30-year fixed loans as of October 15, 2026, M works out to $2,423.33.
Remaining balance after k payments
B = P × (1 + r)^k − M × ((1 + r)^k − 1) ÷ r. The first term is what the loan would grow to with no payments; the second is what your payments, plus the interest they avoided, have knocked off. This is how you find today’s balance on a loan you’ve had for years if you don’t have a statement handy.
Months to payoff with an extra payment (NPER)
N = −ln(1 − r × B ÷ (M + X)) ÷ ln(1 + r), where B is today’s balance and X is your extra monthly amount. The result is usually a fraction. Round up: the whole part is the number of full payments, and the last payment is a smaller one that clears what’s left.
Note the condition hidden in the formula. If M + X is not larger than one month of interest (r × B), the logarithm fails, because the payment never touches principal. That can’t happen on a normal fixed-rate loan, but it’s a useful check on typos.
Example 1: A loan you’ve had for five years
Inputs: $300,000 borrowed on a 30-year fixed loan at 3.09%, the Freddie Mac average for the week of October 21, 2021. The first payment was November 1, 2021, so 60 payments have been made through October 1, 2026. The principal-and-interest payment is $1,279.42.
- Balance today: $267,155.04 on a cents-rounded schedule (the closed-form formula gives $267,155.02; the 2-cent gap is rounding). Five years of payments have repaid $32,844.96 of principal and $43,920.24 of interest.
- No extra: 300 payments remain. The final payment is due October 1, 2051, and the loan will charge another $116,671.20 of interest.
- Extra $300 a month, starting November 2026: NPER returns 222.39, so 223 payments remain, the last one only $612.75. The final payment falls on May 1, 2045. Remaining interest drops to $84,088.95, a saving of $32,582.25, and you finish 6 years and 5 months early.
One honest caveat about this example: at 3.09%, prepaying earns less than safe alternatives. The Federal Reserve’s H.15 release showed the 3-month Treasury bill at 4.26% on October 16, 2026. The math here tells you what prepaying does; whether you should is a separate question, covered in should you pay off your mortgage early.
Example 2: The target-date method
Often the better question is “How much extra do I need to be done by a certain year?” Run PMT on your current balance with the number of months you want left, then subtract your regular payment. Round the result up to the next cent so you don’t end with a stray extra month.
For a new $350,000 loan at 7.40%, finishing in 15 years needs a payment of $3,224.69, so the extra is $801.36 a month. Total interest falls from $522,397.97 to $230,442.87. The same method on the five-year-old 3.09% loan above says you’d need an extra $577.09 a month ($1,856.51 in total) to finish 15 years from now, saving $49,654.62.
| Target payoff | Required P&I payment | Extra per month | Total interest | Interest saved |
|---|---|---|---|---|
| 10 years | $4,136.32 | $1,712.99 | $146,357.93 | $376,040.04 |
| 15 years | $3,224.69 | $801.36 | $230,442.87 | $291,955.10 |
| 20 years | $2,798.22 | $374.89 | $321,569.32 | $200,828.65 |
| 25 years | $2,563.75 | $140.42 | $419,121.73 | $103,276.24 |
| 30 years (no extra) | $2,423.33 | $0.00 | $522,397.97 | — |
The curve is steep at the short end. Moving your target from 20 years to 15 more than doubles the extra payment, while moving from 30 to 25 costs only $140.42 a month. If a goal feels out of reach, pick a target date you can sustain and tighten it later as your income grows. For ways to fund the extra amount, see our early mortgage payoff strategies.
Step 3: Build it in a spreadsheet
Every mainstream spreadsheet program has the same financial functions. They follow a cash-flow sign convention: money you receive (the loan) is positive and money you pay is negative, or the reverse. If a result comes back negative or as an error, flip the sign on the payment or the balance.
| What you want | Formula (Example 1 inputs) | Result |
|---|---|---|
| Regular P&I payment | =PMT(0.0309/12, 360, -300000) | 1,279.42 |
| Balance after 60 payments | =FV(0.0309/12, 60, 1279.42, -300000) | 267,155.02 |
| Payments left, no extra | =NPER(0.0309/12, -1279.42, 267155.04) | 300.00 |
| Payments left with $300 extra | =NPER(0.0309/12, -(1279.42+300), 267155.04) | 222.39 |
| Interest over the next 12 payments | =CUMIPMT(0.0309/12, 300, 267155.04, 1, 12, 0) | -8,153.70 |
| Payment to finish in 15 more years | =PMT(0.0309/12, 180, -267155.04) | 1,856.51 |
To turn months into a date, add the result to your next due date with your program’s month-offset function (often EDATE). In Example 1, 223 payments starting November 1, 2026, end on May 1, 2045.
Build a full schedule to check the shortcut
The functions above assume perfect math; your servicer rounds interest to the cent every month. For an exact answer, set up one row per payment with these columns: payment number, starting balance, interest (starting balance × r, rounded to cents), scheduled payment, extra, principal (payment + extra − interest) and ending balance. Copy the row down until the balance hits zero, and cap the last payment at whatever is left. Our guide to reading an amortization schedule explains each column.
Your hand-built schedule should match your statement to within a few cents. If it drifts by more, check whether your servicer applied an extra payment to principal or held it, and confirm the rate on an adjustable loan hasn’t changed.
Step 4: Get a payoff quote when you’re ready to finish
The balance on your statement is not the amount it takes to close the loan. The CFPB explains that a payoff amount includes interest through the day you plan to pay, plus any unpaid fees, and possibly a prepayment penalty if your loan has one. Mortgage interest is paid in arrears, so the payment you made on the 1st covered last month’s interest; the days since then are still owed.
In Example 1, daily interest on $267,155.04 at 3.09% is $22.62 using a 365-day year ($22.93 if the loan counts a 360-day year). Paying off on October 21, 2026, would add 20 days of interest, $452.40, for a payoff of about $267,607.44 before any fees. Your servicer’s statement will state the exact daily amount and the date it is good through.
Under Regulation Z, section 1026.36(c)(3), the servicer must send an accurate payoff statement within a reasonable time, and no later than seven business days after it receives your written request. Some exceptions apply, such as loans in bankruptcy or foreclosure. Ask for the statement in writing, and request one dated a few days past your planned transfer so a delay doesn’t leave a small balance open.
Common calculation mistakes
- Including escrow in the payment. Taxes and insurance pass through the servicer; only principal and interest reduce the loan. Using the full payment overstates how fast you’ll finish.
- Using the original loan amount. Always start from today’s balance and the number of payments left, not the numbers from closing.
- Using the APR. The APR folds in fees for comparison shopping. Interest accrues at the note rate shown on your statement.
- Assuming an adjustable rate stays put. Your statement shows when the rate can next change. Run the numbers again after each reset.
- Forgetting the extra must reach principal. If your servicer holds an unmarked overpayment or applies it to next month’s bill, the formulas no longer describe what is happening.
When not to act on the numbers
A payoff calculator answers “how fast,” not “whether.” It can make a low-rate loan look like a great target when your money would earn more elsewhere, as in Example 1. It also ignores liquidity: extra principal can’t be withdrawn without selling, refinancing or borrowing against the house.
Skip or scale back the plan if you lack an emergency fund, carry higher-rate debt, are missing an employer retirement match, or expect to move within a few years. If your loan carries a prepayment penalty, the CFPB’s rules limit it to the first three years on loans where it’s allowed at all; check page 1 of your Closing Disclosure. And if your real goal is a lower rate, the right calculation may be a refinance break-even instead; see when to refinance your mortgage.
Frequently asked questions
Why doesn’t my calculation match my servicer’s exactly?
Servicers round interest to the cent every month and may post payments on different days, so formulas and real schedules drift by a few cents to a few dollars. Larger gaps usually mean an extra payment wasn’t applied to principal, a rate change on an adjustable loan, or a fee you weren’t expecting. Ask the servicer for a transaction history.
Can I use these formulas for a biweekly or one-time lump sum?
For a one-time lump sum, subtract it from the balance and run NPER again with your regular payment. For biweekly-style plans, the simplest equivalent is adding 1/12 of your payment each month. True biweekly schedules depend on when your servicer credits each half-payment, so a full schedule is more reliable.
Is the payoff amount the same as my principal balance?
No. The payoff amount adds interest from your last payment through the payoff date, plus any unpaid fees. It’s only valid through the date on the statement, so request a fresh one if your closing or transfer slips.
Do I need to tell my servicer I’m paying extra?
Yes, in effect. Use the “additional principal” field online or mark a check “apply to principal.” Then confirm on your next statement that the principal balance fell by your regular principal portion plus the extra amount.
Does paying extra lower my monthly payment?
Not on a standard fixed-rate loan. The required payment stays the same and the loan simply ends sooner. Lowering the payment takes a recast or a refinance, which your servicer or a lender would have to approve.
Sources
- Freddie Mac, Primary Mortgage Market Survey (30-year fixed averages, weeks of Oct 21, 2021, and Oct 15, 2026), accessed Oct 2026
- Federal Reserve, H.15 Selected Interest Rates (3-month Treasury bill, Oct 16, 2026), accessed Oct 2026
- CFPB, What is a payoff amount? Is my payoff amount the same as my current balance?, accessed Oct 2026
- CFPB, Regulation Z § 1026.36, Prohibited acts or practices and certain requirements for credit secured by a dwelling (payoff statements, (c)(3)), accessed Oct 2026
- CFPB, Regulation Z § 1026.41, Periodic statements for residential mortgage loans, accessed Oct 2026
- CFPB, Regulation Z § 1026.43, Minimum standards for transactions secured by a dwelling (prepayment penalty limits), accessed Oct 2026
- IRS, Instructions for Form 1098 (Box 2, outstanding mortgage principal), accessed Oct 2026
Figures are as of October 2026. This article is general education, not individualized financial, tax or legal advice.
