Make the Savings & Debt template sheet more exacting

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

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