Track Debt Payoff in Excel With a Simple Spreadsheet

A debt payoff tracker in Excel records each balance, interest charge, payment, and remaining amount so you can follow progress over time. Enter each debt’s balance, APR, minimum payment, and any extra payment to estimate payoff timing and total interest; actual results can differ because lenders may calculate interest daily or apply payments differently.

Formula

The calculator estimates the number of monthly payments needed to repay one balance with a fixed APR and level monthly payment. It assumes interest compounds monthly and uses the payment as entered, without rounding it to cents inside the calculation.

Months = ceiling of [−ln(1 − r × B / M) ÷ ln(1 + r)], rounded to six decimal places before taking the ceiling. r = APR ÷ 1,200, the monthly rate as a decimal. B = starting balance in dollars. M = monthly payment in dollars. Total paid = M × months, in dollars. Estimated interest = total paid − B, in dollars.

The formula only works when the payment exceeds the interest charged in the first month. If it does not, the calculator reports that it cannot compute a payoff time.

Worked Example

Using the defaults: balance B = $5,000, APR = 18%, and monthly payment M = $200.

1. Convert the APR to a monthly decimal rate: r = 18 ÷ 1,200 = 0.015.
2. Substitute into the payoff formula: −ln(1 − 0.015 × 5,000 ÷ 200) ÷ ln(1.015).
3. Calculate the terms: 1 − 0.375 = 0.625; −ln(0.625) ÷ ln(1.015) ≈ 31.56 months.
4. Round up to a whole monthly payment: 32 months.
5. Estimated total paid = $200 × 32 = $6,400. Estimated interest = $6,400 − $5,000 = $1,400.

Assumptions

• APR stays fixed throughout repayment.
• Payments are made monthly at the end of each period.
• Interest compounds monthly at APR ÷ 12.
• Fees, taxes, insurance, penalties, and payment-date effects are not included.

This calculator models one debt, not a portfolio payoff plan. Build the spreadsheet below to track several accounts and replace its estimates with statement figures as they become available.

What You’ll Need

• Excel and the latest statement for each debt.
• For each account: creditor or account label, current balance, APR, minimum payment, and due date.
• Your planned extra monthly amount, if any, and a date to update the tracker regularly.

Step-by-Step

1. Create a debt summary. Open a blank workbook. In row 1, add columns: Debt, Starting Balance, APR, Minimum Payment, Extra Payment, Planned Payment, Due Date, and Current Balance. Enter one debt per row. Use consistent units: APR as a percentage (for example, type 18% rather than 18 if the cell is formatted as a percentage), and money as dollar amounts.
2. Enter and check the starting figures. Copy balances and minimums from recent statements, and enter each account’s stated APR. If an account has different APRs for purchases, transfers, or cash advances, track those portions separately when possible; a single blended rate will only be an approximation. Make clear labels so you can match entries to statements.
3. Calculate the planned payment. In the Planned Payment column, enter a formula such as =D2+E2, assuming Minimum Payment is in D and Extra Payment is in E. Fill it down for each debt. Do not set a payment below the required minimum. A minimum can change as the balance changes, so update it from each new statement.
4. Make a transaction log. Add a second worksheet named “Payment Log.” Use columns for Date, Debt, Starting Balance, Payment, Interest, Fees or Adjustments, and Ending Balance. Record each payment and statement-period interest or fees. In the ending-balance cell, use =C2+E2+F2-D2 when those columns match the order shown. This treats payments as reducing the balance and interest and fees as increasing it.
5. Track the balance over time. For a simple monthly estimate, calculate interest as starting balance × APR ÷ 12, then subtract payment and add any fees. For example, if the starting balance is in C2, APR is in the summary sheet’s C2, and payment is in D2, an illustrative formula is =C2*'Debt Summary'!C2/12. Link the ending balance into the next month’s starting balance. Actual statement interest may differ, so use the lender’s posted figures when available.
6. Keep payments within the balance. A final payment should not exceed the amount owed. The estimated payment for a period is the lesser of the planned payment and balance plus that period’s interest and fees. You can use =MIN(planned_payment, starting_balance+interest+fees), replacing the names with cell references. Check that the result cannot become negative.
7. Add payoff and progress indicators. Create columns or cells for amount paid to date, current balance, and percentage repaid. Percentage repaid can be calculated as =(Starting Balance-Current Balance)/Starting Balance, then formatted as a percentage. Avoid dividing by zero if you reuse the sheet after a debt is paid off; mark the account “Paid” or leave the percentage blank.
8. Review and update monthly. After statements arrive, enter the actual payment, interest, fees, and new balance. Compare your calculated ending balance with the statement. Update minimum payments and APRs when they change, and save a dated copy or use a separate monthly snapshot tab to preserve history.

