Income Settings sheet for Google Sheets

[1Email2RuleThemAll]

Overview

On this sheet, you enter in your income information and it calculates a few things like average gross and net pay, as well as sets some things in the hidden section to be used by other sheets that I develop that use this as a base sheet.

Goal of This Workflow

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.

What This Workflow Does

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.

Select your workflow-specific categories at the top, then enter values in the green fields. The grey fields are calculated based on those values. Most of the green 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).

Origins of This 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.

Dependencies

The below required dependencies must be present. If they are not present, they should be installed and set up in the order that they appear in this document. If you already have one of my required community solutions, check to make sure that your version meets the minimum version requirements listed. If it does not, look for the instructions in that sheet’s documentation to upgrade the sheet before proceeding.

Required

Tiller Core Sheets

The following Tiller core sheets are required:

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

Tiller Community Solutions

There are no required Tiller Community Solutions

Solutions that have a dependency on this solution

If this sheet needs replaced as a part of a major upgrade, and you use one of my solutions listed below, there will be instructions in the Version Changes section of the linked documentation to fix the reference issues that would arise for that particular solution.

• Make the Savings & Debt sheet more granular - Changes two formulas in Savings & Debt to modify the Req’d Budget formula to fully fund savings goals by the Deadline date (rather than the end of the Deadline month)

• Upcoming Expenses/Account Health Dashboard - easily see what all expected recurring expenses will need to be paid before you are paid again, when they are due, how much money you have to pay for them, and how much you will have left after paying them.

Installation

To Install

1. Open the attached workflow
2. Add the Income Settings sheet
   a. Right click on the Income Settings sheet and copy to your Tiller workbook
   b. Go to your workbook and rename the sheet from Copy of Income Settings to Income Settings
3. Setup using the information below

To Uninstall

• Right-click and delete the Income Settings sheet
  • Make sure this sheet is not used in a solution of mine that you want to keep before deleting

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.

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

Notes

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

Income Settings

Version Changes

The initial release version of all sheets will be version 1.0 . When a change is made, this number will increment. If the version number jumps to the next whole number, this indicates the need to reinstall the sheet using the steps outlined below. If not, it’s a relatively minor fix that can be applied by following the steps outlined in the appropriate documentation below.

To Base Sheets

There are no custom base sheets that this solution depends on.

To This Solution

If you are jumping multiple minor versions, you can either apply all of the minor fixes individually, or re-install the sheet using the instructions below.

For versions that require a re-install of the sheet:

1. Rename the current Income Settings sheet to Income Settings Old
2. Follow the install instructions above to re-install and rename the new sheet
3. Copy your expense data from the old sheet to the new sheet
   You can copy/paste, but only copy/paste over green rows. The grey rows are generated.
4. Delete the old sheet, Income Settings Old
5. Make sure any solutions that depend on this sheet do not have #REF errors. If they do, there will be steps in that solution’s documentation to resolve the error.

2.0

Reworked a lot of things to streamline development for further solutions. Since there were so many little changes and moves, it is best to re-install the sheet using the instructions outlined above.

[randy]

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.

[yossiea]

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.

[1Email2RuleThemAll]

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.

[sbscheiman]

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!

[1Email2RuleThemAll]

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?

[sbscheiman]

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

[1Email2RuleThemAll]

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.

[bentyre1]

So I’m finally getting around to installing this as part of your new Upcoming Recurring Expense / Account Health Dashboard and realizing that how I have categorized my wife and I’s income may have caused issue for this use case. I use two different categories based on whose income it is so that I can see over time which is which without using tags.

What would you suggest for this? Going back and changing to tags usage? something else? or is there a way to look at two different categories for the income on this sheet instead of the only 1?

Also, I’m not sure what may be happening, but I don’t use the paycheck generator and the avg gross pay result is showing -$8 somehow.

[1Email2RuleThemAll]

Unfortunately, this sheet can only be set up to track one income source at a time. But the only thing that the new solution uses this sheet for is to pull in the next pay date into the top right corner in cell Q1.
.

This date is used to filter all expenses and display the ones that are due by it. If you do not have Income Settings installed, this date will be the end of the current month. You could still track accounts, expenses and everything else the dashboard provides on a monthly basis if you wanted to give that a try.

Or (what I suggest) you can install Income Settings and set it up for one person’s income. Then, the expenses due would generate on that person’s pay cadence, giving you a closer approximation of your financial health. Again, the only thing this will affect in the new solution is the ‘view’ of which expenses will appear on the dashboard. All money will still be accounted for in the account balances when it flows into the workbook.

As for the -$8 avg. gross calculation:

Did you set the Gross Pay category setting in O3 to “N/A”?

Gross pay can be calculated in a few ways. If you have a transfer category set for gross pay, this will be the average amount found for that category in the transactions sheet, otherwise it will calculate it using the pay rate (and average hours worked, if hourly).

If you leave the category settings blank for gross income, this will be the average of all transactions in your transactions sheet without a category assigned (I suspect this might be what’s happening?) If you do not track Gross Income, the category setting for that should be set to “N/A” (and no categories should also be named ‘N/A’). Once this is done, the calculation should use the values entered into columns E, F, and G to generate your gross pay calculation.

[bentyre1]

That was exactly it, I missed that step in the install. Thank you about the -8.

And I will do exactly that because my income fluxuates so much I’ll use mine so the averages can smooth things out in this view instead. I like the thought of that better than always having so much difference like usual.

[1Email2RuleThemAll]

Sounds good! Glad it was a simple fix! I simplified the gross and net formulas on this sheet recently, and was worried that had some unintended consequences, haha.

But yeah, if you set this sheet up to your income source/frequency, the expenses that will generate on the dashboard will be all expenses due by your next expected check. Then, it will update when your payday hits and show the next round of expenses due. When your wife is paid, the balance of the account that her check went into will still update and reflect on the dashboard. Until that happens, though, the ‘available’ balance in that account will not be the true representation of what you have to work with for the period you’re viewing. To get a better approximation, you could do some manual math to add her average paycheck amount to the ‘available’ amount for the account until her check actually hits the account.