Build a Subscription Spreadsheet That Holds Up: The 21-Column Schema, the Formulas, and the 5 Things a Sheet Can't Do

A subscription spreadsheet holds up when it stores the billing cycle as a count plus a unit, derives one monthly-equivalent column from that pair, and totals only the derived column. Most templates do none of the three: twelve columns, a coloured header row, and a SUM at the bottom that quietly adds an annual plan to a monthly one and calls the result your monthly spend.

What follows is the record shape we actually ship: the field list our CSV export writes, the normalization constants in our production code, and a charge projection built the way the app builds it. You can rebuild it in Google Sheets or Excel in about ten minutes.

The second half is the part template pages never write, because they are selling you the template. Five jobs a spreadsheet cannot do — not "is bad at", cannot do — each one traced to something we had to build because a sheet could not carry it.

Last reviewed: August 31, 2026. The column list and the constants below were read out of SubBuddy's own export and analytics code rather than copied from a template. Formulas use US English function names and comma argument separators; if your Sheets or Excel runs in another locale, the names are translated and the separator is a semicolon.

The 21-Column Schema

Twenty of these are exactly what SubBuddy's export writes, in this order. The twenty-first is the one column the export does not carry, and the reason it does not is the first interesting thing about the schema: the app computes it on every render, so there is nothing to store.

Two absences are worth naming, because they tell you what this file is. There is no row id, and there is no is_one_time — a real field in our schema that the export simply does not include. Nor does it carry reminder settings, pin state, or icons. This is a portfolio export, not a backup, and it does not round-trip: the CSV importer expects a bank or card statement you upload, and asks you to map a date column, a description column and an amount column, so feeding this file back in is not a supported move. Nothing arrives on its own either — SubBuddy has no bank connection, so every row in the app got there because somebody typed it or reviewed an imported statement line. If you want the sheet to be your system of record, that is fine — just know it is a one-way door.

The Three Status Columns Are Not Three Booleans

This is the part that will silently corrupt your totals, so it comes before the fun formulas.

Columns I, J and K look like three independent switches. They are not. They are one status enum encoded as three flags, with a strict precedence — trial beats paused beats active beats cancelled — and only four legal combinations:

• active — is_active TRUE, is_paused FALSE, is_trial FALSE
• paused — is_active TRUE, is_paused TRUE, is_trial FALSE
• cancelled — is_active FALSE, is_paused FALSE, is_trial FALSE
• trial — is_active FALSE, is_paused FALSE, is_trial TRUE

Read that last line twice. A canonical trial is stored with is_active = FALSE. Which means the single most natural formula anyone writes in a subscription sheet —

=SUMIF(I2:I, TRUE, B2:B)

— silently drops every trial you are tracking. Your total excludes exactly the rows most likely to charge you a surprise next month. We know this because we shipped that class of bug, on both platforms, before the status logic was pulled into one place. The comment now sitting above it in our codebase says any guard shaped like "is_active AND is_trial" is unreachable, and any guard shaped like "is_active AND ..." silently drops trials.

So derive the status once, in its own column, and never touch I, J or K again:

V2 =IF(K2=TRUE,"trial",IF(J2=TRUE,"paused",IF(I2<>FALSE,"active","cancelled")))

That is a direct transcription of the precedence function every surface in our app calls. Every formula from here on filters on V, not on the raw flags.

The Normalization Formula

Column U turns four different cadences into one comparable number. These are the exact constants in production:

• day — amount x 30.4375 / count (the average month length across a 4-year cycle)
• week — amount x (52 / 12) / count (about 4.3333 weeks per month)
• month — amount / count
• year — amount / (12 x count)

The weekly constant is where most sheets lose money. Using 4 weeks per month instead of 52/12 understates a weekly charge by about 8% — on a $9.99 weekly subscription that is $39.96 a year (four weekly charges) that never appears in your total.

U2 =IF(V2<>"active",0,B2*SWITCH(E2,"day",30.4375/D2,"week",(52/12)/D2,"month",1/D2,"year",1/(12*D2),1))

Note what the gate does. In the app, two separate things happen: a filter keeps only active rows, then the conversion runs. A sheet has no filter step, so you fold both into one cell — and folding them is what stops a paused row from inflating your monthly rate with money that is not currently leaving your account.

