DEV Community

Financely Templates
Financely Templates

Posted on

Budget by Paycheck in Google Sheets (Which Bills Come Out of Which Check)

#googlesheets #productivity #spreadsheets #tutorial

If you're paid every two weeks, a monthly budget can feel wrong: rent is due on the 1st, but your check lands on the 9th. Budgeting by paycheck fixes that. Each check gets its own list of bills, so you always know what has to come out of the money you have right now. Here's how to build it in Google Sheets in about ten minutes.

Step 1: List your paydays

Make a tab called Paydays. In A2 put your next payday, then in A3:

=A2+14
Enter fullscreen mode Exit fullscreen mode

Drag it down about 26 rows for a full year. In B2 put your take-home pay for that check (use the real deposited amount, not gross pay).

Step 2: List your bills with due days

Make a tab called Bills:

A: Bill B: Amount C: Due day of month
Rent 950 1
Phone 45 12
Car insurance 120 18
Internet 60 24

Step 3: Find which paycheck covers each bill

The rule: a bill is paid from the last paycheck that arrives before its next due date. In D2, work out the next due date:

=IF(DATE(YEAR(TODAY()),MONTH(TODAY()),C2)>=TODAY(), DATE(YEAR(TODAY()),MONTH(TODAY()),C2), DATE(YEAR(TODAY()),MONTH(TODAY())+1,C2))
Enter fullscreen mode Exit fullscreen mode

Then in E2, find the paycheck that covers it:

=IFERROR(MAXIFS(Paydays!A:A, Paydays!A:A, "<="&D2), "Before first payday")
Enter fullscreen mode Exit fullscreen mode

Format both D and E as dates.

Step 4: Total the bills for each check

Back on Paydays, in C2, add up every bill assigned to that check:

=SUMIFS(Bills!B:B, Bills!E:E, A2)
Enter fullscreen mode Exit fullscreen mode

And in D2, what's left after bills:

=B2-C2
Enter fullscreen mode Exit fullscreen mode

Add conditional formatting on column D (Format > Conditional formatting, "Less than 0", red fill). A red cell means that check can't cover its bills, so you know weeks ahead to move a due date or set money aside from the check before.

Step 5: Split what's left

What's left in D2 is what you can give a job. Add columns for savings, groceries and fun:

=ROUND(D2*0.2, 2)
Enter fullscreen mode Exit fullscreen mode

for 20% savings, and so on. Keep the percentages in a header row so you can tweak them without editing every formula.

Step 6: Spot the three-paycheck months

Twice a year, biweekly pay gives you a third check in one month. Flag it in E2:

=IF(COUNTIFS(A:A, ">="&EOMONTH(A2,-1)+1, A:A, "<="&EOMONTH(A2,0))=3, "3rd check month", "")
Enter fullscreen mode Exit fullscreen mode

Those checks usually have almost no bills assigned, which makes them perfect for an emergency fund or an extra debt payment.

Done

That's a paycheck budget: every bill tied to the check that pays it, a red warning when one check is overloaded, and an early heads-up on your bonus checks.

If you'd rather start from a finished sheet, I make templates as Financely Templates (disclosure: these are my products):

Top comments (0)