Find Recurring Payments in Bank Statements (Excel)

TL;DR To find recurring payments, get at least three to six months of bank statements into one Excel table, clean the merchant names so the same company always looks the same, then count how many different months each merchant appears in. Merchants that show up in most months at a similar amount are your recurring payments: subscriptions, memberships, insurance, loans, utilities. Add the average gap between payments to separate monthly from annual charges, compare the latest amount with the earlier ones to catch price increases, and annualize everything so small charges show their true cost.

Recurring payments are easy to start and easy to forget. A free trial converts to a paid plan, a streaming service raises its price, an old gym membership keeps charging, a software tool your team stopped using keeps renewing. Individually they're small. Together they can be a real share of monthly spending, for households and businesses alike.

Your bank statements contain the full record, but they don't label anything as "recurring". This guide shows how to find recurring payments yourself with Excel: getting the data in, normalizing merchant names, detecting patterns by month and by interval, catching price increases, and turning the result into a list you can act on. The same approach works in Google Sheets.

Get enough history into one table

Recurring patterns only show up over time. One month tells you almost nothing. Aim for:

• At least 3 months to spot monthly charges.
• 6 to 12 months to see quarterly charges and price changes.
• 13 months or more to catch annual renewals, which are often the most expensive and the most forgotten.

Include every account that pays bills: checking accounts and every credit card. Subscriptions are often on cards, and if you only look at checking, you'll see the card payment but not what's inside it.

Download the statement PDFs and convert them into one table with Date, Account, Description and a signed Amount (money out negative). SheetStatement converts statement PDFs to Excel or CSV and checks each statement balances, so you know no transactions are missing. The free plan covers 3 pages a month; Starter ($9), Pro ($19) and Business ($39) monthly plans suit a year of statements across several accounts. For card statements, see the credit card statement converter.

Then combine everything into one table named Tx. Our guide to combining multiple bank statements in Excel covers that step, including how to avoid duplicates.

Add a Month column:

=TEXT([@Date], "yyyy-mm")

Clean the merchant names

This is the step that makes or breaks the analysis. Bank descriptions for the same merchant vary from month to month:

• STREAMCO*SUBSCRIPTION 12345
• STREAMCO*SUBSCRIPTION 67890
• STREAMCO.COM HELP.STREAMCO.COM

Counting the raw descriptions shows three different merchants, each appearing once. You need one Merchant value for all three.

Option 1: a rules table

Create a table Rules with two columns: Keyword and Merchant. For example STREAMCO maps to StreamCo, CLOUDHOST maps to CloudHost. Then:

=IFERROR(INDEX(Rules[Merchant], MATCH(TRUE, ISNUMBER(SEARCH(Rules[Keyword], [@Description])), 0)), "")

This returns the first merchant whose keyword appears anywhere in the description. In Excel 365 you can write the same with XLOOKUP:

=XLOOKUP(TRUE, ISNUMBER(SEARCH(Rules[Keyword], [@Description])), Rules[Merchant], "")

Fill the rules table by sorting descriptions alphabetically and working down the list. Our guide to Excel formulas to categorize transactions uses the same pattern for categories, and you can extend the rules table with a Category column at the same time.

Option 2: strip the noise

For a quick first pass, strip digits and common noise from descriptions so similar ones collapse together. In Excel 365, with REGEXREPLACE available in current builds:

=TRIM(REGEXREPLACE(UPPER([@Description]), "[0-9#*]+", " "))

If your version doesn't have regex functions, use the first word or two of the description as a rough merchant key:

=TEXTBEFORE(TRIM([@Description]) & " ", " ")

This is cruder but gets you started. Refine with a rules table for the merchants that matter.

Count the months each merchant appears in

With a Merchant column in place, build a summary. Get the list of merchants with spending:

=UNIQUE(FILTER(Tx[Merchant], (Tx[Merchant]<>"") * (Tx[Amount]<0)))

Next to each merchant (in column A of the summary), count the distinct months it appears in:

=COUNTA(UNIQUE(FILTER(Tx[Month], (Tx[Merchant]=A2) * (Tx[Amount]<0))))

And the number of charges, the average and latest amounts:

=COUNTIFS(Tx[Merchant], A2, Tx[Amount], "<0")

=-AVERAGEIFS(Tx[Amount], Tx[Merchant], A2, Tx[Amount], "<0")

=-XLOOKUP(A2, Tx[Merchant], Tx[Amount], , 0, -1)

The last one searches from the bottom of the table, so with the table sorted by date it returns the most recent charge.

Now divide months-present by the number of months in your data. A merchant present in 11 of 12 months is almost certainly a monthly recurring payment. One present in 2 of 12 might be quarterly, occasional, or coincidence.

