Credit Card Interest Formula in Excel or Google Sheets
Updated · Published · By Ali Ahmad, Founder
You can calculate credit card interest in a spreadsheet using a simple formula: =Balance*(APR/12), applied month by month with the previous month's ending balance carried forward. You can also download our free Excel template below, which has every formula already built in.
If you're comfortable in a spreadsheet, building your own credit card interest formula is entirely doable — here's exactly how, along with the common mistakes that throw off the results.
Download the free template
Download the Excel template (.xlsx) Make a copy in Google Sheets
The template opens in Excel, Google Sheets (File > Import), Numbers, and LibreOffice. Enter your balance, APR, monthly payment, and any extra payment in the yellow cells, and it builds a 600-month payoff schedule with the months to pay off, total interest, and total paid. With the sample numbers ($5,000 at 24.99% APR, $150 a month) it shows 58 months and $3,622.26 of interest. If your payment doesn't cover the monthly interest, it warns you instead of showing a payoff date, and if the payoff would take more than 600 months (50 years) it says so instead of showing a total that stops halfway. The template rounds each month's interest to the cent, the way a card statement does, so its total can differ by a few cents from our online Credit Card Interest Calculator ($3,622.30 for the same numbers), which carries the unrounded figures from month to month. The sections below explain each formula so you can check or adapt it.
Use it in Google Sheets
The quickest way: click Make a copy in Google Sheets and Google saves your own editable copy to your Drive (you need to be signed in to a Google account). The template uses only formulas that Google Sheets supports (IF, MIN, ROUND, SUM and COUNTIF), so you can also import the .xlsx yourself:
- Download the .xlsx file above.
- In Google Sheets, open a blank spreadsheet and choose File > Import > Upload, then pick the file. (Or upload it to Google Drive and choose Open with > Google Sheets.)
- Choose Replace spreadsheet when asked, then type your own numbers into the yellow cells.
Your copy lives in your own Google Drive, so the numbers you enter stay private to you.
The basic monthly formula
In a new sheet, set up columns for Month, Starting Balance, Interest, Payment, Principal, and Ending Balance. Your interest formula for each row is simply =StartingBalance*(APR/12). Principal is =Payment-Interest, and Ending Balance is =StartingBalance-Principal. Each new row's Starting Balance references the previous row's Ending Balance, and you drag the formulas down until the balance reaches zero.
Where spreadsheet formulas commonly go wrong
The most frequent error is using a flat annual formula (Balance × APR) without dividing by 12, which dramatically overstates monthly interest. The second most common mistake is forgetting to carry the previous month's ending balance into the next month's starting balance, which produces a payoff timeline that never actually finishes.
Adding an amortization table
Once the basic formula works, extending it into a full amortization table just means continuing the row-by-row calculation until the balance hits zero, then using a COUNT or MATCH function to find your payoff month automatically, and a SUM function to total your interest column for the total interest paid.
Handling a payment that changes month to month
If you're modeling a minimum payment that recalculates as your balance shrinks (rather than a fixed payment), your Payment column needs its own formula — for example, =MAX(StartingBalance*MinPercent, Interest+StartingBalance*1%, FloorAmount) — which is exactly the kind of formula that's easy to get subtly wrong in a spreadsheet.
When a spreadsheet is worth building anyway
A spreadsheet is genuinely useful if you want to model unusual scenarios — irregular extra payments on specific months, a rate that changes partway through, or a custom minimum payment formula matching your exact issuer. For a standard payoff projection, though, an online calculator gets you the same answer instantly, with charts and a payoff date already built in.
Skip the spreadsheet setup
Try the Credit Card Interest Calculator for an instant version of the same month-by-month math, including a full amortization table and chart.
Frequently Asked Questions
Will a spreadsheet match my actual statement exactly?
Not necessarily — a monthly spreadsheet model is a planning estimate. Issuers may use daily periodic rates, average daily balances, fees, and card-specific terms (see our guide to calculating interest by hand), so your statement can differ, sometimes materially.
Is there a downloadable template I can use?
Yes. Download the free Excel template — it has the six columns described above with the formulas already filled in, plus totals and a warning when the payment is too small. You can also rebuild it from scratch in a few minutes using the formulas on this page.
This article is for general educational purposes and is not financial advice. Rates and figures cited reflect industry data available at the time of writing and can change.
Sources
- What is a grace period for a credit card? — Consumer Financial Protection Bureau. Checked September 25, 2026.