Generated Recurring Expenses Workflow

[1Email2RuleThemAll]

Overview

Write a brief description of what the template does.
Essentially, this template takes a user supplied list of inter-grain recurring expenses on one sheet (Recurring Expense Entry), and then dynamically generates a row for every expected due date of those expenses that fall between two user supplied dates on another sheet (Generated Expenses).

It works with 10 frequency grains: One-Time, Weekly, Bi-weekly, Semi-monthly, Monthly, Bi-Monthly, Quarterly, Semi-Annually, Annually, and Bi-Annually. When a user flags an expense as paid in the transactions sheet, it will be flagged as paid in the generated list as well.

What is the goal of your workflow? What problem does it solve?
This workflow is mainly for developers to use as a base for building dashboards/reports to view past, present, or future recurring expenses. End users can use this sheet, but it cannot be sorted in any way due to the logic used to generate it. So viewing it directly isn’t the best experience. I’ve supplied a basic report sheet with it to show how the data can be pulled into another sheet for viewing that could be useful.

You would pass your report start and end dates to the correct fields on the Generated Expenses sheet (or enter dates directly if you just want to use it with no report), and a list of expenses from Recurring Expense Entry would generate between those dates. You could then query that generated list to be displayed in your report.

• I have supplied a really basic ‘report’ example sheet with the solution so you can see how to pull the data into another sheet for your purposes.

The problem that this solution solves is that it allows for use of a more granular expense list. I’ve seen a few solutions that have aimed to tackle recurring expenses, but the smallest grain they all allow for an expense frequency is monthly. This workflow breaks that barrier by adding weekly, bi-weekly, and semi-monthly frequency options.

How did you come up with the idea for your workflow?
I built these sheets for a custom report I’ve been working on (and hope to release soon). The report takes any past, present, or future paycheck date as an input, and then displays a series of paychecks and the expenses that fall between them. For this, I needed a list of expenses, along with the actual dates they should be due. The expense list needed to handle different grains, display the correct due dates, and cover any period that I wanted to look at. Thus, the Generated Expenses sheet was born.

After building out my solution, I realized that this sheet, along with the Recurring Expense Entry sheet that it pulls data from, could be offered as a separate solution for anybody else that has this base need to generate a list of expected expenses between given dates.

Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?

• There are two custom sheets required: Recurring Expense Entry and Generated Expenses
• There is one custom sheet that is optional: Credit Card Settings
• The solution also requires an extra column be added to the Transactions sheet named Expense Tag, and a data validation rule set on it that points to the Expense column on the Recurring Expense Entry sheet (see install instructions).

Recurring Expense Entry - Required. Takes a user supplied list of recurring expenses, and displays a few metrics for that expense pertaining to the current month. All green fields are required, unless stated otherwise. The fields to enter are:

1. Active - You can toggle this on and off if you want to temporarily stop an expense from generating rows. Active is checked, inactive leave blank.
2. Expense - This is the name of the Expense. It must be unique within this column, but can be set to anything you’d like.
3. Frequency - How often is the expense due? There are 10 grains to choose from the dropdown:
   – One-Time: Occurs on the start date only
   – Weekly: Same day every week
   – Bi-Weekly: Same day every other week
   – Semi-Monthly: Twice a month
   – Monthly: Once a month
   – Bi-Monthly: Every other month
   – Quarterly: Every 3rd month
   – Semi-Annually: Twice a year
   – Annually: Yearly
   – Bi-Annually: Every other year
4. Start Date - The first date the expense is due. This will determine the dates that are assigned to the expense in the Generated Expenses sheet.
   – For Semi-Monthly: The first day of the month that needs paid is calculated from the Start Date. The day value must be the first day of the month that the expense is due. So if something is due on the 5th and 20th, the start date should be like 5/5/2018. Day value must be the 5th in this instance.
5. End Date - Optional for all frequencies except Semi-Monthly.
   – For Semi-Monthly: The second day of the month that needs paid is calculated from the End Date. If the expense has a real end date, enter the month and year of the end date, but use the second day of the month that needs paid as the day value. If there is no end date, set the month to 12 and year to 2999 (or some other far off future date). So if something is due on the 5th and 20th, the end date would be like 5/20/2025 if it ends, or 12/20/2999 if there is no end date. If no end date is selected, the last day of each month will be used instead.
