How to Create the Best Personal Finance Spreadsheet

Creating budgets isn’t always the most fun activity, but it can be easy to budget if you take the time to make a template that works for your needs. There are various financial trackers out there, but the most widely used (and usually free) is the classic spreadsheet method. There are templates available in excel, but those can be confusing and unhelpful depending on your goals.

Follow these steps to create a basic fool-proof budget and spending tracker. Open an Excel or Google Sheets tab, and grab a pencil and notebook—let’s get started!

Step 1: write down necessary expenses 

In your notebook, create a category of your necessary monthly expenses. Examples include rent, mortgage, groceries, gas, car insurance, cell phone bill, and utilities.

Underneath necessary expenses, create a sub-list of subscriptions, such as Spotify and Netflix.

Next to each expense, write the amount. And if you’re unsure, it’s best to make an educated guess that’s on the higher end—you would rather be under budget than over budget.

Step 2: take note of your other expenses 

We’ll call this next category of expenses your “slush fund” or all your other miscellaneous expenses, such as takeout, coffee, or a night out on the town.

Take a look at your bank records for the past three months, and get a grasp of how much you typically spend on splurges. Create categories appropriate for your lifestyle, and put that number down next to each category. For example, food and coffee – $100.

It’s important to note that you’re making a realistic budget, so avoid underestimating how much you spend in each of these categories.

Step 3: create an income category

In another column or on another page, jot down your monthly income sources after taxes and benefits deductions. If you freelance or work hourly, try to write down your best estimate. It’s wise to be conservative with this estimate. Consider any consistent odd jobs, such as a side hustle or a second job on the weekends.

Step 4: start your spreadsheet 

You have a few options for structuring the spreadsheet to work best for you visually, but here are some basic guidelines to start.

On the left-hand side of your spreadsheet will be all the numbers you’ve just calculated. Take the lists you’ve written down, and put them in the first column, bolding the name of each category. Put the numbers in the cell immediately to the right of each expense, and label that column “anticipated amount” at the top.

Create a third column spaced further to the right with the label “month” at the top. This column is where you’ll plug in your actual expenses each month.

At the bottom, put your total monthly income in one column and leave the column next to it for the total amount spent this month and the total amount placed in savings this month.

Label this sheet (at the bottom of your screen) “template.” At the end of the month, duplicate this sheet and label it accordingly. Then, at the end of the year, you will have 12 months of consistently organized sheets you can refer to!

Step 5: add a visual element

To make this spreadsheet your own, add color coding and an analysis chart that can help you accomplish your financial goals.

For example, you can color code like this:

Green- under budget

Yellow- on track

Red- over budget

And you can create a quick formula like these in a new column:

• Percent change from previous month (e.g. percent change= this month-last month/last month)
• Difference between anticipated and actual expenses (e.g. difference= anticipated-actual)
• Pie chart of spending by category
• Variance analysis

Reach out to a financial advisor for help creating the perfect budget for you!

 

 

Important Disclosures

This article was prepared by ReminderMedia.

LPL Tracking #1-05210050