Update the Google Sheet App After Shifting to Chase Sapphire

[mbcann01]

Overview

2023-06-30: I decided to shift to paying (almost) all bills with the Chase Sapphire card. See #5 for a complete explanation. As a result, I think it may be best to make some changes to the current layout of the Google Sheet App.

Useful websites

• 2023 Budget
• Monthly Budget Example
• How to create Ultimate Personal Budget in ExcelYouTube · The Office LabNov 18, 2022

Layers of complexity

When making these changes, I sort of jumped in the deep end head first. However, it may be worth recognizing that there is a potential stepped approach here too.

1. Keep everything as before - no automatic payments. Just pay with the Chase Sapphire card instead of the bill pay checking account where doing so is possible.
2. Add automatic payments.
3. Use a credit card for discretionary spending too.

Process for paying Chase Sapphire card off

I decided that I'm going to use the Chase Sapphire card to pay for (almost) everything. What does the process to pay it back look like? Due dates for the actual bills don't really make a difference anymore. They will all get paid on time. Now, it's all about paying off the Chase Sapphire card in an optimal way.

I may have to play around with this some, but right now, I'm thinking the best method for paying off the Chase Sapphire card and tracking expenses is to log into my Chase Sapphire account on the 1st and 15th of each month and essentially pay off whatever expenses have accumulated since my last login. Also, use that statement to update the "actual paid" column in the monthly spending spreadsheet (if you keep that format). You will still need to log into some accounts manually to track your balances, and some accounts will only accept payments directly from a bank account.

Changing the layout of the monthly payment spreadsheets

In this new system,

• Bills that are due between the 1st and the 15th will be auto charging to the Chase card, and then I will pay them off on the 15th.
• Bills that are due between the 15th and the end of the month will be auto charging to the Chase card, and then I will pay them off on the 1st of the next month. For example, a bill that is due on June 22 will be auto-charged to the Chase Sapphire card on June 24th. I will log in on July 1st to do bills and pay off that auto-charge using money from my July 1 paycheck.
• Because I'm not paying the bills from the paycheck corresponding to their due date, it may not even make sense to have the budget split into pay periods. You can still sort everything by the payment due date.

What to track

• Most of the bills are charged to the Chase Sapphire card.
• However, only tracking how much I pay to the Chase Sapphire card will make it difficult to understand where my money is going and adjust my budget accordingly.
• I need a system that won't double count payments. For example, if I track how much was paid to each creditor (on the Chase Sapphire card) and then also track how much I paid to the Chase Sapphire card in the same way, then I will essentially double count that payment in my budget.
  • Maybe just don't count the Chase Sapphire card as a payment? Give this some thought.
• I also need a system for knowing how much is in my primary checking account after accounting for pending payments.
  • For example, I have my mortgage payment pending. So, how much do I really have in checking?
• One simple solution would be to turn off autopay and manually pay with the Chase Sapphire card. Maybe I can do better, though.

Specific potential changes

• Create a separate sheet for income and deductions. Take deductions out of the expenses sheet. It can be approximately correct.
• Create a separate sheet for credit cards.
• Create a separate sheet for annual payments.
• Create a separate sheet for savings/retirement accounts.
• Create a separate sheet for payees.
  • Have links to their websites making it easy to pay bills and check balances.
• Create a sheet for balances. Just update once a month on the first.
  • Perhaps put a dummy variable in the payee sheet that can be used to populate a list of balances to update.
• Create a sheet for monthly payments. Make it longitudinal (i.e., not a separate sheet for each month).
  • Ideally, I'd like opening and closing dates to determine whether a bill needs to be paid in a given month or not.
  • Have a dummy variable for Chase Sapphire and for pending. That will help me with double counting payments (countif) and with addressing the "how much is actually in my checking account" problem.
• Create a dashboard with instructions.
• Figure out the most efficient way to track monthly payments for things like gas, water, and electricity.
• Add Factor meals to the budget
• Add tolls to the budget
• Go back to moving money into the bill pay checking account to pay bills that must come out of a bank account (i.e., can't use Sapphire card) and for paying the Sapphire card itself?
• How will I find payments made on my Chase account to check or uncheck pending payments? Can I create folders or categories on Chase's website that I can use to filter transactions?
• Is there a Chase API?
• • I like the date filter toggles in this example solution. How to create Ultimate Personal Budget in ExcelYouTube · The Office LabNov 18, 2022

Left off at

2023-07-15: I'm rolling back some of the changes I made over the last month or two. See an explanation in #5. However, I still need to make some adjustments to the Google Sheet (see below).

Tasks

• Set up the second half of the month bills on autopay
• Set up the first half of the month bills on autopay
• Convert the second half of the month (July) bills back off autopay (most of them).
• Convert the first half of the month (August) bills back off autopay (most of them).
• Create a column indicating if the payment is going through Chase Sapphire or not.
• Figure out the best way to pay Chase Sapphire in one lump sum. I want to transfer funds over to bill pay checking and then pay from there.
• Figure out how to make the credit card sheet useful.