Spreadsheets

50/30/20 rule in Excel: formulas to split income and check spend

Split take-home pay into 50/30/20 targets in Excel, tag spending as needs, wants or savings and use SUMIFS to see how your actual month compares.

Editorial illustration for 50/30/20 rule in Excel: formulas to split income and check spend

The 50/30/20 rule is a rough split of take-home pay: 50 percent for needs, 30 for wants and 20 for savings. In Excel, multiply your take-home pay by each percentage for the targets, tag every expense as Needs, Wants or Savings, and use SUMIFS to compare your actual month with each target. It is a rule of thumb, not a requirement, and you can change the percentages.

Step one: the targets

In B1 type your monthly take-home pay, meaning what reaches your account after deductions. Say it is ₹70,000. Make a small table from A3:

BucketShareTarget
Needs50%₹35,000
Wants30%₹21,000
Savings20%₹14,000

Keep the percentages in column B as real percentages (0.5, 0.3, 0.2 formatted as percent), and the target in C3 as:

=$B$1*B3

Copy it down. Check the targets: 35,000 + 21,000 + 14,000 = 70,000. A cell that adds the shares, =SUM(B3:B5), should show 100 percent; if you tweak the split, that tells you at once if it no longer adds up.

Step two: tag every expense

Add a Bucket column to your log, D, next to date (A), category (B) and amount (C). Typing a bucket on every row is tedious, so map categories once. On a Map sheet, list categories in column A and the bucket in column B: Rent is Needs, Groceries is Needs, Dining out is Wants, Savings transfer is Savings, and so on. Then in D2 of the log:

=IFERROR(VLOOKUP(B2,Map!$A$2:$B$40,2,FALSE),"Untagged")

VLOOKUP finds the category in the Map list and returns its bucket; FALSE demands an exact match. A category you forgot to map shows Untagged, so you notice. Whether a category is a need or a want is your judgment: groceries are a need, but a heavy takeaway habit may not be. Decide once, write it on the Map sheet, and apply it consistently.

Step three: actual versus target

With the first day of the month in B7, the actual spend for each bucket, in D3, is:

=SUMIFS(Log!$C$2:$C$500,Log!$D$2:$D$500,A3,Log!$A$2:$A$500,">="&$B$7,Log!$A$2:$A$500,"<"&EDATE($B$7,1))

Record transfers to savings as entries tagged Savings, otherwise that bucket will always show zero. Then add share of pay and gap columns: =D3/$B$1 and =D3-C3.

BucketTargetActualShare of payGap
Needs₹35,000₹38,50055%+₹3,500
Wants₹21,000₹17,50025%-₹3,500
Savings₹14,000₹14,00020%₹0

These actuals are invented. They add to 38,500 + 17,500 + 14,000 = ₹70,000, and 38,500 divided by 70,000 is 55 percent, so needs are above the 50 percent target and wants are below it by the same ₹3,500.

What should I do with the result?

Look for patterns over several months rather than one. A single month with an annual premium will swing needs. If needs are consistently above the target, the real question is which fixed costs are negotiable; if wants run high, see which categories drive it. Where the savings share goes is your decision, and for investment choices ask a qualified adviser.

What about months with annual or festival costs?

A school fee, an insurance premium or Diwali shopping can push one month far from the targets without meaning anything about your habits. Either log them in the month they happen and accept the spike, or set aside a twelfth of the yearly cost each month under Savings and let the payment come out of that fund. Choose one method and use it every year, so your month-to-month comparisons stay meaningful.

Checklist

  • Take-home pay is after deductions, not gross salary.
  • Every category is mapped; Untagged shows zero.
  • Savings transfers are logged as Savings.
  • The three shares add up to 100 percent.

If tagging rows is the chore, SpendixAI lets you create custom categories and sub-categories and review its suggestions on imported statements, which can make the Map sheet easier to keep.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports