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!

31replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • This is so cool! Thanks for sharing it, and sorry your post got briefly caught in our spam filter.

    Like 1
    • Matthew Glad you think it's useful!  Also, the spam thing must be why I wasn't notified of any replies 🙂.

    • Matthew If you click on the link, are you able to download it for yourself?  I was assuming that other folks could, but I don't share too much from my OneDrive so I wasn't sure.

    • ukimagic209 When I clicked on it, it was a "read only" file. But it looks great.

    • B's Gambit If you go to the File menu in the upper-left, can you choose "Save As"?

    • ukimagic209 As I've stated elsewhere, I'm not that great with excel :) So something learned at 0430 is a good way to start the day! I'll play around with it after work, but you are correct, it did download just fine.

      Like 1
    • B's Gambit I just updated the spreadsheet slightly, with some instructions.  Please re-download and let me know what you think!

      I also updated the calculated per-pay field to take into account how many pays you get per year, based on how often you're paid.  Previously this was hardcoded to 26 pays (bi-weekly).

  • Sure would be nice if YNAB had such a capability... hint, hint.

    Like 3
  • 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.

    • dakinemaui Thanks for bringing that up.  I should have mentioned that the balances in red are not negative; they are just the lowest balances.  I used Excel's built-in conditional formatting, which goes from green to yellow to red, highest to lowest.  I can see now how it could easily look like negative balances - whoops 🤦🏻‍♂️.

    • ukimagic209 I wasn't talking about the red formatting, but actual negative balances. The reality is the nominal contribution (1/26 of the yearly total) is likely to leave you short of funds because you don't necessarily have all year to save for a yearly expense. That means larger than nominal contributions are necessary in the short term. Continuing to put that much in, however, will lead to a balance that is larger than necessary.

    • dakinemaui I understand what you're saying, but that's the entire purpose of the spreadsheet; put in where the category is now and your contributions and see if it will cover your subscriptions when they come due.  I think your scenario would be if you began the year at zero and put in that monthly amount, which I agree would most likely result in negative balances somewhere along the way *cough* Amazon Prime *cough*.  That concern is one of the reasons I created this for myself.

    • ukimagic209 This might clarify the concept a bit:

      And this might be interesting to check out as well:

      Obviously, it's not new territory, which is one reason it's good you've brought it up again.  There is still a need. 😄

      Like 1
    • Move Light Sound Life Interesting! Thanks for letting me know.  I agree, there is definitely a need out there.  Lots of folks combine expenses throughout the year into a single category.

  • 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.

    Like 1
    • To illustrate, here's a typical scenario of someone coming across this thread. ukimagic209 , if you have the time would you please describe how they should use your tool in the following scenario?

      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 due in March each year, that I need to add to the category. What should I do??

    • dakinemaui I agree with you about recalculating, but every time there is a change to your subscriptions, not every time you make a payment.  I believe the scenario you're concerned about is, in my terms, "not making it over the hump", meaning you have a large payment coming up before your contributions can cover it, say like my Amazon Prime that's due in late January.  In that case:

      1.  Increase your starting balance amount (add more to subscriptions category before that payment comes due) to cover that early large subscription.
      2. Adjust the contribution amount (it's a manual entry field) so that your remaining subscriptions are covered.
      3. (Optional) extend your subscription list into the following year, as I did, to ensure you will cover that bigger one when it comes due next time.

      Of course, you can always recalculate after you make it over the hump, or maybe your subscription "year" should start right after you pay it, so you can slowly build up the category for next time.

    • ukimagic209 Yes, it's about getting over the hump. In an ideal world, you would adjust the starting amount, allowing the ongoing contributions to be at the steady-state level. Not everyone can do that, nor is that wise if that up-front money could be put to better use in some other category (e.g., debt reduction).

      Yes, the analysis needs to be done when amounts change. However, if the monthly contribution is larger than nominal, it also needs to be done again after every payment to ensure too much is not put into the category.

      (Other calculations could tell you exactly when to change the monthly contribution, but those are not implemented in your tool. Thus, repeated evaluation is necessary.)

    • dakinemaui I still disagree.  The tool is there to help you look ahead and adjust what you need in order to make sure everything is covered.  If you can't increase the initial amount, the tool helps you to see you won't make one of the payments and then you can decide what to do.  Maybe that expense needs to be in its own category.  If you are contributing too much, then the tool shows you that, and you can make decisions on your own.  I think what you're suggesting by recalculating after every payment is changing the premise that the tool is built upon and therefore you probably shouldn't be using it.  I completely see your point of, at times, having too much money in the category, which might be better spent elsewhere but, again, then maybe combining a number of relatively small, planned, expenses into a single category isn't what you should be doing.

      For myself, I just want to be able to set up a per-paycheck contribution to my subscriptions account and know that when every subscription comes due, the money's there.  I accept the fact that, at times, there will be excess money in the category and I'm okay with that.  If there are others out there with the same mindset, then this is a great tool for them.  If not, that's okay!  Maybe using YNAB's built-in goals is a better choice.

  • 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.

    • dakinemaui What I meant was, maybe you should use the tool to recalculate your subscription category balance and contribution after your big expense is paid, that way you have twelve months to make sure the category is funded by the time it rolls around again.
      I think we're getting too deep into scenarios.  There are of course lots of possibilities out there for a combined category like this and my tool is not designed to be an answer for all of them.  It really is just a way to look ahead and play with some values and then make your own decisions.  The premise is that the expenses will be relatively small and planned.  Folks can recalculate whenever they see fit, as the sheet does not take today's date into account.

    • ukimagic209 said:
      recalculate your subscription category balance and contribution after your big expense is paid

      The cycle may not start after the "big" expense is paid. That's why I'm suggesting that you check whether contributions can be reduced after each one is paid. It's easy.

      You've made your tool available in order to help others. Having extensively studied this problem, that's all I'm trying to do as well.

    • I'm suggesting that you check whether contributions can be reduced after each one is paid.

      I re-read my original response where I suggested checking whether contributions can be reduced after the point of lowest balance (in contrast to checking after each payment). Either choice works.

  • 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!

    Like 1
    • dakinemaui Thanks for your input, as always!  I agree that 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).
      At first I was thinking that you were just poking holes in my tool but I began to understand that your goal was to impart some wisdom to others who might be reading this (basically so they take into account some factors they might not thought of).  So thanks, and cheers! 🙂

    • ukimagic209 LOL, no not poking holes. :-) Trying to broaden the realm of applicability.

  • ukimagic209 said:
    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.

    Like 1
  • 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.

    Like 2
    • One final comment while I'm thinking of it regarding the two approaches. The ongoing contributions that I can afford  each month to this category are influenced by how much I budget to higher priority categories elsewhere in the budget. In some cases where I'm trying the second (gradual) approach, I may not have enough of my check left over (after the higher priority categories are funded) to make the required contributions, so a mix of #1 and #2 might be in order. I might be able to pull something out of lower priority categories up front -- not enough to put me on track for the nominal contribution level but enough that I can afford the required monthly contributions until I get over the hump.

    • dakinemaui Thank you for taking the time to explain approaches to using the spreadsheet!  I am sure this will help those who decide to download and use it.  👏🏻👏🏻

      Like 1
  • Wow! Really awesome! I'm a spreadsheet geek, so I can really appreciate this!

    Like 1
Like5 Follow
  • 1 yr agoLast active
  • 31Replies
  • 355Views
  • 7 Following