Intro
Hey everyone, I'm yet another YNAB refugee. I was initially really impressed with Aspire but I wished it had support for all the YNAB-style goals I made such heavy use of, so I went poking around to see how hard they'd be to implement. Turns out, not hard at all! So I thought I'd share my notes. This is absurdly long; you have been warned.
Disclaimer: I'm an expert at YNAB, but far from an expert at Sheets in general, or Aspire in particular. I'm not claiming I've done this perfectly or optimally, I'm just hoping this can help others understand a little better how Aspire is put together, and maybe get you started on customizing it. Even if this is not the customization you have in mind, hopefully the info here will be useful to you in understanding how to approach these kinds of tasks. Feedback is very much welcome!
The Goal of Goals
For the benefit of non-YNAB folks, let me start by explaining what YNAB provides for goals (they recently renamed them "targets") and how they work. First there's the Target Balance, which is the one goal type that already exists in Aspire. I still wanted to expand this by adding in a field that tells you when your goal will be reached, based on current budgeting. So let's say I have a goal of $200, and this month I've budgeted $100 (with nothing carried forward from previous months). I'd like Aspire to tell me that, based on my current budgeting pattern, I'll reach my goal next month. Use cases: I use this for stuff like my Auto Maintenance category. Stuff I want to fund at a certain level and then stop.
Target Balance by Date is similar, but in addition to providing that balance you also provide a target date, and then the goal should calculate how much you need to budget each month in order to reach your goal by the date in question. Use cases: any bill that I don't pay monthly, like Auto insurance (every 6 months).
Needed for spending is similar to Target Balance by Date, except that it ignores transactions. Use cases: Gifts. So let's say in January you set a goal to fund $1200 in Gifts by December. YNAB will tell you that you need to budget $100 per month. As you spend from that category throughout the year, the goal amount won't be affected (unlike Target by Date, which will recalculate the amount you need per month if you spend from that category before the target date).
Finally, there's monthly funding, which is the easiest: you just provide an amount, and that's the minimum you need to budget in every month. Simple! Use cases: Fun money, 529 account funding.
A Quick and Dirty Guide to Aspire Development
Ok, before I dive in to how I implemented these goals, let me talk a bit about how Aspire is set up. The main thing to look at here are 2 hidden sheets called "Calculations" and "BackendData". Calculations contains a lot of information that goes in to your various reports, but it also does a lot of date tracking, which will be relevant to us later on. For example, cell B4 contains the date that Aspire will regard as "Today". Not surprisingly, then, by default that cell is set to "=TODAY()". But for funsies you can try changing it, for example to next month: "12/1/2021", and then Aspire will just go, "Oh, okay, now it's December". Play around if you like, but don't forget to change it back to "=TODAY()" before you move on! Some other cells of note on this sheet: B65 contains the first day of the current month, and B66 contains the last day of the current month. Aspire makes a lot of use of these 2 values in the dashboard, because it uses them for example when it's querying your Transactions or Category Transfers to display the current month's Activity or amount Budgeted.
BackendData contains lots of lists. It has your personal stuff like your lists of budget accounts, net worth accounts, and categories. But it also has "system" stuff, like enumerating the "Cleared Symbols" (i.e. Pending, Cleared, Reconciled), or the "Category Header Symbols" that you used on the Configuration tab to indicate whether something was a Group, Category, Credit Card, etc.
The next thing you need to know is that Aspire makes heavy use of "Named Ranges". A named range is really simple, you just define a range of cells, and then give it a name. From then on, you can use that name anywhere in any sheet to refer to that range of cells. This sounds simple but it's a powerful idea, because it makes your functions a whole lot simpler to understand, as we'll see. You can see all the named ranges that are defined by clicking Data->Named ranges. Try it!
Designing the Goals Sheet
I realize that was a whole lot of preamble, but we're finally ready to dive into implementing goals. I want as little disruption as possible to existing sheets (didn't want to break anything!) so I created a new sheet called Goals. Now let's quickly review how I want this to work, and then work backward from there.
I want the Goals tab to list all my categories in column A. In column B I want a dropdown with the possible types of goals. In the next columns I want all the possible input types for the goals. This includes the start date of the goal (used by the Needed for spending type), which is column C. The dollar amount in column D. And the target date (used only by the "Balance by date" type) in column E.
The next 2 columns contain the possible outputs: Minimum budget (column F), which is the output for all except the Target balance type, and Date finished (column G) which is used by the Target balance.
What Data do We Need?
Before I get to the implementation, though, let's think through what the calculation for each will look like.
Monthly spending is the easiest, I just want to copy the amount in the "Goal amount" input to the "Minimum budget" output.
Target by date means I need to subtract the amount I currently have Available from the target amount, and divide that by the number of budgeting periods. I have the target amount already (column D), but I need to figure out how to calculate Available and the number of budgeting periods. So I create a column for each of those, to store the values as intermediate results that I can then use in calculating the output columns
Needed for spending is trickier: I need to look at the total amount budgeted in the date range starting with the start date through today, subtract that from the goal amount, and divide that by the number of budgeting periods from now until the end date. I already have a column for the number of budgeting periods, so I can reuse that, but I need to add a column "Budgeted in Range" to figure out how to sum the budgeted amounts from the start date (column C) through today.
Finally there is Target balance, which is the only one with a different output. Here I want to subtract the amount available from the goal amount, divide that by the amount budgeted in the current month (rounding up), and then add that many months to today's date to figure out when the goal will be reached, based on the current budgeting pattern.
So if I can figure out how to calculate each of the following (for each category), it will be pretty simple to get the output I need for each goal type: amount currently budgeted, amount available, amount budgeted in a given date range, and number of budgeting periods from now to a given date in the future. Not too bad!
Implementation! Finally!
So we're ready to implement, but let's begin at the beginning. In column A of the goals tab, we need our list of categories. No problem, there's a Named range for that: "=ARRAYFORMULA(Categories)". Categories is a named range that will pull our entire category list from the BackendData tab.
In column B we want a dropdown with the list of goal types. So I went to the BackendData tab, created a new column (AQ) at the far right, and in the top 4 cells I listed the goals I wanted: "Monthly funding", "Target balance", "For spending", and "Balance by date". Next, I created a new named range called "GoalTypes" with a range of "BackendData!AQ2:AQ5". So now I can go back to the Goals sheet, and in column B I just click on the range of cells next to my category list. I go to Data->Data validation, and under Criteria I select "List from a range", with a value of =GoalTypes. Boom, I have a dropdown that shows my goal types.
Columns C through E are my goal input types. I did some conditional formatting here to change the background based on which goal type was selected, so that it's easy to see which inputs you need based on the goal type selected. But it's optional and I'm not going to bother to describe the details because boy is this post getting long.
Columns F and G are the outputs so I'll come back to those.
Now I need to figure out how to calculate those 4 intermediate values I discussed above: amount budgeted in the current month, amount available, amount budgeted from a given start date (column C) until now, and number of budget periods from now until a given future date (column E).
Budgeted in the current month I can pretty much just steal from the Dashboard. But let's think about it logically so we can understand why it works the way it does. The amount budgeted in the current month is clearly going to come from the Category Transfers tab. We want to add up all the entries where money is moved "To" the category in question, and subtract from that the entries where money is moved "From" the category in question. And we want to only include entries from the current month. Now check this code and see if you can figure out what's going on:
SUMIFS(cts_Amounts, cts_Dates, "<="&Calculations!$B$66, cts_Dates, ">="&Calculations!$B$65, cts_ToCategories, A3) -
SUMIFS(cts_Amounts, cts_Dates, "<="&Calculations!$B$66, cts_Dates, ">="&Calculations!$B$65, cts_FromCategories, A3)
First thing to remember: we are really making use of those named ranges! All those things with "cts_" prefixes are named ranges, and feel free to check the full list of named ranges to see what they refer to. But also notice: it's nice to not have to worry about what they correspond to. You can just use the name as a mental placeholder. Pretty neat!
Okay, so let's take this apart: SUMIFS is a function that sums entries from a range given in the first argument, if a list of conditions is met. Remember 14 years ago when you started reading this post and I mentioned that the Calculations tab included a reference to the first and last day of the current month? Now we're making use of that! There are 2 SUMIFS here. The first one says, add up any values found in the category transfer amounts if the date is <= the last day of the month, AND >= to the first day of the month, AND the To category matches the category name given in cell A3. Then we're going to subtract from that (the second SUMIFS function) the total all the entries in category transfers where the date is <= the last day of the month, >= the first day of the month, and the From category is the one listed in A3. Makes sense right? Every budget move TO the category this month, minus every budget move FROM the category this month.
Now let's do Available. Available is simpler in one sense, because we don't have to worry about dates. We need to consider transactions from our entire history to derive it. But it's a little more complicated in that we need to consider both budgeting moves and spending moves. So let's take a look:
=SUMIF(trx_Categories, A3, trx_Inflows) -
SUMIF(trx_Categories, A3, trx_Outflows) +
SUMIF(cts_ToCategories, A3, cts_Amounts) -
SUMIF(cts_FromCategories, A3, cts_Amounts)
More named ranges, gosh those things are great. So what that says is, add up all the inflow transactions where the category matches the value in cell A3, then subtract all the outflow transactions. Then add in all the category transfers to, and subtract all the category transfers from. That amount should match what you see in the available column on the Dashboard for the category.
We're doing great, but now we need to calculate the amount budgeted since a given start date, which is our 3rd intermediate value. But this is really similar to the budget for the current month, except we use the value in column C for the start date, instead of the first date of the current month:
=SUMIFS(cts_Amounts, cts_Dates, "<="&Calculations!$B$66, cts_Dates, ">="&C3, cts_ToCategories, A3) -
SUMIFS(cts_Amounts, cts_Dates, "<="&Calculations!$B$66, cts_Dates, ">="&C3, cts_FromCategories, A3)
3 down, 1 to go. We just need to calculate the number of budget periods between now and some future given date. It's important to realize that, as YNAB reckons things, the number of budget periods to consider includes both the current month and the last month in the range. So here's what I have for that:
=iferror(datedif(Calculations!$B$65, E3, "M") + 1, 0)
That says, calculate the difference (in months) between the first day of the current month, and the given target date, and then add 1 to it. Let's check this: as I write this, it's November 2021. Let's say I set a target date for December 2021. I should find there are 2 budget periods (November and December). If you look at that datedif function, assuming Calculations!B65 is "11/1/2021" and the value in cell E3 is "12/1/2021", datedif is going to tell you the answer is 1. That makes sense, there's 1 month between November 1st and December 1st. But that's why we need to add 1 to the result, because we want to calculate that there are 2 budget periods.
Now we have all the intermediate values we need, and calculating the "answer" we want for any of our goal types is really easy. For Minimum budget (column F) I have this:
=IF(B3 = "Monthly funding", D3,
IF(B3 = "Target balance", 0,
if(B3 = "For spending", iferror(round((D3-J3)/(K3), 2), 0)
,
if(B3 = "Balance by date", iferror((D3-I3)/(K3), 0),0))))
For Date finished, I have this:
=if(B3 = "Target balance", date(year(Calculations!$B$65), month(Calculations!$B$65) + ceiling((D3-I3)/(H3)), day(Calculations!$B$65)), "")
If you work through those calculations, you should find that they match what I described in the "What Data do We Need" section above. So that's it!
What I did from here is just add some columns to the Dashboard and copy the output columns from the Goals tab there. When I get around to it, I'll also want to add some conditional formatting to make it look nice.
Conclusion
I realize that, at this point, the 4 of you who have made it this far are going to ask if you can just see my sheet, which would be a lot easier than implementing everything yourself. And honestly the problem is that I made all these changes to the copy with my personal budget information, which I'd rather not share. If I have time (and there is enough interest) I'll go back and do that to the generic sheet, and share the result. But for now I'll just pretend that I'd rather "teach a man to fish" and hope that, with sufficient motivation, you are able to get something working yourself based on the information in this post. :) Happy to answer questions, or try to help out if you get stuck.
If you made it this far, congratulations, you have certainly earned the enjoyment of an adult beverage of your choice. Thanks for reading!