This is so cool! Thanks for sharing it, and sorry your post got briefly caught in our spam filter.

Just keep in mind setting a contribution that avoids negatives in the short term will likely lead to an excessive balance thereafter. Expect to reduce contributions at some point. Suggest re-evaluation after the point of lowest balance.

ukimagic209 said:
that's the entire purpose of the spreadsheet

Yes. 🙂 That is also why I am pointing out that a single contribution level will not typically achieve the goal to "be sure you don't have too much or too little in there".

Since the spreadsheet you've put together only has a single contribution level, I'm suggesting that anyone using it should re-evaluate after every expense is paid as to whether contributions can be decreased to the steady-state level (1/26th the total or cell H5 in your example).

In other words, you've created a focused tool that will solve part of a complex problem. In order to solve the complete problem, it typically needs to be used multiple times.

ukimagic209 said:
maybe your subscription "year" should start right after you pay it

We don't get to pick when the cycle starts. It's solely a function of the expense amounts and timing. If we are lucky, we start saving when the cycle starts, but the odds of that are very poor (1 in 26 under your assumed bi-weekly contributions).

That is precisely why re-evaluation is necessary. The cycle starts after one of the payments, but you don't know which it is without more analysis.

ukimagic209 said:
my tool is not designed to be an answer for all of them

My point is that repeated application compensates for the limitations of your tool. It *can* be an answer for all of them.

Perhaps you've not considered this, but the application goes well beyond Subscriptions and "small" amounts. The same problem occurs for the water bill, electric bill, birthdays scattered throughout the year, even auto fuel (summer costs are higher for many, covid notwithstanding).

At any rate, I hope the discussion benefits someone. Cheers!

dakinemaui said:
Been using YNAB for a few months, and my subscription category has a semi-annual expense: $200 due in Feb. and $200 due in Aug. I've been using a goal for this, so I have $133.32 carrying over from December. I haven't budgeted January yet, but I was just reminded that I have another subscription, $150 [$100] due in March each year, that I need to add to the category. What should I do??

Here are some usage tips for ukimagic209's tool as applied to this scenario. I first entered the three expenses in my example, using the 1st of the month for each. I then copied that block of 3 expenses to immediately follow the original block and edited the dates to be 2022. (This is the first 6 expense rows.)

I then adjusted the range for the Calculated Per Pay formula to reference just the first 3 expenses. (As described in the instructions, double-click and then drag the range definition corner.) Optional, delete the other (example) expenses, but don't delete the entire row!

Then I put the Current Category Balance of 133.32 and pay date of 1/8/20201. The Calculated Per Pay is $19.23, so I put that in the Per Pay Contribution (bold). 

I can immediately see I'm not going to make the Feb. payment at that contribution level. The reason is that calculated level is based on 1/26 the yearly total, and YNAB's goal is based on 1/24 the total AND assumes you'll budget in the target month. This tool highlights that we won't be able to budget in Feb. before the expense is due, which is a common mistake in setting up YNAB goals -- nice bonus. (YNAB's goal is actually 1/12, but then I was dividing that by 2 for a per-check contribution.)

The bottom line that I need to consider, though, is the worst-case in the first year -- the March expense. Now I'm faced with a choice:

1. Adjust the starting balance to put me "on-track" for that nominal contribution
2. Make up the shortfall in the months leading up to the expenses

Let's evaluate approach #1 by driving that worst-case balance up to $0 by adjusting the Current Category Balance upward. Some versions of Excel have a Solver or Goal Seek function to make this easy, but you can just start typing bigger numbers by hand until you find it. The answer is $223.08. That means that in addition to the $19.23 per-check, I need to come up with nearly $90 from somewhere else in the budget to put in the category right now. If that's feasible, great. I'm done since the per-check contributions are at their nominal level. (Well, at least I'm done until the bills increase!)

OTOH, let's assume I can't pull that $90 from my other categories right now, leading to approach #2. Reset the Current Category Balance back to the original $133.32. Goal-seek or manually iterate on the per-check contribution until the worst-case balance (in March) is $0. The answer is $41.67 per check. That's easier for me to do right now vs. robbing other categories, so I'll do that.

Hang on, though... The category balance is over $800 by the end of next year! That's because the $41.67 contributions are excessive once I've "gotten over the hump". The hump is that March expense. Therefore, I'd want to re-run this spreadsheet in March. Redoing the analysis with the then-current balance of $0 and pay-date of 3/5, I find that the nominal check contribution of $19.23 per-check is perfect. Since contributions from March onward are at nominal levels, I'm done (until bills increase).

In summary, you can deal with shortages up front or over time. If you choose the latter, you will need to periodically reduce contributions until they are at the nominal level. (Just an FYI, other scenarios may not reduce to the nominal contribution level after the first re-evaluation. Like many things, it just depends. However, using the tool in this way will make sure you don't get caught short or tie up too much.)

Edit: I just realized I analyzed things for a $100 March expense. I've changed the example scenario to match rather than re-do the analysis. That larger expense would obviously make it even harder to make up the shortage up front.