15 Essential Google Sheets Formulas for Personal Finance

Quick answer. Five formulas do most of the work in a personal finance spreadsheet: SUMIFS, ARRAYFORMULA, XIRR, FV, and IFERROR. The other ten on this list are situational but worth recognizing. The point of the list is recognition, not memorization. When you hit a problem, knowing which formula solves it is more useful than being able to type the syntax from memory.

A personal finance spreadsheet is mostly the same five or six formulas, repeated across different ranges. The novelty is in how they combine. Below is the working list - the 15 that show up across budgets, trackers, and projections - with syntax, a real example for each, and an honest note on which earn their place versus which are nice-to-know.

Examples assume a transaction log with columns A: Date, B: Category, C: Merchant, D: Amount. Most personal finance sheets boil down to a variant of that table plus a summary tab.

1. SUMIFS - the one formula every budget uses

SUMIFS sums a range of numbers where one or more conditions are met. It is the single most useful formula in personal finance.

=SUMIFS(sum_range, criteria_range_1, criteria_1, [criteria_range_2, criteria_2, ...])

“Total spent on Groceries in July 2026”:

=SUMIFS(D:D, B:B, "Groceries", A:A, ">="&DATE(2026,7,1), A:A, "<"&DATE(2026,8,1))

The & joins the comparison operator to the date so Sheets parses it correctly. Without it, the formula breaks silently and returns zero.

Most budget dashboards are a grid of SUMIFS calls - rows are categories, columns are months, each cell sums the same transaction log. That single pattern handles 80 percent of what people want from a budget spreadsheet, and survives copy-paste better than pivot tables.

2. SUMIF - the older sibling

SUMIF is the single-condition version. It predates SUMIFS and is still everywhere in older templates.

=SUMIF(criteria_range, criteria, sum_range)

Argument order is different from SUMIFS, which trips people up. In SUMIF, the criteria range comes first; in SUMIFS, the sum range comes first. =SUMIF(B:B, "Groceries", D:D) returns total spent on Groceries across all dates.

SUMIFS does everything SUMIF does and adds multi-condition support. There is little reason to start a new spreadsheet with SUMIF - the only reason to know it is reading templates other people built.

3. COUNTIFS - counting transactions

COUNTIFS counts rows where conditions are met. Useful for “how often did this happen” rather than “how much did this total”.

=COUNTIFS(B:B, "Dining", A:A, ">="&DATE(2026,7,1), A:A, "<"&DATE(2026,8,1))

That returns the number of dining transactions in July. Combined with SUMIFS / COUNTIFS, you get average transaction size by category. That is a sharper signal than total alone: a category might be high in dollars because of one annual bill or because of 40 small purchases.

For a household tracking eating out, count rising faster than dollars points to more frequent small meals rather than fewer large ones - a different problem than the headline total suggests.

4. ARRAYFORMULA - the scaling trick

ARRAYFORMULA applies a formula to a whole range instead of one cell. Google describes it as a function that “enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays” (Google Sheets reference).

Plain language: instead of dragging =A2*B2 down 500 rows, write the array form once at the top of the column.

=ARRAYFORMULA(A2:A500 * B2:B500)

For a transaction log, suppose column E should hold a year-month tag derived from column A. Without ARRAYFORMULA, every new row needs the formula dragged or copied. With it, one cell at the top covers all rows:

=ARRAYFORMULA(IF(A2:A="", "", TEXT(A2:A, "yyyy-mm")))

This is the single biggest reason Google Sheets can replace a small database for many personal finance tasks. The cost: one extra layer of mental overhead, plus the fact that not every function plays nicely inside ARRAYFORMULA. SUMIFS does not, for instance. That is a real limit, not a small one.

5. VLOOKUP and INDEX/MATCH - category from merchant

Recurring merchants belong to recurring categories. A lookup table maps merchant names to categories, and a formula in the transaction log fills in the category automatically.

VLOOKUP is older and simpler:

