Debt Payoff Calculator in Excel: Build One Step by Step

How-to basics · Updated November 22, 2026

Yes. You can build a debt payoff calculator in Microsoft Excel by entering a balance, APR, and monthly payment, then using a row-by-row schedule to calculate interest, payments, and the remaining balance. For example, a $5,000 balance at 18% APR with a $250 monthly payment accrues $75 interest in month one and falls to $4,825 after that payment, assuming monthly interest and no fees.

What an Excel debt payoff calculator shows

A useful calculator estimates how payments reduce a balance over time. It separates each payment into interest and principal, shows the closing balance for each period, and indicates roughly when the debt will be paid off. It can also compare the effect of adding an extra amount to the regular payment.

The estimate depends on what you enter. In the simple monthly model below, APR is divided by 12, interest is calculated once per month on the opening balance, and the payment is applied after that interest is added. That is a common planning assumption, not a guarantee of how a lender calculates interest. Some accounts use daily balances, payment dates, fees, promotional rates, or other rules.

A calculator is most useful when its assumptions are visible. Record the current balance, the rate that applies now, the required minimum payment, any planned extra payment, and the date or frequency of payments. If your statement lists a variable rate or a changing minimum-payment rule, the schedule will need updating as those terms change.

Set up the inputs in Excel

In a blank workbook in Excel for Microsoft 365 on Windows, put labels in column A and values in column B. Use this small example as a starting point:

  • A2: Starting balance; B2: 5000
  • A3: APR; B3: 18%
  • A4: Regular monthly payment; B4: 250
  • A5: Extra monthly payment; B5: 0

Enter percentages as percentages, not as whole numbers: type 18% in B3, rather than 18. Format B2, B4, and B5 as currency with Home > Number. Format B3 as Percentage. The extra payment is optional; entering a value there lets the schedule model a larger monthly payment without changing the regular-payment input.

These inputs describe one debt. If you have multiple accounts, keep each balance, APR, and required payment distinct rather than averaging the rates. A combined balance can be useful as a headline, but it cannot accurately show interest or payoff timing when rates and payment rules differ.

Build a month-by-month payment schedule

In row 9, create these headings: A9 Period, B9 Opening balance, C9 Interest, D9 Planned payment, E9 Actual payment, and F9 Closing balance. The planned payment adds the regular amount and any extra amount. The actual payment is capped at what is owed, so the final payment does not exceed the balance plus that month’s interest.

With the inputs in B2:B5 and the schedule headings in A9:F9, enter =1 in A10 for the first period. With B2 holding the opening balance input, enter =$B$2 in B10. With B10 as the opening balance and B3 as the annual APR, enter =ROUND(B10*$B$3/12,2) in C10. This rounds the modeled monthly interest to cents.

With B4 as the regular payment and B5 as the extra payment, enter =$B$4+$B$5 in D10. With B10 as the opening balance, C10 as interest, and D10 as the planned payment, enter =MIN(B10+C10,D10) in E10. With those same balance and interest cells plus E10 as the actual payment, enter =MAX(0,B10+C10-E10) in F10. The MIN and MAX functions prevent an overpayment from creating a negative closing balance.

For period two, with row 10 containing the first period’s calculations, enter =A10+1 in A11 and =F10 in B11. With B11 as the new opening balance and B3 as APR, enter =ROUND(B11*$B$3/12,2) in C11. With the regular payment and extra payment still in B4 and B5, enter =$B$4+$B$5 in D11. With B11, C11, and D11 in place, enter =MIN(B11+C11,D11) in E11 and =MAX(0,B11+C11-E11) in F11. Select A11:F11 and drag the fill handle down to create more months; Excel adjusts the relative row references while keeping the input references fixed.

For the example, the first row calculates $75.00 of interest, a $250.00 actual payment, and a $4,825.00 closing balance. In the second month, interest is $72.38 after rounding, and a $250 payment leaves $4,647.38. These results follow the stated monthly model; lender calculations may differ.

Estimate the number of payments

Excel’s NPER function can estimate the number of equal payment periods needed for a fixed-rate balance. With the annual APR in B3, the payment in B4, and the starting balance in B2, enter =ROUNDUP(NPER(B3/12,-B4,B2),0) in an unused cell. For the example, this returns 24 monthly periods. The negative sign marks the payment as money paid out, while the balance is money owed.

That result is an estimate, not a schedule replacement. NPER assumes a constant rate and equal payments, and it does not reproduce the monthly cent-rounding in the schedule. The final payment will normally be smaller than the regular payment. The calculation also requires a payment large enough to cover interest; if the payment is no greater than the monthly interest, the debt may not decline, and the estimate may not be meaningful. Fees, new charges, late payments, or a changing APR can extend the actual payoff.