Common Mistakes

• Mixing APR formats: Typing 18 into a cell intended for 18% can make a formula calculate at 1,800%. Check the stored value and cell format.
• Confusing payment with principal: Interest and fees are added before the payment reduces the balance. Subtracting the full payment without recording interest overstates progress.
• Assuming monthly interest is exact: Many statements use daily balances or other timing conventions. Treat APR ÷ 12 as an estimate and reconcile to the posted statement.
• Forgetting new charges or fees: Add them to the log; otherwise, your tracker will not match the account.
• Using a fixed minimum forever: Minimum payments may change. Refresh them rather than relying on the original figure.

Tips

• Format balances as currency and APR as a percentage with a consistent number of decimal places.
• Use Excel’s data validation to prevent negative balances, payments, or rates outside a reasonable range.
• Freeze the header row, turn the summary into a table, and use conditional formatting to highlight zero balances or missed updates.
• Keep an “as of” date beside every balance. A tracker is only useful when you know how current its figures are.
• Separate estimated values from statement-confirmed values with labels or different formatting.

Troubleshooting

The estimated balance rises despite payments: Check whether the payment covers the interest and fees added for the period. Verify APR formatting and confirm the payment is entered as a positive amount that the formula subtracts.

Excel shows an error or an impossible payoff time: The payment may be no greater than the monthly interest, or a cell reference may point to text or a blank. Check the inputs and ensure the payment is sufficient to reduce the balance. The calculator’s formula cannot return a finite payoff when payment does not cover interest.

Your spreadsheet differs from the statement: Compare dates, posted interest, fees, new purchases, and payment allocation. Use the statement’s ending balance as the starting figure for the next tracking period, and adjust the model rather than forcing it to match an incomplete estimate.

A paid account shows a negative balance: Cap the last payment at the amount owed, including interest and fees, and set the next balance to zero once the account is settled.

Frequently Asked Questions

Can Excel calculate when I will pay off several debts?
Yes. Track a separate balance schedule for each debt and mark the month each reaches zero. The result is an estimate if rates, minimums, or payment timing change.

Should I use the APR or the monthly interest rate?
Enter the APR as an annual percentage in the summary, then divide by 12 for a basic monthly estimate. For statement-level accuracy, use the actual interest charged by the account provider.

How often should I update the workbook?
Update it after each statement or payment cycle, using posted balances, interest, fees, and minimums. Record the date so older estimates are not mistaken for current figures.

Why does my actual payoff date differ from the estimate?
Daily interest, payment posting dates, changing APRs or minimums, fees, new charges, and payment allocation can all affect the outcome.

Can I use this tracker for a debt with a promotional rate?
Yes, but record the rate period and its end date, then update the APR when it changes. A single fixed-rate estimate will not model a future rate change.

Is the calculator result a guaranteed payoff amount?
No. It is estimate arithmetic for education; your statement and lender disclosure provide the exact figures.

Related

• Debt Service Ratio Calculator and Formula
• Salesforce Data Recovery Cost: What to Expect
• Debt Service Ratio Calculator and Formula

in bal | Starting balance | $ | 5000 | 0.01 | 100000000
in apr | Interest rate (APR) | % | 18 | 0.01 | 100
in pmt | Monthly payment | $ | 200 | 0.01 | 100000000
out months | Estimated months to repay | months | ceil(round(-ln(1 - (apr / 1200) * bal / pmt) / ln(1 + apr / 1200), 6)) | 0
out total | Estimated total paid | $ | pmt * months | 2
out interest | Estimated interest paid | $ | total - bal | 2