This page provides a credit card payoff formula you can use in Excel to calculate how long it will take to pay off a balance and how much interest you will pay, based on your balance, interest rate, and monthly payment. You enter your current balance, APR, and monthly payment, and the calculator returns the number of months to payoff and the total interest cost. The formula is built into the calculator below, and the explanation shows you exactly how to replicate it in your own spreadsheet.
Formula
The credit card payoff formula calculates the number of monthly payments needed to reduce a balance to zero when you make a fixed payment each month. The formula is:
n = –ln(1 – r × P / M) / ln(1 + r)
Where:
- n = number of months to payoff (rounded up to the next whole month)
- P = current credit card balance (in dollars)
- r = monthly interest rate = APR / 12 (as a decimal, e.g., 18% APR = 0.18 / 12 = 0.015 per month)
- M = fixed monthly payment (in dollars)
- ln = natural logarithm function
In Excel, you can write this as:
=CEILING(-LN(1 - (APR/12) * Balance / Payment) / LN(1 + APR/12), 1)
This gives the number of months. To get total interest, multiply the payment by the number of months and subtract the balance.
Worked Example
Using the calculator defaults: balance = $5,000, APR = 18%, monthly payment = $200.
- Monthly rate r = 18% / 12 = 0.015 (as a decimal).
- Compute r × P / M = 0.015 × 5000 / 200 = 75 / 200 = 0.375.
- Compute 1 – 0.375 = 0.625.
- Take natural log: ln(0.625) = –0.4700036 (approximately).
- Compute denominator: ln(1 + 0.015) = ln(1.015) = 0.0148886.
- Divide: –(–0.4700036) / 0.0148886 = 31.568 months.
- Round up to the next whole month: 32 months.
Total paid = $200 × 32 = $6,400. Total interest = $6,400 – $5,000 = $1,400. The calculator shows 32 months, $6,400 total, and $1,400 interest, matching this example.
Assumptions
- The interest rate is fixed for the entire payoff period. If your APR changes, the estimate will not be exact.
- Interest is compounded monthly at APR / 12. Most credit cards compound daily, but monthly compounding gives a close approximation for planning.
- You make the same payment every month, on time, and you do not make new charges on the card.
- The formula does not include annual fees, late fees, balance transfer fees, or other charges. It only covers interest and principal.
How to Use the Formula in Excel
To set up your own spreadsheet, put your balance in cell A1, APR in B1 (as a percentage, e.g., 18 for 18%), and monthly payment in C1. Then enter:
- In D1 (months):
=CEILING(-LN(1 - (B1/1200)*A1/C1) / LN(1 + B1/1200), 1) - In E1 (total paid):
=C1*D1 - In F1 (total interest):
=E1-A1
Excel’s LN function returns the natural logarithm. If the payment is too low to cover the interest, the formula returns a #NUM! error because the argument to LN becomes negative. This is correct: with a payment below the monthly interest, the balance will never be paid off.
Common Mistakes and Misconceptions
- Using APR as a monthly rate: A common error is to divide APR by 12 but forget to convert to a decimal. For example, 18% APR should be 0.18/12 = 0.015, not 18/12 = 1.5.
- Rounding months down: If you round 31.568 down to 31, you will underpay. Always round up to the next full month.
- Ignoring daily compounding: Most credit cards compound interest daily. The monthly formula slightly underestimates interest because daily compounding adds a little more. The difference is usually small for typical balances and rates.
- Assuming the minimum payment is fixed: Minimum payments usually decrease as the balance drops, so the formula works only for a fixed payment. If you pay the minimum, the payoff time will be longer and the interest higher.
When to Use This Formula
Use this formula when you plan to make a fixed monthly payment above the minimum and want to know how long it will take to become debt-free. It is helpful for comparing different payment amounts: for example, you can see how much faster you pay off the card with $250 per month versus $200. It also shows the total interest you will pay, which can motivate you to increase your payment.
The formula is not appropriate for cards with a variable rate, promotional 0% APR periods, or if you plan to make irregular payments. In those cases, the estimate will be off, and you may need a more detailed amortization schedule that accounts for rate changes.
Alternatives to the Manual Formula
If you prefer not to build the formula yourself, you have a few options:
- Use the calculator on this page: It does the math for you instantly.
- Use Excel’s built-in NPER function: The formula
=NPER(rate, payment, -balance)returns the number of periods. For the example,=NPER(0.015, 200, -5000)gives 31.57, which you then round up. - Use a credit card payoff spreadsheet template: Many spreadsheet programs offer templates that include a payoff schedule with monthly breakdowns of interest and principal.
The NPER function is convenient, but it does not round up automatically, so you still need to wrap it with CEILING. Also, note that the payment argument must be positive and the balance negative in NPER to get a positive result.
Frequently Asked Questions
What if my monthly payment is less than the interest charge?
If your payment does not cover the monthly interest, the balance will grow, not shrink. The formula will give a negative number inside the logarithm, which results in an error. In practice, this means you will never pay off the card with that payment amount. You need to increase your payment to at least exceed the monthly interest.
Does the formula work for a 0% APR promotional period?
No, the formula assumes a constant APR. If you have a 0% period, you can calculate the payoff during that period separately (simply divide the balance by the payment to get months, since no interest accrues). After the promotional period ends, you would need to recalculate with the new APR and remaining balance.
How do I account for daily compounding?
Daily compounding means interest is calculated on the balance each day. The monthly formula underestimates the interest slightly. To get a more precise number, you would need to use a daily periodic rate (APR/365) and compound daily, but the difference is typically small—often a few dollars or a few extra days for a typical balance. For planning purposes, the monthly approximation is usually sufficient.
Should I round the months up or down?
Always round up to the next whole month. If you round down, you will not have made enough payments to cover the final interest, and you will still owe a small amount. Rounding up ensures your final payment clears the balance completely.
Can I use this formula for a credit card with a variable rate?
You can, but the result is only an estimate. If the rate changes, your actual payoff time and interest will differ. To track a variable rate, you would need to update the APR in the formula each time it changes, or use a more complex spreadsheet that recalculates monthly.
What if I make extra payments or pay more than the fixed amount?
The formula assumes a constant payment. If you make extra payments, the payoff time will be shorter and the interest lower. You can recalculate using the higher payment amount, or you can build a schedule that tracks each payment individually. The formula is a planning tool, not a precise tracker for irregular payments.
Limits of the Formula
This formula is a simplified model. It assumes a fixed APR, monthly compounding, and a constant payment. Real credit cards often have daily compounding, variable rates, and minimum payment requirements that change over time. The formula also ignores fees, such as late fees or balance transfer fees, which can increase your balance. For these reasons, the result is an estimate, not a guarantee. Your credit card statement and the lender’s disclosure will show the exact figures, including the actual payoff date and total interest, based on your specific terms.
The result is an estimate, and the lender’s disclosure gives the exact figures.
Related
Formula used
in p | Balance | $ | 5000 | 0 | 1000000 in apr | Interest rate (APR) | % | 18 | 0.01 | 100 in pmt | Monthly payment | $ | 200 | 1 | 1000000 out months | Months to payoff | months | ceil(round(-ln(1 - (apr / 1200) * p / pmt) / ln(1 + apr / 1200), 6)) | 0 out total | Total paid | $ | pmt * ceil(round(-ln(1 - (apr / 1200) * p / pmt) / ln(1 + apr / 1200), 6)) | 2 out interest | Total interest | $ | pmt * ceil(round(-ln(1 - (apr / 1200) * p / pmt) / ln(1 + apr / 1200), 6)) - p | 2
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.
More in this section
- Debt Ratio Benchmarks: What Is a Good Debt Ratio?24.0 %
- Debt Ratio Calculation Formula & How to Use It25.00 %
- Car Loan Calculator: Estimate Your Monthly Payment$405.53
- Calculate My Payoff Date: Loan Payoff Calculator
- Student Loan Interest Deduction Limit: 2025 Rules$405.53
- Car Loan EMI Calculator Month Wise$405.53