tutorial: tracking your savings goals using excel

While these days you can have multiple savings accounts without incurring extra fees, it’s often easier to keep all your savings in one account. But if you’re working towards a variety of savings goals, how do you keep track of your progress towards each goal?

I like to use an excel spreadsheet to allocate various savings amounts to each savings goal and monitor my progress. This sheet forms part of my budget. Below is a tutorial on creating a savings spreadsheet similar to the one that I use. This spreadsheet may seem a little complicated, but once it’s set up it is easy to use, you just plug in your numbers ever month the formulas take care of the rest. I like to update mine every time I do any online banking.

Of course, you can create a much simpler spreadsheet, or just use an exercise book and a pen, which is how I used to track my savings for years before moving to excel.

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

You will notice I’ve added two adjustments column. These aren’t necessary, but sometimes I like to track “transfers” between savings goals. If you’re not interested in doing this, you can leave these columns out.

2. Add in your formulas. In each subtotal row, enter the subtotal formula as show below and then again at the bottom in the total column.

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 under the less adjustments and less expenses headings, double checking that the formulas are calculating correctly.

3. Now in the Total Savings for the Year column, use the autosum function to add together all savings amounts from January to December. Copy this formula down the column and delete the rows where there are headings.

4. In the Total column, you want to calculate exactly how much you have saved towards your goal including balances from last year and adjustments. The formula is:

= Opening Balance + Add Adjustments + Total Savings for the Year – Less Adjustments – Less Expenses

Again, copy your formula down and delete the contents of the cells where there are headings.

5. Finally, in the Remaining to Save column calculate how much left you have to save to reach your savings goal (and copy down etc):

= Target – Total

6. Select all of the cells that contain numbers and format them to currency.

7. Format the rest of the spreadsheet to your liking and enter some test data to make sure all of the formulas are working correctly.

8. There are two more things I like to add to this spreadsheet. One is a check that my spreadsheet accurately reflects the amount that is actually in the bank. I do this by adding a little checksum / reconciliation as follows:

I also like to track inflows and outflows to see how my savings are progressing overall.

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 leave a comment below.

---

Have you read these posts?

1. Tutorial: Building A Basic Budget In Excel (For The Absolute Excel Beginners) Part 2
2. Tutorial: Building A Basic Budget In Excel (For The Absolute Excel Beginners) Part 1
3. how to save towards multiple savings goals
4. creating a savings plan and reaching your savings goals
5. Charting Your Progress – Creating Graphs In Excel