Excel is a very useful tool if you want to keep track of your finances and be in control of your budget. Setting up a budget in Excel is a great idea, particularly for people who are totally disorganized and spend more than what they earn simply because they don’t have a clear picture of their financial situation. Besides, Excel can be a great substitute of any complicated financial planning software you may find in the market that requires more time to learn and deal with.
Many people think that setting up an Excel budget is difficult and requires a great deal of knowledge. Especially, if you have never had a budget before, you may be feeling intimidated by the prospect of using Excel. However, things are not so complicated. If you use an Excel spreadsheet to track your income and expenses, you have to be honest and plug in real numbers so that you derive an outcome that portrays your real financial situation.
A) Expenses
Typically, a complete Excel budget should include the following categories:
- Housing
Under this title you can include mortgage or rent (include home equity loans as well); second mortgage or rent; phone; electricity; cable TV; gas; water & sewer; supplies; waste removal; maintenance/repairs; and other.
- Transportation
Under this title you can include car payments; car insurance; fuel expenses; maintenance/repairs; bus/taxi fares; licensing and other.
- Insurance
Under this title you can include home insurance; health insurance; life insurance and any other type of insurance you may have.
- Food
Under this title you can include groceries; dining out and other food-related expenses.
- Children
Under this title you can include medical expenses; clothing; school tuition; school supplies; lunch money; child care; toys/games; and other.
- Pets
Under this title you can include pet food expenses; medical expenses; grooming expenses; toys; and other.
- Personal Care
Under this title you can include medical expenses related to personal care; hair/nails; clothing; dry cleaning; health club; and other.
- Entertainment
Under this title you can include video/DVD; cds; movies; concerts; sporting events; live theater; and other.
- Loans
Under this title you can include personal loans; student loans; credit card expenses and other loans.
- Taxes
Under this title you can include federal taxes; state taxes; local taxes and other.
- Savings / Investments
Under this title you can include retirement account; investment account; college; and other.
- Gifts / Donations
Under this title you can lists all the charities you have contributed money to.
- Legal
Under this title you can include attorney expenses; alimony; payments on lien or judgment and other.
Each category has a subtotal that can be calculated as follows:
In cell A1, you plug in the title ‘Monthly Budget’.
In cell A3, you plug in the title ‘Housing’.
In cell A4, you plug in the title ‘Mortgage or rent’. In cell B4, you plug in the relevant figure.
In cell A5, you plug in the title ‘Second mortgage or rent’. In cell B5, you plug in the relevant figure.
In cell A6, you plug in the title ‘Phone’. In cell B6, you plug in the relevant figure.
You go on until you finish with all your entries that are related to the Housing category until you reach cell A15. There, you plug in the title ‘Subtotal’ and you sum up all B cells in cell B15 to calculate the subtotal of Housing category. The formula used is =SUM(B4:B14), which actually calculates the sum from cell B4 where your first entry in the Housing category is, until cell B14 where you last entry in the Housing category is. Just make sure that you plug in the formula in cell B15 to avoid circular references problems.
You do the same for all other categories and you derive 13 subtotals, as much as your categories are.
Then, to calculate the grand total of all your expenses you go to cell B88 and you add all subtotal cells. For instance, if subtotal cells are B15, B23, B28, B32, B41, B46, B53, B61, B66, B71, B76, B79, and B84, in that cell you plug in =B15+ B23+ B28+ B32+ B41+ B46+ B53+ B61+ B66+ B71+ B76+ B79+ B84, and you derive the total of your monthly expenses.
B) Income
In cell D3, you plug in the title ‘Income 1’. In cell E3, you plug in the relevant figure.
In cell D4, you plug in the title ‘Income 2’. In cell E4, you plug in the relevant figure.
In cell D5, you plug in the title ‘Extra Income’. In cell E5, you plug in the relevant figure.
In cell E6, you calculate you total monthly income by plugging in the formula =SUM(E3:E5).
C) Difference
To calculate your actual financial position, you deduct your expenses (cell B88) from your income (cell E6). In an empty cell at the bottom you plug in =E6-B88. If the difference is positive, it means that you are building wealth and you are in control of your finances. But even if the difference is negative, you have a clear picture of the areas that need improvement and better financial management.
All in all, with Excel you are less likely to make mistakes and you can keep a running total of how much money is spent per month and how much is left. These totals are automatically adjusted every time a new entry is made into the spreadsheet. By setting up a budget in Excel you can make long- and short-term projections about your financial situation and plan for major financial changes. In that way, you always get the most of your money and you, ultimately, achieve peace of mind.
