fbpx

How to Set Up a Spreadsheet to Track Your Financial Goals

Tracking your personal finance goals in a spreadsheet is incredibly simple as well as motivating. Here are tips for getting off to a strong start.

Setting personal finance goals is one thing, but actually measuring your progress towards them is another.

Luckily, tracking your personal finance goals in a spreadsheet is incredibly simple as well as motivating.

Getting Started with Personal Finance Goals

First you’ll want to set your goals. In our example, we’re setting goals for a six-month emergency fund, a vacation, and maxing out an IRA. 

Personal Finance Goals 1

We’re using a Google Sheet powered by Tiller Money for this job.

If you’re not yet a member of Tiller, you can sign up for a 30-day free trial.  

Or you can set up a new Google Sheet to match the sheet below. You’ll need to at least add headers for “Category,” Group,” “Type,” “Subtype,” as well as sheets for “Categories,” “Transactions,” and “Savings Goal” in your spreadsheet

1 – Add a Category for Each Savings Goal

On your “Categories” sheet, go in and add a category for each savings goal.

We’re going to delineate each one of them as a transfer using the drop-down menu in Column C.

In Column F, insert your end goal for each category. Vacation Fund, for example, is set at $3,000.

Personal Finance Goals 2

Categorize Transactions Appropriately

Now you’re going to want to make sure that when you’re transferring money to your savings account or IRA, it’s posting properly. Head over to the “Transactions” tab.

Check to make sure contributions to your vacation goal is registering as “Vacation Fund” in Column D. If it’s not, use the drop-down menu to change it.

Create a Savings Goals Data Sheet

Personal Finance Goals 3

Before you can make that pretty chart, you’ll need data to draw that chart from. You already have most of it; you just have to tweak the way it’s presented a little to get it to show up as a stark visual.

First, copy Columns D and E from the “Transactions” tab. Then, add a new sheet, head to H1, and paste.

You should now see the transaction categories and amounts on your new sheet in Columns H and I. 

Now head to A1. Create a title row where A1 is the goal/category name, B1 is the amount you already have saved, C1 is a control for absolute value, D1 is the difference between what you have saved and your total goal and E1 is your total goal. 

Filling out Column A is as easy as typing in the category names. In Column B, you will want to write a SUMIF formula in order to add together all the spending from each savings category. For row 2, that formula looks like this:

=SUMIF(H1:H, “6-Month Emergency Fund”, I1:1)

This formula tells Sheets to look at Column H (H1:H). If Column H reads, “6-Month Emergency Fund”, then Sheets is to pull its value from Column I. Once it has all the qualified values, it is to add them up together and put the sum in B2.

This number will be a negative, as it’s money that was taken out of your linked checking. You need it to show up as a positive value on the end chart, though, so use Column C to derive the absolute value. In row 2, you’ll do that with the following formula:

=ABS(B2)

All you have to do is change the row number from B2 to B3 to B4 and so on as you move down the rows. 

You will then move over to Column E where you’ll pull the total savings goal from the Categories tab. For our six-month emergency fund, the formula in Column E will look like this:

=Categories!F24

The exclamation point separates the tab—Categories—from the cell from which you want to pull the data, which in this case is F24.

As you move down Column E, simply replace the cell number after the exclamation point to correspond with the appropriate category.

Finally, you’ll need to move back over to Column D to calculate the difference between your actual savings and your target goal. To do this, you’ll add together Column B where we had those negative values and Column F.

Your formula will look something like this, with the numbers changing as you move down the rows:

=SUM(E2, B2)

Create a Visual Savings Chart

Personal Finance Goals 4

Charting your progress can be a huge motivator as you work towards your savings goals.

You can play around with several different ways to display your data, but my personal favorite is progress bars, which allow you to quickly see how far you’ve come along with how far you have to go. That’s the type of chart we’ll focus on today.

First, highlight cells C2 through D4. The number after “D” may vary depending on how many savings goals you are currently working on. Go all the way to the last one.

Right-click and choose to create a chart. You’ll want it to appear in its own, separate tab. Once your chart is generated, go to the Chart editor on the right of the chart.

Make sure your chart is set up to be a stacked column chart with standard stacking. Scroll to the bottom of the setup menu and make sure the “Use Column A as labels” option is checked.

From here, all your numbers should automatically update to display your progress as long as the transfers to your savings or IRA accounts are categorized properly in the “Transactions” tab.

For example, here is what the chart would look like at the end of the year if we kept saving at the pace:

Personal Finance Goals 5

Applying to Other Personal Finance Goals

You can use this same basic concept of creating charts to visualize and track your personal finance goals across all areas—not just savings. You can create charts for debt using categories and the =SUMIF() formula to pull the sum of what you have paid to date.

You can track investing over time—both what you project and what the market actually returns. 

At the core of it all is having the raw financial data in your spreadsheet. By automatically importing this data for you, Tiller Money can make tracking your financial goals in a spreadsheet 10x faster. Learn more here.

Brynne Conroy

Brynne Conroy

Motivation for women in business & on the homefront. Smart money management for success & true wealth. Author of The Feminist Financial Handbook.

Leave a Reply

Start Your Free Trial

"There isn’t another tool on the market that does what Tiller can do.”
Will Hinton, Google Review October 30, 2023