=VLOOKUP(C2, MerchantTable!A:B, 2, FALSE)

Where MerchantTable has merchant in column A and category in column B, and the FALSE forces an exact match. VLOOKUP only looks right - the return column must be to the right of the lookup column.

INDEX/MATCH is the more flexible pattern:

=INDEX(MerchantTable!B:B, MATCH(C2, MerchantTable!A:A, 0))

The return column can be anywhere, and it is faster on large sheets. XLOOKUP is the newer hybrid, but compatibility across Sheets and older Excel versions is still uneven, so INDEX/MATCH is the safer default for shareable templates.

For personal finance, the merchant-to-category lookup is the second-most-useful pattern after SUMIFS. Build the lookup table once, and most transactions categorize themselves.

6. QUERY - SQL inside a spreadsheet

QUERY lets you write SQL-style queries against a sheet range. It is the most powerful formula in Sheets and the one that takes longest to learn.

=QUERY(A:D, "SELECT B, SUM(D) WHERE A >= date '2026-07-01' AND A < date '2026-08-01' GROUP BY B ORDER BY SUM(D) DESC LABEL SUM(D) 'Total'", 1)

That returns a small table: category, total spent, sorted highest first, for July 2026. A pivot table can do the same thing, but QUERY lives in a cell, which means it updates as the data updates and is easier to embed in a dashboard.

QUERY is Sheets-only - it does not exist in Excel. If a template needs to be portable, SUMIFS plus a static category list does the same job with no platform lock-in.

For a personal dashboard, one QUERY block can replace 30 SUMIFS cells. The trade-off is debugging: when it returns #VALUE!, the error message is rarely specific. Build it one clause at a time and verify the output before adding the next clause.

7. FV - future value with monthly contributions

FV is the function behind every “what will my $500/month become” projection.

=FV(rate, nperiods, payment, [present_value], [type])

For $8,500 starting balance, $475/month contribution, 6 percent annual return, 30 years: =FV(0.06/12, 30*12, -475, -8500) returns about $528,300. The negatives represent money flowing out of your pocket (into the account); FV returns a positive future value when contributions are entered negative, which trips people up the first few times.

Compounding frequency matters: the example compounds monthly because the rate is divided by 12 and the periods multiplied by 12. The walkthrough in Compound Interest Calculator in Google Sheets (Step-by-Step) covers the real-vs-nominal return question that goes with this formula.

8. PMT - monthly payment for a loan

PMT calculates the payment required to pay off a loan over a fixed period. It is FV solved for the payment instead of the future value.

=PMT(rate, nperiods, present_value, [future_value], [type])

A $300,000 mortgage at 6.5 percent over 30 years: =PMT(0.065/12, 30*12, 300000) returns about -$1,896.20 per month. The negative sign means money flowing out; wrap in ABS() for display.

PMT is also useful in reverse - given a payment you can afford, what loan principal does that buy? Combine it with Goal Seek for a rough affordability check. Worth knowing the formula exists; not worth reaching for more than a few times a year for most households.

9. XIRR - portfolio return with irregular flows

This is the most underused formula on the list.

=XIRR(cashflow_amounts, cashflow_dates, [rate_guess])

For a portfolio with three contributions and an ending value:

=XIRR(B2:B5, A2:A5)

Returns about 7.0 percent annualized - the money-weighted return on the actual cash flows, accounting for both timing and amount.

The simple alternative - end value minus contributions, divided by starting balance - is wrong for any portfolio that received contributions during the year. It treats every dollar as if it had been invested for the full period, which inflates the apparent return on recent money.

A common follow-up question is time-weighted return (TWR), which strips out timing entirely. XIRR is the timing-included answer; TWR is the timing-stripped answer. How to Track Investment Returns in Google Sheets (TWR vs MWR) walks through both side by side.

10. NPV - net present value

NPV discounts future cash flows back to today at a given rate. Useful when comparing a stream of future income to a lump sum today.

=NPV(rate, value1, [value2, ...])