6. Amount Per Occurrence - Budgeted amount planned each time the expense is due.
7. Pay Method - The credit card or checking account responsible for paying the expense.
8. Auto-Pay - Optional. Choose an option from the dropdown if the expense is set up for Auto-pay
   = auto-pay is set up to pay the full amount due
   ● = auto-pay is setup for either the minimum due balance, or a set amount that may or may not cover the expense
9. Notes - Optional. Enter whatever you want here.

The automatic calculated metrics on this sheet are:

1. Last Paid On - The date of the most recent transaction on Transactions that has an Expense Tag matching the expense name.
2. Occurrences in { month } - Will calculate how many occurrences of that expense to expect in the current month. This will automatically update on the 1st of every month.
3. Total Monthly Amount - Will be the amount per occurrence multiplied by the expected occurrences that month. This will automatically update on the 1st of every month.
   – If the expense is set to end in the current month, or started in the current month, this will likely be off. It is looking for how many times that this expense will occur in the entire month, and not taking into account start/end dates. Adding that in may be possible, but would be challenging and have little payoff.
4. Master Pay Method - If the Credit Card Settings sheet is installed and the Pay Method for an expense is set to a credit card, this will retrieve the checking account that is set as the payment method for that credit card. If the sheet is not installed, it will mirror the pay method you set in this sheet.

Generated Expenses - Required. Takes the expense list entered in Recurring Expense Entry and dynamically generates a row for every expected due date of those expenses falling between two supplied dates. The only two cells that should ever change here are the View Start and View End dates at the top.
– These dates can be entered directly into the sheet, but can also be fed from start & end date fields on a report page. The latter is preferred, as this sheet cannot be sorted in any way, due to the logic used to generate it. See the number list below for a rundown of that logic.

For any solution you build, the name of Generated Expenses should be changed to reflect the report that it’s feeding. (“Quarterly Projected Generated Expenses”, etc…) Then, that name should be referenced in the report you build. This way, if a user wants two solutions that both use this solution as a base, there won’t be any sheet naming conflicts. They would just have two instances of this sheet, with different names, driving different reports, both using the same Recurring Expense Entry sheet as a base.

The dynamic list on Generated Expenses is built in multiple steps. This is all automatic. The only manual step is step 1. This info is not necessary for use, but is added as a “How does it work” section. You can skip to the next screengrab/section if you’d like.

1. The View Start and View End dates are supplied at the top. Typically, these cells would reference user input cells in a report, but you can enter data directly here as well. Must be valid dates, and the start date must be before the end date.
2. The amount of Total Months that fall within the view time frame are generated to the right of the start/end dates.
3. The Expense list on the left is generated from Recurring Expense Entry. This is every expense entered that is:
   – Active
   – The start/end dates of the expense overlap the view start and end dates from above.
4. Depending on the frequency for each expense, the number of Occurrences between the view dates for the expense will be generated.
5. The dynamic list starts to generate. The Expense Name from the left is repeated however many times the corresponding Occurrences column says using a custom formula that utilizes the REPT and BYROW functions.
6. Amount, Pay Method, Start Date, and Auto-Pay are pulled into the generated section next to the Expense using Vlookups.
   – I could pull these all in with the expense name, but the rept function has an upper character limit (due to each cell having a character limit), so I kept that part as lean as possible.
7. The Date for the expenses are generated, depending on the frequency set. Each frequency calculates this differently. All frequencies use the expense Start date in some fashion. Semi-Monthly(2x a month) also uses the expense End Date to calculate the second due day of the month.
8. RowNumber is Generated using a simple formula. This is not used for anything.
9. Paid column is generated next. It uses a query that looks to see if the Expense name was entered into the new Expense Tag column in the Transactions sheet on a date near the generated date. The window that the query looks for expenses is also dependent on the frequency.
10. Active is calculated: If the date we generated falls between the view start and end dates, it will be 1. Otherwise, it’s zero (0)
    – If an expense starts or ends halfway through the dates set in the view start/end, the process will still generate out the full amount of instances needed to cover the view start/end, along with the generated due dates. This then looks at that generated date and makes sure it falls within the expense Start/End dates. You would query the list where Active = 1 to get a list of all the true expense dates.

Credit Card Settings - Optional. Used to pull back the checking account set to pay a credit card if the card is selected as a pay method for an expense. If the Credit Card Settings sheet is not installed, Master Pay Method will mirror the pay method you set in Recurring Expense Entry. More information on the Credit Card Settings sheet can be found here.

Installation

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

