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.
Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
Here’s the setup and the process I use: The Setup
Paychecks Sheet - Create a new sheet called “Paychecks” with the first four columns holding the “Description”, “Category”, “Amount” and “Account”.
Description Column - Using past paycheck stubs, list all the deductions in the Description column, with any that are periodic at the end of the list so you can more easily not select them to copy to the Transactions sheet if they are blank.
Category Column - Add a category for each deduction, using “Data Validation” to include the same drop down menu that’s used in the Transactions sheet. Note that I have AutoCat set my actual paycheck bank deposit to “Transfer”, and then set the “Net” amount in the deductions so they zero out. This leaves the actual Net amount as my income.
Paycheck Table - Since the Amount column can very each month (I’m currently paid monthly), I found it useful to create a table to enter the paycheck amounts, and the Amount column is automatically populated (and changed to a negative so it is seen as an expense in the transactions). This allows you to keep track of all your paychecks, provides a sum of the totals you’ve entered that can be compared against the Gross total to be sure nothing was missed, and always shows the most recent checks totals in the Amount column, so you can easily copy all four columns together at once.
Amount Column - The Amount column simply copies all the numbers from the right-most column, making all the deductions negative while leaving the gross amount positive.
Account Column - Though not necessary, including an Account name for these transactions can be useful for filtering, so prepopulating them here makes it easy to copy/paste along with the other data.
The Process
After receiving a paycheck, copy the previous paychecks column and paste it in the next column to the right. Change the date, and then modify any of the deductions to properly reflect the current paycheck (enter them all as positive, the Amount column will properly add negatives where necessary).
Once entered, confirm that the Gross amount is the same as that listed on the paycheck to be sure nothing was missed (If there’s a new deduction it may need to be added to the list).
Copy the range that includes the first five columns of all the line items that aren’t zero (if there are items with a zero total that aren’t at the end you’ll just need to delete that row after pasting) and note the number of rows that will be needed to hold the data
Go to the Transactions sheet and insert the number of necessary rows to hold the paycheck transactions
Paste the data into the new rows starting in column A. **Note you need to “Paste Special/Paste Values Only” so the amounts get pasted in, not the formulas that grabbed the amounts.
The date will be inserted into column A based on the date you entered in the Paycheck Table. Since I’m paid monthly, I like to put each Paycheck on the first of the month, even though the actual deposit sometimes may happen a day or two before or after (which can screw up budgeting when two checks show up in one month, and then none in another).
The Group column should automatically populate based on the Category.
If you want other columns filled in, like “Full Description”, you can manually enter them, or you can expand your Paycheck sheet to include these extra columns. You should avoid entering anything into the ‘Group’ or ‘Type’ columns though or you’ll break the formulas that auto-fill these values for all the other rows.
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:
Here’s a spreadsheet with a sample of the Paycheck sheet using dummy data, and a Categories sheet so the Categories column works properly:
Welcome to the community, and congratulations on slaying the paycheck dragon! Brilliant use of sheets and a great record-keeping-rich solution to an important process.
I note this is a similar approach Quicken uses to capture a paycheck’s elements in one step and apply them to a ledger. (Matching your OCD, I used to track these, too, and then…eh…gave it up. Miss it though!)
One question I have is how do you handle the downloaded transaction from your credit union that carries the net deposit of your paycheck? Do you simply delete it, opting for the transfer-flagged net transaction in your copied set? Wondering if that is an unnecessary duplication? (You could leave that line out, since the net amount is already downloaded from your credit union and flagged as a transfer.)
Finally, in your roster of categories, I really appreciate the one called, “Fraud” and admire that, well, if one is going to pursue it, why not track and measure its impact on others along the way!
Thanks for the kind words! I think of my gross pay as my “Income”, and net amount as a deduction/transfer from my “Work” account to my bank account, so I have AutoCat assign the net pay bank deposit to the “Transfer” category. By adding a matching net transaction as a deduction (which is also a transfer), they zero each other out, and behave the same as a transfer from your checking account to your credit card. This allows my Gross income to show up in budgets, etc, instead of the net amount.
Here’s an example with a few numbers hidden:
Notice the two ‘Net’ entries, each categorized as ‘Transfer’, one positive (the bank deposit), the other negative (the ‘paycheck deduction’).
Fraud was one I had to add after someone went on a DoorDash spree with my credit-card number. VISA refunded me the money, but I needed a way to call out for my budgets that I didn’t really spend that much on food (they had quite a feast).
I just updated my paycheck info for this month and realized I left out an important step. When you paste the paycheck into into the Transactions sheet, you need to do a “Paste Special” and choose the option “Paste Values Only”. If you don’t, the formulas that were used to grab the latest months amounts from the table will be pasted in instead, and won’t work properly. I’ve updated the instruction above to include this.
Hey @jpfieber ! Thank you for this workflow. I’m new to Tiller and this has been something I’ve been trying to figure out. I’ve got a question, and I think I have an additional instruction that will help people using the monthly and/or yearly budget sheets.
Question: Is your “paycheck” account just a manual account that you use as a placeholder? Do you track any balances in that account?
When I was using this to put in my first paycheck of the year today, I looked at my budget sheet and the transactions weren’t reflected. Then I scrolled over and realized that I needed to add the appropriate date to the Month column (since I use the Monthly Budget Sheet). Once I did that, it worked!
I don’t have an actual account for “Paycheck” (I suppose maybe that’s possible to do, I just haven’t realized a need for it), I just think of it “like an account”.
Funny you should mention the date thing. Just today I was thinking I might as well automate adding the date as well so I don’t have to remember to do it manually next month. I just updated the sample sheet to include a column A with the data pulled from the top row of the right most column of the paycheck table. I’ll update the instructions above now to reflect the change. Thanks for bringing it up!
Just a clarification, someone please correct me if I’m wrong. For people using the Monthly or Yearly Budget tabs, the date for the 1st of the in which the paycheck occurs needs to be entered into the “month” column (Column J for me) of the transaction sheet in order to transfer correctly to the budget sheets. For someone paid on the first of the month, the transaction date (Column B for me) and “month” date would be the same. If you are paid multiple times in a month, or not on the 1st, you still need to enter the 1st of the month in the “month” column for the amounts to transfer to the budget sheets in the current month.
I’d be curious if this is the case as well. I haven’t really gotten into budgeting too much yet, so I haven’t noticed if anything is wrong with my methodology yet. Plus, I’m curious what those two Month and Week columns are actually used for.
