December 20th, 2012

Stupid Easy Budgeting with Excel

I shared my budgeting system with Alyssa today and she was all, YOU SHOULD BLOG THIS! So… here ya go!

I’ll go out on a limb here and say, there are better people at budgeting. We are by no means rolling in it or 100% on top of our finances. But if we were, we wouldn’t need to take charge of our budget, now would we?

After moving to Florida, Big Daddy and I decided to really look at our money and make sure we knew where it was going. It took us a few months to get there but now our bank account runs like a well-oiled machine. This is due to two factors: multiple checking accounts, and Microsoft Excel.

First let me tell you about our checking accounts. Message me to hear about my bank and what I use specifically, but you could probably do this method with any bank that has online banking. I think all people should have at least two linked checking accounts. I have three checking accounts for us, and one savings.

  • The first checking account is our basic checking. We have our debit cards out of it, and our direct deposits go there.
  • The second checking account is called our “reserve” account. This account is still checking, so as long as they’re linked, you shouldn’t have any fees to transfer between them. I put money in there when I want to “set it aside,” like part of a large bill that’s due later, or money I am not sure how we are going to use, but don’t want to spend it.
  • The third checking account I call our “Bills Account.” This one also has a check card. I use this account to pay all our bills (I use online bill pay from my bank online for most of them), and also for putting through random payments if I am buying something online, paying a one-time bill, etc.

The reason I set up the bill account specifically (and why I think you should have it!) is because I would have enough money, pay my bills, and then overdraft a few days later. I would give the bill payments a few days to go through, but some would take longer than I anticipated, and then I would look at our account and say, “Oh, $100! Let’s get dinner!” Then the $80 bill that took forever would go through, and so would a $30 dinner, and I’d be out $10, plus overdraft fees. NOT FUN. Getting gas for the cars seemed to especially take a while to go through!

We started using Dave Ramsey’s method for paying off our debt, and wanted to really know where the money was going. The bills account enabled me to pay bills and REALLY know how much we had left after, with no waiting or guessing involved. If on payday we had $1,000, I could tally up bills – let’s say, $850 worth. Then I would transfer $850 to my bills account, and pay the bills out of that account. Then, my primary checking showed $150 immediately, which was accurate. No waiting! It didn’t matter how long the bill payments took to go through, because the exact amount was in the bills account ready to go, and since I don’t carry that debit card around with me, I didn’t have to worry about us overdrafting it.

After setting up the bill account, we went through our finances and made a list of ALL the monthly expenses we had, and when they were due. Here is a (fake) example. Not that you care- but none of the examples here are my finances. I made the list in Excel- you’ll see why in a minute. Mine is also done in Google Docs (or “Drive” now, I guess?) so I can access it even when I’m traveling, and share it with B.

Being crazy, I added to this list our monthly gas, spending (we give ourselves $50 a week each, and use it for anything for ourselves like clothes, dining out, etc), and groceries. I then added in our monthly pay (B’s is estimated based on the lowest pay he should make, because he’s hourly.) I was relieved to find it came out to a positive number. You need to check your monthly out versus income to make sure you are making more than you’re spending. If not, find out where you can cut. Cable, personal spending, find cheaper insurance, refinance loans, etc. (We’ve been there- why do you think we moved into B’s mom’s house last year?) Or, where can you add income? You need to make sure you’re “in the black” before setting your Excel budget.

Once you know your list of bills is within your monthly means, set up your month.

First step is to set up a column for each payday. In the example above, I show person “A” gets paid twice a month, as does person “B,” on the opposite every-other-week schedule. Input your weekly groceries, spending, and gas on each week first. (This example shows them spending $100 on groceries, $50 each spending money, and $20 each on gas.) Then, input all of the bills you had listed out above.

