Documentation: Recurring Expense Entry sheet

[1Email2RuleThemAll]

Overview

This is to provide a central install path & documentation for my custom Recurring Expense Entry sheet that is used in some of my solutions.

What this sheet does

The Recurring Expense Entry sheet takes a user supplied list of recurring expenses and displays a few metrics for each expense pertaining to the current month. It also utilizes my Credit Card Settings sheet to display the pay method that is set for a credit card, if that credit card is set as the pay method for an expense.

As a stand-alone, that’s about all it does. However, this sheet is used as a data source for multiple instances of another custom sheet that I designed, Generated Expenses Workflow for Developers, which I can use to project the expenses entered across any timeframe I wish, calculating precise due dates based on 10 different frequency grains.

Custom Scripts, Formulas, and Additions

Once the Recurring Expense Entry sheet is installed, an extra column (named Expense Tag) will need to be added to the Transactions sheet, and a data validation formula set up on that column to reference the Expense column on the new sheet. This will be used in other solutions that use this sheet to tie the transaction back to the expense, and mark it as paid. Instructions for doing this is below in the Installation section.

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:

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

Tiller Community Solutions

There are no required Tiller Community Solutions

Optional

Tiller Community Solutions

The following Tiller community solutions are optional, but other solutions that I develop may require them (for instructions on how to add/upgrade solutions after install, see the Version Changes section below):

• Credit Card Settings by myself - If a credit card is set as the pay method for an expense, this sheet is used to pull back the checking account that is set as the pay method of that credit card.

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.

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

• Generated Expenses Workflow for Developers - I released this solution to allow others to build their own (personal use) reports using the automatic recurring expense logic that I developed.

Installation

Dependencies must be present in your solution before installation. The Tiller core sheets should already be present, but you have to ensure that any required community built solutions present above are installed (click on the solution name to be taken to its install documentation). Afterward, the below steps must be followed in the order listed.

To Install

1. Add a column to your Transactions sheet named Expense Tag
   a. Right-click on Column E on the Transactions sheet
   b. Select “+ Insert 1 column right”
   c. Label the new column Expense Tag. This should be column F
2. Add the Recurring Expense Entry sheet
   a. Open the attached workflow
   b. Right click on the Recurring Expense Entry sheet and copy to your Tiller solution
   c. Rename from Copy of Recurring Expense Entry to Recurring Expense Entry
   d. Populate Recurring Expense Entry sheet fields that are green (See setup below).
3. Set up Data Validation dropdown on Transactions sheet in order to easily select expenses
   a. While on the Transactions sheet, go to Data > Data Validation in the top toolbar
   b. Select “+ Add Rule” to add a new validation rule
   c. Enter the following rule for the new Expense Tag column, which should be F2:F:
   • ='Recurring Expense Entry'!$B$7:$B
     • Column B should be the Expense name on Recurring Expense Entry
   • Once you enter the formula and click out of the box, a list of recurring expenses should populate. Scroll to the bottom of this list and click Advanced Options and set the display style to Arrow.
4. Once the validation is set up, and the dropdown is confirmed to be populating with the expenses entered in the Recurring Expense Entry sheet, this column can be moved to wherever you want in the Transactions sheet. It only needs to be set as column F until validation is in place. Afterward, the validation formula will update automatically to reference wherever you place it.

To Uninstall

1. Right-click and delete Recurring Expense Entry.
2. Delete the added Expense Tag column on the Transactions sheet.

Setup

