Working towards multiple savings goals?
Want to keep track of how much you’ve saved towards your upcoming bills?
Even though we stopped keeping a traditional budget and moved onto a more effective budgeting system, I still like to track our individual savings goals. It’s not essential, but it’s motivating keeping track of your goals.
These days you can have multiple savings accounts to separate your savings, but I find it easier to manage a single account and then use an excel spreadsheet to allocate a single savings amount to different savings goals and expenses.
Updating this spreadsheet takes about 3 minutes a fortnight, a few minutes longer if I’m paying bills. That’s all the time I now spend on budgeting, and yet we now find it easier to stick to our budget than we ever did when I was tracking expenses.
It’s important to note that this is a two-step system.
Step one: (this one is the most important) automate your savings. If you’ve calculated how much you need in advance, there’s no need to track your savings goals unless you like to watch your progress for motivation, or you’re a data lover like me.
Step two: allocate your savings. The rest of this post is all about how to do this in Excel.
Now, you can create a much simpler spreadsheet if you prefer, or you can just use an exercise book and pen, which is how I tracked my savings for years. The most important thing is to do what works for you.
Below is the system I use.
How to Track Your Savings Using Excel
This tutorial goes into detail on how to set up a savings tracker in Excel, but it assumes some basic Excel understanding. If you have any questions, feel free to email me.
1. Set Up the Backbone of Your Spreadsheet
1. Start by creating the headings as per below. Add your own savings categories down the side of the spreadsheet similar to the picture. (You can click to enlarge the pics).
Along the side are your savings goals or the things you want to save for. Put in specific goals you want to save for. For ideas check out the articles:
Along the top includes:
- Your Target – the amount you want to save
- Your opening balance – how much you’ve already saved
- Add adjustments – (optional) in case you get extra money to add or want to swap some money between your savings goals
- The months of the year
- Total savings for this year
- Less expenses – for when you spend some money
- Less adjustments – (optional) in case you want to swap money between savings goals
- The total amount you have for that savings goal including your opening balance, expenses and adjustments
- Remaining – how much more you have to save to reach your goal.
It might look a bit complicated at this point, but once we add the formulas, this all calculates automatically, all you have to do is put in your savings amount each month.
(click images for larger view)
2. Add Your Formulas
In each subtotal row, enter the subtotal formula, following the steps in the image below.
Then enter the formula:
Then you can easily calculate the total for the column.
Important!: Don’t forget to enter some numbers to make sure it’s all calculating properly!
If you have an older version of Excel, rather than using the subtotal function, you can use the Autosum function on each subtotal row, and then add each subtotal row together as shown below.
Once you have your formulas, use the fill handle to copy them along to December.
Also, copy and paste the subtotal formulas in the subtotals row for less adjustments and less expenses headings.
Next, in the Total Savings for the Year column, use the Autosum Function to add together all the savings amounts from January to December. Drag to copy this formula down the column.
In the Total column, you want to calculate exactly how much you have saved towards your goal, including opening balances, expenses and adjustments. So in the Total column, type the following formula
=Opening Balance + Add Adjustments + Total Savings for the Year – Less Adjustments – Less Expenses
Again, copy your formula down and test by adding a few numbers.
Finally, in the Remaining to Save column calculate how much you have left to save until you reach your savings goal with this formula:
= Target – Total
Finally, test test test.
Type in a few numbers in the cells to make sure it’s all calculating as it should.
3. Formatting Your Spreadsheet
Select all of the cells that contain numbers and format them to currency.
Format headings, fonts and colours to your liking. This isn’t just to make things look pretty, it helps you to read and understand your spreadsheet, so take a few moments to pretty things up.
At this point, you’re all set up!
Now it’s a matter of simply entering in your savings each month. You won’t have to go through this process again, simply Save As your spreadsheet each year.
There are however, two more things I like to add to this spreadsheet to make sure it’s all working accurately. The first is a little Checksum / Reconciliation at the bottom of the spreadsheet to make sure the spreadsheet reflects the total amount I have in the bank.
At the bottom I add a spot to type in the balance at the bank.
Underneath I enter the formula:
=Bank Balance – Total Savings
If these numbers aren’t the same, then there’s either an error in the spreadsheet or I’ve been paid interest that I haven’t added!
I also like to track inflows and outflows to see how my savings are progressing overall.
Inflows = total savings for the year (not total savings all up) Type “=” and then click on the Total Savings cell and then press enter.
Outflows = total expenses for the year. Again, type “=” and then click on the Total Expenses cell and then press enter.
Net Savings = Inflows – Outflows as shown below. You can click on the cells instead of typing them.
And that’s it. You’re all ready to reach your savings goals. Don’t forget to automate your savings and you’ll never look back.
I hope that these instructions make sense. If not, drop me a line. If you have any suggestions for improvement or would like to share how you track your savings, please let me know.