• Accounts - Used to pull back checking and credit card accounts for use in the Pay Method dropdown on Recurring Expense Entry.
• Transactions - Used to pull back the Last Paid On date on Recurring Expense Entry, as well as if an expense has been Paid on Generated Expenses

The following solutions have a dependency on this template:

• No solutions currently have a dependency on this template.

To install:

• Add a column to your Transactions sheet named Expense Tag
• Open the attached workflow
• Optional - Right click on Credit Card Settings and copy to your Tiller solution
  – Rename from Copy of Credit Card Settings to Credit Card Settings
• Right click on the Recurring Expense Entry sheet and copy to your Tiller solution
  – Rename from Copy of Recurring Expense Entry to Recurring Expense Entry
• Right click on the Generated Expenses sheet and copy to your Tiller solution
  – Rename from Copy of Generated Expenses to whatever you’d like, as it is the end of the dependency chain for this template. If you want to build a report on top of it, It is suggested that you rename this sheet to reflect what report it feeds.
• Remove all sample data from the sheet.
• Populate Credit Card Settings & Recurring Expense Entry sheet fields that are green.
• While on the Transactions sheet, go to Data > Data Validation
  – Enter the following rule for the entirety of your new Expense Tag column (mine is on F2:F):
  – ='Recurring Expense Entry'!$B$7:$B
  – Change column B in the formula if you move the Expense column on Recurring Expense Entry!
  
  

To Uninstall:

• Right-click and delete the installed sheets above.
• Delete added Expense Tag column on the Transactions sheet.

Install notes:

Please follow the installation order path above. Install a sheet, rename it to remove "Copy of ", then install the next sheet in the list. Each sheet references the previous one in some way. If you install a sheet out of order, Google Sheets will not recognize these reference formulas and throw a #REF error, even after the sheet has been added and is currently present. The only way I’ve found to get the reference to re-validate is to cut the formula out of the cell completely and paste it back in. Once this is done, Google Sheets has an “Oh yeah!” moment and recognizes the sheet is there and the formula is now valid.

Setup

Once installed using the above instructions, you can enter values into the green fields on Credit Card Settings & Recurring Expense Entry. Then, you can hook into the View Start and View End dates by referencing date fields in your report. After feeding dates to View Start and View End on Generated Expenses, You should see that sheet come to life with your projected expense data.

For the included solution, the View Start and View End dates are controlled by the Sample Report sheet.

• If you would like to install the sample report into your own solution:
  – Right click on it and rename from Copy of Sample Report to Sample Report
  – Change the value in cell N2 from Generated Expenses to whatever you named that sheet
  – On Generated Expenses, paste the following formula into cell L4: ='Sample Report'!C2
  – On Generated Expenses, paste the following formula into cell L5: ='Sample Report'!C3

Usage

There is no real upkeep for this solution outside of adding/updating expenses and credit cards.
You change the View Start and End Dates, and the generated expenses change to reflect the timeframe.

Permissions

Is it ok for others to copy, use, and modify your workflow?
No. This permission has been revoked as of 9/29/2023. All who already have the solution are free to keep it for personal use. But I do not give permissions to modify or re-upload my work, in part or in full, to Tiller or any other site. Truthfully, I spent too much time and effort on it to have somebody else re-upload it and receive more of a reward than I did for designing the original. I will also not be releasing the documentation I’ve been building to describe all formula functions in detail.

Notes

1. If more rows are required on the Generated Expenses sheet, you just have to add them to the bottom, then drag formulas in three cells down to populate the new rows. The cells you need to do this to are dark grey: Date, RowNumber, and Paid. All other formulas are wrapped in array functions, and automatically handle new rows.
   

   

2. The accounts that can be selected on the Credit Card Settings sheet are filtered down to accounts with a Type of ‘Credit’ on the Accounts sheet. Unfortunately, account types are set by the institution, and cannot be changed. If your institution sends an account through as a different type (i.e: “OTHER”), there is a section in the hidden area (Y7:Y) that will allow you to select additional types. Adding a type to the list will add all accounts of that type to the dropdown.
   

   

3. The accounts that can be selected on the Recurring Expense Entry sheet are filtered down to accounts with a Type of ‘Credit’ or ''Checking on the Accounts sheet. Unfortunately, account types are set by the institution, and cannot be changed. If your institution sends an account through as a different type (i.e: “OTHER”), there is a section in the hidden area (U7:U) that will allow you to select additional types. Adding a type to the list will add all accounts of that type to the dropdown.
   

FAQ

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

Solution Link

Generated Recurring Expenses

Version changes

[jpfieber]

Very cool, I look forward to playing with it! Thanks for sharing it!

[1Email2RuleThemAll]

Thank you for looking at it! I would love to hear your thoughts on the workflow. I’ve been using it in my personal solution for about 5 months now, with two reports sitting on top of it, and it has been a lifesaver.
Let me know if you run into any issues!

[randy]

Thanks for sharing and documenting this solution to generating transactions, @1Email2RuleThemAll. As with your Income Settings sheet for Google Sheets contribution, you have done a really nice job making a complex tool accessible to others with extensive in-cell notes, granular intermediate calculations of layered outputs, and thorough documentation in this thread.

You’ve done a great job integrating Tiller core sheet conventions and leveraging header-row and ARRAYFORMULA(). I also appreciate your clear statement of purpose— that existing templates don’t allow for finer-than-monthly intervals.

Especially if the output of the “Generated Expenses for Check Expense Page View” were mapped to the user’s Transaction’s sheet header order, a tool like this could have been helpful in creating sample data.

As part of the Tiller Builder Rewards Program, the Tiller team is awarding you $25 for building this unique tool for developers and builders. Thanks again for sharing your creations and your process with us.

P.S. You mentioned an upcoming Report template…? Any hints on what you will be reporting on?

P.P.S. Your “grain” terminology is intriguing. What is the origin of that?

[1Email2RuleThemAll]

The report I mentioned is more of a projection of recurring expenses, broken into pay periods. Essentially, it uses my Income Settings Sheet to determine your pay date and frequency. You then select a date on the report, as well as how many “paycheck lanes” you want to generate (from 1 to 6). The report will generate all of the recurring expenses expected to be due within the given period, and place them under the paycheck that the expense will be due from. It also uses my Credit Card Settings Sheet to optionally pull in the totals due for each credit card, and maps them to the paycheck they will be due from as well. There’s a section at the top that aggregates the final totals of each period to give an overall total due/available.

Black entries are upcoming expenses that have yet to be paid, and shows the budgeted amount, which is used in the calculation to show what’s available. Green entries are expenses already paid, and shows the actual amount spent. When you mark a transaction as an expense in the new Expense Tag column on the Transactions sheet that the Generated Recurring Expenses Workflow introduces, the expense turns from black to green, and the budgeted amount is replaced with the actual amount of the transaction marked. The amount available is then updated. Red entries are expenses that have not been marked in the Transactions sheet, and the due date has passed. Blue entries are credit card totals due, and can be excluded by the control in cell W3. Additionally (for periods that are current or in the past), you can set the control in cell W4 to Yes to include all transactions not marked as expenses that occurred within that paycheck period, and they will show up in Italicized grey.

This allows you to project the available amount of money that you will have left per paycheck after all of your recurring expenses and credit card debt is covered. This, in turn, allows you to better plan for what you can stash back into savings, and also allows you to see where exactly your budget might be tight. So if you see that you have $400 projected to be available after covering all expenses this check, but only $100 available after the next check, it’s likely a good idea to cover one of the expenses due out of that check to free up some money in that next period. So instead of thinking “I have $400 to spend or save”, you can think “I should earmark some of this money for a future expense and keep my spend/save amount to about $250 instead so next week isn’t so tight.”

[1Email2RuleThemAll]

As for the use of ‘grain’, I’m a Business Intelligence Developer. I deal with databases, cubes, reports in various systems, etc… The word is really just a synonym for ‘granularity’. Data granularity is a measure of the level of detail in a data structure. The “grain” of a report or a table is important in determining what analysis can be performed on the data.

Most of the Tiller foundation workflow is at the monthly grain. This was no good for my reporting purposes, as I wanted to see which expenses were due out of each of my paychecks. Since I was paid more frequently than monthly, I had the need to drill down to a more precise grain. The report I mentioned in my previous reply does that by drilling down to a custom sort of shifting granularity (grain). The granularity of the data is at the “Paycheck” level. If you get paid weekly, the grain of the data is weekly. If you’re paid bi-weekly, the grain is bi-weekly.

With the Generated Recurring Expenses workflow, this type of analysis on expected expenses is no longer cut off at a monthly grain. Projections can be analyzed at any grain you wish. For instance, you can build a report using this workflow that graphs your daily expected burndown of money for the entire year on the left, and another graph with an actual spend-to-date analysis on the right. To your point, this could be used to build out a more robust sample data set for developers. I know I’ve had troubles when building reports because the current sample data is a bit too random (such as paychecks are not entered in any reliable cadence). The use cases are wide, and I feel its value cannot be overstated.

