How I Use Excel to Manage My Family’s Budget

I have long been a money nerd. As a small child, I used to empty my piggy bank, count everything that was inside, and put it all back in, feeling pretty good about my net worth as an 8-year-old.

So, it should come as no surprise that the detail-oriented piggy-bank counter has grown up to use a pretty complex system for keeping track of my family budget. I mentioned to PT on our podcast conversation that I have an Excel spreadsheet that I use to keep tabs on every penny that passes through our family's coffers, and he suggested I explain my system. So without further ado, here is a breakdown of the system I use.

Editor's note: even with all of the cool budgeting tools available today, the vast majority of my Masters of Money guests use(d) some type of modified Excel spreadsheet to track expenses or budget (just like I did).

The Big Picture Excel Budget

With the help of my husband, who uses Excel a great deal for work, I have set up the following spreadsheet to track our family's income, savings, investments, and expenses:

Blurred Excel Budgeting

Our August 2017 Sheet

::

Right click here to download this family budget Excel template.

::

During the first week of each month, I print off the previous month's page so that my husband and I can look over and discuss our finances. We use this monthly check-in to make sure we are on the same page and to increase or decrease any spending categories that might need it.

For instance, we tend to overspend in the dining out category every month. Between getting lunches at work, going out to dinner together for dates or as a family, or getting pizza or takeout on evenings when neither of us feel like cooking, we have a tendency to blow through our dining out budget every month. After a few months of this habit, we had to decide whether to scale back our dining out spending, or increase that budget–or both.

Now let's take a look at each individual component of my tracking:

Income

This is the simplest part of filling out our spreadsheet. My husband (Income 1) gets paid on the 1st and 15th of the month. When his paycheck clears, I type in the amount in the corresponding week.

We also set money aside in a Flexible Spending Account to help pay for our four-year-old son's preschool care. We receive a (very) partial reimbursement for his pre-school fees twice a month from our FSA. Sometimes the FSA money comes in within the same week as my husband's paycheck, as it did in August, and sometimes it comes the following week. I include that FSA reimbursement as part of my husband's income each month.

Income 2, which is my freelancing income, tends to be much more variable and will often come throughout the month. Some of my clients pay on the first of the month, some pay on the 15th, and some pay upon receipt of my work. Whenever I receive a paycheck, I enter it into our spreadsheet.

“Envelopes” Savings Account

This portion of our budgeting system hearkens back to when we used to follow the Dave Ramsey cash envelope system. When we first got married, Dave Ramsey's cash envelopes helped us tame our finances and end many of our money arguments. It got us in the habit of setting money aside with each paycheck for regular and irregular expenses.

However, after several years of dutifully filling envelopes with cash, my husband and I found that we were causing ourselves–and by that I mean me–more work. That's because we would often forget to take the cash with us when we went out, which meant we would use our credit card to pay. I would then have to take the cash out of the envelope, deposit it back in the bank, and then pay the credit card with that money. Ain't nobody got time for that.

That's why I decided to simply make the savings account in our home bank into an “envelopes” account. Twice a month, we make a deposit into the savings account for the full amount that we would otherwise be putting into cash envelopes. I personally keep track of how much goes into each “envelope” (we don't separate them out in the bank account, just in my Excel spreadsheet), so I know how much we have to spend in each “envelope.”

Capital One 360 Savings Accounts

