fbpx

Paycheck Deductions Tracker Spreadsheet

The Paycheck Deductions Tracker is an easy way to generate transactions based on the deductions taken from your paycheck so you have better insights into where your money is going.

The Paycheck Deductions Tracker Spreadsheet 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 into where your money is going.

“I wanted a way to simplify the process of entering the paycheck information every time I got a check. 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).

From Tiller Community Solutions builder jpfieber

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

Installation

You can now find this in the Tiller Community Solutions extension, called “Paycheck Deductions Generator.” Install the template from the Tiller Community Solutions Add-on

Questions and Feedback

Visit this template’s dedicated thread in the Tiller Community.

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.

Installation

You can now find this in the Tiller Community Solutions extension, called “Paycheck Deductions Generator.” Install the template from the Tiller Community Solutions Add-on

Questions and Feedback

Visit this template’s dedicated thread in the Tiller Community.

FAQ

This is a free template built by a member of the Tiller Community. While it’s free for anyone to use, it’s designed for spreadsheets automated by Tiller. Additionally, it’s recommended for use within the Foundation Template.

Tiller is the only automated personal finance service for Google Sheets and Microsoft Excel. Tiller automatically imports your daily spending, income, and balances in your spreadsheets, so you can see all your finances in one place and manage your money, your way. Learn more →

Unless otherwise noted above, this free template is installed with the Tiller Community Solutions add-on for Google Sheets. It's an easy way to browse and install dozens of free, prebuilt sheets for tracking budgets, debt payoff goals, net worth, savings, and more, all from the Google Sheets sidebar.

This template for Excel works best when installed in Tiller’s Foundation Template.

Visit the Tiller Community with any questions about this template.

Start Your Free Trial

"There isn’t another tool on the market that does what Tiller can do.”
Will Hinton, Google Review October 30, 2023