On that note, are there any resources available that explain the reward system better than this snippet?

Prize level will be determined based on the quality, ambition, and value of your solution:

Tiller t-shirt
$25 gift card
1-year Tiller subscription extension
$100 gift card
$500
$1,000

I do appreciate the $25 gift card, but I’m just a little perplexed on how rewards are actually determined. For my first submission, I changed two formulas in an existing workflow and received a $150 reward, which I just don’t feel is even on the same level as this solution in terms of quality, ambition, or value.

[MidnightPrzm]

Not all my credit card accounts are showing up. My Sam’s Club Credit Card, which I just recently added (not sure if that has anything to do with it) is correctly listed on my Tiller Accounts page and is categorized as a liability so I am unsure what is wrong.

[1Email2RuleThemAll]

Please check out the Notes section. The issue is likely due to the institution sending over a Type other than Credit.

@1Email2RuleThemAll

• The accounts that can be selected on the Credit Card Settings sheet are filtered down to accounts with a Type of ‘Credit’ on the Accounts sheet. Unfortunately, account types are set by the institution, and cannot be changed. If your institution sends an account through as a different type (i.e: “OTHER”), there is a section in the hidden area (Y7:Y) that will allow you to select additional types. Adding a type to the list will add all accounts of that type to the dropdown.

[hickson567]

This looks really helpful. Has it been released yet? I like the Sample Report in the Recurring Expenses solution, have you thought about integrating it with Income as well? Then it would give a great overview, while this report you have sampled here provides even greater visibility per pay check.

[1Email2RuleThemAll]

Thanks! The check expenses report has not been released. I’m trying to work out a few areas that I’m not satisfied with. There’s a lot going on, and I’m still trying to tweak some things in it to make it better. I’ll either be adding a lot to this report, or scaling it back before I decide to release.

But I created a sort of “Account Health” report that also uses the Generated Recurring Expenses solution as a base. I’m more happy with the state that one is in, and would likely release that first. It shows your checking, savings, and credit card totals, as well as expenses due by your next check (which disappear after being marked as paid). For savings, it shows the total amount in savings, the amount allocated to savings goals, and the amount available that’s not allocated. For checking, it shows the amount you have in each account, and the amount available after you pay all expenses due by your next paycheck that you set as being paid from those accounts. There’s also an option to change the amount available from the “Total Due” (total after current paycheck expenses are paid) to “Total Pending” (total after current paycheck expenses and all credit card totals are paid).

I have thought about integrating income and expenses into a report, and might work towards making that in the future. The sample report was just a straight pull from the Generated Expenses sheet, ordered by date, as a way to kind of show how to pull the data in. You could easily use my Income Settings sheet to set up your paycheck cadence, then use that to generate a list of paychecks between the start/end dates of the report, then combine the expenses and paychecks together in one list, ordered by date.

[hickson567]

@1Email2RuleThemAll

You could easily use my Income Settings sheet to set up your paycheck cadence, then use that to generate a list of paychecks between the start/end dates of the report, then combine the expenses and paychecks together in one list, ordered by date.

Not sure I am skilled enough for the combo part of this exercise but am going to try =) And looking forward to your release of the “Account Health” and “Check Expenses” sheets in the future.

[1Email2RuleThemAll]

I can try to work on a solution to this soon and send you a personal link via direct message. But to be honest I’m not really feeling up to developing for Tiller right now after being ignored for two weeks. If I build the above report, please do not upload it to the site. It would be for personal use only.

Thank you for showing so much interest in my workflows. I apologize, but any solutions I build in the future will likely not be making their way to Tiller. I’d rather they stay where they’re appreciated.

[jjfelton4]

If you do build this solution, I would also greatly appreciate a DM with that included.

Your workflows are extremely impressive, and the closest tool to planning for the future. My wife and I pay for everything with credit cards, and pay off total balance each statement. The hardest thing to do is visualize how much cash we will have left over to invest or put into savings.

I haven’t quite figured out how to get the Recurring Expenses setup (I’ll be working on that this afternoon). If I were to have a dashboard with an overview of previous/next statements, future paychecks and future expenses incoming, this would be the ticket to finally get our finances organized how my brain likes it and to be able to maximize savings.

Thank you for your hardwork on these solutions!