DEV Community

Black
Black

Posted on

Auto-categorizing bank transactions and simulating a debt snowball with plain spreadsheet formulas

#excel #spreadsheet #tutorial #productivity

I built a budget spreadsheet this week and two formula problems were more interesting than I expected: categorizing bank transactions by keyword without a script, and simulating a debt snowball with plain cell formulas. Both work in Excel and Google Sheets with no macros.

1. Keyword auto-categorizing with LOOKUP(2^15, SEARCH(...))

The setup: a Rules table with a keyword column (kroger, netflix, payroll...) and a category column. A transaction description like KROGER #4412 should come out as Groceries.

=IF(C6="","",
  IF(F6<>"", F6,
    IFERROR(
      LOOKUP(2^15, SEARCH(Rules!$B$6:$B$155, C6) / (Rules!$B$6:$B$155<>""), Rules!$C$6:$C$155),
      "Uncategorized")))
Enter fullscreen mode Exit fullscreen mode

How it works:

  • SEARCH(keywords, C6) returns an array: a position number where the keyword appears in the description, #VALUE! where it doesn't. SEARCH is case-insensitive, so kroger matches KROGER.
  • Empty keyword cells are a trap: SEARCH("", text) returns 1, so every blank rule would match. Dividing by (keywords<>"") turns those into #DIV/0!.
  • LOOKUP(2^15, array, result) looks for a number bigger than any possible position. LOOKUP ignores errors and falls back to the last numeric entry, so it returns the category of the last matching rule. That gives you rule priority for free: later rows win.
  • F6 is a manual override column with a dropdown, checked first.

One catch: Google Sheets needs this as an array formula. If you write the file with a library, store it as a CSE array formula ({=...}). Sheets converts it to ARRAYFORMULA on import.

2. Debt snowball in a grid of formulas

Snowball = pay minimums on everything, throw all extra money at the smallest balance; when a debt is gone, its minimum rolls over to the next one. Avalanche is the same thing ordered by highest APR.

The payoff order is a rank with a tiebreak, so two equal balances never get the same rank:

key:   =IF($L$5="Snowball", Balance, -APR)*1000 + ROW()
order: =COUNTIFS(Balances,">0", Keys,"<"&ThisKey) + 1
Enter fullscreen mode Exit fullscreen mode

A hidden sheet then lays debts out left to right in payoff order (via MATCH(k, order, 0)), one row per month, three columns per debt: amount owed after interest, extra payment, ending balance.

owed_k   = prev_balance_k * (1 + APR_k/12)
pool     = (sum of all original minimums + extra) - sum(MIN(owed_k, min_k))
extra_k  = MAX(0, MIN(owed_k - MIN(owed_k, min_k), pool - extras of debts before k))
balance_k = owed_k - MIN(owed_k, min_k) - extra_k
Enter fullscreen mode Exit fullscreen mode

Because the pool is "total budget minus the minimums still due", a paid-off debt's minimum automatically joins the pool next month. That's the snowball rollover with no IF chains.

Payoff month per debt is just COUNTIF(balance column, ">0") + 1. For the "minimums only" comparison line, FV(rate, n, min, -balance) gives each debt's balance after n months, and NPER gives its payoff month, so total interest saved is:

=SUM(IFERROR(NPER(APR/12, -Min, Balance) * Min - Balance, 0)) - TotalInterestOnPlan
Enter fullscreen mode Exit fullscreen mode

With the sample debts ($34k across four debts, $200/month extra) the snowball finishes in 49 months versus 138 on minimums, and saves about $6,600 in interest.

Checking it without Excel

I generated the workbook with Python (xlsxwriter) and checked every formula headlessly: LibreOffice can recalculate and re-save the file (--convert-to xlsx with the OOXML recalc mode set to "always" in the profile), then openpyxl with data_only=True reads the computed values. Any cell starting with # is a bug. That caught a helper column overwriting two labels before anyone saw it.

The finished template (dashboard, 2,000-row transaction log, rules, year view, debt planner, goals, bills) is here if you want it: 2027 Budget Dashboard. I post more tools and templates at patreon.com/123black.

Top comments (0)