Compare extra payments and payoff approaches

To see the effect of paying more, change B5 and review the closing balances or the first period that reaches zero. For instance, setting B5 to $50 makes the planned payment $300. The first month still accrues $75 interest, but the closing balance becomes $4,775 rather than $4,825. The difference grows over time because a lower balance generally produces less interest in later periods under this model.

For several debts, two common payoff orders are the snowball and avalanche approaches. A snowball plan directs extra money toward the smallest balance first, while continuing required payments on the others. An avalanche plan directs extra money toward the debt with the highest interest rate first. The calculator can help compare estimated interest and dates, but the best order can also depend on account terms, fees, and the value a person places on seeing an account cleared.

Do not compare strategies by changing the total amount paid each month without noticing. To make a like-for-like comparison, keep the overall monthly budget for debt payments the same and change only where the extra amount goes. When one account is paid off, a detailed multi-debt model can roll its freed payment into the next account in the chosen order. A single-debt schedule does not automate that allocation, so a multi-account workbook needs separate balances and an explicit payment-allocation method.

Make the workbook easier to check

Formatting and basic checks make errors easier to spot. Format balances and payments as currency, APR as a percentage, and the period column as whole numbers. Use Home > Conditional Formatting > Highlight Cells Rules to highlight closing balances that are greater than zero or values that appear unexpected. You can also add a note beside the schedule stating the assumed payment timing and interest method.

  • Confirm the opening balance matches the lender’s current figure, not an older statement balance.
  • Check that APR is entered as a percentage and that the monthly rate is not entered as the annual rate.
  • Verify that actual payment never exceeds the amount owed and that the closing balance does not go below zero.
  • Extend the schedule far enough to cover the expected payoff, and check for a remaining balance in the last displayed row.
  • Update the inputs if the APR, required minimum, or account balance changes.

If the schedule’s balance rises each month, investigate before relying on its payoff estimate. The payment might be too small to cover interest, the rate may be entered incorrectly, or the lender may add fees or new purchases that the model does not include. A negative or implausibly short payoff estimate can likewise point to a sign or input error.

Common limitations and ways to improve accuracy

The largest limitation is that a monthly APR-divided-by-12 model is not the same as every lender’s billing calculation. Credit cards often calculate interest using daily balances and may have different APRs for purchases, transfers, and cash advances. A loan may use a particular day-count convention or have a prepayment charge. Check the account agreement or ask the lender how interest and payments are applied before treating an estimate as exact.

The example rounds interest to cents each month. That makes the displayed schedule readable and often closer to statement arithmetic than carrying many hidden decimal places, but it still may not match the lender’s method. For a more detailed model, add columns for payment date, days in the period, and the applicable daily rate, if the lender confirms those are the right inputs. Avoid adding complexity based on guesses: an accurate simple assumption is more useful than a detailed but incorrect formula.

It is also important not to confuse a payoff calculator with a complete household budget or a lender payoff quote. The workbook does not automatically include future purchases, annual fees, changing minimums, payment processing dates, or promotional-rate expirations. Use it to explore scenarios, then check real terms and payoff figures with the lender or a qualified professional. This is spreadsheet guidance, not financial, tax, investment, or legal advice.

Frequently Asked Questions

What Excel formula calculates debt payoff time? For a fixed annual APR and equal monthly payment, NPER can estimate the number of months. With APR in B3, payment in B4, and balance in B2, use =ROUNDUP(NPER(B3/12,-B4,B2),0). It assumes a constant rate and does not account for fees or changing payment rules.

Can Excel calculate a credit card payoff date? Yes, as an estimate. A monthly schedule can project how many payments it takes at a chosen payment amount, but the result may differ from the card issuer’s figure if interest accrues daily, rates change, fees apply, or new charges are made. Use the issuer’s current terms for a more reliable estimate.

Why does my debt balance increase in the spreadsheet? The planned payment may be smaller than the interest added for the period, or the APR may have been entered as 18 instead of 18%. Check the inputs and the payment-allocation rules. If the balance still grows under correct assumptions, contact the lender to understand the account’s interest and fee calculations.

Should I pay the smallest debt or the highest-rate debt first? Those are the basic snowball and avalanche orders. The snowball targets the smallest balance; the avalanche targets the highest APR. A spreadsheet can compare estimated outcomes when the total payment budget stays consistent, but account terms and personal circumstances matter too.

Can I use this calculator for more than one loan? Yes, but give each loan its own balance, APR, required payment, and schedule. To model an overall strategy, define how extra money is assigned and what happens to a payment after one debt is cleared. Simply adding all balances together will not accurately represent different interest rates.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.