Excel's PMT function returns a fixed monthly loan payment when you know the loan amount, the annual rate, and the number of months, using the formula P = (B·r) / (1 - (1 + r)^(-n)), where B is the balance, r is the monthly rate (APR divided by 12, as a decimal), and n is the term in months. When you already know the payment you can afford and want to know how long until the balance hits zero, the same relationship can be rearranged through inverse amortization into n = -ln(1 - B·r/P) / ln(1 + r), which is what Excel's NPER function does inside a spreadsheet. Both are perfectly valid tools, but they answer two different questions, and the Loan Payoff Calculator handles the inverse case directly: you provide your current balance, the APR, and the fixed amount you pay each month, and the tool returns the number of months to payoff, the total interest, and the total amount paid on one screen. Building the same answer in Excel means writing a formula, choosing cells for input, formatting the output as currency, and double-checking that the rate is entered as a decimal rather than a percentage. The web tool does that work for you and updates the moment you change any input.
What "calculate loan payment in Excel" usually means
For most borrowers, the question is forward-looking: I have a loan amount, an interest rate, and a term, and I want to know the monthly payment. That is the world of the PMT function, which has been in Excel for decades and is the default answer in nearly every "loan payment in Excel" tutorial. The shape of the function is PMT(rate, nper, pv), where rate is the monthly interest rate, nper is the number of monthly payments, and pv is the present value of the loan (the amount you borrow today, written as a negative number if you want a positive result). For a $20,000 car loan at 6% APR over 60 months, PMT(0.06/12, 60, -20000) returns a fixed monthly payment, and you can extend that with IPMT and PPMT to see how much of each payment is interest and how much is principal.
That approach assumes you are choosing a payment. It works well for mortgages, auto loans, and student loans where the lender sets the term and you decide whether to accept the deal. It does not work well when you already have a fixed monthly amount and want to know how long the debt will last, which is the situation most people are actually in with credit cards, personal loans, medical bills, and any balance they are chipping away at with a fixed transfer amount each month. The PMT function would still return a number, but it would be the answer to a different question.
The inverse problem: starting from the payment you already make
If you pay $300 a month toward a credit card with a 21% APR and a $5,000 balance, the question is not "what should my payment be?" It is "how long until this is paid off, and how much will I have paid in interest by then?" Excel can answer that with NPER(rate, pmt, pv), which inverts the PMT formula. For the credit-card example above, the entry would be NPER(0.21/12, -300, 5000), and the result is the number of months until the balance hits zero. From those months, you can compute total paid (months × payment) and total interest (total paid − original balance) with simple arithmetic in two more cells.
That is the same relationship the Loan Payoff Calculator uses, just packaged for the web. You enter three values, the current balance, the APR, and the fixed monthly payment, and the tool returns the months, the years-and-months breakdown, the total interest, and the total paid. There is no formula bar to maintain, no NPER or LN function to remember, and no risk of mixing up the sign of the payment or the rate. Drop the numbers in, get the answer, and adjust.
PMT vs NPER vs the Loan Payoff Calculator
| Approach | Inputs you provide | Output you get | Best for |
|---|---|---|---|
| Excel PMT | Loan amount, APR, term in months | Fixed monthly payment | Mortgages, auto loans, and student loans where the term is set |
| Excel NPER | Loan amount, APR, monthly payment | Months to payoff | Spreadsheets where you want to compare scenarios in cells |
| Loan Payoff Calculator | Current balance, APR, monthly payment | Months, years-and-months breakdown, total interest, total paid | Quick estimates for credit cards, personal loans, and any balance you chip away at |
Find the payoff time with the Loan Payoff Calculator
- Enter your current balance in the field labeled for the dollar amount you owe today, the same as the statement balance.
- Enter the annual interest rate as a percentage (APR), the same one shown on your most recent statement or loan agreement.
- Enter the fixed amount you pay each month, the same payment you plan to send every month until the balance is gone.
- Read the payoff time in months, then check the years-and-months breakdown for a more intuitive view of your debt-free date.
- Review the total interest and total paid alongside the months, so you can see the cost of the timeline in the same view.
- Try raising the monthly payment by a small amount and compare the new payback time and total interest to set your next realistic target.
How to calculate the same answer in Excel
Open a blank sheet and reserve four cells: B1 for the balance, B2 for the APR, B3 for the monthly payment, and B5 for the result. Enter 5000 in B1, 0.21 in B2 (as a decimal), and 300 in B3. In cell B5, type =NPER(B2/12, -B3, B1) and press Enter; Excel returns the number of months to payoff as a positive number. To get the years-and-months breakdown, you can format the result with INT(B5/12) for the whole years and MOD(B5, 12) for the leftover months. To compute total paid, multiply the months by the payment: =B5*B3. To compute total interest, subtract the starting balance: =B5*B3-B1.
A few Excel-specific traps catch people here. The rate must be entered as a decimal (0.21, not 21), and the payment passed into NPER must be negative if you want a positive months result, otherwise NPER returns a negative value. The monthly payment must also be greater than the first month's interest (balance × monthly rate), or the formula returns an error instead of a usable number. If your payment is set to automatic, you can build a small table with different payment values in one column and watch the months and total interest update, which is a useful way to compare "what if I add $50?" against "what if I refinance at a lower rate?" without rewriting anything.
Reading the months, total interest, and total paid
Months to payoff is the headline number, and the years-and-months breakdown is the one most people actually plan around. Total interest is the dollar amount of interest the loan will generate from today until the balance is zero, given the inputs you entered. Total paid is the sum of every payment you will make, which equals the months times the fixed monthly payment. Total paid minus the starting balance equals total interest, which is why the tool can show both without asking for a separate input. Together those three numbers answer the two questions most people have: how long, and how much over the original balance the debt will cost you.
The math behind the months
Each month, interest accrues on the outstanding balance at a monthly rate equal to APR divided by 12, and whatever is left of your payment after that interest reduces the principal. Inverse amortization solves that recurrence directly for the number of periods rather than looping through every month, producing the closed-form n = -ln(1 - B·r/P) / ln(1 + r), where B is the current balance, P is the fixed monthly payment, and r = APR/100/12 is the monthly interest rate. When the APR is 0%, the formula simplifies to n = B / P, which is the number of interest-free payments it takes to retire the balance. That precision is why two payments only a few dollars apart can still land on different payoff months: the formula is exact, not estimated, and the browser-side calculator uses the same closed-form to give an instant answer. The broader amortization assumptions are documented in the Wikipedia entry on amortization calculators.
When the payment is too low to pay off the balance
One rule matters above all: the monthly payment must be larger than the first month's interest, which is the balance times the monthly rate. If the payment is equal to or less than that interest, the principal never decreases, so the debt cannot be repaid. The Loan Payoff Calculator flags this case directly with a plain-English message rather than returning an infinite or misleading number. This is the same trap that keeps borrowers stuck in minimum-payment cycles for decades, paying far more in interest than the original balance. Increase the payment until it exceeds the first month's interest, and a valid payoff time will appear; if the payment is fixed and the rate is high, the honest answer is that you need to refinance or pay more than the statement suggests before the balance will eventually reach zero.
What the calculator does not account for
The model assumes a single fixed rate, the same payment every month, standard monthly compounding, and no new charges added to the balance. Real accounts can differ. Credit cards typically accrue interest daily rather than monthly, promotional rates expire, lenders may apply fees or specific payment-timing rules, and any new spending on a credit card resets the balance upward. The result is a planning baseline rather than an exact quote, and the same caveat applies to any spreadsheet you build in Excel. Treat the months and total interest as a clean estimate, then confirm your exact payoff terms with your lender before making decisions, and use the calculator to compare scenarios side by side as your payment or rate changes over time.