50/30/20 Budget in Excel: The Formulas, and What to Do When Needs Are 64%

The 50/30/20 rule splits your after-tax income into three buckets. Half goes to needs, 30% goes to wants and 20% goes to savings and extra debt payoff. It is popular because it is simple. It is also easy to get wrong in a spreadsheet, because three small choices change the answer by several points.

This guide builds the rule in Excel from one worked example. It covers the formulas, the income figure to divide by, where debt payments go and what to do when your needs come in well over 50%, which for most renters in an expensive city they will.

The worked example

A single renter takes home 5,200 a month after tax and after a 420 pre-tax 401(k) contribution. Here is one month of spending.

| Category | Amount | Bucket | |---|---|---| | Rent | 1,650 | Need | | Utilities | 180 | Need | | Groceries | 520 | Need | | Car payment | 340 | Need | | Car insurance | 140 | Need | | Gas | 160 | Need | | Phone | 60 | Need | | Credit card minimum | 75 | Need | | Student loan payment | 220 | Need | | Dining out | 310 | Want | | Subscriptions | 65 | Want | | Shopping | 240 | Want | | Entertainment | 120 | Want | | Travel fund | 150 | Want | | Roth IRA | 500 | Save | | Emergency fund | 300 | Save | | Extra credit card payment | 170 | Save |

Every dollar of the 5,200 has a job, so the three buckets add back to take-home pay.

Step 1: give every category a bucket

Make a small table on its own sheet with two columns, Category and Bucket. Format it as an Excel table (Ctrl+T) and name it Categories. The bucket column holds one of three words: Need, Want or Save.

This table is the whole rule. Every judgment call about whether something is a need lives in one place, so you change it once and every month updates.

Step 2: tag each transaction

Your transaction log has Date, Category and Amount. Add a fourth column that looks up the bucket:

` =XLOOKUP([@Category], Categories[Category], Categories[Bucket], "Unassigned") `

If you are on an older Excel without XLOOKUP, use:

` =IFERROR(VLOOKUP([@Category], Categories, 2, FALSE), "Unassigned") `

The "Unassigned" fallback matters. A typo in a category name would otherwise drop that spending out of every bucket, and the budget would look better than it is. Add one check cell that counts them:

` =COUNTIF(Tx[Bucket], "Unassigned") `

Anything above zero means a category needs fixing before you trust the totals.

Step 3: total each bucket

With income in B1, the three totals are one SUMIFS each:

` Needs: =SUMIFS(Tx[Amount], Tx[Bucket], "Need") Wants: =SUMIFS(Tx[Amount], Tx[Bucket], "Want") Savings: =SUMIFS(Tx[Amount], Tx[Bucket], "Save") `

To limit it to one month, add a date range:

` =SUMIFS(Tx[Amount], Tx[Bucket], "Need", Tx[Date], ">="&D1, Tx[Date], "<"&EDATE(D1, 1)) `

where D1 holds the first day of the month. Then the shares and targets:

` Share: =Needs / $B$1 Target: =$B$1 * 0.5 (0.3 for wants, 0.2 for savings) Gap: =Needs - Target `

Last, add a check that the three totals add back to income. If they do not, some income is unassigned or some spending is missing.

The result

| Bucket | Actual | Share | Target | Target amount | |---|---|---|---|---| | Needs | 3,345 | 64.33% | 50% | 2,600 | | Wants | 885 | 17.02% | 30% | 1,560 | | Savings | 970 | 18.65% | 20% | 1,040 |

Needs are 745 over target. Wants are well under. Savings are 70 short of 20%. Rent alone is 31.73% of take-home pay, which uses most of the needs bucket before anything else is counted.

Choice 1: which income do you divide by

The rule, as Elizabeth Warren and Amelia Warren Tyagi set it out, uses after-tax income. That is income after taxes, not after every deduction. The 420 that goes to the 401(k) before the paycheck arrives is still savings. If you divide by take-home pay alone, it disappears from both sides.

