Paycheck Deduction Transaction Generator

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:

  • Copy the “Paychecks” sheet to your Tiller Foundation template.
  • In your Tiller Foundation template, rename the copied sheet from “Copy of Paychecks” to “Paychecks”.

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.

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”.

This looks really cool, @jpfieber. I love that you keep iterating on this and making it better.

1 Like

This looks really nice! Now that we’re in the start of 2022, I am going to try this out.
One note for me, and for others in the same boat, I have my Net Pay deposited into several accounts. I think best practice is to have those as a paycheck deduction, and the main transaction should be the net pay. That way everything matches.
Also, just to confirm that column H should be a negative number, but the absolute value matches the paycheck net amount. At least that is how it is on my end, once I put in all my deductions.
One suggestion, you might want to change/rename AH11 and onward to match the column name M4 and onward. I changed those columns to match what is being deducted, but then I still had to go back to reference it for the description field.

Also I think this a good time to show my experiment. :slight_smile: 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.
I don’t think there is a huge need for this, so I stopped looking into it, but it also includes an ESPP calculator and spots for after tax deductions.

Yea, if you have multiple bank deposit transactions, then you should have multiple off-setting ‘net pay’ transactions that are marked as ‘Transfer’ types as well. I’ll give some thought as to how multiple PayNet values might be included in this. You would then change the bank deposits to ‘Transfer’ types as well, so just the ‘Gross Pay’ transaction is counted as income.

Yea, values in H should be negative, since they are going to offset the bank deposits.
Good idea on the column names, I’ll make that change today!

Also, I found an error in AI4.
You have the array go to column 20, but it should be:

{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27}

because the VLOOKUP includes the dates, etc.

Right you are, thank for catching that! I just updated the template with that fix and the column header links as well.

Another thing to look at, and it might be my sheet, but I now have $0 actuals in the Monthly Budget sheet for Gross Pay, and of course my Net Pay isn’t there since it’s transfer.

Did you put your Gross Pay into one of your Income type categories? Presumably it should be in the same category that your bank deposit transactions used to be.

It’s in an Income category with income type.

Gross Pay Income Income Income
Net Pay Income Transfer Income

I don’t see any transactions for my paycheck in the transaction comparison report, whether I filter hidden/by tag/by category.

I narrowed the issue down a bit more. If I change the actual transaction for my paycheck (from my bank) to the Gross Pay category, then that shows up in the report. So it might be something to do with the formatting of the paste job, or the tags perhaps.

Maybe try manually changing the category for the Gross Pay transaction using the drop-down menu, that should overcome any kind of typo, though I’d think you’d get a red ‘error’ indicator if there wasn’t a match to anything in the menu

I got it working, but it’s a weird one. I changed the date from 1/6 to 1/5 and now it works. (even when I change it back)

hmm, so perhaps the date that you’re pasting in isn’t being recognized as a valid date. What date format do you use for your normal transactions? Mine are M/D/YYYY.

Same, it’s m/d/yyyy, I’m going to try my second Jan paycheck and see what happens.

I just tried it again and I had the same issue.
I paste special, it doesn’t show up in all the sheets.
I then change the date, and change it back and it works.

Yea, it not being recognized as a valid date definitely explains why the transactions don’t show up anywhere. When you go to ‘File’ and choose ‘Settings’, does your ‘Locale’ equal ‘United States’?

Yeah, it’s set to the US. But I don’t think the date is the issue. After I pasted, I also copied format from the line above and that didn’t work. Also, I only changed the date back and forth for that one item, the others I pasted I didn’t touch. Regardless, I found a workaround, just a weird issue.
Next month I’ll take a look again and see if it has anything to do with delays or different days for pasting, etc. But in any event, this is a great template. I now see my complete income/expenses/tax picture.
(Not sure if the waterfall template is yours, but I have to modify that to exclude “Transfer” type, not sure if the newer template already excludes that.)

OK, if you figure out what’s making it do that, I’d love to hear about it so maybe the template can account for it. Yea, the Waterfall template is mine as well. It does filter out ‘hidden’ categories.

I take back what I said about the date. I do have to flip the dates for the other lines for it to show up in the waterfall.
I’ll keep an eye on it for next time.
Thanks for the waterfall template comment, I guess I had an older version, I was manually excluding a specific category and not an entire “Type” so I guess it’s time to update.

Implemented this, thank you very much! I had the same issue with dates. I had to just copy a date from the transactions sheet and paste it appropriately.

I downloaded a blank Tiller template and am getting the same date issue. I’ll poke around and see what I can figure out. If anyone has ideas on what’s causing this, I’m all ears!

1 Like