How to Track Credit Card Credits in a Spreadsheet (Free Template Formulas)

We make OfferBee, an app that does this job automatically, so read the last section knowing that. The rest of this is a genuinely free method, written to work: the formulas below are tested, and the honest failure modes are named rather than buried so that our product looks better.

A statement-credit tracker is not a budget. It answers one question per row: is there money on this card I have not collected yet, and when does it disappear? That makes it a small, well-shaped spreadsheet, which is why the method works at all.

Here is one you can build in about ten minutes.

The columns

One row per credit, not per card. An Amex Platinum is not a row; each of its credits is.

Two of these get skipped and both come back to bite. Column E is what makes anniversary-year credits computable at all. Column J records why a credit failed. For example, you selected Delta in January, bought a United bag fee in June, and nothing posted.

The formulas

Paste these into row 2 and fill down. They work in Google Sheets and in Excel.

F: the next reset date. Five cadences, one nested IF:

=IF($D2="Monthly",      EOMONTH(TODAY(),0)+1,
 IF($D2="Quarterly",    DATE(YEAR(TODAY()), FLOOR(MONTH(TODAY())-1,3)+4, 1),
 IF($D2="Semi-annual",  DATE(YEAR(TODAY()), IF(MONTH(TODAY())<=6,7,13), 1),
 IF($D2="Annual",       DATE(YEAR(TODAY())+1,1,1),
 IF($D2="Anniversary",  EDATE($E2, CEILING((DATEDIF($E2,TODAY(),"M")+1)/12, 1)*12),
 "")))))

Why each one works, since you will want to modify them:

• Monthly: EOMONTH(TODAY(),0)+1 is the day after the end of this month, i.e. the 1st of next month.
• Quarterly: FLOOR(MONTH-1,3) snaps to 0/3/6/9; +4 gives the first month of the next quarter. December resolves to month 13, and DATE rolls that into January of the following year on its own.
• Semi-annual: first half resets in July, second half in month 13, which is next January.
• Annual: calendar year. January 1st.
• Anniversary: count whole months since you opened the card, round up to the next multiple of 12, and add that many months to the open date. This is the one people give up on and hand-type, and hand-typed dates are the ones that go wrong.

G: days left:

=$F2-TODAY()

Format the column as a plain number, not a date, or you will get 1900.

I: status:

=IF($H2, "Claimed", IF($G2<=7, "URGENT", IF($G2<=30, "Soon", "OK")))

Then one conditional-format rule on column I: red if the text is URGENT, amber for Soon, grey for Claimed. That colour is doing most of the work; it is the only thing in the sheet that catches your eye before you have read anything.

The summary block. Put these somewhere above the table:

Money at risk in 30 days   =SUMIFS($C$2:$C, $H$2:$H, FALSE, $G$2:$G, "<=30")
Total credit value / year  =SUMPRODUCT($C$2:$C, $K$2:$K)
Annual fees paid            (type it in)
Net                         =B2-B3

That needs one more column:

K: periods per year:

=IFS($D2="Monthly",12, $D2="Quarterly",4, $D2="Semi-annual",2, TRUE,1)

"Money at risk in 30 days" is the single most useful cell in the file. It is the number you want on a Sunday evening.

A filled-in example

These figures were verified against the issuers' own pages on 2026-09-08, the same pass behind our card teardowns. They will still rot: Amex's own CLEAR+ line moved $209 → $219 between its September 2025 fact sheet and its live page inside a year. Copy the shapes, then pull your own numbers from your card's benefits page.

Assume today is 8 September 2026 and the Sapphire Reserve was opened on 15 March 2024.

Money at risk in 30 days: $35, the three unclaimed rows resetting on 1 October. Tracked credit value across these nine rows: $1,740/yr (amount × periods), against $2,015 of annual fees. That is not the whole picture and the sheet should not pretend it is: these nine rows are a subset, and the full published lineups are far larger. It is the number you are actually capturing that decides a renewal, which is exactly why the Claimed column is the only one that matters in December.

Two rows are doing more work than the rest.

The lululemon row has question marks, and that is the honest state. Amex publishes the cadence ("each quarter"), but not the boundaries. Its Gold page publishes Resy's windows as January–June and July–December; its Platinum page names no dates for the quarterly credits at all. So a hand-kept sheet genuinely cannot compute that reset date, and writing a confident "1 Oct" in the cell would be inventing one. Leave it unknown and check the Benefits section in your account.

