To calculate loan payoff in Excel, use the NPER function with your balance, monthly interest rate, and fixed payment to return the number of months required to reach a zero balance. NPER stands for "number of periods" and solves the amortization equation in a single cell, so you do not need to build a month-by-month schedule to find your payoff date. The same calculation can also be done by hand using the inverse-amortization formula n = -ln(1 - B·r/P) / ln(1 + r), which the NPER function evaluates internally. Excel does the heavy lifting for you: enter the balance as the present value, the monthly rate as the rate, and the payment as a negative number, and NPER returns the count of payments. If you would rather skip the spreadsheet entirely, a free browser-based Loan Payoff Calculator applies the identical math and returns the months, the years-and-months breakdown, the total interest, and the total amount paid the moment you change a number. Both approaches answer the same question: given what you actually pay each month, how long until the debt is gone?
What "Loan Payoff Time" Actually Means
Payoff time is the number of months from today until your balance reaches zero, assuming the rate and payment both stay fixed. That is the question a payoff calculator answers, and it is a different question from the one a mortgage or car loan calculator answers. A mortgage or car loan calculator starts with a loan amount and a loan term, then works forward to find the monthly payment. A payoff calculator starts with the payment you already make and works backward to find the term. The distinction matters because most people paying down a credit card balance, a personal loan, or a student loan already know what they send in each month, yet have no clear idea how many of those payments stand between them and a zero balance. That gap is exactly what loan payoff math is designed to fill.
The Math Behind Payoff Calculations
Every payoff calculation, whether you run it in Excel, in a browser tool, or on paper, is built on the same recurrence. At the end of each month, interest accrues on the outstanding balance at a rate equal to the annual percentage rate divided by 12, and the rest of the payment reduces the principal. If B is the current balance, P is the fixed monthly payment, and r is the monthly rate (APR divided by 100, then divided by 12), then a closed-form expression for the number of periods n that takes the balance to zero is:
n = -ln(1 - B·r/P) / ln(1 + r)
If the rate is zero, the formula collapses to n = B divided by P, which is just the balance divided by the payment. The Wikipedia article on amortization calculators walks through the same derivation and explains why a single closed-form expression replaces the loop of subtracting interest and principal one month at a time. Two practical quantities follow directly: total paid equals P multiplied by n, and total interest equals total paid minus B. Those three values, months to payoff, total paid, and total interest, are the standard outputs of any payoff calculator.
How to Calculate Loan Payoff in Excel
Excel gives you a built-in function that evaluates the closed-form expression above in one cell. The function is NPER, which stands for "number of periods." Set up your workbook with three labeled cells, then write one formula.
- In cell A1, enter your current balance as a positive number, for example the full amount still owed on the card or loan.
- In cell A2, format the cell as Percentage, then type 19.99 to represent a 19.99% APR. Excel stores that value as 0.1999, so dividing A2 by 12 inside the formula gives the correct monthly rate.
- In cell A3, enter the fixed monthly payment you actually make, expressed as a positive number.
- In any other cell, type =NPER(A2/12, -A3, A1) and press Enter. The result is the number of months required to bring the balance to zero.
- To see a years-and-months breakdown instead of a single number, wrap the result in INT and MOD. In one cell put =INT(result/12) & " years, " & MOD(result, 12) & " months", or split it across two cells with =INT(result/12) for the years and =MOD(result, 12) for the leftover months.
- For total interest, multiply the months by the payment and subtract the original balance: =result*A3 - A1.
A few details matter. The payment argument is negative because, from Excel's perspective, the payment is money leaving your account. The rate must be the monthly rate, which is why A2 is divided by 12 inside the formula. NPER returns a decimal rather than a rounded whole number of months because the closed-form solution does not always land on an exact whole number of periods; for planning purposes, round up to the next whole month, since the final payment will be smaller than your usual one. If NPER returns a "#NUM!" error, the payment is too small to cover monthly interest and the formula has no solution, which is the same condition the Loan Payoff Calculator surfaces as a clear "payment is too low" message. For a focused walkthrough of the same method with screenshots and additional tips, see this step-by-step guide to calculating your loan payoff date in Excel.
Skip the Spreadsheet With the Loan Payoff Calculator
For readers who want the answer without setting up cells, the Loan Payoff Calculator runs the same NPER-style math inside the browser. There are three steps.
- Enter your current balance, the annual interest rate (APR), and the fixed amount you pay each month.
- Read the payoff time in months, with a years-and-months breakdown, along with total interest and total paid.
- Try raising the monthly payment or lowering the rate to see how much faster you become debt-free and how much interest you save.
The calculator solves the recurrence directly for the number of periods and updates the moment you change an input, so you can compare scenarios side by side without copying formulas across rows. Because everything runs locally in your browser, your numbers never leave your device, and there is no sign-up, upload, or wait.
Why Your Monthly Payment Must Beat the Interest
The one rule that determines whether a payoff date even exists is this: the monthly payment must be larger than the first month's interest, which is the current balance multiplied by the monthly rate. If the payment only covers the interest, or falls short of it, the principal never decreases, the balance never reaches zero, and no number of months will get you there. That is the trap that keeps people stuck on minimum-payment cycles for years, paying the lender steadily while the underlying balance barely moves. The Loan Payoff Calculator reports the condition plainly, with a "payment is too low" notice, instead of returning an infinite or misleading value, which makes the constraint visible rather than hidden inside a spreadsheet error. The fix is mechanical: raise the payment above the monthly interest amount, or refinance to a lower APR so the same payment clears a larger share of principal each month.
How This Tool Differs From a Mortgage or Car Loan Calculator
The loan payoff calculator answers a different question than a mortgage or car loan calculator. The differences are useful to know before you choose which tool to open.
| Tool | You provide | It returns | Best for |
|---|---|---|---|
| Loan Payoff Calculator | Balance, APR, fixed monthly payment | Months to payoff, total interest, total paid | Credit cards, personal loans, student loans, medical debt, any balance you are paying down with a fixed amount |
| Mortgage Calculator | Loan amount, term, rate, down payment | Monthly payment, total interest, full amortization schedule | Buying a home and choosing a loan term |
| Car Loan Calculator | Loan amount, term, rate | Monthly payment, total interest, total cost | Financing a vehicle and comparing lender offers |
If you already send a fixed amount every month and want to know when the debt ends, the payoff calculator is the right tool. If you are shopping for a new loan and need to know what the monthly payment would be, a mortgage or car loan calculator is the right starting point.
Treat the result as a clean planning baseline. The model assumes a single fixed rate, the same payment every month, monthly compounding, and no new charges added to the balance. Real accounts can differ: credit cards accrue interest daily, promotional rates expire, and lenders may apply fees or specific payment-timing rules. Confirm your exact payoff terms with your lender before making any financial decisions. Figures here are estimates for general information only and are not financial advice.