DEV Community

Black
Black

Posted on

How to check the 50/30/20 rule in Excel or Google Sheets (with formulas)

#excel #spreadsheets #productivity #tutorial

The 50/30/20 rule says 50% of take-home pay goes to needs, 30% to wants and 20% to savings. Checking it by hand each month is tedious. In a spreadsheet it takes four formulas, and it works the same in Excel and Google Sheets.

If you just want the answer for your own numbers, I made a free 50/30/20 budget check that runs in the browser. Nothing is uploaded. Below is how to build the same check yourself.

1. Give every category a group

Make a small table on a sheet called Setup:

Category Group
Rent Needs
Groceries Needs
Car and gas Needs
Eating out Wants
Subscriptions Wants
Emergency fund Savings
Extra debt payment Savings

Name the range Cats (A2:A40) and Groups (B2:B40). Keep the group list to exactly Needs, Wants, Savings, and add a data validation dropdown so nobody types need.

2. Pull the group onto each transaction

On the Transactions sheet you have Date (A), Description (B), Amount (C) and Category (D). Add a Group column (E):

=IFERROR(INDEX(Groups, MATCH(D2, Cats, 0)), "")
Enter fullscreen mode Exit fullscreen mode

XLOOKUP is shorter, but INDEX/MATCH works in every Excel version and in Google Sheets.

3. Total each group for one month

Put the month you want in H1 as a real date (the 1st of the month). Then:

=SUMIFS(C:C, E:E, "Needs", A:A, ">="&H1, A:A, "<"&EDATE(H1,1))
Enter fullscreen mode Exit fullscreen mode

Copy it for Wants and Savings. Using EDATE for the upper bound avoids the end-of-month mistakes that DATE(year, month, 31) causes in short months.

If your bank export shows spending as negative numbers, wrap the amount in ABS. SUMIFS cannot do that, so use SUMPRODUCT:

=SUMPRODUCT(ABS(C2:C2000) * (E2:E2000="Needs") * (A2:A2000>=H1) * (A2:A2000<EDATE(H1,1)))
Enter fullscreen mode Exit fullscreen mode

4. Count unspent money as savings

This step is the one most templates skip. If you earned $4,200 and your tracked spending adds up to $3,900, the other $300 is still in your account. Leave it out and your savings rate looks worse than it is. Count it, and remember to move it on payday:

Unspent:        =MAX(0, Income - Needs - Wants - Savings)
Savings total:  =Savings + Unspent
Enter fullscreen mode Exit fullscreen mode

5. Compare to the rule

With income in H2:

Needs %:   =Needs / H2
Gap:       =Needs - H2*0.5
Enter fullscreen mode Exit fullscreen mode

Do the same for wants (0.3) and savings (0.2). Add conditional formatting so the gap turns red when needs or wants go over target, or when savings fall under it. Watch the direction: for savings, under target is the bad case.

6. Say where the money comes from

Showing a red number is not enough. The useful output is one sentence: "Savings are $455 short; wants are $435 over, mostly eating out." Add a third column CatTotals (C2:C40) to the Setup table with each category's total for the month (the same SUMIFS, matched on category instead of group). The biggest want is then:

=INDEX(Cats, MATCH(MAXIFS(CatTotals, Groups, "Wants"), CatTotals, 0))
Enter fullscreen mode Exit fullscreen mode

MAXIFS needs Excel 2019 or newer, or Google Sheets.

Notes on the rule itself

  • Use take-home pay (after tax), not salary.
  • Minimum debt payments are needs. Anything above the minimum is savings.
  • In high-rent cities 60/20/20 is common and still healthy, as long as the savings line holds. The rule is a starting point, not a test you pass or fail.

If you would rather not build it, my Budget Dashboard 2027 (Excel and Google Sheets) sorts a pasted bank export into needs, wants, savings and debt and runs this check for any month you pick. It also shows budget vs actual and charts. The browser version is free.

Top comments (0)