Your headline number is then =SUM(U2:U), and the annual figure is that times twelve. If you would rather not build the cadence maths yourself, our annual cost calculator runs the same constants, and the subscription calculator totals a whole portfolio. For why the annual-versus-monthly choice is rarely the saving it looks like, see the annual plan math trap.

The 90-Day Charge Projection

The monthly equivalent answers "what does this portfolio cost per month on average". It does not answer "what is my card actually billed in October". Those are different measures and neither substitutes for the other — an annual renewal shows up as a twelfth every month in the first, and as one large hit in the second. The second is the one that overdraws your account.

Three more derived columns give you it:

W2 =IF(V2="trial",L2,F2) — the anchor. Trials anchor on trial_ends_on, because that is the day the card is first charged.

X2 =IF(OR(W2="",V2="paused",V2="cancelled"),0,IF(W2<TODAY(),"STALE",IF(W2>TODAY()+90,0,IF(OR(E2="day",E2="week"),INT((TODAY()+90-W2)/(D2*IF(E2="week",7,1)))+1,IF(EDATE(W2,3*D2*IF(E2="year",12,1))<=TODAY()+90,4,IF(EDATE(W2,2*D2*IF(E2="year",12,1))<=TODAY()+90,3,IF(EDATE(W2,D2*IF(E2="year",12,1))<=TODAY()+90,2,1))))))) — charges landing in the next 90 days.

Y2 =IF(ISNUMBER(X2),X2*B2,0) — money due in that window.

Two things in there are load-bearing.

The STALE guard. If renewal_date is in the past, the sheet is not wrong so much as abandoned, and a projection built on a stale anchor is worse than no projection. Flagging it is one of the things a sheet does genuinely well — it is a data-quality check, and data-quality checks are cheap in a spreadsheet.

Every occurrence is computed from the original anchor. Notice that the month branch uses EDATE with a multiplied step — EDATE(W2, k*m), never EDATE(previous_result, m). This is not stylistic. Step a 31 January anchor forward one month and you get 28 February — correct. Step forward from that and you get 28 March, then 28 April, and the date has permanently drifted three days early and never recovers. Compute from the anchor each time and 31 January plus two months is 31 March, which is what the merchant will actually charge. Our projection code multiplies rather than steps for precisely this reason.

To read the result, Google Sheets QUERY beats a pivot table:

=QUERY(A1:Y, "select A, B, C, W, Y where V = 'active' and Y > 0 order by W asc", 1)

That is your next 90 days of charges, in the order they will hit, in one cell.

Writing a CSV That Survives

Two of your columns — notes and cancellation_notes — are free text, which means sooner or later one of them contains a comma and your file develops an extra column. RFC 4180 settles it: fields containing line breaks, double quotes or commas should be enclosed in double quotes, and a double quote inside a quoted field is escaped by preceding it with another double quote. That is the whole rule, and it is exactly what our export does — double the quotes, wrap anything containing a delimiter. (The RFC specifies CRLF between records; in practice every parser worth using accepts a bare newline too.)

The other failure is encoding, and it is a real one we hit shipping the export. A UTF-8 file full of accented category names opens as mojibake in Excel unless Excel knows it is UTF-8. Microsoft's guidance is that a UTF-8 CSV opens normally if it was saved with a byte order mark; otherwise you have to bring it in through Data, Get Data, From Text/CSV. Our export writes the BOM, which is why double-clicking the file works. If you build the file yourself and it does not, that is the reason — and the From Text/CSV route also lets you pick the delimiter explicitly, which matters if your locale's list separator is a semicolon rather than a comma.

Five Things a Spreadsheet Cannot Do

Everything above is a sheet at its best. Here is the honest boundary. These are not features we are withholding from a template; they are jobs whose shape a spreadsheet cannot hold, and each one is a thing we had to build because the sheet could not carry it.

1. It cannot fire a reminder

A spreadsheet has no clock. Conditional formatting will turn a cell red — the next time you open the sheet. If you were going to open the sheet, you did not need the reminder. That is the whole failure, and it is structural: a reminder is a scheduled job, not a value in a cell. Note that reminder_days_before does not even appear in the 20 exported columns, because there is nothing meaningful to hand over. In the app it is an integer from 0 to 365 you can set per subscription, on top of account-level notification intervals. In a sheet, the honest substitute is a recurring calendar event that tells you to go look.

2. It cannot keep a price-change diff

When a service raises its price, you edit cell B7. The old value is gone. Nothing records what it was, when it changed, or by how much — and worse, every historical total you compute now uses today's price for months you were charged the old one. Your "spent in the last six months" figure quietly rewrites the past.

