Here's a Spreadsheet to Audit Your "Subscriptions" Category
Like I assume many of you do, I have a category that combines a number of payments for annual subscriptions, all which come out at various times during the year. To make sure I'm properly funding the category, I just divide the annual total by the number of my paychecks (paid biweekly). Subscriptions change, so there are times I just want to be sure that each payment will be covered at the time it's due, or to just make sure the category isn't overfunded (and I could move the excess somewhere else).
I came up with an Excel spreadsheet that does this for me. Just fill in your subscriptions with their due dates and the costs, along with your category starting balance, next pay date, pay frequency, how much you're funding the category and it will show the category balance after each subscription is paid. Now you can be sure you don't have too much or too little in there and it's being properly funded 👍.
Subscription Auditor (OneDrive link)
Note that "Per Pay Contribution" is a manual entry field, so put in there whatever you want to contribute to the category each pay. The calculated field above that is based on 26 pays per year, so you might want to adjust that.
⭐This spreadsheet something that I created for myself and is presented as-is. Please let me know if you find any flaws!
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.
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.
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!
a more complex tool could be developed that could help you ride the category's highs and lows throughout the year (reducing your contributions when there is little coming out and ramping them up before a big one)
Ha, ha. Ramping up -- a.k.a., "scrambling" -- is what many people without YNAB do. I would definitely NOT advocate such a tool. Things are far easier when contributions are stable. This lessens the impact on other categories in the budget.
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:
- Adjust the starting balance to put me "on-track" for that nominal contribution
- 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.