Recurring Expense Entry

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. If you want to go from active to inactive, you can simply hit backspace while the cell is highlighted.
2. Expense - This is the name of the recurring expense. It is used as the value you would tag the transaction with (via a dropdown) when it comes into the Transactions sheet.
   • Do not use a Pipe (|) or at sign (@) in the expense name.
   • You can have expenses with the same name, but they must not also have the same start date. This is to allow changes to a recurring expense while keeping record of the old settings. If duplicate expenses exist with the same start date, the names for both records will turn yellow.
   • If you make an expense with the same name as an existing expense, set an End Date value for the existing (old) record that is earlier than the new expense’s Start Date. It is not recommended to have two expenses with the same name active during the same timespan.
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 weekday (Monday, Tuesday, etc…) every week
   • Bi-Weekly: Same weekday every other week
   • Semi-Monthly: Twice a month on set days
     • 1st & 15th, 14th & 22nd, etc…
   • Monthly: Once a month on the same day
     • 1/13/2023, 2/13/2023, 3/13/2023, etc…
   • Bi-Monthly: Every other month on the same day
     • 1/13/2023, 3/13/2023, 5/13/2023, etc…
   • Quarterly: Every 3rd month on the same date
     • 1/13/2023, 4/13/2023, 7/13/2023, 10/13/2023, etc…
   • Semi-Annually: Twice a year: once on the same start date, and another 6 months later
     • 3/13/2023, 9/13/2023, 3/13/2024, 9/13/2024, etc…
   • Annually: Yearly on the same date
     • 3/13/2023, 3/13/2024, 3/13/2025, etc…
   • Bi-Annually: Every other year on the same date
     • 3/13/2023, 3/13/2025, 3/13/2027, etc…
4. Start Date - The first date the expense is due.
   • 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 12/5/2018. Day value must be the 5th in this instance.
5. End Date - Optional for all frequencies except Semi-Monthly. If you enter an expense with the same name of another expense, the old expense should have an end date earlier than the new expense start date.
   • 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 (see Notes if an account is missing from the selection)
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.
4. Master Pay Method - If the Pay Method that you selected 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 in the Credit Card Settings sheet. Otherwise, it will be the pay method that you chose.

Usage

This sheet is used as a data source for another custom sheet that I designed, Generated Expenses, which I can use to project the expenses entered across any timeframe I wish, calculating precise due dates based on 10 different frequency grains.

There is no real upkeep for this sheet outside of adding/updating expenses and credit cards, then tagging expenses with their corresponding transaction in the Transactions sheet.

Permissions

Is it ok for others to copy, use, and modify your workflow?
This solution is free for personal use. I do not give permissions to modify or re-upload my work, in part or in full, to Tiller or any other site.

Notes

Expense Name Limitations

You can have expenses with the same name, but they must not also have the same start date. This is to allow changes to a recurring expense while keeping record of the old settings. If duplicate expenses exist with the same start date, the names for both records will turn yellow.

• Do not use a Pipe (|) or at sign (@) in the expense name. This will cause errors for solutions that will use this sheet.
• If you make an expense with the same name as an existing expense, set an End Date value for the existing (old) record. It is not recommended to have two expenses with the same name active during the same timespan.

Frequency

There are 10 frequencies. They all function similarly, using the start date to set when the subsequent expenses will be due, with an optional end date. The special case being for semi-monthly, where the end date is required.

• If an expense is due semi-monthly (twice a month on set days like the 5th & 20th), then:
  • The Start Date needs to be set to the first day of the month that needs paid.
    • So if something is due on the 5th and 20th, the start date should be like 12/5/2018. The day value must be the 5th in this instance.
  • The End Date needs to be set for the second day of the month that needs paid.
    • If no end date is selected, the last day of each month will be used instead.
    • If the expense has a real end date, enter the month and year of the end date, using the second day that needs paid as the last day.
      • So if something is due on the 5th and 20th, the end date would be like 3/20/2025. The day value must be the 20th in this instance.
    • If there is no end date, set the month to 12 and year to 2999 (or some other far off date).
      • So if something is due on the 5th and 20th, the end date would be like 12/20/2999

Pay Method - Missing Accounts

This is a dropdown list that is being populated by the Expense Payment Methods list in the hidden section of the sheet. It should be set to the credit card or checking account responsible for paying the expense. The available accounts to select from, by default, are all accounts on the Accounts sheet with a type of either “Checking” or “Credit” that are not set to hidden.