Add it back to income and to savings:

| Bucket | Actual | Share of 5,620 | |---|---|---| | Needs | 3,345 | 59.52% | | Wants | 885 | 15.75% | | Savings | 1,390 | 24.73% |

Same person, same month. The savings rate moves from 18.65% to 24.73% and clears the 20% target. Needs fall from 64% to under 60%.

The same logic works on the needs side. Anything else your employer takes out before pay arrives is still spending, so it belongs in a bucket. Say a 180 pre-tax health insurance premium also comes out of each paycheck. Income becomes 5,800 and needs become 3,525, which is 60.78%. Savings is 23.97% and wants are 15.26%. If you leave the premium out, needs look 1.26 points lower than they really are.

In Excel, keep pre-tax savings and pre-tax needs as their own input cells. Add both to the income in B1, the 401(k) to the Savings total and the premium to the Needs total. Pick one version and keep it, because comparing a take-home month with an adjusted month makes your progress look like noise.

Choice 2: where debt payments go

Minimum payments are needs. You owe them whether you like it or not. Anything above the minimum is a choice to get ahead, so it belongs in savings.

Spreadsheets that put the whole credit card payment in one bucket get this wrong both ways. In the example, if the extra 170 were filed as a want, wants would rise to 20.29% and savings would fall to 15.38%. The person would look like a worse saver for paying down a card faster.

Set up two categories, "Credit card minimum" (Need) and "Extra debt payment" (Save), and split the payment when you log it.

Choice 3: monthly or annual

Some needs arrive once or twice a year. Say car registration is 360, paid in March. In March, needs jump to 3,705, which is 71.25% of take-home pay. A monthly view would flag March as a disaster when nothing changed.

There are two fixes. You can put 30 a month into a "Car registration" sinking fund and file that as a need. Or you can judge the rule on a trailing 12-month total instead of a single month. The sinking fund is cleaner, because it also makes sure the money is there in March. Give the March payment out of the fund a category whose bucket is Transfer, which none of the three SUMIFS picks up, or you will count the 360 twice.

When needs are 64%, defend the 20

The rule was written as a guide, not a law. In a high-rent city many people cannot get needs to 50% without moving. The good news is that the three numbers are not equally important.

The 20 is the one that builds wealth. The 50 and the 30 are just a way to get there. So treat 20% savings as the floor and let needs and wants trade places.

In the example the take-home version needs savings of 1,040. Needs are fixed at 3,345 this year. So wants can be at most:

` =B1 - Needs - B1*0.2 `

That is 5,200 minus 3,345 minus 1,040, or 815. Current wants are 885. Cutting 70 a month from dining out hits the target. That is a much smaller problem than "needs are 745 over," and it is the one you can actually solve this month.

The needs number is still useful over a longer horizon. Rent and the car payment are 1,990 of the 3,345. Those are decided at lease renewal and at the next car purchase, not at the grocery store. Put a note in your sheet with the renewal dates, because that is when the 64% can move.

A checklist for your sheet

A Categories table with one bucket per category.
A bucket column on every transaction with an "Unassigned" fallback and a count.
One SUMIFS per bucket, with a monthly date filter.
Pre-tax deductions added to income and to their bucket, or left out of both. Never one side only.
Minimum payments as needs and extra payments as savings.
Sinking funds for annual bills.
A wants ceiling formula that holds savings at 20%.

A ready-made version

If you would rather start from a working file, the Annual Budget and Net Worth Tracker has the transaction log, the category dropdown and Budget versus Actual by month already built. It groups categories into eight groups (Housing, Transport, Food and so on) rather than three buckets, so you would add the Need, Want and Save mapping from Step 1 yourself. It also tracks your savings rate against a target on the dashboard.

Written with AI assistance and checked by recalculation. For education and planning only, not financial advice.