🏆 Paycheck Register - Excel

[jpfieber]

I thought it might be interesting to have an easy way to compare my paycheck against the spending I do in the pay period following the reception of that check. This looks a lot like the Account Register template, but instead of focusing on transactions from an account, it focuses on transactions within a ‘pay period’. It will list the paycheck from a selected date, and then include all expense/income transactions during the period starting when you get your check to the time the next check is received. There is an option to exclude other income transactions if you just want to compare your spending against the selected paycheck.

Installation

1. Download the Shared-Paycheck_Register.xlsx file
2. Follow these instructions to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data. (The instructions are for the desktop version of Excel, the web version is not recommended for the installation but does work once the template is installed.) Note I didn’t create connections to other sheets, so the step for changing the source of the links may not be necessary.

At this point your new template should be functional and linked to your local workbook’s data.

Setup

Expand the Helper Data section by clicking the ‘+’ above column AA.

• How to Find Paycheck: Choose “Account” if you break your paycheck out into multiple transactions for deductions, like those created by the Paycheck Deduction Transactions Generator template. Otherwise, choose “Category”.
• Paycheck Category: If you chose “Category” above, then pick which category you associate with your paycheck. This will only work properly if you have a dedicated category for each paycheck.
• Paycheck Account: If you chose “Account” above, enter something like “ACME Paycheck” in the Account column on the Transaction sheet for all paycheck related transactions, including deductions, and enter the same string here. You can enter this same string in the Paycheck Deduction Generator template (cell AO6), or you can use AutoCAT to automatically add it to future transactions.
• Paycheck Frequency: What is the frequency of your paychecks? Choose from Weekly, BiWeekly, SemiMonthly or Monthly. Note “SemiMonthly” means twice a month (I avoided "BiMonthly which is also used to refer to every other month).
• 1st Pay Date of the Year: On what date were you supposed to get your first paycheck of the year? This defines the day of the week, which is used in “Weekly” and “BiWeekly”, the date of the month for the first paycheck for “SemiMonthly”, and the date of the month for “Monthly”.
• 2nd Pay Date of the Year: Only used for “SemiMonthly” frequency. When were you supposed to get your second paycheck of the year? This defines the date of the month for second “SemiMonthly” paycheck.
• Paycheck Date Padding: If your paycheck deposit isn’t always dated on the expected pay date, specify how many days before or after the normal pay date we should look for it (keep this number small to avoid accidentally pulling in other paychecks) by adding numbers to the “Days Back” or “Days Forward” cells.
  With all that set, you can close the Helper Data section.

Usage

In cell G3, choose whether to include other “Income” type transactions. Choosing ‘No’ will filter all transactions of type ‘Income’ except the defined Paycheck transactions.

In cell G5, select the “Paycheck Date” you’d like to examine. You’ll see displayed a list of transactions related with the Paycheck itself (deductions if defined, otherwise just the deposit transaction) followed by all the expense (and optionally income) transactions that occurred after the paycheck was received but before the next paycheck was received. Note: The template cannot account for time of day, so it assumes you received the paycheck at midnight on the selected date, and that all transactions on that date come after it.

The way I use it

I use the Paycheck Deduction Transaction Generator template (not required for use of this template) to generate all the deduction transactions for each paycheck. For each paycheck related transaction, I set the “Account” column to be “ACME Paycheck”, which allows me to easily find all the ACME related paycheck transactions (you can do this by hand and even have AutoCAT add them for future transactions). This becomes important in the Paycheck Register template because occasionally a paycheck may show up a day early or a day late. If it shows up early, it and its deductions wouldn’t be found during a selected pay period. I’ve worked around this problem by allowing the addition of ‘padding days’ to search for paychecks (but not non-paycheck related transactions), and I’ve associated all the deductions with the payment by using the “Account” field. If you don’t do deduction transactions, and just have one deposit transactions, you can use “Category” instead, and simply choose a category that include only your paychecks from that one source.

Note

• This template relies on predictable pay periods, so if you get paid at random intervals this template isn’t going to work for you.
• There is a version for Google Sheets.
• I gave thought to accommodating multiple paycheck sources (eg. His check and Her check), but it doesn’t seem it would make sense without some way of assigning transactions to specific pay sources, which is out of scope for this template.
• If you find any bugs, let me know!

[randy]

We were excited by this template when you first published it.
What a great addition to the options available to Excel users.

For templates ported to a second platform, with our Builder Rewards Program, we are excited to award you another $125 for the Paycheck Register.

Well done, @jpfieber!