Five years of $9,400 cash flows at a 5 percent discount rate: =NPV(0.05, 9400, 9400, 9400, 9400, 9400) returns about $40,697. That is what those payments are worth today, given a 5 percent assumed alternative return.

Most household personal finance does not need NPV. The cases where it matters are pension lump-sum versus annuity offers, structured settlements, and small-business or rental decisions. For day-to-day budgeting, it sits on the shelf.

One quirk: NPV in Sheets assumes the first cash flow happens one period from today. If the first flow is today, add it outside the function: =value0 + NPV(rate, value1, value2, ...).

11. GOOGLEFINANCE - live stock prices and rates

GOOGLEFINANCE pulls live market data into a cell. Sheets-only; does not exist in Excel.

=GOOGLEFINANCE("AAPL", "price")

Returns the current Apple share price. The second argument controls what is pulled: "price", "priceopen", "high", "low", "volume", "marketcap". Currency exchange rates work too: =GOOGLEFINANCE("CURRENCY:EURUSD") returns the live EUR-to-USD rate.

Two limits: the data is delayed by up to 20 minutes, and it occasionally returns #N/A when the feed hiccups. Wrapping it with IFERROR prevents one bad pull from cascading through a sheet.

12. EOMONTH and DATEDIF - date math for budgets

EOMONTH returns the last day of the month, optionally offset by a number of months.

=EOMONTH(TODAY(), 0)

Returns the last day of the current month. EOMONTH(TODAY(), -1) + 1 returns the first day of the current month - useful as a “current month” filter in SUMIFS.

DATEDIF calculates the difference between two dates in years, months, or days: =DATEDIF(A2, TODAY(), "Y") returns the number of full years between A2 and today. Useful for age fields, account age, or “years until retirement” cells. The unit argument accepts "Y", "M", "D", "MD", "YM", "YD".

These two formulas, plus MONTH() and YEAR(), cover most of what date columns need.

13. SPARKLINE - mini-charts in a cell

SPARKLINE renders a tiny chart inside a single cell. Useful for trend lines next to category totals on a dashboard.

=SPARKLINE(D2:D13, {"charttype","line"; "color","#1f7a4d"})

Returns a green line chart showing 12 months of values. Other chart types include "bar", "column", and "winloss" (positive/negative bar pattern).

Sparklines work best on a 12-month grid - one cell per category, showing the year’s trend at a glance. They do not zoom or label points; for anything more detailed than “is this going up or down”, switch to a real chart.

A column of sparklines next to a column of totals is one of the highest-information-density layouts in Sheets, and one of the easiest to build.

14. IFERROR - handling missing data

Spreadsheets break in predictable ways: VLOOKUP returns #N/A when the lookup fails, GOOGLEFINANCE returns #N/A when the data feed lags, division returns #DIV/0! when the denominator is zero. IFERROR catches the error and returns a fallback instead.

=IFERROR(VLOOKUP(C2, MerchantTable!A:B, 2, FALSE), "Uncategorized")
=IFERROR(A2/B2, 0)

The fallback can be anything - a number, a blank, another formula. IFERROR rarely shows up on lists of “powerful” functions, but it is the difference between a working sheet and a broken one. The trade-off: it hides errors, including the ones you want to know about. For critical calculations, log the error to a side cell instead of swallowing it silently.

15. UNIQUE and SORT - building category lists

UNIQUE returns the distinct values in a range. SORT orders them.

=SORT(UNIQUE(B2:B))

From a transaction log’s category column, that returns a sorted, deduped list of every category that has appeared. Useful for building a category drop-down without typing the list by hand, and for catching typos - “Grocries” sitting one row above “Groceries” in the sorted output is hard to miss.

Combined with COUNTIFS, the same pattern surfaces categories that get used rarely:

The bottom of the list often holds the interesting information.

A worked example - five formulas, one mini-budget

A small monthly budget that uses five of the formulas above. The transaction log is on Sheet1, columns A-D. The budget summary is on Sheet2.