Measure the gap between payments

Months-present finds monthly charges. Annual and quarterly charges need the interval between payments. For each merchant, the average gap in days:

=LET(d, SORT(FILTER(Tx[Date], (Tx[Merchant]=A2) * (Tx[Amount]<0))), IF(ROWS(d)<2, "", ROUND(AVERAGE(DROP(d,1) - DROP(d,-1)), 0)))

This sorts the merchant's charge dates, subtracts each from the next, and averages the gaps. Interpret roughly:

A merchant with a steady gap and a steady amount is a strong recurring candidate even if it only appears twice, for example an annual software renewal that appeared in both years of your data.

Also look at the spread. If one merchant's gaps are 3, 40, 12 and 60 days, it's a place you shop, not a subscription. A quick measure is the standard deviation of the gaps, with the same LET structure using STDEV.P instead of AVERAGE.

Check that the amount is steady

Recurring payments usually have a fixed or near-fixed amount. A merchant you visit often (the supermarket, a fuel station) can appear every month but with varying amounts, which isn't a subscription. Compare the spread of amounts with the average:

=LET(a, -FILTER(Tx[Amount], (Tx[Merchant]=A2) * (Tx[Amount]<0)), IF(ROWS(a)<2, "", STDEV.P(a) / AVERAGE(a)))

A result near 0 means the amount barely changes. A high value means it varies a lot. Utilities and phone bills vary somewhat but are still recurring, so treat this as a signal, not a rule.

Flag recurring payments

Put the signals together in a Recurring column. One reasonable rule:

=IF(AND(C2>=3, OR(AND(E2>=27, E2<=33), AND(E2>=85, E2<=95), AND(E2>=355, E2<=375)), F2<0.15), "Recurring", "")

where C is the number of charges, E the average gap and F the amount variation. Adjust the thresholds to your data. For annual charges, relax the count requirement to 2.

Then review the flagged list by eye. Automatic rules are a starting point; your own knowledge of what you signed up for finishes the job.

Catch price increases

Subscriptions often go up quietly. Compare the latest amount with the first amount in your data:

=-XLOOKUP(A2, Tx[Merchant], Tx[Amount], , 0, 1)

returns the earliest charge (searching top-down with the table sorted by date). Then:

=IF(Latest > First, "Up " & TEXT(Latest/First - 1, "0%"), "")

Sort by that column to see which services raised prices and by how much.

Annualize everything

Small monthly charges look harmless. Annualized, they look different. Add an Annual Cost column:

=IF(G2="Recurring", D2 * ROUND(365 / E2, 0), "")

where D is the latest amount and E the average gap. A 12.99 monthly charge becomes about 156 a year. Sort descending and the most expensive commitments rise to the top.

A month-by-merchant grid

Formulas give you flags. A grid lets you see the pattern with your own eyes, which is often faster for spotting odd ones out. Build a pivot table from Tx with Merchant in rows, Month in columns, and the sum of Amount in values, filtered to money out. Or build it with formulas, with merchants down column A and months across row 1:

=-SUMIFS(Tx[Amount], Tx[Merchant], $A2, Tx[Month], B$1, Tx[Amount], "<0")

Apply conditional formatting so non-zero cells are shaded. Recurring payments show up as solid horizontal bands across the grid. A band that stops tells you when you cancelled something. A band that starts tells you when a trial converted. A band whose number steps up partway along is a price increase. A cell missing in the middle of a band is worth a look: a failed payment, a pause, or a charge that moved to a different card.

Our guide to bank statement pivot tables in Excel covers the pivot approach in more detail.

Build a renewal calendar

Once you know what recurs, predict when it next charges. For each recurring merchant:

=XLOOKUP(A2, Tx[Merchant], Tx[Date], , 0, -1) + E2

takes the most recent charge date and adds the average gap. Sort by that column to get an upcoming-charges list. For annual renewals, set a reminder two to four weeks before the predicted date, which leaves time to decide whether to keep the service, switch to a cheaper plan, or cancel before it renews.

For a business, the same list feeds a cash forecast: you know roughly what will leave the account each week, and the big annual charges no longer arrive as surprises.

Make it a routine

Recurring payments creep back. A light routine keeps them under control:

• Monthly: add the new statements, refresh the summary, and glance at the grid for new bands.
• Quarterly: review the full recurring list with everyone who uses the services. Ask, for each one, whether it's still needed and whether a cheaper plan would do.
• Before annual renewals: use the calendar to decide in advance.
• Yearly: compare the annualized total with last year's. If it's grown, the grid shows where.

The rules table you built is the expensive part, and it carries over. Each month you only add keywords for new merchants.

A worked example

A household gathers 13 months of statements for one checking account and two credit cards, converts them, and combines them into one table of around 1,400 transactions.