We store price changes as events instead: a price_changed row with a before-snapshot, an after-snapshot, and a timestamp, which is what lets the detail view bill the old amount for the months before the change and the new amount after. A sheet with one amount cell per subscription cannot represent "this cost $15.99 until March and $17.99 since" without you hand-rolling a second table — at which point you have built a worse database. If you want to check whether a price change is coming, our price hike checker is a faster answer than a column.

3. It cannot convert currencies at a dated rate

The moment column C holds two different values, =SUM(U2:U) is adding euros to dollars and reporting a number that means nothing. Sheets offers GOOGLEFINANCE, which gives you a rate — today's, live, and different tomorrow, so last month's total changes every time you open the file. The problem is not which rate source you wire in. It is that the cell holds one number and not that number plus the date it was true on, so a total you computed in March cannot be reproduced in June.

We hold rates EUR-based, from ECB reference rates, stored with the time they were fetched, and convert with amount_in_B = amount_in_A x (rate_B / rate_A). The part that matters more than the formula: when a rate is missing, our totals skip the row rather than counting it at face value in the wrong currency, because a silently understated month is worse than an obviously incomplete one. A SUM has no such option. It will add 12 and 12 and give you 24 whatever the symbols in front of them.

4. It cannot treat a trial end date as a first-charge date

This is the compound of problems 1 and 3, and it is the one that costs people real money. trial_ends_on is not an expiry date; it is the date your card gets charged for the first time. A sheet models it as a date in a cell, next to an is_active of FALSE, next to an amount your totals are excluding.

So the row that is about to charge you is the row your spreadsheet is most confident costs nothing. That is why the projection column above anchors trials on L rather than F — without that line, a 90-day forecast omits precisely the charges you built it to catch. Apple's own guidance is to cancel a trial at least 24 hours before it ends, which is a window a sheet cannot enforce and a notification can.

5. It cannot hold cancellation evidence

Here I want to be precise, because the sheet does better than you might expect. Columns S and T — cancellation_url and cancellation_notes — are both in the export, and a spreadsheet holds them fine.

What it cannot hold is the event. When a merchant bills you after you cancelled, "I edited a cell at some point" is not evidence. What you need is a dated record: a cancellation event with a snapshot of what the subscription looked like immediately before, and a timestamp you did not write yourself. We keep those in a history log for exactly this argument. A spreadsheet has version history, but that is a diff of a file, not a record of a decision, and it is a weak thing to put in front of a support agent.

The related skill — knowing what to say — is a separate problem, and our cancellation email generator handles that half.

Keep the Sheet or Outgrow It

A threshold, not a pitch.

Keep the spreadsheet if all four are true: you have roughly 15 rows or fewer, every row is in one currency, no row is a trial with a live card behind it, and finding out about a charge a few days late would annoy you rather than hurt you. Under those conditions the sheet is genuinely better than an app — it is free, it is yours, it has no account, and you understand every formula in it. Build the 21 columns, write the four derived ones, and stop.

Outgrow it the first time any single one of these becomes true — not all four, any one:

1. A second currency appears in column C.
2. You add a trial with a real card behind it.
3. A price changes and you want to be able to prove what it used to be.
4. A renewal arrives that you only noticed on the statement.

Each of those is one of the five, and each has the same tell: the sheet is still correct, and it still did not help. That is the moment the tool changed, not the moment you failed at spreadsheets.

Whichever side of the line you land on, the schema is the durable part. Fill it honestly and you will find rows you forgot existed — which is its own exercise, covered in the forgotten subscriptions checklist and the bank statement audit. A sheet you can fill completely is worth more than an app you never populate.

Sources and Scope

• RFC 4180 — the quoting and escaping rules our export follows for the free-text columns.
• Microsoft: opening UTF-8 CSV files correctly in Excel — the byte-order-mark rule behind the mojibake fix.
• Microsoft: import or export text or CSV files — the From Text/CSV route and where the delimiter is chosen.
• Google Sheets: EDATE — the month-stepping function the projection anchors on.
• Google Sheets: QUERY — the syntax used to read the projection back out.
• Apple: cancel a subscription — the 24-hour rule for cancelling a trial before it renews.

Scope note: the schema, the constants and the five limits come from SubBuddy's own code, checked on August 31, 2026. The dollar figures are worked examples, not measured spending.