What is the goal of your workflow? What problem does it solve, or how does it help you?
Though I see my net paycheck deposit as a transaction that Tiller pulls in from my credit union, I wanted the gross amount to show up in my balances, and to have all the deductions categorized as well (see how much I’m paying for insurance, taxes, etc.). I started by copy/pasting one months transactions to the next, but wanted something a little nicer. There’s no way for me to automate that, but I found a way to simplify it so for each paycheck I could include these transactions.
How did you come up with the idea for your workflow?
I wanted a way to simplify the process of entering the paycheck information every time I got a check. I had previously done something similar, which I posted about here. This new method expands on that original idea.
Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
I created a ‘Paychecks’ sheet, which includes data for past and future paychecks. I use this currently for two purposes:
- Create a template for transactions that I copy to the Transactions sheet after a paycheck is received so all paycheck deductions appear in budgeting categories
- Categories sheet uses a VLOOKUP to get future amounts for categories related to paychecks (I’ll do another write-up for how I handle this part)
For the following, lets assume I work for ACME Inc. My paychecks used to be monthly, and were very consistent. They recently changed to bi-weekly, so there are a few times a year when I get three checks in a month instead of two, and some of the deductions are different for those checks. Thankfully, ACME’s HR gives a list of which deductions happen on which pay periods, so it was possible for me to project out for the next year what the amounts will be for each paycheck.
The paycheck list I created includes the following columns that should apply to anyone working in the US:
- Pay Period - Labels like ‘JAN’ and ‘FEB’ for monthly, or ‘JAN A’, ‘JAN B’ and ‘JAN C’ for bi-weekly that describe which paycheck this is
- Period Start - When the pay period this check is for started
- Period End - When the pay period this check is for ended
- Pay Date - The date the check was released/auto deposited
- Pay Net - The total amount you earned
- Pay Gross - The amount you were paid after deductions
- Fed OASD/EE - Social Security deduction
- Fed MED/EE - Medicare deduction
- Fed Withholding - Federal Tax deduction
- State Withholding - State Tax deduction
I then also have columns for other deductions that will vary depending on your situation, but usually include deductions for health insurance and retirement, but could also include things like paid parking and other options your employer may offer to auto-deduct from your checks. For a while I even had to include a ‘Furlough’ deduction during ACME’s hard times (thankfully that has ended).
With the columns setup (I started mine in column ‘T’ to allow room for the staged transactions I’m going to build in the next step), I then entered all the data for each paychecks that I’ve already received, and for future paychecks based on the schedule ACME provided. If you don’t have a future schedule, you might just copy the last paycheck to all future checks, assuming your payments are fairly consistent. Once finished, whenever I get a paycheck ‘stub’, I check it against the list, and make changes where necessary. Here’s how part of my list looks after adding some headings:
To stage the paycheck transactions, you need to replicate the layout of your Transactions sheet in the ‘Paychecks’ sheet you should create. You may not be filling in every column though. For instance, in the Transactions sheet columns like ‘Group’ automatically populate based on an ARRAYFORMULA, manually filling anything into this column would break it, so it needs to remain blank in our template. Other columns simply don’t apply, like one I have called ‘Statement’, which is used with the Statements template. Because this can vary so much for each user, I’m not providing a downloadable template, but will walk you through how to set it up:
- In my case, I start with column B, which is ‘Date’, and I use the following formula:
=VLOOKUP(today(),$W$36:$W,1,True)+1which looks in the ‘Pay Date’ column of the list for the most recent paycheck based on today’s date. - Column C is ‘Description’, so I just fill in ‘ACME Paycheck - Gross’ or whatever you prefer.
- Column D is ‘Category’, so I include ‘Work-Paycheck’ which is one of my categories, fill in the category you want your Net paycheck amount put into.
- Column E is ‘Amount’, so I use the formula
=VLOOKUP(today(),$W$36:$AL,2,True)to grab the ‘Pay Net’ from the latest paycheck row. Note the range it looks in starts with the ‘Pay Date’ column and includes all columns over to your last deduction, which in my case is column ‘AL’. The ‘2’ in the formula tells it to grab the data from the second column of the range. - I then skip over columns F & G, which on my sheet don’t relate to this type of transaction
- Column H is ‘Account’, which I include ‘ACME Paycheck’. You don’t necessarily need to fill anything in here, but this easily allows me to filter/sort paychecks, so it’s worth including. You don’t have to have an actual account in your accounts sheet for this to work, and it won’t appear in your ‘Balances’ sheet.
- I then skip over columns I, J and K, which on my sheet include ‘Group’, which I don’t want broken, ‘Statement’ which is unrelated, and ‘Account #’ which I don’t need.
- Column L is ‘Institution’, which I don’t really need, but I fill in ‘ACME’
- I then skip over columns M through Q, which are either automated, or unneeded.
- Column R is ‘Full Description’, which isn’t necessary, but I repeat what I included in the Description column.
To make things fit on the screen a little easier, I shrink each ‘blank’ column down. You can’t ‘Hide’ them, or when you copy/paste your data, things aren’t going to go where they need to (copy/paste doesn’t know there’s columns that should be skipped over). To resize, just click on the triangle that appears when you hover over the column header, choose ‘Resize Column’, and then enter a size. I set mine to 5 pixels, so I can still see there’s a blank column there, but it doesn’t take up much space.
For the second row, I like to think of ACME as it was an account, and the payment I receive is like a transfer from that account to my bank account. For that reason, I create a transaction for ‘ACME Paycheck - Net’. The formula for Amount would change to =-(VLOOKUP(today(),$W$36:$AL,3,True)). Notice the second to last value changed from ‘2’ to ‘3’, referring to ‘the third column of the referenced list’. Also, notice we made the number negative, since this is like a ‘transfer out’ of the account, which will get balanced by the positive ‘transfer in’ to your checking account. This number will increment for each following row. I would then set the category to a transfer category so this amount isn’t included in budgets. This is the major change in thinking when including all your deductions in your transactions. Gross pay is now what should be included in budgets, net pay is simply what’s left over after all the other deduction transactions are budgeted. You’ll also want to set the actual paycheck deposit transaction to a transfer category for the same reason.
The third row, ‘Paycheck - Fed OASDI/EE’ is similar with one difference. When you put the formula for amount, if you used positive numbers in your list for everything (I did), you’ll need to make it negative to work as a transaction, so the formula would be =-(VLOOKUP(today(),$W$36:$AL,4,True)).
Repeat each row this way, setting an appropriate category, and incrementing the number in the VLOOKUP formula. You may want/need to create new categories to track the deductions if you weren’t previously. I find most of my deductions relate to ‘Health’, ‘Retirement’ and of course ‘Taxes’, so I created separate categories for each deduction, and then use groups to gather all the similar ones together in my budget.
Here’s what mine looks like with background color removed so you can see the column widths:
Now, when a new paycheck arrives, you can insert the required number of blank rows in your Transactions sheet (I usually do it below the bank deposit transaction), then copy the staged transactions you just created, and in the Transactions sheet, click into the first blank row you created, and to a ‘Paste Special/Values only’ so it doesn’t try to put formulas into the cells, which wouldn’t work properly.
Anything else you’d like people to know?
I hope others find this useful and contribute ideas on how to make it even better!
Is it ok for others to copy, use, and modify your workflow?
Yes, I’m hoping others will use it and find ways to improve it!
If you said yes above, please make a copy of your workflow and share the copy’s URL:
All details provided above, no template needed.
