Make a Personal Budget on Excel in 4 Easy Steps

Ads by Google

excel_logoAbout 10 years ago, my wife and I were straddled with so much debt that we thought it would take the rest of our lives, or at least the next sixty years, to pay it all off. The combination of school loans, car loans and credit card debt was enough to make a grown man double over and cry. There came a moment when we realized that we either had to make a personal budget that could outsmart the system or it would keep us enslaved for our entire adult lives.

That’s when I sat down with a blank Microsoft Excel spreadsheet in front of me and just started playing around, using various techniques to cut down our budget to bare bones, and create a debt plan that wouldn’t take decades to eliminate our debt, but also wouldn’t keep us eating Macaroni and Cheese for dinner until we retire. In the end, I was able to eliminate all of our credit card debt in only 5 years, and we even had good enough credit in the end to get approved for a low-rate mortgage to buy our first home.

Today, I’m going to share a few of the nifty spreadsheet techniques that I used to generate a usable (and useful) budget, and finally, I’m going to share a technique to pay down your debt in a fraction of the time using the same exact payments you’re making today.  It’s a trick that I’ve seen a lot of guys trying to sell elsewhere on the net – I’m going to share it with MakeUseOf readers here, for free.

Step 1: Structure a Personal Budget Spreadsheet That Doesn’t Drive You Nuts

Anyone who has tried to make a personal “budget” knows the basics. You need to make a log of all of your bills and all of your income. Your bottom line is how much you have left over for fun, or how much fun you have to cut out of your lifestyle before you go bankrupt. It sounds easy, but when you start entering all of your details into a spreadsheet, things get very messy very quickly. A lot of people give up after the first attempt.

sheetlayout1

The basic layout is easy enough. List your bills in the first left column, and then in the next few columns list total balance you owe, monthly required payments, and the date that the bill is usually due.  These four columns are really all you need to create a budget.

However, here I’ve gone an extra step and added a column to the right for each month for easy expense tracking. However, once you get a large number of columns and rows, the screen starts to scroll and you can’t always see the bills to the left or the header at the top. The quick and easy technique to fix this is using the “freeze panes” feature.

freezepanes2

First, select the box where the intersection at the upper left represents the row and column that you don’t want to scroll when you use the spreadsheet’s scrollbars. First, select Window->Split, and then go back again and select Window->Freeze Panes.

Ads by Google

scrolldown3

Now, when you scroll up or down (as shown here), the header and left column remain static, so you always know what the value you’ve selected applies to. This is a very useful feature and since I have a very bad short term memory, it has saved me a great deal of frustration where I normally would have had to keep scrolling back to check which bill I’d selected.

Step 2: Lay Out an Organized Budget Using Shading

I remember looking for a free budget spreadsheet back then and finding all of these templates filled with data that just made my head ache. Without clear lines separating the major sections of your budget, you’ll have a hard time zoning in on the area that you’re interested in. The best way to organize a budget spreadsheet is by shading each summary section between your major groups.

topsections4

As you can see here, the first section of the budget pertains to bills, including household utilities and fixed bills, as well as another section devoted to only credit cards. At the bottom of this particular section, the total for fixed bills is highlighted with light green shading so it’s clear and easy to find.

midsections5b

As you can see, once you start shading rows, the entire spreadsheet becomes much more organized and easier to follow.

fill

The “Fill” tool is located on the Excel menu bar and appears as a paint can tipping over with paint pouring out. Just highlight the entire row (click the numbered gray cell to the left) and then click the Fill button and select what color you’d like to use.

Step #3 – Use Excel Formulas to Project Your Credit Card Balances Into the Future

Now that you can make a personal budget that is well organized and structured in a way that’s very easy to follow, the next step is attacking that nagging credit card debt that’s been plaguing you for years. In these next examples, the same formatting techniques are used to create a list of credit card balances and monthly payments.

debtsetup7

Set up your debt log in the same way – split and freeze the panes, but this time list each month along the left, and your credit card balances (and monthly payments) to the right. After you’ve entered in your current balance in the top cell (for example, in this case Capital One is $3,000), in the next cell below it you would enter a formula that multiplies that balance by your card’s interest rate and divides by twelve. That is your estimated monthly interest.