Step 1 - Category column with lookup. On Sheet1, column E auto-fills the category from the merchant:

E2: =IFERROR(VLOOKUP(C2, Categories!A:B, 2, FALSE), "Uncategorized")

(Drag down, or wrap in ARRAYFORMULA to fill the whole column from one cell.)

Step 2 - Unique category list. On Sheet2 column A, list the categories that actually appear:

A2: =SORT(UNIQUE(FILTER(Sheet1!E:E, Sheet1!E:E<>"")))

FILTER removes blanks before deduping. Without it, the unique list includes an empty row.

Step 3 - Monthly total per category. On Sheet2 column B, total each category for the current month:

B2: =SUMIFS(Sheet1!D:D, Sheet1!E:E, A2, Sheet1!A:A, ">="&EOMONTH(TODAY(),-1)+1, Sheet1!A:A, "<="&EOMONTH(TODAY(),0))

That filter window is “first day of current month through last day of current month”. EOMONTH does both endpoints.

Step 4 - Transaction count per category. Column C:

C2: =COUNTIFS(Sheet1!E:E, A2, Sheet1!A:A, ">="&EOMONTH(TODAY(),-1)+1, Sheet1!A:A, "<="&EOMONTH(TODAY(),0))

Step 5 - Sparkline trend per category. Build a hidden 12-month grid first (one SUMIFS per month for each category, using EOMONTH(TODAY(), -n) to step back through months), then point SPARKLINE at that row:

D2: =SPARKLINE(F2:Q2, {"charttype","line"})

Where F2:Q2 holds 12 monthly totals for the category in row 2. The intermediate grid is unglamorous, but it keeps the formula auditable - and it sidesteps QUERY’s quirks around mixed-year MONTH() groupings.

Five formulas - VLOOKUP, UNIQUE, SUMIFS, COUNTIFS, SPARKLINE (with EOMONTH supporting) - produce a one-page dashboard: category, monthly total, count, 12-month trend. Drop in a transaction log, and the dashboard fills itself.

The pattern scales. Add a second column of SUMIFS against a “Budget targets” sheet and you have a planned-vs-actual variance column.

Which formulas earn their place

Rough ranking, based on how often each appears in working personal finance sheets:

The bottom row is not “skip these”. It is “know they exist so you can look them up when the situation appears”. A pension buyout question lands once a decade; when it does, having NPV in the recognition layer saves an hour of research.

When the spreadsheet runs out

The 15 formulas above cover most of what personal finance asks of a spreadsheet. The cases where Sheets starts to creak:

• Transaction logs over ~10,000 rows, where ARRAYFORMULA calculations slow the sheet visibly.
• Cross-sheet aggregation across many separate workbooks via IMPORTRANGE, where authentication adds friction.
• Investment portfolios with multi-currency holdings and tax-lot accounting.
• Year-over-year planning where last year’s history and this year’s projection have to coexist.

At that point, a pre-built template with the formulas and structure already wired in saves the rebuilding effort - the same SUMIFS/XIRR/FV patterns, just embedded in a workbook designed for the case.

Templates that fit

• Monthly Budget Template - $19 - For the “I can see where money goes, but want to plan where it should go” question. Pre-built SUMIFS grid for category targets versus actuals, with IFERROR and EOMONTH already wired in.
• Financial Planning Spreadsheet - $29 - For the “I want one place that ties income, expenses, savings, and investments together” question. Uses FV for projections and XIRR for portfolio return, with the formulas exposed so you can edit them.
• Google Sheets Formulas - A reference library covering syntax for the functions above and several more, with copy-paste examples.

Related

• How to Create an Advanced Personal Finance Tracker in Google Sheets - The longer walkthrough that uses many of these formulas in one tracker.
• How to Track Investment Returns in Google Sheets (TWR vs MWR) - XIRR in context, with the time-weighted return alternative.
• Compound Interest Calculator in Google Sheets (Step-by-Step) - FV in context, with real vs nominal returns.