• There is an option in the hidden part of the sheet to Exclude Hidden Accounts (by default, columns X4:X5). To allow hidden accounts to be selected, set Exclude Hidden Accounts to No.
• The Account Type is imported from the financial institution. Unfortunately, sometimes institutions send over the wrong type for an account, or lump an account under a type of “Other”. In this instance a checking or credit card account that you want to set as the payment method for an expense might not show up.
  • If this occurs, you can pull in other Account Types into the list using the Account Type Override column in the hidden section of the sheet (by default, column V6:V).
  • When you add other types, there is the possibility that accounts will be pulled in that you would not want to set as the method in which you intend to pay an expense. If you use this feature, make sure that the accounts you are setting make sense as pay methods.
    • If an installment loan, like a car loan, has a type of “Other”, and you allow that type in the list (maybe because a credit card you wanted to set also had the type of “Other”, for instance), then you could set the car loan as the pay method for your groceries, which does not make sense.

Occurrences in {month}/Total Monthly Amount - minor bug

If the expense is set to end in the current month, or started in the current month, these two fields will likely be off for that expense. The calculation is looking for how many times that this expense will occur in the entire month, and not taking into account start/end dates like starting halfway through the month. Adding that in may be possible, but would be challenging and have little payoff. The totals will be correct for the every full month that the expense will be active.

FAQ

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

Solution Link

Recurring Expense Entry

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

When a base sheet has a minor change, it will usually not cause any issues in this sheet. If it does, a major or minor version change update in the below To This Solution section will indicate the steps necessary to take in order to get everything up-to-date. If a major update was made to a base sheet that required it to be reinstalled, some reference links could break in this sheet. To re-establish these references and get everything back in order, follow the below steps after reinstalling the base sheet.

Fix #REF errors arising from reinstalling a base sheet

This simple fix will also resolve any issues that would arise if you installed this solution before any of the sheets that it depends on.

Credit Card Settings

When you have to re-install the Credit Card Settings sheet, follow the below steps afterward to reinforce any references that might have broken. This will also fix any issues that would arise if you installed this solution before the Credit Card Settings sheet.

1. Navigate to to the reference columns in the hidden section of the sheet
   a. Click on the + sign above column N.
   
   b. Click on the horizontal arrow to expand the hidden section of the sheet.
   
2. Make sure that Y21:AA35 does not contain #REF errors
   a. If #REF errors are present, double-click to enter into cell X20
   • This should be the cell holding the text Credit Card Settings
   • Do not cut the entire cell, or this will not work. You need to double-click into the cell first
   • Press CTRL+A, then CTRL+X to cut the Credit Card Settings text from the box.
     • Conversely, highlight all text, right-click, and select Cut.
3. Press enter to save the changes and empty the cell. Double-click back into the same cell (X20), paste what you just cut back in, and hit enter again to save it tot he cell.
   • Once this is done, Google will have an “oh yeah!” moment and recognize the new base sheet is present. The #REF errors, and any issues you saw on the sheet, should be resolved.

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 Recurring Expense Entry sheet to Recurring Expense Entry Old
2. Follow the install instructions above, starting at #2 - Add the Recurring Expense Entry sheet.
   Note: You will need to set up the data validation to tie Expense Tag to the new sheet’s Expense column again, but you will not need to re-add the column.
3. Set up any Account Type Overrides that were present before (see notes section below)
4. 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.
5. Delete the old sheet, Recurring Expense Entry Old
6. 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 (pretty much the same process as outlined in the Fix #REF errors arising from reinstalling a base sheet section above.

Version 2.0

I added the ability to allow for multiple expenses with the same name, as long as they have different start dates. This is to keep record of the values of an expense over time for potential historical reporting purposes. With version 1.0, you would have to replace any old values with new values for an expense instead of closing the record out and creating a new row.

• To upgrade, follow the instructions above to re-install the sheet