And the Sapphire Reserve row resets in March, not January, because it runs on the cardmember year, not the calendar. Its travel credit renews on the account anniversary, because it runs on the cardmember year. That one row is the reason column E exists, and it is the row that a mental model built around "credits reset in January" silently loses.

Where this breaks

Every one of these is real, and none of them is a formula problem.

1. Nothing prompts you. This is the whole thing. The sheet computes "URGENT" perfectly and then sits there. Your credits do not expire because you did the arithmetic wrong; they expire because it was a Tuesday and you did not open a spreadsheet. Everything below is secondary to this.

2. The checkbox has no memory. On 1 October you have to uncheck every monthly row, and the moment you do, the sheet no longer knows you claimed September. You cannot answer "have I actually been using the Uber credit, or have I claimed it twice this year?"

The fix, if you want it: replace the checkbox with a Last claimed date and derive the status from whether that date falls inside the current period.

Period start (col L):
=IF($D2="Monthly",     EOMONTH(TODAY(),-1)+1,
 IF($D2="Quarterly",   DATE(YEAR(TODAY()), FLOOR(MONTH(TODAY())-1,3)+1, 1),
 IF($D2="Semi-annual", DATE(YEAR(TODAY()), IF(MONTH(TODAY())<=6,1,7), 1),
 IF($D2="Annual",      DATE(YEAR(TODAY()),1,1),
 IF($D2="Anniversary", EDATE($F2,-12),
 "")))))

Status (col I):
=IF(AND($H2<>"", $H2>=$L2), "Claimed",
   IF($G2<=7,"URGENT", IF($G2<=30,"Soon","OK")))

Now nothing has to be unchecked; the period rolls forward and the status corrects itself. This is the single upgrade worth making.

3. Partial usage. A $200 airline credit with $63 used is neither claimed nor unclaimed. Add a Used so far column and set Remaining = C - used, then point the summary at remaining instead of amount. Otherwise a checkbox throws away $137.

4. The sheet records intent, not reimbursement. You spent the money; that is not the same as the issuer paying it back. Credits post days or weeks later, and they fail silently: wrong card, ineligible merchant, never enrolled. A spreadsheet cannot see your statement, so it cannot tell you the difference between "used" and "actually credited." This is the gap that costs real money, and it is structural.

5. Enrollment. Several credits require a click in the issuer's app before they exist. Column J is where that lives, and column J is the one nobody fills in.

6. Terms change under you. Your sheet is a copy of the terms as of the day you typed them. When an issuer changes a monthly credit's amount or splits an annual one into halves, nothing tells you, and your sheet keeps confidently computing the old answer.

7. It scales badly. One card with three credits is a delight. Three premium cards is 15 to 20 rows across four cadences plus two different anniversary dates, and the maintenance cost crosses the value line somewhere in there.

The honest verdict

Build the sheet if you hold one or two cards, most of your credits are monthly or annual, and you already have a habit of opening spreadsheets. It is free, it is yours, it works offline, and no one gets your transaction history. That is a genuinely good deal and plenty of people should stop here.

It stops being enough when the reset dates outnumber what you can hold in your head, or when you find a credit expired that your sheet said was fine, which almost always means it never posted and you had no way to know.

One test: open your issuer's app right now and check whether last month's credit posted. If you knew the answer, the sheet is fine. If you had to look, that is the gap.

What an app adds, in one paragraph

The thing a sheet cannot do is interrupt you. OfferBee's manual credit tracking does the same job as the spreadsheet above: one row per credit, the same five cadences including anniversary years, and the ability to backfill a month you forgot. It adds reminders before a reset rather than after, and it stays free after the 14-day trial for cards already in your wallet (Pro is $9.99/mo or $80/yr at offerbee.ai, $12.99/mo or $104.99/yr through the App Store, and adding a new card needs it). Connecting a bank closes gap 4 above by reading the transactions so the app can tell you a credit actually posted. It is optional; the hand-kept ledger works without it. If you would rather keep the spreadsheet, keep the spreadsheet. Just add the period-start upgrade from point 2, because that one is free and it fixes the error you are most likely to be making.

---

Formulas tested in Google Sheets on 2026-09-08 and written to work in Excel as well. Credit amounts in the worked example were verified against the issuers' own pages on 2026-09-08 and will change. Verify yours against your card's benefits page. Re-checked quarterly.