How to create a househould budget using Excel

When it comes to budgeting, there are thousands of software programs available for sale. Some of the more popular ones, that integrate everything including your checking accounts and stock accounts, are from Quicken and Microsoft. There are also thousands of websites selling their own version of online budgets and budget programs with downloadable software. We prefer the most flexible way to create a budget, which is to do it yourself. It is also free to do it yourself.

To get started, make sure you have some kind of spreadsheet software on your computer. Microsoft Excel is by far the most popular version but it is expensive to own. If you are on a budget (and I'm guessing you are if you're reading this) you can download a free spreadsheet program that is almost identical to Excel and that is compatible with Excel. It is called OpenOffice.org Calc and you can get it directly from their website.

Once you've installed the spreadsheet software, you're ready for Step 2.

Next you'll need to setup your spreadsheet to get ready to create your family budget. We recommend starting with the first column, and filling in all of the budget line items. For reference, here is a copy of a free household budget worksheet.

Start with the income category, and create a budget line item for any type of income you receive. Include wages, salaries, child support, alimony, social security benefits, welfare payments, pension payments, trust payouts, and any part time or seasonal job income. Near the bottom of this list, you should include offsets to your income, such as witholdings for social security, income taxes and retirement accounts.

Below this, start a category for your expenses. You can order these expenses by category, such as living expenses, auto, insurance. Or you can order them by the largest expenses first, which is typically rent or mortgage. Be sure to include expenses that you only pay once per year like real estate taxes or auto license renewals. There are hundreds of items to include in this section of your budget, and the more detailed you are the better control you will be able to have over your personal finances. Be sure to include the following expenses, as well as any other expenses you can think of: rent, mortgage, property taxes, auto payments, repair costs, maintenance costs, clothing, electronics purchases, groceries, dining out expenses, dry cleaning, lawn services, cleaning costs, annual gifts, charity payments, parking costs, car insurance, home insurance, renters insurance, health insurance, life insurance, camper or boat payments and insurance, gas costs, utilities including gas, electric, water and sewer, hair and beauty costs, sports costs, entertainment expenses, cable or satellite, daily coffees, work related expenses, dentist visits, vision costs, education expenses, student loan payments, any other loans, pet and petsitting costs, and any other budget line items you can think of.

Now that you've got a fairly complete list of all off your income and expenses in the first column of your spreadsheet, its time to make the next three columns. Column 2 should be Weekly Expenses, Column 3 should be Monthly Expenses, and Column 4 should be Annual Expenses. Format the columns in numeric or dollar format with either two or no decimal places.

Now, you're ready to move on to Step 3.

Now that you have the line items filled in on your household budget worksheet, it's time to actually gather the data to populate your spreadsheet. Most items you can readily find using your pay stubs, bank and credit card statements. Other items you can estimate, but for most items its best if you do the research and find the actual figures. The reason its important to base your budget on actuals rather than estimates is because it is extremely common for people to underestimate their expenses. For example, ask someone how much they spend on coffee each month and they might say around $30. However, add up the 30 K-cups at 50 cents each, the four $5 mochas each week, and the other ten $2 coffees purchased while at work, and the total coffee cost is really about $55, and that doesn't include tips. You can see that in this case the estimate was off by over 80%!

Your budget will only be as accurate as you make it. If you really want to make it better, track every expense for a week or even a month and add the real figures to your budget. For help doing this, you can carry around a notepad or get a free app for your mobile phone that can do it for you.

Once you've gathered all of the information for your budget, its time to go to Step 4.

Now that you've collected the information needed to complete your budget, it's time to enter the information into the budget template. Add the monthly expenses in the monthly column, the annual expenses in the annual column, and all other expenses in the weekly column.

When you're finished, you'll need to make a few calculations on your spreadsheet. First, for all annual expenses and income, add a formula for the monthly figure that is equal to the annual figure divided by twelve and to the weekly column that is divided by 52. Do the same thing for the monthly budget but multiply by 12 for the annual adjustement and multiply by 12/52 for the weekly adjustment. Finally, propogate the weekly items into the monthly and annual columns by multiplying by 52/12 and by 52, respectively.

Take your time and use common sense to make sure that your figures are in line with your expectations. If they are way off, there is likely a mistake and you should investigate.

Now that you've got your budget spreadsheet filled out, its time to move on to Step 5.

Visit your budget spreadsheet and add rows that total all of the income fields, and all of the expense fields. Then, at the bottom, add a row that subtracts all of the expenses from your budget income. If the number is positive, you have a monthly surplus, and if negative, you have a monthly deficit, or shortfall. Surpluses can be used to fund retirement, investment and savings accounts. Deficits will end up becoming debt on either credit cards or something else.

Remember that the reason you created your budget is to analyze your spending. If you have a deficit, you'll need to find a way to change it to a surplus.

The final part of this step is to add several other rows at the bottom of your spreadsheet that help you analyze your spending. Some suggestions would be to add up all fixed expenses (that cannot be change), all discretionary expenses (expenses that are not neccessities like cable and dining out), all household expenses, entertainment expenses, and of course, all necessities. Looking at these figures can help you when you move on to Step 6.

It's time to really look hard at your budget and go over each line item. Determine if there is any way to raise any income figures or lower any expense figures. Here are some examples on how to find ways to lower your expenses:

- Refinance any debt such as mortgage to a lower interest rate
- Find better rates on any insurance, cable, phone or other fixed costs
- Get rid of any services that you are spending money on but that you can do yourself (like cleaning, lawn care, coffee)
- Find ways to reduce food expenses by being more frugal and changing your ordering habits at restaurants
- Focus on a few costs each week and research ways to save money on those expenses
- Work toward increasing income through a promotion, extra schooling, moonlighting, or overtime

Do whatever it takes to lower your expenses. There are thousands of ways to save money on every aspect of your normal life. Do some research and find new and unique ways to save money.

Now, you're ready for the final step.

Keep your budget active and continue to update it with the new budget methods you've put into place. Keep a copy of your actual budget each month and compare them each month. Make a chart that shows your total expenses each month and focus on lowering them. Constantly look for ways to improve your budget that we haven't discussed here.

And stay motivated. Make a new investment account with all of the additional savings you find. Keep adding to it and watch it grow. It will keep you motivated to keep saving and budgeting. The more it grows, the more impressive you've done. Imagine what all that savings will do for your future!