Exactly How I Use Excel Formulas To Make Super-Quick Changes To My Budget

TFD is all about making personal finance as simple and accessible as possible, and finding a system that works for you. And while I love simplicity as much as the next person, I also really like tinkering with data and formulas. Lady in the streets, freak in the spreadsheets, as the t-shirt says. I spent a few hours fussing over rows and columns in a coffee shop before creating a self-calculating Excel system that lets me break down all my expenses and see, in real time, exactly how an adjustment in one category impacts my overall budget.

With this setup, you can plug in your own numbers and start experimenting with how you want your money to work for you.

How to use excel budget formulas

In Column A of my spreadsheet, I made a list of everywhere my money goes in a given month. This includes my investments, savings, and regular living expenses. (I don’t currently have any debt, but if I did, that would go on this list, too.)

In Column B, I noted the interval of those expenses — i.e. how often I have to pay them. Since this is a monthly budget spreadsheet, most of my items are marked as “monthly.” However, I put money into my Roth IRA when I get paid every two weeks, so I mark that as “every two weeks.”

In Column C, I typed in how much I pay per interval for these expenses. My rent payment, my monthly food budget, my payroll deductions, etc. Some of these numbers were guesses based on a quick scan of my Mint history. I knew I might adjust them later.

Here are my food and Roth IRA budgets as examples:

Column D was an easy one. I referred back to Column B, my “how often” table, and turned that interval into a number “per year.” For monthly expenses, I put a 12, for “12 times per year.” If a particular expense correlated with my biweekly paycheck, such as a payroll deduction, I put down 26, for “26 times per year” (based on 52 weeks per year divided by 2 = 26 payments).

Calculating my spending in Excel

Then came my favorite part: formulas and calculations!

In Column E, I multiplied the dollar amount of a given expense (Column C) by its frequency (Column D) to figure out how much I spend each year in that area. I used the formula =MULTIPLY (C#, D#) and was able to copy and paste this formula all the way down my list to get my annual spend for each line item. I like typing the formulas myself, but any of them can be found using the ∑ menu in Excel.

The next step was to break down all these annual numbers into monthly numbers. In Column F, I put =DIVIDE(E#, 12). Once again, I copied and pasted this formula all the way down Column F. This averaged out all of my expenses that occur at differing intervals, giving me some consistency in my planning.

It’s worth noting here that you can change the number in your Column F formula to match whatever interval you want. For example, if you want to see all of your expenses broken into two-week segments, to plan a budget for your every-other-week paycheck, you can divide your annual spending by 26 instead of 12. Planning monthly just works best for me.

In the row below my last expense in Column F, I added up all my monthly expenses to get my total monthly spending. My last expense was in Row 10, so I plugged =SUM(F2:F10) in Cell F11. I’m going to use example sums going forward.

Once I had an official, though easily adjustable, number for my expenses, I needed to calculate my income. I get paid 26 times per year, so I had two options:

1. I could divide my total annual take-home pay by 12 to get my average monthly pay.

2. I could add up my total take-home pay based on my usual two paychecks per month. Then, twice a year, when I got a third paycheck in a given month, I could put that entire amount toward my savings goal of choice.

I chose the second option, and I entered that number into the cell right next to my monthly expenses. In another empty cell, I plugged in one more formula, to subtract my monthly expenses from my monthly income.

This final number had a lot of power for me. I knew if it was a positive number, then my monthly expenses amounted to less than my total income and I was living within my means. If it was a negative number, then the opposite was true.

The importance of having a budget system

I was a little nervous about hitting this final number. I’d made the mistake of not adjusting my living expenses to match my income before and sinking myself into credit card debt, and I wanted to stay off that path. Here’s the outcome from my example numbers:

At this point, I had a number and, more importantly, a system. I could and did go back and tweak any of the numbers in Column C (my expense column) and see immediately how adjusting any of them would impact that final number.

I did this exercise before the New Year and set all my Mint budgets to reflect my numbers. After a month or two, I noticed that my spending in a few areas didn’t quite match the dollar amounts I’d originally entered, so I went back to my spreadsheet and adjusted them to reflect my real spending habits. Thanks to the system, I could see immediately how these changes impacted my overall budget. I had the real numbers I needed to decide whether I could afford a little more spending or savings wiggle room or whether I needed to cut back in a few areas so I could stay within my budget.

Doing some work up front has given me a tool I can use at any time to adjust my budget quickly without having to crunch the numbers every time I need to evaluate a change. My spreadsheet does the crunching for me. And if I have to add something new to my list of expenses, like a hospital bill or a short-term savings goal, I can just add another row and see what happens. Even the most stable finances can suddenly be in flux at any given moment, and this system lets me adjust to any changes by making the cold, hard numbers accessible right away.

Maggie Olson is a marketing professional living in northeast Ohio, who went from knowing nothing about money to being the kind of personal finance nerd who texts her friends about IPOs and Roth IRAs. She is a voracious reader, an amateur artisan bread hobbyist, and a hiker/biker/runner/kayaker. You can find her on twitter at @maggiebolson.

Image via Unsplash

Like this story? Follow The Financial Diet on Facebook, Instagram, and Twitter for daily tips and inspiration, and sign up for our email newsletter here.