Then you subtract your monthly payment from the balance, and add the interest that you just calculated. Once you’ve got that first cell calculated correctly, you can duplicate the formula for every month below it by clicking and holding the small box to the lower right of the cell you just calculated, and dragging it down as far as you like. Each month will have a new calculated balance based on the previous months balance.

regularpayments8

When you do this projection, you’ll eventually find the spot where the balance is completely paid off. As you can see from my own calculations, when I maintain a $250 payment every month until it’s paid off, it’ll take me until July 2012 to pay off the entire Advanta credit card balance.

Step #4 – Recalculate Payments Based on Interest and Eliminate Your Debt

By playing around with this kind of spreadsheet, I uncovered the very simple, common-sense solution that a lot of scammers out there are charging people for. Instead of maintaining constant payments on each of your credit cards until it’s paid off – you pay the minimum balance on all of them, and divert all of your current “debt-payment” money toward the credit card with the highest interest. Here is how it works.

quickerpayoff9

This is also why I love Excel. By using the ability to “autofill” the monthly balance calculations, I tested different scenarios to pay off debts faster. Instead of paying $100 on Discover and taking until 2011 to pay that balance off, I paid $200 and will have it paid off by December 2009. Then I take that $200 and add it to the existing $250 I pay on Advanta.

As you can see, the Advanta balance is paid off in April of 2011, almost a year earlier. If you have additional credit card balances, you simply “snowball” the payment and eliminate your debt in months rather than years. Excel allows you to see that concept in a very cool graphical form by using the formula “autofill” feature.

Do you use Excel or other spreadsheets to make a personal budget or to plan out your family bills? Share your own tips and resources in the comments section below.

Ads by Google
Comments (22)
  • Tami G

    This was so helpful…I loved following your instructions for the credit card debt and seeing the difference of what even an extra few dollars a month can make toward eliminated those balances. I bought a book from a well known “manage your finances” guy quite awhile back who uses the snowball payment theory and just couldn’t get into the blah blah blah of it. You nut shelled it here and I thank you for that. You are right….Excel really makes the goal clear and it was fun to play with the payment amounts and watch the timeline shorten for payoff!

  • Anne Kleiber

    omg i totally love these steps!! gives me so much life and love in my body. So fulfilling. Praise Ryan and Jesus!

    xoxo

  • Rhys

    Hi Ryan

    I have created a website (EasyBudgeting) that makes using an Excel budget planner simple and straightforward.

    Excel is a great tool for budgeting but some people find it difficult or a bit overwhelming. I invested my time and knowledge to provide people with a solution to their budgeting needs without having to master Excel.

    It’s helped 100’s of people worldwide and may be useful for your visitors.

    Good luck, Rhys

  • Factopo

    These are some good tips, especially in the economy right now.

  • Peter

    I have always been the type that buys stuff until my account shows a low balance, then I back off until next payday. It’s gotten me into some trouble a few times but has never been a huge issue because I wasn’t dealing with massive amounts of money and was living with my parents. Well I got married this summer and both my wife and I haven’t changed our ways much. We buy what we need, with extra’s here and there (magazines, lunches, etc). And as of now it’s not a problem, but after reading your article and thinking about it, I realize that it could become a problem later on when we have more debt. I also think we could be saving a lot more money for future purchases (house mainly, future kid’s college), if we only paid more attention to our finances.
    Sorry for the wall-o-text, but I genuinely thank you for posting this. I am in the process of entering all my info now into a spreadsheet, and for the first time in a while I feel good about my financial future.

    • Ryan Dube

      Thanks Peter – it feels good reading this and I’m very happy that the article served as inspiration to crack open a spreadsheet and give it a shot. Best of luck to you and your wife – and here’s to a great financial future!

Load 10 more
Affiliate Disclamer

This review may contain affiliate links, which pays us a small compensation if you do decide to make a purchase based on our recommendation. Our judgement is in no way biased, and our recommendations are always based on the merits of the items.

For more details, please read our disclosure.
Affiliate Disclamer

This review may contain affiliate links, which pays us a small compensation if you do decide to make a purchase based on our recommendation. Our judgement is in no way biased, and our recommendations are always based on the merits of the items.

For more details, please read our disclosure.