Spreadsheets

Credit card spend tracker sheet organised by statement cycle

Track credit card spends by statement cycle, not calendar month, in a spreadsheet with a statement-month formula and totals that match your bill.

Editorial illustration for Credit card spend tracker sheet organised by statement cycle

To make your credit card tracker match the bill you actually pay, group transactions by statement cycle, not calendar month. Give each card a tab with date, merchant, amount, category and a calculated statement month based on the day your cycle closes. A SUMIFS on that column then gives a total you can compare line for line with the statement.

Why do calendar months not match the bill?

A card's statement covers a window that ends on a fixed day, often in the middle of the month. A purchase on the 16th falls into the next statement. If your sheet adds up 1 to 30 and your bill covers 16 to 15, the two numbers will never agree, and you will waste time hunting a difference that is only a boundary.

How do I set up a card tab?

Create one tab per card, named after the card. Use these columns:

  • A: Transaction date
  • B: Merchant
  • C: Amount, entered as positive for spends and negative for refunds
  • D: Category
  • E: Statement month, calculated
  • F: Note, such as EMI, annual fee or refund

On a Settings tab, put the day of the month on which your statement is generated in B1. Read it from your own statement, since it differs by card, and check it after any change the issuer makes.

What is the statement month formula?

In E2:

=IF(DAY(A2)<=Settings!$B$1, DATE(YEAR(A2), MONTH(A2), 1), DATE(YEAR(A2), MONTH(A2)+1, 1))

A transaction on or before the statement day belongs to that month's statement; one after it rolls to the next. Format column E as mmm yyyy. If a card's cycle dates are not on the same day every month, replace the formula for those rows with the statement month typed by hand.

A worked example with made-up purchases

Say the statement day is the 15th. Six transactions:

DateMerchantAmountStatement month
12 OctElectronics store₹2,400Oct
15 OctPharmacy₹1,600Oct
16 OctRestaurant₹3,000Nov
30 OctFuel station₹4,500Nov
2 NovGrocery delivery₹900Nov
16 NovOnline store₹700Dec

The October statement is 2,400 + 1,600 = ₹4,000. The November statement is 3,000 + 4,500 + 900 = ₹8,400. Group by calendar October and you would see 2,400 + 1,600 + 3,000 + 4,500 = ₹11,500, which would not match anything on the bill.

How do I total each statement and track payment?

Add a Statements tab with one row per statement month. In B2, spend total for the month in A2: =SUMIFS('Card 1'!$C$2:$C$2000, 'Card 1'!$E$2:$E$2000, A2). Lay the columns out as A statement month, B recorded spend, C statement amount typed from the bill, D difference, E amount you paid and F status.

  • Difference in D2: =C2-B2, which should be near zero if you recorded everything
  • Status in F2: =IF(E2>=C2, "Paid in full", IF(E2=0, "Unpaid", "Part paid"))

Paid in full is a statement-level fact, not a transaction-level one, which is why it lives here. A difference that is not zero usually means an annual fee, an interest charge, a refund or a transaction you forgot to enter.

Refunds and reversals deserve their own care. Enter them as negative amounts on the date they are credited, and give them the statement month by the same formula, so a refund that lands after the cycle closes reduces the next statement and not the one you have already paid.

A quick checklist when the statement arrives

  1. Read the statement date and total from the bill itself.
  2. Compare it with the sheet's total for that statement month.
  3. Explain any difference, line by line, before you pay.
  4. Record the payment and its date in the Statements tab.

Typing every swipe is the tedious part. SpendixAI lets you pick the card account and upload its statement as PDF, CSV or Excel, review the rows before saving, and flags possible duplicate imports, so the sheet can focus on the cycle view.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports