Yearly charges have a way of ambushing you: a renewal hits the card and you think, "wait, I still pay for that?" The fix isn't another app — it's a ten-minute audit in a plain spreadsheet. Two formulas do all the work, and the number at the end is always more honest than you expect.
The two formulas
1. Next renewal date: EDATE
Each row lists a subscription: name, billing cycle (monthly / quarterly / half-year / yearly), amount, and the date it was last charged. The next renewal computes itself:
=EDATE(last_charged, IF(cycle="月",1,IF(cycle="季",3,IF(cycle="半年",6,12))))
EDATE adds calendar months, so a yearly plan last charged 2026-06-20 renews 2027-06-20. No manual date math, no "was that 30 or 31 days" bugs.
2. True monthly cost: normalize everything to one unit
A ¥1,800/year gym membership and a ¥25/month streaming plan can't be compared until they speak the same unit:
=ROUND(amount / IF(cycle="月",1,IF(cycle="季",3,IF(cycle="半年",6,12))), 2)
That gym works out to ¥150/month — instantly the most expensive line on the list. Normalizing to monthly is the entire trick of a subscription audit: yearly plans hide their true weight behind one big number you only see once.
The overview sheet
With those two columns in place, a second sheet aggregates the picture:
- Total monthly / yearly cost — SUM of the normalized column, and SUMPRODUCT of amount × 12/months-per-cycle for the yearly figure
- Active subscription count
- Renewals in the next 30 days — so nothing surprises you
- Priciest subscription in ¥/month — your first cancellation candidate
To show what the output looks like, the template ships with 8 sample rows (a realistic household mix: video streaming, music, cloud storage, a gym, mobile plan, broadband, a domain name, and a notes app still on trial). They total ¥346.34/month — ¥4,156/year — with 4 renewals landing in the next 30 days and the gym as the priciest line at ¥150/month. Your numbers will differ; the shape of the surprise usually doesn't.
The three audit questions
Once the number is honest, only three questions matter:
- Trial or zombie? Anything still marked "trial" long after the trial ended is a zombie. Kill it.
- Yearly but unused? A yearly plan you forgot about is the most expensive kind of subscription — twelve months paid for zero months of value.
- Monthly that should be yearly? The flip side: something you genuinely use every month is often 20–40% cheaper on the yearly plan. The normalized column makes the saving obvious.
I packaged this as a small bilingual (EN/中文) Excel template — the same two sheets, sample rows included, yellow cells for your numbers and blue cells for formulas, works in Excel, WPS, LibreOffice and Google Sheets: https://ko-fi.com/s/bbc130f6e7 ($0.99, one-time download).
No app, no account, no "smart" anything. Just EDATE and division.
Ran your own audit recently? Curious what turned out to be the priciest line item.
Top comments (0)