calculate-loan

01 Loan payments

Credit Card Amortization Excel: Formula and Payoff Calculator

Calculator No. 01-12751 · Updated · Formula and assumptions shown below

The calculator on this page is switched off while it is checked. The formula is below, and the worked example shows how to do the sum by hand.

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

A credit card amortization calculator estimates how a balance changes each month and how long it takes to repay when you enter the balance, APR, and fixed monthly payment. The calculator below shows the estimated payoff time, total paid, and interest under a simplified fixed-rate model; Excel can reproduce the same month-by-month schedule.

Formula

The calculator estimates the number of whole monthly payments needed to amortize a balance using a constant monthly interest rate and a fixed payment. Its rate is the annual percentage rate divided by 12:

r = APR / 1200

For balance B, monthly payment M, and monthly rate r, the level-payment payoff estimate is:

n = ceil(−ln(1 − rB/M) / ln(1 + r))

  • B is the starting credit card balance in dollars.
  • APR is the annual percentage rate entered as a percentage, such as 20 for 20%.
  • r is the monthly rate as a decimal.
  • M is the fixed monthly payment in dollars.
  • n is the estimated number of monthly payments, rounded up to a whole month.

Total payments are calculated as M × n, and estimated interest is total payments minus the starting balance. This treatment assumes every payment is the same, including the final payment. A real account’s final payment may be smaller, so these totals can slightly overstate actual payments.

Worked Example

Using the calculator’s default inputs: balance $5,000, APR 20%, and monthly payment $250.

  1. Convert APR to a monthly decimal rate: r = 20 / 1200 = 0.0166667.
  2. Substitute in the payoff formula: n = ceil(−ln(1 − 0.0166667 × 5000 / 250) / ln(1.0166667).
  3. The expression inside the first logarithm is approximately 0.6666667. The unrounded result is about 26.56 months, so the estimated payoff time is 27 months.
  4. Total payments: $250 × 27 = $6,750.
  5. Estimated interest: $6,750 − $5,000 = $1,750.

The result uses the same rounding rule as the calculator. It does not model a smaller last payment or daily interest accrual.

Assumptions

  • The APR stays fixed throughout the payoff period.
  • One fixed payment is made at the end of each month; payments are assumed to reduce the balance after monthly interest accrues.
  • Interest compounds monthly at APR divided by 12.
  • Fees, new purchases, cash advances, promotional-rate periods, taxes, and other account-specific adjustments are not included.

How to Build an Amortization Schedule in Excel

A schedule makes the changing balance visible instead of showing only a payoff estimate. Create columns for month, beginning balance, interest, payment, principal paid, and ending balance. Put the opening balance in the first row and use formulas to calculate each period.

  • Beginning balance: the prior month’s ending balance.
  • Interest: beginning balance multiplied by the monthly rate. If APR is in cell B2 as a percentage, use =B5*$B$2/12 when B5 contains that month’s beginning balance.
  • Payment: enter the planned payment, but cap it at the amount owed plus interest so the schedule does not show an overpayment.
  • Principal paid: payment minus interest.
  • Ending balance: beginning balance plus interest minus payment.

For a simple spreadsheet, if row 5 is the first month and columns B through F hold beginning balance, interest, payment, principal, and ending balance, formulas might be C5=B5*$B$2/12, E5=D5-C5, and F5=B5+C5-D5. In the next row, set the beginning balance to the previous ending balance, for example B6=F5, then fill formulas downward. Use cell references suited to your own layout and verify that the ending balance approaches zero rather than becoming negative.

Excel’s built-in NPER function can estimate the number of periods for a fixed payment and fixed rate. For example, =NPER(B2/12,-B3,B1) uses APR in B2, payment in B3, and balance in B1. The payment is negative because it is a cash outflow while the balance is a cash amount received or owed in the function’s sign convention. It returns a fractional number of periods; round up for a whole-payment estimate. The calculator above applies that same level-payment relationship and reports whole months.

How Credit Card Amortization Differs from a Loan

Amortization means applying payments over time to interest and principal. A fixed-rate installment loan commonly has a scheduled term and a payment calculated to repay its balance on schedule. A credit card is revolving credit: the balance can change when purchases, fees, payments, or rate changes occur. Consequently, a fixed-payment spreadsheet is a planning model, not a statement of what an issuer will charge.

Credit card interest may be determined using daily balances and the account’s billing-cycle terms rather than a simple monthly multiplication. An issuer’s agreement and statement explain the applicable calculation. APR alone may not fully specify the result if the account has multiple balance categories, such as purchases and cash advances, each with a different rate or payment allocation.

Common Spreadsheet Problems and Limits

  • Payment too low: if the payment does not exceed the month’s interest, the balance will not decline. In the formula this occurs when M is less than or equal to rB, so a finite payoff estimate cannot be calculated.
  • APR entered as the wrong value: enter 20 for 20%, not 0.20, when the formula divides the APR input by 1200. If the cell is formatted as a percentage, check whether the stored value is 0.20 and adjust the formula accordingly.
  • Rounding each month: rounding interest to cents in every schedule row may differ from a model that carries additional decimal places. A card issuer can also use daily calculations, producing another difference.
  • Changing balance or rate: new charges, fees, variable APR changes, and promotional periods invalidate a constant-payment, constant-rate projection unless you add rows or rules for them.

Use the result to understand how payment size and APR affect a simplified payoff path, not to predict an exact statement balance. For a more faithful projection, use the account’s actual balance categories, rate terms, payment dates, and interest method where available.

Frequently Asked Questions

Can Excel calculate how long it will take to pay off a credit card?
Yes. Use the level-payment formula or Excel’s NPER function with the monthly rate, payment, and balance. The estimate assumes a fixed rate and payment and excludes new charges and account-specific interest details.

What Excel formula shows credit card interest each month?
For a simplified monthly model, multiply the beginning balance by APR divided by 12. For example, if APR is entered as 20, use beginning balance × 20 / 1200. An issuer may calculate interest using daily balances instead.

Why does my Excel payoff formula return an error?
A common cause is a payment that does not exceed the month’s interest, making repayment impossible under the fixed-payment model. Also check that APR is entered in the format the formula expects and that the payment and balance use consistent units.

Does this calculator include new purchases or minimum payments?
No. It assumes one fixed payment and no new charges. It does not determine a card’s required minimum payment; that amount depends on account terms and should be checked on the statement or with the issuer.

Why does the spreadsheet differ from my credit card statement?
The model uses monthly compounding and a fixed rate, while an account may use daily balances, changing rates, multiple APR categories, fees, or transactions during the billing cycle. Those differences can change the interest and ending balance.

This result is an estimate for educational use; the lender’s disclosure gives the exact figures.

Related

Formula used
in bal | Credit card balance | $ | 5000 | 0.01 | 100000000
in apr | Interest rate (APR) | % | 20 | 0.01 | 100
in pmt | Monthly payment | $ | 250 | 0.01 | 100000000
out mos | Months to repay | months | ceil(round(-ln(1 - (apr / 1200) * bal / pmt) / ln(1 + apr / 1200), 6)) | 0
out paid | Total payments | $ | pmt * mos | 2
out int | Total interest | $ | pmt * mos - bal | 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

The whole Loan payments section →