I’m not going to lie, it felt like putting together an impossible jigsaw puzzle the first time. You need to make sure that the bills don’t go over what you get paid that week, but also that they’re not being paid after when they’re due. (PS- you can change due dates usually- just call!) It can be made easier or more difficult by your pay schedule. I get paid monthly, and B is paid weekly, which works out GREAT for us. Most of our bills (all the large ones) are paid the week I’m paid, and smaller ones and our weekly needs come from B’s pay on the other weeks. If you get paid twice monthly, you’d need to budget out two weeks of groceries, spend, etc. If you only have smaller weekly paydays, you might need to split up larger bills, like rent. That’s where a “reserve” checking account comes in handy- so you can put aside that part of the payment until you have the rest of it. Some in-depth examples:

  • A – Put the estimated pay and date on the left column for that pay week.
  • B – Put the exact pay on the right hand column. If you had to estimate for an hourly employee, put the estimate (which should be a “worst case” estimate) and then on payday, before you pay bills, change it to the real pay, so you can see where you’re at financially. I’ll get to what to do with leftover money later.
  • C – If you have to split a payment of something large (in this example, rent) note that it’s a partial. Some bills you might be able to pay a few times (I can split up utilities into a few payments, which is nice in the Florida summer when they are $250 plus) or you might have to put aside that money in a reserve account, or even as cash. Find a way to set it aside without accidentally spending it!
  • D – Represent the bills as a negative number so that the Excel formula works. Remember to list ALL bills, even the small ones (like Netflix!) After I pay a bill, I change the color to lighter grey so I know it’s been scheduled or paid.
  • E – On the left column, write “TOTAL.” On the right, under all the bill amounts, type the formula. =SUM(B2:B10) (For this example, it’s in column B, but replace B with whatever letter column it’s in.) In this formula,”B2″ should be whatever cell (column and row) your pay amount is in – for this example, 1,000. “B10” is the last bill’s column – for this example, -150. After you type in the formula and hit enter, it should do the math and show the number that represents the money you have left. Check it with a calculator to be sure.

What do you do with leftover money? That’s up to you. If you are trying to pay down debt, you might put it towards a loan or credit card that’s high interest, or close to being paid in full. You might decide to save the extra money towards something, like a fancy dinner out. You may want to put it aside in a reserve account if you know another week will be tighter.

What do you do if your “total” is negative on a payday? Sometimes, you just have to have a negative week when creating your monthly budget. If you can, try to set aside money from a week prior. Since you’re planning out your whole month, you should see this coming. But if you can’t protect yourself ahead of time, then you should take money out of whatever spending money you set aside, then groceries.

How do you handle gas/spending/grocery money? I take out cash every week for our gas, groceries, and spending money. It makes it easier for us to keep track of how much we spend. If we want to buy something online, I’ll either deposit the cash and then buy it, or wait until payday to buy it, and then take out less cash for myself that week. It also makes us more responsible with groceries and gas- we have to stay under budget (generally it’s me with grocery shopping, and B uses way more gas) or it comes out of our pocket. (You could also create another checking account to handle this “pocket” money if you don’t like using cash. It does get a bit confusing to have a few more checking accounts, but it would make it easier if you shop online a lot, or hate holding onto cash.)

How do you pay people with checks? I hate checks! That whole waiting for people to cash them dance is a slow torture. I suppose, I could have checks made from our bills account, and then if I write a $100 check, transfer $100 to the bills account. However, our bank account allows free cashier’s checks. That is how I do all my checks- so the money is already taken out (and protected) same-day. If I owe someone money, I’ll send it over PayPal or using my bank’s free money sending service – again, out of the bills account.

I got one month done- how do I do the next? I copy and paste the next month below the current one, change the dates to be accurate, then based on due dates for the bills, I adjust them as needed. When making changes, always check the “TOTAL” formula to make sure it’s still including all cells. It never hurts to double check!

The difference in where we are today from just one year ago is huge. We’re getting ahead of the game and starting to take control of our finances. It is weird to see close to zero dollars in our account and not panic- but each week we realize more that $0 is good, because all the bills have been taken care of, and our spending/gas/grocery money is in our wallets.

