Modified Savings & Debt sheet to see if you're on track

For Google Sheets: I’m doing something that manipulates two things in the Savings & Debt solution (Docs: Savings Goal & Debt Tracking Sheet), as well as adds a new sheet to the workbook (full explanation below). I’ve never offered a solution to the community before, and don’t know how to go about it. I know for users that want to implement this (might be none, as it’s just fixing an issue that bugs me personally), the easiest way is to add the new sheet, then copy the two formulas I manipulated and drag it to the bottom of the Savings & Debt sheet to fill the replace the rest of the formulas. But do I upload a solution with the Savings & Debt sheet already fixed as well as the new sheet, or just the new sheet? I know when I integrate some community solutions into my own, I just hit a button and the sheets download into my solution. But with other solutions I’ve come across, I’ve had to download a separate workbook and manually add the sheets/rename to remove “Copy of”. Just want to get some insight into how it’s all done. Thanks!

Full explanation of what I’m doing. No real need to read below unless interested:

I really like the ‘Savings & Debt’ template, but I do not like that it shows that the full amount set for the current month is available right at the beginning of the month (and thus the full amount is added to the ‘allocated to goals’ total for the account its tied to). It’s accounting for ‘future money’ that I do not have yet. It makes sense on the Savings Budget sheet where you are tracking what you have available and trying to stay under that amount. But not as much so on Savings & Debt, where you’re trying to see if you’re on track with a budget goal.

I set savings goals to be funded from savings accounts. When I get paid, I put what I need into the savings account from my checking to make the budget that I set for the month. (So with a monthly budget goal of $600: If I get 2 checks in a month, I move $300 to savings. If I get 3 checks, I move $200, etc…). When I get my final check of the month and transfer the final amount, the savings goal finally says I’m current on funding. But I show as underfunded until that final transfer. Then, on the 1st of the month, it shows underfunded again until I meet the monthly goal. It’s a vicious cycle that robs me of the serotonin boost of seeing that I’m on track. I built a home page that displays my savings account balances, and the amount allocated to goals assigned to that account, displaying a red value if it’s underfunded. So the issue is even more in-my-face.

To fix this, 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, 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 essentially 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)) + (($AB7/'Income Settings'!$U$26) * 'Income Settings'!$U$27) ,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.



$AX$8-$AX$7)) + (($AB7/'Income Settings'!$U$26) * 'Income Settings'!$U$27) ,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. You would have to enter the calculation into AC7, then drag it all the way down 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'!$U$26) * 'Income Settings'!$U$27))/(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 + (($I7:$I/'Income Settings'!$U$26) * 'Income Settings'!$U$27))/(H7:H+1),2))))}

This solution assumes you have one job that is paid weekly, bi-weekly, 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.

Full example:
So for a monthly goal of $600, instead of adding $600 to the available balance on the 1st, it adds nothing to start. You set up that you’re paid bi-weekly, and put in that your 1st check date was March 3, 2023, and that you are paid bi-weekly on Friday. My code would calculate that you get three checks in March (3rd, 17th, 31st), and two in April (14th, 28th).

Say we’re back in March. March 1st and 2nd, nothing is added for this month. The Savings & Debt sheet picks up that you are supposed to be paid three times this month, and you’ve received none. So you’re “On track” as far as funding goals go. Then on March 3rd, you get paid. You tag the transaction as ‘Income - Net Pay’ (or whatever you use for paycheck income). Savings & Debt sees you will receive 3 checks this month, and have now received one. So the ‘available’ balance will increase by $200 (1/3rd of the total monthly goal). It will update each time you get paid to reflect that you now need to put more money back for the goal (2nd time $400 is available, 3rd, all $600 available). Then on April 1st, it resets. It sees you are supposed to get paid twice this month, and have been paid zero times. The available balance will only consist of the balance from prior months, nothing from this month. It will show as on track until the 14th (your 1st check in April). It will then change to say you have all of the prior month’s budgets available, plus $300 of the current months budget ‘available’ (1/2 of the monthly goal amount).

Hi @1Email2RuleThemAll - this is perfectly fine to put in the show & tell category as is :slight_smile: 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 :slight_smile:

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 :stuck_out_tongue:

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


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.

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:

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))))}


={"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))))}

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.

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?

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.