In addition to our “envelopes” savings account, my husband and I also have a number of Capital One 360 savings accounts. (I've been a Capital One savings customer since 2004, back when it was ING).

My husband and I refer to this as our mid-level savings. These are generally the irregular bills that we have to pay each year. With each paycheck, we put aside some money in each of these accounts.

For instance, back when my eldest child started Sunday school at our synagogue, we received a bill for his tuition for the first year. We were easily able to pay for the tuition, but after putting that check in the mail, my first action was to create a new savings account for Sunday school tuition (labeled here as LJCRS), and set up an automatic transfer of 1/12 of the amount necessary for a year's tuition on the first of each month. That way, the next year's tuition was already be funded and paid for.

Both the Car Insurance and Honda Motorcycle Insurance totals are relatively low in the above spreadsheet because we pay those bills yearly in June, and we have not yet rebuilt those accounts.

In addition, you'll notice I have two different accounts labeled Taxes. The first one is where I park 35% of each of my paychecks. As a freelancer, I need to pay quarterly estimated taxes, so I make sure to set aside 35% of everything I make into this account. Putting the money here until I need to pay it earns me a little interest and protects the money from me accidentally spending it out of my checking account.

The License and Taxes account is for any Wisconsin state requirements, like renewing our car licenses and titles and paying our property taxes. I set aside 1/12 of what I'll need for each of these bills on the first of each month.

Finally, my husband and I each have a savings account for 40th birthday shenanigans. (His is labeled J's Midlife Crisis). He bought a new motorcycle (in addition to his Honda 400) for his 40th birthday in June of this year, and I plan to go to Hawaii when I turn 40 in 2019. We started saving for these milestone birthdays four years ago so that we were and are prepared to celebrate in style and in cash.

Spending

This portion of the spreadsheet is where I spend the bulk of my time in working on our finances. Each day, I log onto our credit card account and our checking account and check for any spending transactions. I categorize each and every transaction (and sometimes drive my husband crazy by calling him at work to demand “what did you buy from PayPal on the 23rd for $34.76?!” only to realize after I hang up that I was the one who bought something and totally forgot about it).

If we paid for something in cash, with a check, with a debit card, or as any kind of checking account transaction, I enter the number in the cell and center it. That's because any transactions made on the credit card are initially entered as a number aligned right.

Every Monday, I go through the transactions entered into the spending spreadsheet since the week before. For any numbers aligned right (credit card transactions) I transfer the money from the appropriate “envelope,” and send a payment to the credit card. Once I've done that, I center the number to show myself that it has been paid off on the credit card.

This means that our credit card is always paid off, and I make a credit card payment every Monday to cover all cleared transactions for the previous week.

In general, it's the envelopes that pay for our transactions, with a couple of exceptions. You'll see that J GP and Emily GP are listed in the purple section above. GP stands for Gazingus Pin, which is the term coined in Your Money Or Your Life for unnecessary purchases. This is my silly way of categorizing fun money purchases, and those come from our checking accounts. (We have one joint account and we also each have a separate account).

In addition, our Mortgage, Utility Bills (most of the pink section), Gas (for automobiles), 529 Deposits, and Life Insurance are all just taken directly out of either our joint checking account or one of our personal accounts, depending on how we've divvied up particular bills. (I take care of Mortgage and Life Insurance, for instance, while he pays the Utilities, and each one of us takes care of one of our son's 529 Deposits, and Gas is paid for out of the joint account.)

Investments

My favorite part of this spreadsheet is the one I work on the least. This is the breakdown of the money in our Vanguard account.

We opened this account about two years ago when we got uncomfortable with the amount of money we had sitting in our Capital One 360 accounts. For instance, we had been setting aside the equivalent of a car payment into our CO360 New Car fund for many years, without needing to invest in a new car. While we've occasionally dipped into that fund for any car repairs or expensive maintenance that might otherwise exhaust our car repair envelope, we've grown the New Car fund in such a way that we could easily replace one or both of our cars if necessary.

However, that means we had a lot of money sitting in accounts earning 0.75% APY. We could do better.

So, we opened up a Vanguard account and transferred money from any CO360 account that I did not anticipate us needing within the next year to two years. That included New Car, New Computer, our someday dream trip to France, my husband's project car, which is playfully nicknamed Ziggy, money leftover from my husband's most recent large Bonus, Furniture, and big-ticket Home Repair.

You'll also notice a section titled Taxes 2015. I was lucky enough to have money leftover that year from my Taxes savings account, so I decided to move it into Vanguard to earn some interest. Finally, the Emily Retirement section was where I parked some windfall money (from a life insurance policy) that I put into my tax-advantaged retirement accounts in annual chunks.

My favorite part of working on this portion is adding the growth, so I can know exactly how hard our money is working for us.

Two Hours a Week on Excel Budget Spreadsheets?

Altogether, I probably spend about two hours per week total in filling out these spreadsheets. About half of that time is on Monday, when I figure out how much the envelopes owe to the credit card, transfer the money and pay our credit card, then update the spending cells to show that those transactions have been paid and update the envelopes section to show the amount that has been transferred from them.

The rest of the week, it generally takes me less than 10 minutes per day to log into our checking accounts and our credit card account to update the spreadsheet and make sure everything is balanced.

Finding a System That Works For You

I imagine the spreadsheet-and-budget-phobic probably think I'm a three-headed alien from Mars right now. And this is definitely not a program that I would recommend to everyone. I know that a lot of people would rather clean a stadium with their own toothbrush than put in the kind of work that I do to manage my money.

This system works for me because I enjoy paying attention to the details and feeling like we're on top of our finances. You can wake me from a dead sleep and I could tell you how much is in our accounts, and that feeling would be worth the work even if I didn't enjoy the taking care of the details.

However, I certainly know that my way is not the only way to budget and that people can find other systems (even automated ones) that fit their personal preferences and money psychology.

How do you keep track of your family finances? Is there any aspect of my system that you think would work for you?

Join 36,000 subscribers improving their financial life.

Subscribe for free. Get my book (31 Days to Improve Your Financial Life), intro series, and article digest.

Powered by ConvertKit
Last Edited: October 9, 2017 @ 9:05 amThe content of ptmoney.com is for general information purposes only and does not constitute professional advice. Visitors to ptmoney.com should not act upon the content or information without first seeking appropriate professional advice. In accordance with the latest FTC guidelines, we declare that we have a financial relationship with every company mentioned on this site.
About Emily Guy Birken

Emily Guy Birken is a former English teacher and respected personal finance blogger. She lives in Milwaukee, Wisconsin with her engineer husband and two high-energy little boys. She has written two books: The Five Years Before You Retire and Choose Your Retirement. Emily's thoughts on parenting and life in general are found at The SAHMnambulist.

Comments

    Speak Your Mind

    *

  1. This is fantastic! I do something similar and when people find out how we manage our finances they think I’m crazy. You are a kindred spirit.

  2. Thanks for sharing, I also use excel. Always looking for something better….