Income Settings sheet for Google Sheets

Google Sheets Show & Tell
1

Overview

What is the goal of your workflow? What problem does it solve?
The goal of this workflow is to keep track of primary income settings over time, and provide a few insights to the currently active income row that can be used by developers to build dashboards/reports.

This standalone sheet is mainly for developers to use as a base for custom solutions using the calculated metrics. A typical user can also get value out of it, though, in terms of keeping a sort of salary record (when did I get that pay raise?, What was I making back in 2020?, etc…).

How did you come up with the idea for your workflow?
I built my own finance tracker in Google Sheets back in 2019, before I knew of Tiller. This sheet was a part of that workflow, albeit in a more primitive state. When I joined Tiller, there were a few solutions that I built for myself that I could not live without. Those solutions used this sheet, so I ported it over and enhanced it so I could rebuild those solutions in Tiller.

Describe your workflow:
The sheet supports only one current active row at a time, so this can only track a single income for a given date range. The active row will be the one with the latest “1st Pay Date” column that is not a future date.

image
image1915×762 75.5 KB

image
image1767×757 78 KB

The end user selects their workflow-specific categories at the top, then enters their values in the green fields. The grey fields are calculated based on the user values. Most of the user fields are self-explanatory, and there are plenty of informative notes in the sheet. I go into detail on the grey fields and the hidden section in the Usage section below

The sheet works with Weekly, Bi-weekly, Semi-Monthly, and Monthly pay frequencies for both hourly and salaried rates. The 1st Check Date is important, as it sets the cadence for when a check is expected, depending on the current frequency selected. If a paycheck posts to your transactions page on a day that wasn’t your actual pay date, there is logic to correct it to the date you should have been paid (“Adjusted Pay Date” in the hidden section). The logic for each frequency is special, but basically:

  • Weekly & Bi-weekly will adjust the posted date to the closest date where the difference of that date and the current “1st check date” is evenly divisible by 7 (weekly) or 14 (bi-weekly).
  • Monthly will generate three pay dates closest to the beginning of the current month, based on the day of the month set in the current active “1st check date” column, then choose the date closest to the date that the most recent paycheck posted.
  • Semi-Monthly needs an entry in the Notes column to work. Enter both days of the month you get paid, separated by a pipe character (e.g. - 15|30). The logic will generate dates for these days just like the Monthly flow, then choose the date closest to the date the paycheck posted.

Once data is entered, a generic average Gross/Net pay will be generated. The gross value is calculated by the pay rate (and average hours worked if hourly). The Net pay is 73% of the gross pay. Once you start categorizing paychecks under the respective category chosen at the top, the averages will change to use an average of the actual amounts received (going back 60 days).

Installation

Only two Tiller foundation sheets are required for this to work:

  • Categories - Used to pull back all Income and Transfer categories into separate lists. These lists are then used as the data validation for the drop-downs in the category settings section at the top.
  • Transactions - Used to gather information, such as average amounts and last transaction date, on transactions that are categorized under the income categories selected at the top.

The following solutions have a dependency on the Income Settings sheet:

To install:

  • Open the attached workflow
  • Right click the Income Settings sheet
  • Copy to your Tiller solution
  • Rename from Copy of Income Settings to Income Settings
  • Setup

Setup

  1. Fill in the category settings section with your personal categories
    – Gross pay is optional. This was added because I use the Paycheck Deduction Transaction Generator by @jpfieber to split my paycheck into its individual parts. If you don’t track gross pay, you can select N/A from the list.
    – Net pay is required. This should be the category that you select when categorizing the positive paycheck transactions that hit your bank.
    – Transfer In is optional. When you transfer a balance between accounts (pay a credit card bill, transfer money from checking to savings, etc…) what category do you place the paid transaction under? e.g: +$300 to Chase Freedom from Primary Checking
    – Transfer Out is optional. When you transfer a balance between accounts (pay a credit card bill, transfer money from checking to savings, etc…) what category do you place the paying transaction under? e.g: -$300 from Primary Checking to Chase Freedom

  2. Enter Income Settings

  • If Semi-monthly is selected as the pay frequency, there must be a note in the corresponding Notes column that is formatted: {Day 1}|{Day 2}
    Example: 14|28
    – If you want to enter a note after this, append a colon ( : ) to the end, and type your note.
    Example: 14|28: Whatever I want to add as a note goes after the colon.

