What is the goal of your workflow? What problem does it solve, or how does it help you?
This is a replacement for the method I originally proposed here. It provides an easy way to generate transactions based on the deductions that are taken from your paycheck. These transactions, when properly categorized, can then give you better insights on where your money is going.
How did you come up with the idea for your workflow?
Many, many, many iterations of trying to simplify and clarify the process I started some time ago.
Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
- Copy the “Paychecks” sheet to your Tiller Foundation template.
- In your Tiller Foundation template, rename the copied sheet from “Copy of Paychecks” to “Paychecks”.
Section 1 is “Configure Paycheck Deductions”. This is where you enter the detailed information from your paycheck stub/statement.
- Using your latest paycheck stub/statement, enter the date you got paid in F4. Note that columns C, D and E are optional. They aren’t used anywhere in the generation of transactions, but you may find them useful for keeping track of your past paychecks. As an example, my last paycheck had ‘JAN B’, ‘1/2/2022’ and ‘1/15/2022’ in those columns, and ‘1/28/2022’ in column F.
- Put your Gross Pay (the large amount before they started taking out deductions) in column G. Column H should now show the negative value of your Gross Pay. As you add deductions, the PayNet value will decrease, showing you what remains.
- Columns I through L hold the standard deductions that most people have taken from their Gross Pay. Enter them as negative values, since that’s how we want them to show up on our transaction sheet. If any of them don’t apply to you, leave it blank.
- The D01-D20 columns are for other deductions that are probably specific to your company. Type in each deduction, again as a negative number.
Once you’ve finished entering deductions, check the PayNet total and compare it to the Net Pay total on your paycheck (the amount you end up getting paid). If they don’t match, you’re missing or mistyped a deduction, go back and correct! Once they do match, you can copy this row, and paste it down to fill as many of the rows below as you like. Change the PayDate to the dates of your upcoming checks. If your Gross Pay will be different on any upcoming checks, adjust it now. Also, if any of your deductions will be different (eg. I’m paid bi-weekly, and twice a year there are three pay periods in a month. They don’t take some of my deductibles out of that third pay period). You can also add any past paychecks if you like as a way of keeping a record. I have all of mine back through January of 2019. Make sure they are sorted by PayDate to the formulas are able to find the most current check based on the current date. Conditional formatting will highlight the row of the most recent paycheck.
Now that you’ve completed this section, click the ‘-’ above column B and it will close.
Section 2 is “Configure Individual Transactions”. This is where you determine the ‘Description’, ‘Category’ and optionally, ‘Tags’ for each deduction you’ll have. Click on the ‘+’ above column AG and it will expand showing the settings that need to be configured. You’ll see a list of columns that match those you just entered your deductions into. Column AI should show the totals for the information you added for the most recent paycheck.
- Determine what you’d like to appear as the Description of each of your deduction transactions.
- Determine what category you’d like each of your transactions to be classified as. Click the drop-down menu in the Category cell to see a list of your existing Categories. You’re Gross Pay transaction should be categorized as an ‘Income’ type category. Note that the Net Pay transaction should be categorized as a ‘Transfer’ (hidden) type category. More on that later. The rest of the transactions should be categorized as ‘Expense’ type categories.
- Optionally, if you have a ‘Tags’ column on your Transactions sheet, you can enter any tags you’d like associated with the transaction.
Here’s an example of what I have in this section:
Description Category Tags ACME Paycheck - Gross Wages Paycheck ACME Paycheck - Net Transfer Out Paycheck ACME Paycheck - Fed OASDI/EE (Social Security) SocialSecurity Paycheck ACME Paycheck - Fed MED/EE (Medicare) Medicare Paycheck ACME Paycheck - Fed Withholding (Federal Tax) FederalTax Paycheck ACME Paycheck - WI Withholding (State Tax) StateTax Paycheck ACME Paycheck - Bobs Health Insurance HealthInsurance Paycheck ACME Paycheck - Bobs Life Insurance HealthInsurance Paycheck ACME Paycheck - ACME Retirement System Pension Paycheck ACME Paycheck - 401K 401K Paycheck
Once you’ve completed these settings, click the ‘-’ above column AG and it will close.
Section 3 is “Configure All Transactions”. This is where you determine what you want to call the ‘Account’, ‘Account #’ and ‘Institution’ that will be listed for all your paycheck transactions. None of these are necessary, and you don’t need an actual Tiller Account that matches this, but having them filled in could be a useful way to sort or filter your paycheck transactions. Click on the ‘+’ above column AM and it will expand showing the settings that need to be configured.
- Leave “Date Override” blank for now, we’ll address that later
- I named my Account “ACME Paycheck”, note that you don’t need a matching Tiller Account. You can also leave this blank if you choose, but having it filled in may make it easier to sort or filter your Transactions sheet if you’re looking for paycheck info.
- I left the Account # blank. You can enter whatever you’d like.
- I named my Institution “ACME”. Again, this can be left blank with no repercussions.
- There is one other column you can configure called ‘Note’. This column doesn’t exist in the default template, but you can add it if you want the ability to add notes to transactions. If you don’t add the Notes column to your transactions sheet, you can ignore the configuration here. I have a Notes column, and I configured mine to add “Copied from Paycheck Deduction Transaction Generator”, which is filled in by default (but of course you can change it to anything you’d like).
You may not have all the columns listed here, that’s OK, they’ll just be skipped over. Also, if you have other columns beyond these, and you’d like to add something, just add the name of the column in AN14, and what you’d like to appear for every transaction in AO14. Once you’re done configuring these options, click the ‘-’ above column AM, and it will close.
Section 4 is “Paycheck Deduction Transactions”. You’ve done everything you need to, time to reveal your Paycheck Deduction Transactions! Click the ‘+’ above column AP and it will expand. If everything worked correctly, you should see a list that included all the column headings from your Transactions sheet. There should be the same number of transactions as there were in section 2 when you provided descriptions for each transaction. Note that if any deduction for this check had a total of $0, it is filtered out. Each column that we provided information for should be filled in, many others are left blank either because they don’t apply, or because they will automatically be calculated (eg. Group, Category Type, etc).
How to Use:
I usually add paycheck transactions when I notice the transaction for my paycheck deposit in my checking account, and I have the paycheck stub/statement. Open Section 1 and confirm that the totals for your new paycheck match those you entered in the chart. Correct any differences. Then close that section and open Section 4.
Note the three yellow boxes on the left side of the section. The first box tells you how many transactions you have. Note this number, and go to your Transactions sheet. Find your most recent paycheck deposit transaction, click on the row number and drag down, selecting the number of transactions you need to add. With the rows selected, right-click on the selection and choose “Insert XX Rows Above”. New, blank rows will be added. Now go back to the “Paychecks” sheet and note the second yellow box, telling you to select the cells from “$AS$4” thru the last cell of your transactions (will vary by number of columns you have, and number of deductions). Click on cell “AS5” and drag over to the last cell in the lower right corner of your transactions. Once selected, choose “Copy” from the “Edit” menu. Now switch to your “Transactions” sheet, and click in the “Date” column of the first blank row you created. From the Edit menu, choose “Paste Special” and then “Values Only”, and your paycheck transactions should appear! The reason we don’t do a normal paste is that formatting from the Paychecks sheet probably won’t match that of the Transactions sheet, and can cause problems, especially if you use Conditional Formatting.
Normally you wouldn’t need to include a transaction for ‘Net Pay’, since it’s just what’s leftover after the deductions are taken from your ‘Gross Pay’. However, now that you’re adding a “Gross Pay” transaction, and are presumably categorizing it as income, you have a problem. You probably already have the bank deposit categorized as income. With both now being called income, your budgets/reports are going to show you earned way more than you really did. To fix this, change your paycheck deposit to also be a ‘Transfer’ category that is hidden. The “Net Pay” deduction you created will offset the bank deposit (that’s why it needs to be negative), so only your ‘Gross Pay’ shows up in budgets and reports, as do the deductions themselves. Think of the ‘Net Pay’ deduction as the transfer out of the ‘ACME’ account, and your paycheck deposit as the ‘transfer in’ to your checking account (I actually use ‘Transfer In’ and ‘Transfer Out’ as categories in the ‘Transfer’ group to help make more obvious how money is moving around)…
- If you’d rather not keep a list of every paycheck, you can optionally just fill in the paycheck information in section 1 and not include a date. The generator will use today’s date instead, which you could always change as necessary once the transactions have been pasted into the Transactions sheet. Each time you get a paycheck, you can just update the numbers as needed, and copy the generated transactions.
- If you’re just starting out, and have entered information for past paychecks in Section 1 and you’d like to generate transactions for them, open Section 3 and use the “Date Override” dropdown to choose which PayDate you’d like to generate a transaction for. Once you’re done generating transactions for past paychecks, delete the date in that cell to get back to the default setting, which is to use the most recent PayDate.
Is it ok for others to copy, use, and modify your workflow?
Yes! And please let me know if you encounter any problems or if you have ideas on how to improve it!
If you said yes above, please make a copy of your workflow and share the copy’s URL:
You can now find this in the Tiller Community Solutions extension, called “Paycheck Deductions Generator”.