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?
Installation:
- Install the template from the Tiller Community Solutions Add-on
Configuration:
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.
- If you do enter more than 1 paycheck, keep the oldest at the top, and add newer ones below, so the list is sorted from oldest to newest. Some features wonât work right unless they are sorted this way.
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.
IMPORTANT!:
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)âŚ
Additional Notes
- 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â.
I was working on a paycheck reconciliation and this works for those in Pennsylvania (and can easily be modified to any gross tax state) and for 2021 tax brackets for single tax payer.