Make the Savings & Debt template sheet more exacting

[1Email2RuleThemAll]

Overview

What is the goal of your workflow? What problem does it solve?

For users of the original Savings & Debt template, I have made two important changes to make the savings workflow more exacting:

• I have built a paycheck modeler and linked it to the Savings Req’d Budget field to account for paycheck timing.
• I have also modified the Req’d Budget formula to fully fund savings goals by the Deadline date (rather than the end of the Deadline month)

By default, the available amount of a savings goal in the Savings & Debt sheet will update with the entire monthly total that you allocate to that goal at the beginning of each month. That doesn’t make much sense to me, as my savings goals are funded by my paychecks, which I receive 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 my custom 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.

How did you come up with the idea for your workflow?
I build the Income Settings sheet for a custom finance tracker that I created back in 2019. Once I found Tiller and went through the trial, I was sold and converted to using the foundation template. But I absolutely had to have a few aspects of my old tracker. Once I recreated this part, I noticed that I had all the tools on this sheet to fix the behavior I didn’t like in Savings & Debt.

Describe your workflow:
I’ve added a new sheet called ‘Income Settings’ where you enter in your 1st pay date (or any past true pay date), and pay frequency, as well as choose the category that you categorize your paycheck under in Transactions (and some other stuff that I have for other custom solutions).

In the hidden section on this sheet, it calculates how many paychecks you should expect to get in the current month (monthly = 1, Semi-monthly = 2, bi-weekly = 2 or 3, and weekly = 4 or 5 depending on how the days fall), as well as queries against the transactions table to get a count of how many paychecks you’ve categorized under the above category this month.

I’ve changed two of the formulas on the Savings & Debt sheet to look at these two fields, and assess the current month’s total that is ‘available’ based on paychecks expected & accrued this month, instead of being a ‘through end-of-month’ balance. The first formula I manipulated was in column AC7 (‘total budget’ under the Savings Categories (through period) section).

I changed the formula in cell AC7 from:

=if(ISBLANK($Z7),iferror(1/0),iferror(SUM(OFFSET(Categories!$A$1,match($Z7,INDIRECT("Categories!$"&$AX$27&"$2:$"&$AX$27),0),$AX$7-1,1,$AX$8-$AX$7+1)),0))

To:

=if(ISBLANK($Z7),iferror(1/0),iferror(SUM(OFFSET(Categories!$A$1,match($Z7,INDIRECT("Categories!$"&$AX$27&"$2:$"&$AX$27),0),$AX$7-1,1,$AX$8-$AX$7)) + (($AB7/'Income Settings'!$X$37) * 'Income Settings'!$X$38) ,0))

The only part that is different is at the end. I am removing the +1 to remove the current period’s budget from the calculation. I am then taking the monthly total, dividing it by the expected paychecks this month, and then multiplying that value by how many checks have been received this month.

From:

$AX$8-$AX$7+1)),0))

To:

$AX$8-$AX$7)) + (($AB7/'Income Settings'!$X$37) * 'Income Settings'!$X$38) ,0))

That assigns the ‘to date’ value for the savings goal, and only updates once you have marked that you have received the income to do so (by categorizing a transaction under the selected ‘Net Pay’ category on the Income Settings sheet). You will have to enter the calculation into AC7, then drag it all the way down via the fill handle to change the rest of the sheet.

This does mess up one value in the Savings & Debt sheet, which is in cell J6, the req’d budget calculation. This value is calculated by taking the remaining balance, adding the current period budget, and dividing by the periods left. Since the ‘amount remaining’ in Column F changed (due to part of the current month balance moving to the ‘Available’ balance), the calculation was thrown off.

={"REQ'D BUDGET";arrayformula(if(isblank(A7:A)+ISBLANK(D7:D)+(E7:E>=C7:C),iferror(1/0),Iferror(ROUNDUP((F7:F+I7:I)/(H7:H+1),2))))}

To fix this, I replaced it with this formula:

={"REQ'D BUDGET";arrayformula(if(isblank(A7:A)+ISBLANK(D7:D)+(E7:E>=C7:C),iferror(1/0),Iferror(ROUNDUP((F7:F + I7:I + (($I7:$I/'Income Settings'!$X$37) * 'Income Settings'!$X$38))/(H7:H+1),2))))}

Again, the end is the only thing different. The new formula essentially rebalances that calculation above by adding back in the value that was taken out and moved to ‘Available’

Iferror(ROUNDUP((F7:F+I7:I)/(H7:H+1),2))))}

vs.

Iferror(ROUNDUP((F7:F + I7:I + (($I7:$I/'Income Settings'!$X$37) * 'Income Settings'!$X$38))/(H7:H+1),2))))}