– I believe I have it working to where income periods don’t have to be in order, but I haven’t tested that yet.

Usage

For End Users: This sheet only needs to be updated when something changes with your current pay (new job, pay raise, etc…). If you want historical records, you should enter a new row each time anything changes. Reusing the rows (overwriting values) will not affect anything, but you lose out on that historical data.
– I enter a new row whenever I change jobs, receive a raise, change my 401(k) contribution, etc… This way, I can build reports that can look at a snippet in time and grab income values as they were during that timeframe.

For Developers: This sheet can be used as a base sheet for custom solutions. I’m using it for three of my own custom solutions so far. The metrics that I calculate for use are:

  • Average Gross and Average Net Pay for all rows
    – This will be the average between the start/end dates for old rows, and the most recent 60 days for the current row. If an old row does not have an end date, it will be the next check date minus 1.
  • Days Active for all rows
    – This is a count of days that each record spent in an active state.

There are more sections in the hidden area to the left that can be used for a whole litany of things pertaining to the current pay period.

image
image1767×757 78 KB

These metrics include:

  • Current: Avg. Gross, Avg. Net, 401(k) contribution, 401(k) match, Notes, and Frequency
  • Pay Day - For weekly & bi-weekly, this will be the day of the week that’s selected for the current record (Monday, Friday, etc…). For monthly, this will be the day of the month of the current “1st check date” (15, 28, etc…). For semi-monthly, this will be the two days entered into the notes field (15|30, etc…)
  • Frequency in Days - 7 for weekly, 14 for bi-weekly. Blank for semi-monthly and monthly, as these frequencies vary.
  • Pay Date - This will be the posted date of the most recent paycheck under the Net Pay category in the transactions sheet.
  • Adjusted Pay Date - This will use some of the other metrics calculated on this sheet (depending on the current frequency selected) to adjust the pay date to the date you should have been paid. So if your paycheck hits your account +/- a day or two, this will re-align it to what it should have been.
  • Next Projected Pay Date - This calculates the next expected paycheck, based on the current pay frequency and adjusted pay date.
  • Current Monthly Paycheck Metrics - This section is used to calculate how many paychecks are expected in the current month, and how many paychecks have been received in the current month. It is also used to calculate the adjusted pay date for weekly/bi-weekly frequencies.
  • (Semi-)Monthly Date Range - When Monthly or Semi-monthly is selected as the current frequency, this will generate 3 paycheck dates:
    – Monthly - pay dates of last month, this month, and next month
    – Semi-Monthly - final pay date of last month and both pay dates expected this month.
  • Monthly/Semi-Monthly Days - Used to generate the (Semi-)Monthly Date Range above, depending on if monthly or semi-monthly is selected as the current frequency.
    – Semi-monthly - this will split the days out of the current notes section to be used in the formula
    – Monthly - this will generate the day of the month set in the current “1st pay date” twice

Permissions

Is it ok for others to copy, use, and modify your workflow?
Yes! If you can get any value out of my solutions, then please adopt, use, modify to your hearts content.

If you have any questions, comments, or find any issues during use, please reach out and let me know!

Notes

Anything else you’d like people to know?

One solution I have used the Income Settings sheet for is to Make the Savings & Debt sheet more granular. By default, the available amount of a savings goal in the Savings & Debt sheet will update with the entire monthly total you allocate to that goal at the beginning of each month. That didn’t make sense to me, as my savings goals were funded by my paychecks, which I received throughout the month. If I get 2 checks in a month, I put half of my goal back from each. If I get 3 checks, I put back a third from each, etc… I don’t have the full amount available until my last check of that month.

With the Income Settings sheet, I was able to modify two formulas to update the available amount with each paycheck I receive. This way, the Savings & Debt solution is more granular and exacting. This is just a simple example of what the sheet can be used for.

I will be creating a few more Show & Tells over the coming weeks. Some of them use this sheet, and others are used in conjunction with this sheet for other solutions.

FAQ

Please ask any questions you have below and I will reply, and update this section accordingly.

Solution Link

2

Thanks for sharing this building block, @1Email2RuleThemAll. I really appreciate your thoroughness throughout the project— the detailed documentation, the in-cell notes, the many configurations for time periods, etc.

