calculate-loan

01 Loan payments

Credit Card Amortization Spreadsheet and Payoff Calculator

Calculator No. 01-12801 · 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 spreadsheet estimates how a balance declines as you make monthly payments, showing each month’s interest, principal reduction, and remaining balance. Enter your balance, APR, planned monthly payment, and any extra payment to estimate a payoff schedule; the results are a mathematical estimate, not a statement from your card issuer.

Formula

The calculator uses a fixed-rate, equal-payment model. It first estimates the number of monthly payments needed to reduce the balance to zero:

n = ceil(round(-ln(1 - rB / M) / ln(1 + r), 6))

Here, B is the starting balance in dollars, r = APR / 1200 is the monthly rate as a decimal, and M is the monthly payment plus any extra monthly payment, in dollars. n is the estimated number of months, rounded up because a partial final month still requires a payment. The interest estimate is M × n − B; total paid is M × n.

For a balance after k payments, the standard level-payment balance equation is B(1 + r)^k − M((1 + r)^k − 1) / r. The calculator shows this balance after 12 payments, with a floor of zero so it does not display a negative balance after payoff. The model assumes the same payment is made each month, including the final payment; a real final payment may be smaller.

Worked Example

Using the spreadsheet’s default values, the starting balance is $5,000, APR is 18%, the monthly payment is $250, and the extra payment is $0. The monthly rate is 18 / 1200 = 0.015, or 1.5%, and the payment used in the calculation is $250.

  1. Calculate the payoff months: ceil(round(-ln(1 − 0.015 × 5000 / 250) / ln(1.015), 6)).
  2. Inside the logarithm, 1 − 75 / 250 = 0.7. The unrounded quotient is about 24.16, so the estimated payoff time is 25 months.
  3. Total paid under the model is $250 × 25 = $6,250.00.
  4. Estimated interest is $6,250.00 − $5,000.00 = $1,250.00.
  5. After 12 payments, the balance formula gives about $2,693.66.

The 25-month estimate treats every payment, including the last, as $250. In practice, a final payment may be adjusted to the actual remaining balance and interest, so the displayed total is a schedule estimate rather than an exact payoff quote.

Assumptions

  • The APR stays fixed for the calculation.
  • A payment is made once per month, after that month’s interest accrues.
  • Interest compounds monthly at APR divided by 12.
  • The regular payment and extra payment are constant and are applied to this balance.
  • Fees, new purchases, cash advances, promotional rates, taxes, and other account activity are not included.

How to Use a Credit Card Amortization Spreadsheet

Use the inputs to model a single balance, not necessarily every balance on a card account. Enter the current amount you want to pay down, the APR that applies to it, and a monthly payment amount. Enter an extra payment only if you expect to make that amount regularly in addition to the ordinary payment. The calculator combines the two amounts, so do not enter a total payment as the regular amount and then repeat part of it as extra.

A spreadsheet can also show the month-by-month path. A useful table has columns for month, starting balance, interest, payment, principal paid, and ending balance. For each month, calculate interest as starting balance times the monthly rate; subtract that interest from the payment to find principal paid; then subtract principal paid from the starting balance. For example, at a 1.5% monthly rate, a $5,000 opening balance accrues $75 interest in the first modeled month. A $250 payment would reduce principal by $175 and leave $4,825.

Reading the Payoff Estimate

The estimated months indicate how many full modeled payments are needed, not a guaranteed date. Total paid and estimated interest follow from multiplying the full monthly payment by that rounded-up month count. Because this simplifies the final payment, the total may differ from the amount a card issuer would calculate for an actual payoff date.

The 12-payment balance is useful for seeing whether the balance is moving in the intended direction. It is not a separate payoff forecast. The calculator has no input for spending or credits, so adding purchases, making irregular payments, or changing the payment amount will make the real balance diverge from the estimate. Recalculate with updated figures when the balance or payment changes.

Common Spreadsheet Problems and Limits

  • Payment does not cover interest: In the first month, interest is balance times monthly rate. If the payment is no greater than that amount, the balance does not decline under this model and a finite payoff estimate cannot be calculated.
  • APR is not the same as a monthly percentage: Enter annual APR as a percentage, such as 18, not 0.015. The formula divides APR by 1,200 to convert it to a monthly decimal.
  • Rounding each row: A spreadsheet that rounds monthly interest to cents can produce a slightly different result from a formula that keeps extra precision internally. Issuer calculations may also follow account-specific timing and rounding.
  • Different balances may have different rates: Purchases, transfers, and cash advances can be subject to different terms. A single-rate schedule cannot accurately represent multiple portions of an account.
  • Changing rates or payments: A fixed APR and fixed payment are simplifying assumptions. If either changes, a schedule based on the old inputs no longer represents later months.

A spreadsheet therefore helps explain the mechanics and compare hypothetical inputs, but it cannot replace an account statement or payoff amount. Use the APR and balance applicable to the debt being modeled, and verify actual payment allocation and accrued interest with the issuer.

Frequently Asked Questions

What is a credit card amortization spreadsheet?
It is a worksheet that estimates how monthly payments are divided between interest and principal, and tracks an estimated balance over time. Credit card balances may not follow a fixed schedule if rates, payments, or purchases change.

How do I calculate credit card interest per month?
For this simplified monthly model, divide the APR by 12 to get a percentage rate, then by 100 to express it as a decimal. Multiply that rate by the month’s starting balance. For example, 18% APR gives a model rate of 1.5% per month.

Why does my spreadsheet show no payoff date?
If the monthly payment does not exceed the interest calculated for the month, the balance will not be paid down under the fixed-rate model. Check the APR, balance, and payment entries. Fees or additional charges can also cause an actual balance to decline more slowly.

Does the estimate include new purchases or fees?
No. This calculator models one starting balance with a fixed rate and steady payments. Additions, fees, different rate categories, and payment allocation rules are outside its assumptions.

Why is my card statement different from the spreadsheet?
The spreadsheet uses a simplified monthly interest model and fixed payments. The account’s actual daily balance, transaction timing, rate changes, fees, payment posting, and rounding may produce a different result. The result is an estimate; the lender’s disclosure gives the exact figures.

Related

Formula used
in bal | Starting balance | $ | 5000 | 0 | 100000000
in apr | Interest rate (APR) | % | 18 | 0.01 | 100
in pmt | Monthly payment | $ | 250 | 0.01 | 100000000
in extra | Extra monthly payment | $ | 0 | 0 | 100000000
out months | Estimated payoff time (months) | months | ceil(round(-ln(1 - (apr / 1200) * bal / (pmt + extra)) / ln(1 + apr / 1200), 6)) | 0
out total | Total paid at payoff | $ | (pmt + extra) * months | 2
out interest | Estimated interest paid | $ | total - bal | 2
out bal12 | Balance after 12 payments | $ | max(0, bal * (1 + apr / 1200) ^ 12 - (pmt + extra) * ((1 + apr / 1200) ^ 12 - 1) / (apr / 1200)) | 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 →