Today I’m sharing something a little different with you and I hope you enjoy it.
I really enjoy crunching the numbers for our personal finances and keep meticulous track of where we are at all times. I take pride in knowing that (barring some unforseen major expense) I can tell you right now approximately how much money will be in our checking account on say, April 30th. Yes, that specific.
I’ve tried many programs – Mint, Personal Capital, and YNAB. Nothing has helped me as much as this basic spreadsheet and nothing has been as easy to maintain. I love it and it brings me great comfort to have a thorough understanding of exactly where we are financially.
I’m sharing it with you here today because I truly think someone might find it useful. Not everyone, but someone. And that’s enough for me because it truly changed my life and my relationship with money.
The explanation below might seem complicated but I promise, once it is set up it is the most simple way to see an overview of your current finances, what you spend money on, and where you’re headed.
How This Can Help You
What is most helpful about this spreadsheet is that every recurring expense is already recorded. I generally have my recurring expenses filled in for at least 6 months. That means that nothing catches me off guard and I know well in advance how much money should be available in our checking account before any bill is auto-deducted.
But the most helpful thing about it? It allows me to see the future.
It lets us take a look at how much money we could have if we were really careful about our spending. I guarantee you that when you fill it in, you’ll be shocked by the projected number that stares back at you for April.
Because when we really remove all the little things – the Starbucks runs, the lipgloss you threw in your cart at CVS, the book you bought on Amazon, or those booties you really really wanted – when you remove those things you see the HUGE potential of your income.
This is by far my biggest motivator for making better financial choices on a day-to-day basis. I know how much we could have and checking in with my spreadsheet helps me keep that number in the forefront of my mind and prevents me from spending on silly things.
To start, download the spreadsheet here.
What? A freebie and you didn’t have to provide your email address?! What a world!!! If you want to thank me for that, consider pinning the above image to Pinterest. Thank you!The file will download as an Excel file (.xlsx). I keep mine in Google Sheets so if you’re planning to do that, import it in to Google sheets. Otherwise, open it in Excel.
Please note that if you don’t read through the below, the spreadsheet may not make sense to you. I suggest downloading it and reviewing it while reading the below.
If you’d prefer to start with a blank version, there is also a blank version here.
I would still recommend downloading the pre-filled version to follow along with the directions below.
How I Came Up With the Numbers
When you look at the spreadsheet, you’ll find that example numbers are filled in.
I based these numbers on the median household income as reported by the Census Bureau for 2017 (although I did not factor in taxes, which is huge but since they are placeholders, you’ll still get the point). I divided that amount up between two people and broke it down by month.
I got most of the expense details from various semi-reliable articles on the internet (like this one that says the average used car payment is $381 – that’s where I got that number). Some, like Hulu and Netflix, I copied from my personal tracking spreadsheet.
But as I said, they’re placeholders. You have to replace the placeholders with your own expenses.
How to Use this Tracking Spreadsheet
List Your Recurring Expenses
First, go to the second tab – Recurring Monthly Expenses – and fill it in with your own details. Include every single bill you pay every month.
I’d generally suggest logging in to your bank account and going through last month transaction by transaction to make sure you don’t miss anything.
Before you move on, sort it by column D – Day of the Month. This way you can see all of your recurring monthly expenses in order of how they’d come out of your account each month.
Add Your Recurring Expenses to the Day to Day Tracking Tab
Once it is filled in and you are sure you’ve listed all recurring expenses, hop over to the first tab – Day-to-Day Tracking. This is where the fun begins.
Whatever day you’re starting on, delete all the rows for every day before that (leaving the START row). In the START row, add your current checking account balance to column G – Checking Balance.
Start adding your expenses and income in to the spreadsheet by date, deleting the placeholders as you go. I’d recommend starting with your expense due on the 1st of the month. Add that expense to the 1st of the month for every month of the year in the Day-to-Day Tracking sheet. If you’re starting this spreadsheet half way through the month, skip to your next recurring expense and start there for the current month.
Continue on with this until you’ve added every recurring expense to the spreadsheet for every month of the year (or the next few months – your call).
Next, add your income. If you get paid every other Friday, pull up a calendar and add your paycheck (or approximate pay amount) to Columns E (Deposit Description) and F (Deposit Amount). You’ll see that Column G (Checking Balance) will automatically recalculate as you add your income.
Checking Balance Formula
The checking balance formula is super simple. I’ll add it here in the event that you delete it accidentally and need to re-add it.
=G(row above the row you’re starting on)-B(row number)+F(row number)
What to Do When Two Expenses Fall On the Same Day
Add a row and then add that expense. You’ll see that you now have a gap in column G (Checking Balance).
Go to the last cell in that column before the break and hover in the bottom right corner until you see a little plus sign. Click and hold down and drag down the column. It will drag the formula down and will recalculate. Here is a helpful Youtube video on how to do that.
As an example, Partner 1 (gets paid the 1st and 15th) and Partner 2 (gets paid every two weeks) both get paid on the same day a few times in the spreadsheet. I added a row to accommodate this.
What About Savings?
Great question! I got the savings amount number from the median amount the average American family has in savings. You’ll notice that it stays the same the entire time.
What I generally do is add “transfer to savings” as an expense, with the amount that we’re transferring to savings, and then manually update column H “Amount in Savings”.
We do some automatic saving and some manual transfers so whatever works for you, calculate column H accordingly.
We still haven’t factored in a HUGE expense – food!
Here’s how I generally factor in groceries. I know that I usually do 2 big grocery shops a month, plus 5-8 smaller grocery shops. If I was filling this out today, I know I just did a big grocery shop, so I’d probably add $20 in 5 days or so, $35 in another 3, $20 in another 5, and so on. I know what my grocery shopping pattern looks like.
If you do a big twice a month Costco run, fill in your “expense description” with “costco”, the amount you estimate it to be under “expense amount” and finally, categorize it as “groceries”. If you stop at the market every other day on your way home from work, add those in. Make estimates of how much you’d expect these trips to cost.
Groceries are the only expenses that I estimate in my spreadsheet. We rarely use our car (city life) so we don’t factor in gas estimate however if you use your car daily, I would repeat the same thing with gas. Do you fill up every 5 days? Add those expenses in.
I know this part is tedious. I KNOW. But it’ll all be worth it.
Take A Look
Once you feel confident you’ve recorded everything, take a look. Are you surprised? How do you feel? Is the number in your checking account in a few months far bigger than you’d thought it be?
How to Maintain Your Financial Spreadsheet
Every few days I go through my spreadsheet and delete rows (for passed days) and remove the deposits or expenses that have already happened.
I then get the balance from my checking account website and update column G – Checking Balance, making sure that the formula updates and dragging it down the sheet if it does not.
You’ll see that thanks to incidental expenses, you’ll probably have a little less than before you updated it. If you’re like me, it’ll make you sad and that might help keep your spending in check.
I also use this to plan larger expenses. For instance, if we have an upcoming wedding, I’ll add in hotel, travel, and trip costs. If I’ve been eyeing a pair of shoes that aren’t in the budget immediately, I’ll add them once I’ve completed all updates and can see exactly where we’ll have some financial cushion and I’ll be able to buy them responsibly. This helps us be prepared for large expenses that are out of our ordinary budget and factor in “wants” in a responsible way.
If you want to really understand your financial picture as a whole, I recommend adding a sheet that has all of your current debt – the current amount owed per debt, the minimum payment, and the due date.
I have this sheet and keep it up to date and it’s encouraging to see those numbers being chipped away at.
I hope you find this helpful! Please feel free to leave questions below and I will try to answer them. I’d also recommend Youtube for Excel or Google Sheets tutorials if you’re less experienced with spreadsheets.
And if you’re a regular reader here and right now you’re like, wtf was that? Spreadsheets? Personal finance? Just know that for nearly seven years I’ve been keeping my passionate love for spreadsheets from you. You’re welcome.