Paycheck Register - Google Sheets

I thought it might be interesting to have an easy way to compare my paycheck against the spending I do in the pay period following the reception of that check. This looks a lot like the Account Register template, but instead of focusing on transactions from an account, it focuses on transactions within a ‘pay period’. It will list the paycheck from a selected date, and then include all expense/income transactions during the period starting when you get your check to the time the next check is received. There is an option to exclude other income transactions if you just want to compare your spending against the selected paycheck.

Installation

Copy the “Paycheck Register” sheet from the demo template below to your Tiller Foundation template.

Setup

Expand the Helper Data section by clicking the ‘+’ above column H.

  • How to Find Paycheck: Choose “Account” if you break your paycheck out into multiple transactions for deductions, like those created by the Paycheck Deduction Transactions Generator template. Otherwise, choose “Category”.
  • Paycheck Category: If you chose “Category” above, then pick which category you associate with your paycheck. This will only work properly if you have a dedicated category for each paycheck.
  • Paycheck Account: If you chose “Account” above, enter something like “ACME Paycheck” in the Account column on the Transaction sheet for all paycheck related transactions, including deductions, and enter the same string here. You can enter this same string in the Paycheck Deduction Generator template (cell AO6), or you can use AutoCAT to automatically add it to future transactions.
  • Paycheck Frequency: What is the frequency of your paychecks? Choose from Weekly, BiWeekly, SemiMonthly or Monthly. Note “SemiMonthly” means twice a month (I avoided "BiMonthly which is also used to refer to every other month).
  • 1st Pay Date of the Year: On what date were you supposed to get your first paycheck of the year? This defines the day of the week, which is used in “Weekly” and “BiWeekly”, the date of the month for the first paycheck for “SemiMonthly”, and the date of the month for “Monthly”.
  • 2nd Pay Date of the Year: Only used for “SemiMonthly” frequency. When were you supposed to get your second paycheck of the year? This defines the date of the month for second “SemiMonthly” paycheck.
  • Paycheck Date Padding: If your paycheck deposit isn’t always dated on the expected pay date, specify how many days before or after the normal pay date we should look for it (keep this number small to avoid accidentally pulling in other paychecks) by adding numbers to the “Days Back” or “Days Forward” cells.

With all that set, you can close the Helper Data section.

Usage

In cell G3, choose whether to include other “Income” type transactions. Choosing ‘No’ will filter all transactions of type ‘Income’ except the defined Paycheck transactions.

In cell G5, select the “Paycheck Date” you’d like to examine. You’ll see displayed a list of transactions related with the Paycheck itself (deductions if defined, otherwise just the deposit transaction) followed by all the expense (and optionally income) transactions that occurred after the paycheck was received but before the next paycheck was received. Note: The template cannot account for time of day, so it assumes you received the paycheck at midnight on the selected date, and that all transactions on that date come after it.

The way I use it

I use the Paycheck Deduction Transaction Generator template (not required for use of this template) to generate all the deduction transactions for each paycheck. For each paycheck related transaction, I set the “Account” column to be “ACME Paycheck”, which allows me to easily find all the ACME related paycheck transactions (you can do this by hand and even have AutoCAT add them for future transactions). This becomes important in the Paycheck Register template because occasionally a paycheck may show up a day early or a day late. If it shows up early, it and its deductions wouldn’t be found during a selected pay period. I’ve worked around this problem by allowing the addition of ‘padding days’ to search for paychecks (but not non-paycheck related transactions), and I’ve associated all the deductions with the payment by using the “Account” field. If you don’t do deduction transactions, and just have one deposit transactions, you can use “Category” instead, and simply choose a category that include only your paychecks from that one source.

Note

  • This template relies on predictable pay periods, so if you get paid at random intervals this template isn’t going to work for you.
  • Once the bugs are worked out and features set, I’ll create an Excel version.
  • I gave thought to accommodating multiple paycheck sources (eg. His check and Her check), but it doesn’t seem it would make sense without some way of assigning transactions to specific pay sources, which is out of scope for this template.

Looks interesting.
For the payperiod, if I select Jan, I will see from Jan and onwards. What is the utility of looking back at a payperiod, or is this mostly for current period budgeting? (Right now the query uses I21, but perhaps it should use M15:M where the Start Date is G5.)

If I get paid on the 6th, but I backdate 4 days, I’ll see the paycheck transactions, but not transactions done from the -4 to the 0 day.

My thinking was to start with a paycheck, and then see what my balance of that check is as I spent it. As you suggest, I find it most interesting to view the most recent check, but it was easy to add the ability to look at past checks so I figured I might as well add the feature. You’re correct about the date padding, that’s expected behavior as it’s only intended to find a paycheck that didn’t arrive exactly on the expected date, not to alter the paycheck period.

1 Like

OK, understood. Although the Sun-Friday paycheck move would be a regular occurrence, but I realize it’s not a major issue to resolve, just something to keep note of.

Another week… another great share by @jpfieber!

Paycheck Register is an innovative and intuitive workflow. I really like how it it connects the dots of prefunding your expenses in a very tangible way. Many conceptually-similar workflows adopt a “use last month’s income to pay for this month’s expenses” approach that can feel (to me) forced and clunky.

Your approach of finding the paycheck in the Transactions sheet and then using that amount as a starting balance for a checking-register-like one-month tally, with funds running down over the course of the month, is intuitive and tangible.

Probably because I initially skipped over the instructions, I had a few false starts during setup but I eventually got it working. One thing that could help run the concept to ground is cutting off the register at the next paycheck— it seemed to run out to the end of the next month for me.

The Tiller Builder Rewards Program is excited to award you $250 for this very-cool new community template.

:trophy:

P.S. Sorry for the delay in reviewing your new share, @jpfieber.

2 Likes