It looks like this is a robust and flexible analysis tool to render annual gross pay into real-world net pay with additional timing data captured as a granular input to other solutions. Having spoken last week, I understand you are laddering all these helper sheets into something big.

As part of the Tiller Builder Rewards Program , the Tiller team is awarding you $25 for building the Income Settings sheet and documenting it so it can be used by the entire Tiller community.

Thanks for sharing this and for your extensive documentation.

:raised_hands:

2 Likes
3

Just a minor issue, I also use the Paycheck Deduction sheet, so my positive bank transaction is one of the Transfer categories, so it doesn’t show up in your drop down. Basically, the PD uses the Gross Amount then subtracts the taxes, etc. so it doesn’t use the NetPay in any of the reports. (Also, if you use multiple bank accounts for your Net Pay in the PD, you would only be able to use one of them in this sheet, so if you’re just starting, you might want to use one account in PD (even though it will make separate rows).)
A suggestion, would be to have the dates of the paycheck be a variable, since I don’t get paid on the 15th or 31st, it would be good to know for forecasting.

4

You are correct that this sheet can only track one income ‘category’ and one income ‘source’ at a time. If you split your check into different accounts, you would need to file each transactions under the same category in order for the amounts to be correct, as it’s querying the Transactions sheet for the average amount for the chosen category at the top.

I forgot that I use the Paycheck Deductions Generator slightly different from others. I set my gross pay and net pay to separate income categories, then hide the gross pay category so that only net pay comes through to all reports. This allows me to budget on net pay, while still capturing the gross pay metrics and deductions. I then pull my transaction data into SQL Server via a custom python script, and do further analysis on it.

With that said, I forgot that the initial setup of the PDG is set up like you stated, with the final positive net amount being a transfer category. This issue can be fixed by altering the query in column S8 of my sheet to also pull back transfer categories as well. This will allow transfer categories to be chosen in the income drop-downs.

={"Income Categories"
	;QUERY
	(
		{INDIRECT(Z12),INDIRECT(Z13)}, 
		"SELECT Col1 WHERE UPPER(Col2) = 'INCOME' OR UPPER(Col2) = 'TRANSFER'"
	)
	;"N/A"
}

This will work, but be warned that if you categorize anything else under the chosen transfer category other than your net pay, it will be taken into account for the Net Pay average calculation. So if you use a generic “Transfer In” category for everything, it will average all transactions categorized under “Transfer In”.

Another possible solution is to keep it blank or choose N/A, but that will then use the Average Gross Pay calculation to estimate the Average Net Pay. It will not be a true amount. I have it set to calculate avg. net pay as 73% of avg. gross pay. This could be changed by altering the formulas in O9:O. At the very bottom of the formula, there’s a line of code ($N9 * 0.73). You would change 0.73 to another percentage in O9, then drag the fill handle to the bottom to propagate the change to the rest of the column.

A suggestion, would be to have the dates of the paycheck be a variable, 
since I don’t get paid on the 15th or 31st, it would be good to know for forecasting.

I’m not quite sure I follow what you mean here. Could you please elaborate?

There are 4 settings to choose from for paycheck frequency: Weekly, Bi-weekly, Semi-monthly, and Monthly. Both the frequency you choose and the 1st pay date determine the logic that calculates your next projected pay date in cell T3, as well as a few of the Current metrics in W21:Z38.

5

The function in Cell X9 is broken for me: =MATCH(W9,INDIRECT(“'”&#REF!&“'!$1:$1”),0), which seems to break a lot of the other functions.

What cell should it be referencing?

Thanks!

6

Hello @sbscheiman,

There should not be a formula in cell X9. There are formulas in cells X4 through X8 that look like the one you posted, but it should not be in cell X9. What kind of functionality are you seeing not working? I can maybe track down the issue.

Also, could you please send a screenshot of that whole area?

image
image756×367 24.6 KB

7

Thanks so much for the quick response! After comparing your sample above to my copy of the sheet, I realized that my issues were self-inflicted. I “may” have deleted the rows that held the sample data rather than clearing the individual cells :slight_smile:

8

Ah, yeah that’ll do it! Unfortunately with the way the sheet is structured, you have to clear the sample data instead of deleting. At least in Google Sheets, it’s as easy as highlighting all the green cells, and hitting backspace.

Do not clear the grey cells, as there are formulas in them that are needed for functionality.