Please don’t hesitate to reach out to me with any questions, or for help with creating the Excel document! I am the world’s biggest Excel nerd, but this should be pretty basic. Still- I’m available if you need me. I hope this has helped give you some ideas about budgeting using your bank and Excel!

12 comments to Stupid Easy Budgeting with Excel

  • Layla

    I love you for this – thank you. Going to add a “bills” account to my checking account tomorrow.

  • Kelly

    I love that you posted this!! I love the whole idea of having several checking accounts, especially since Brad does all the bill paying and I just see what’s in there immediately so I could potentially overspend. Another idea for the bills is to do budget billing all year so you know exactly what your bill will be each month. We do it with our electric since it usually skyrockets in the summer.

  • Katie

    Oh hell, my husband would love you. We have an excel budget and I run and hide everytime it comes out. It definitely works but I hate hearing about it when I go over.

  • Judy Kelley

    Thanks, Kelly! You have some great ideas and methods. The bills account is a really good idea that I can see would really help my family.

  • Katherine

    Several banks (ING, Allies) allow for unlimited checking accounts under the same account. We have sub accounts for every bill (mortgage, student loan, cell phone, etc.). We also use the excel/google drive method to track where each paycheck needs transferred! The other thing we do is also have a savings/debt spreadsheet that we update monthly. We track our debt to make are it is going down (student loans, credit card, mortgage) and savings to make sure it is going up (long term savings, general savings, and emergency fund). Just thought I’d share what is working for us!

  • Great post! During the last year I’ve done some budgeting & really tried to come up with a system and budget that works. Unfortunately I’ve been too busy to take the time & keep up with it. Next year I won’t have school, so I’m hoping I can really focus on our budget!

  • mommers

    So proud of you! I guarantee that if you continue these good habits you will be sitting pretty when you are older, and believe me – it comes fast!!!

  • Debbie

    Interesting post. Just wondering how you budget for gifts (like Christmas) and things like wedding gifts that happen throughout the year. My husband and I put $20 aside every month for Christmas so that in December we have $240 saved up to spend on Christmas gifts, and then we have a monthly gift budget that we use to buy birthday gifts, etc….but the wedding gifts throw me off because I don’t know with certainty that we will be invited to a wedding until we get an invitation and wedding gifts are usually something we would want to spend more than our normal $20/month gift budget. And then if a wedding falls the same month as someone’s birthday or worse–two weddings fall in one month–it throws us off. Advice?

    • kjpugs

      Hey Debbie!! For Christmas, we started early and used our own weekly spend towards gifts. Mostly, I covered my family/friends and B covered his. Weeks that he worked a lot and we had some extra, we grabbed a little to use towards gifts too. We like to plan ahead. For a wedding, we’d plan ahead and I like to see if I can work it into the budget (same with travel, I usually know ahead of time so I’ll try to see where we can take out $50 here, $100 there.) If we can’t afford to go, we don’t go. I’m also a HUGE discount/coupon shopper so that helps! But with both of us getting $200-250 a month EACH for personal spending, that covers a lot of incidentals like gifts.

  • lex

    i do this too, i have 3 accounts – 2 chequing and 1 savings. the main chequing is the one that i use for everything, the second is for cheques and bill payments. i divide all my bill payments in two and take them from each paycheque, same with rent and large loan payments.

    i like seeing where my money goes every payday. i also stick 100$ each payday into savings for trips or stupid car repairs :p

  • Nina

    I SUCK at budgeting! I talked with my manager at work about the Dave Ramsay approach and got really into it and then my budget changed and we got lazy about it. I wish you were here! Then you could really get in depth with me! Miss you!

  • I so needed to read this! We’re just on my income while Ronnie’s in school and it gets really tough sometimes. I’ve never been one to budget either but I know that there’s no better time than right now. This post will be such a huge help to us, thanks for sharing!!

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>