This solution assumes you have one job that is paid weekly, bi-weekly, semi-monthly, or monthly, and that you fund your savings goals from that pay source. You don’t have to fund it from there, but the solution will still just break down the total monthly into chucks instead of the full month balance based on the input in income settings.

Installation

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

• Categories
• Transactions

Non-foundation templates needed:

• Savings & Debt template
• Income Settings Custom sheet by me (@1email2rulethemall)

To install:

1. Open the attached workflow
2. Right click the Income Settings sheet
3. Copy to your Tiller solution
4. Rename from Copy of Income Settings to Income Settings
5. Clear all sample values in the green cells.
   – Do not overwrite or clear any of the grey cells. They contain needed formulas.
6. Setup Income Settings sheet by filling in the green fields for your current income. You can enter past periods if you would like, but it is not necessary for this solution.
7. Open Savings & Debt sheet

• In cell AC7, paste the following formula, then drag the fill handle in AC7 to the bottom of the page.

=if(ISBLANK($Z7),iferror(1/0),iferror(SUM(OFFSET(Categories!$A$1,match($Z7,INDIRECT("Categories!$"&$AX$27&"$2:$"&$AX$27),0),$AX$7-1,1,$AX$8-$AX$7)) + (($AB7/'Income Settings'!$X$37) * 'Income Settings'!$X$38) ,0))

• In Savings & Debt, replace the formula in cell J6 with the following:

={"REQ'D BUDGET";arrayformula(if(isblank(A7:A)+ISBLANK(D7:D)+(E7:E>=C7:C),iferror(1/0),Iferror(ROUNDUP((F7:F + I7:I + (($I7:$I/'Income Settings'!$X$37) * 'Income Settings'!$X$38))/(H7:H+1),2))))}

9. (Optional) Change text in AC6 to read “Total to date”.

Setup

Once you enter in your current Income Settings and replace the two formulas above, you are good to go!

Usage

This is a passive solution, for the most part. The Income Settings 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 if any other solutions use this sheet and expects that 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.

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

Thanks for reading!

FAQ

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

Working Solution Link

Savings & Debt Intra-Month Tracking

Version changes

• 1.0: Original Income Settings solution. Worked with Weekly and Bi-weekly pay frequencies only.
• 2.0: Added Monthly and Semi-monthly pay frequencies. Major formatting and logic changes.
  – To update from 1.0, rename the current Income Settings sheet, follow install instructions above, and delete old sheet after moving all data in green cells over. Make sure you change the formulas in Savings & Debt again.

[heather]

Hi @1Email2RuleThemAll - this is perfectly fine to put in the show & tell category as is I’ve just updated the title of your topic and added it to the Google Sheets Show & Tell Category.

Anytime you want to post there, just create a new topic and it will give you some prompts to answer

[1Email2RuleThemAll]

Thanks for that info! I hope to add a few of my solutions there in the coming weeks. This little solution actually came about from me implementing a few custom sheets into Tiller that I created years ago to track things (been using Tiller for about 2 months now and LOVE it!). One of the things I couldn’t live without, though, was my ‘paycheck expense planner’, which I’ve rebuilt to now work with my Tiller solution. I built ‘Income Settings’ initially for that, but then afterwards saw this little thing in Savings & Debt could be solved using it as well

I have a few more solutions I worked out for myself, but think I could make even better. Once they’re in a place I’m happy with, I’d love to post those too. I also read a post from @randy about best practices when building solutions, and realized I am not accounting for when a user overrides the account group in a few formulas. Gotta fix that too, haha

[randy]

This is really cool, @1Email2RuleThemAll. I’m sorry it has taken me so long to dig into it. I really like it when community members dive into something that works and build on it and make it their own. It’s a great way for us to learn from each other in how we build, how we think about workflows, how we think about user interfaces, and, ultimately, how we think about finances.

The problem you are solving is a great refinement on the Savings & Debt solution. It essentially makes intra-month tracking more granular and exacting. I’m impressed that you have worked through the formulas and figured out how to modify them and make them work for you.

Regarding your initial questions… Most builders initially share public copies of their creations (without personal data) with a link to the spreadsheet and some instructions for setup in the Show & Tell subcategory. It looks like you might have posted initially in a different category and missed the prompts in the Show & Tell topic template.

If there is interest in what you’ve built from the community in this topic, you can consider submitting your solution for inclusion in the Tiller Community Solutions add-on (so other users can “just hit a button and the sheets download into my solution”.)

Your instructions and explanations for the changes are excellent but it would probably be helpful if you could share the additional sheets that you make reference to (probably in a standalone public spreadsheet that is fully functional for this workflow but is stripped of unnecessary cruft).

Would you be willing to do that?