1. Merchants. A rules table of about 60 keywords covers most descriptions. The rest are one-off shops.
2. Summary. 180 merchants appear at least once. 24 appear in 10 or more months.
3. Gaps and amounts. Of those 24, 15 have monthly gaps and steady amounts: streaming services, phone, internet, insurance, a gym, cloud storage, a meal kit, a news site, a charity donation. The rest, groceries, fuel and a café, vary too much to be subscriptions.
4. Annual. Three merchants appear exactly twice, about 365 days apart: a software suite, a password manager and a roadside assistance plan.
5. Price rises. Four services increased their price during the year, between 10% and 25%.
6. Annualized total. The 18 recurring payments add up to a meaningful annual sum, and five of them are things the household no longer uses: a second streaming service, a meal kit they paused but never cancelled, a gym, a magazine and an app subscription from a free trial.
7. Action. They cancel the five and set a reminder a month before each annual renewal.

The analysis took a couple of hours, most of it building the rules table, which is reusable next year.

Business use: software and vendor audits

Businesses benefit even more, because recurring software spend grows as teams grow, and charges spread across several cards and accounts. A periodic recurring-payment review can:

• Find duplicate tools. Two teams paying separately for the same service.
• Find seats for departed staff. Per-user charges that didn't drop when someone left.
• Find forgotten annual renewals, before they renew.
• Track vendor price changes for budgeting.

Add an Owner column so each recurring charge has someone responsible for it. Our guides to categorizing business expenses from bank statements and tracking expenses from bank statements cover the wider workflow, and a pivot table gives a quick monthly view by vendor.

Recurring income too

The same method finds recurring money in: salary, rent from tenants, retainer clients, regular transfers. Flip the filter to Tx[Amount]>0. That's useful for budgeting, for showing a lender regular income, and for spotting a client who stopped paying. Our guide to self-employed proof of income from bank statements uses a similar monthly view.

Cancelling and confirming

Finding a forgotten subscription is only half the job. After cancelling:

• Keep a cancellation log with the service, the date you cancelled, any confirmation number, and the date the next charge would have happened.
• Check the next statement. If the charge appears again after the cancellation date, contact the merchant with your confirmation. If that doesn't resolve it, your bank or card issuer can explain the dispute options available to you.
• Watch for charges moving. Some services, when a card expires or is replaced, retry on another payment method on file. The month-by-merchant grid makes this easy to spot, because the band continues on a different account.

Pitfalls to watch for

Payment processors hide merchants. Some charges appear under a payment processor's name rather than the merchant's. Look at the full description; there's often a merchant name after an asterisk.

Card payments on checking. The monthly payment to your credit card appears on checking every month at a varying amount. It isn't a subscription; it's a transfer. Exclude it.

Bundled charges. An app store or marketplace charge may include several different subscriptions. Check the platform's own subscription settings for the breakdown.

Foreign currency charges. A subscription billed in another currency varies slightly each month with the exchange rate. Allow a little more amount variation.

Missing months. A gap in your data looks like a gap in the subscription. Make sure every statement is present and balances before trusting month counts.

FAQ

How many months of statements do I need?

At least three for monthly charges, and 13 or more to catch annual renewals. More history makes patterns clearer.

Why do I need to clean merchant names?

Banks print slightly different descriptions for the same merchant each month, often with reference numbers. Without cleaning, one subscription looks like several unrelated one-off charges.

How do I tell a subscription from a shop I visit often?

Subscriptions have steady amounts and regular gaps between charges. Shops you visit often have varying amounts and irregular gaps.

Can Excel find annual subscriptions?

Yes. Calculate the average gap between charges for each merchant. A gap of around 365 days with a similar amount points to an annual renewal.

Should I include credit card statements?

Yes. Many subscriptions are billed to cards. Checking alone only shows the monthly card payment, not the charges inside it.

How do I find when a subscription will renew next?

Take the date of the most recent charge and add the average gap between charges. For annual renewals, set a reminder a few weeks before that date.

What if a cancelled subscription keeps charging?

Contact the merchant with your cancellation confirmation first. If that doesn't work, ask your bank or card issuer about the options for disputing the charge.

Does this work in Google Sheets?

Mostly. UNIQUE, FILTER, COUNTIFS and XLOOKUP exist in Sheets. Replace LET-based formulas with helper columns if needed, and use REGEXREPLACE, which Sheets has supported for years.

Can I use this for business expense audits?

Yes. Combine all company accounts and cards, add an Owner column, and review recurring charges quarterly for unused tools, departed staff and upcoming renewals.

How much does SheetStatement cost?

The free plan includes 3 pages a month. Starter is $9 a month, Pro $19 and Business $39.