P.S. I’m so happy to hear that you’re excited about your Tiller subscription. Thanks for sharing that.

[1Email2RuleThemAll]

Thanks for taking the time to look over this, @randy! I was actually going to start building a version with scrubbed data when I ran across your sample data sheet: Tiller Feeds Sample Data (for Builders)

Here’s a link to my working solution. It works for weekly and bi-weekly pay frequencies:

Savings & Debt Intra-Month Tracking

I was going to add it to the original post and also go through and edit what I originally wrote to be in the Show & Tell template format so that it’s better organized, but it does not appear as though I can edit the original post. I am also going to add a post that goes more into the added ‘Income Settings’ sheet soon, in case anybody is wondering how I’m doing anything what I’m doing (I think it’s neat).

I also noticed that the current Savings & Debt logic derives the required budget amount by including the month that is set as the deadline as a period available for saving. It is more intuitive to me that the deadline for funding be the beginning of the month that is set, not the end. The goal should be funded by the date shown.

So for a trip I’m taking on August 2nd, 2023, I want to save $4,500, starting in March with a deadline of Aug 2023. The default logic tells me that I need to save $750 a month for that to happen. But that’s taking the month of August into account as a period ($4,500/6 months). My true window of saving, though, is March to the end of July. So it should be $4,500/5 months = $900 a month.

I could change the deadline above to be July 2023, but again I find it more intuitive that the goal be funded by the date set. My solution changes the required budget to be in line with that logic. If you prefer to think of it the original way (through the deadline month), you would need to change the formula in J7 from:

={"REQ'D BUDGET";arrayformula(if(isblank(A7:A)+ISBLANK(D7:D)+(E7:E>=C7:C),iferror(1/0),Iferror(ROUNDUP((F7:F + I7:I + (($I7:$I/'Income Settings'!$U$26) * 'Income Settings'!$U$27))/(H7:H+1),2))))}

to:

={"REQ'D BUDGET";arrayformula(if(isblank(A7:A)+ISBLANK(D7:D)+(E7:E>=C7:C),iferror(1/0),Iferror(ROUNDUP((F7:F + (($I7:$I/'Income Settings'!$U$26) * 'Income Settings'!$U$27))/(H7:H+1),2))))}

[randy]

Thanks for sharing the rest of this solution, @1Email2RuleThemAll. I’m impressed at how you dove into the original template, found the formulas that needed changing to realize your vision, and then did it so seamlessly. Well done.

I think your Income Settings sheet is great. I like how you partitioned it off as a standalone module. I can envision it being useful to others for other applications. You did a nice job with the structure and organization. It looks like something someone well versed in the Tiller conventions would build.

You’re just changing one cell, J7, in the Savings & Debt template, right? With such an isolated change, it probably makes sense to publish your solution essentially as a shared Income Settings sheet with instructions do download the latest version of the Savings & Debt template and have the user make that one formula change to the master. This means that the Savings & Debt template can be maintained and versioned without being forked for such an isolated change.

When you update the instructions, just make sure the headline is crystal clear on the unique value your solution brings… so people can tell quickly whether they should use the Tiller Labs version or yours. I think you have a strong case to make. I think it is something like:

For users of the original Savings & Debt template, I have made two important changes to make the savings workflow more exacting:

• I have built a paycheck modeler and linked it to the Savings Req’d Budget field to account for paycheck timing.
• I have also modified the Req’d Budget formula to fully fund savings goals by the Deadline date (rather than the end of the Deadline month)

If you can concisely hint at these differences in the title, even better.

As part of the Tiller Builder Rewards Program , the Tiller team is excited to award you $150 for building and sharing this template for use by the Tiller community.

Congratulations on a great contribution, @1Email2RuleThemAll.
Randy

P.S. I’m sorry you’re not able to rework your original post. We use a Discourse community and users accrue increasing trust levels through visits and activity. I think because you are a newer user, a Rising Star, you didn’t yet have permissions to edit even your own posts. I just bumped your trust level. Do you want to try editing the topmost post again?

[1Email2RuleThemAll]

Oh wow, thanks @randy and the Tiller team! I greatly appreciate it! I am able to edit the original post now, so I’ll be doing that in the near future when I get the time.

For the solution, I’m changing two formulas in the Savings & Debt sheet. One formula is in J7 (Req’d Budget) and the other is in AC7:AC. For the latter, you would just paste my formula into AC7 and use the fill handle and drag to the end of the column to populate the rest of the column cells. Should be an easy mod for anybody wanting to implement it.

I initially designed the Income Settings sheet for another (much larger) solution that I hope to offer to the community soon. Once I implemented it into Tiller, I noticed I could use that sheet to manipulate Savings & Debt how I wanted it. I’m sure there are uses for this sheet beyond what I’m using it for.