Crash Course: Use Excel to Conquer Your Taxes and Manage Your Budget

Taxes are painful. And for most of us, so is Microsoft Excel. However – and I know this is counterintuitive, but I swear it's true – when you use one, you can mitigate the pain of the other. Microsoft's spreadsheet software can bring some sanity to the world of taxes. 

There are plenty of good online tools, like Mint, that do some of this for you, but you’re often dealing with perpetual software changes and an annoying up-sell to services you neither want nor need. Excel is consistent and ensures everything is on your hard drive should you find yourself without Internet access. And it’s the gold standard for the accounting world, so it’s sure to make your tax preparer happy, too.

I asked my wife, a former cost and forensic accountant with a Fortune 500 company, for the ways in which she's optimized Excel to make tax preparation less painful. Here are the general principles she recommends, and here's how you can put them to work.

(One more note before we get started:  2015 may be a wash, and you’ve already filed your taxes. But there is hope for 2016 if you start now.  You’ll have a little catch up work to do to pull January and February into shape, but it can be done. ) 

STEP ONE: BITE THE BULLET AND SET UP A BUDGET SPREADSHEET

Why you'll want to do this: Excel lets you keep a lot of data tidily organized in one place. You'll have one-click access to a record of your expenditures so you can cross-reference them with your bank account and credit card statements. This way if there’s a purchase you’re not sure about, you’re able to check it against your spreadsheet. Additionally, Excel offers tremendously powerful tools for crunching data and finding discrepancies. 

How you'll do this:  Pictured is a sample spreadsheet that illustrates the goal. There’s one column for your projected expenditures, another for what you actually spend, and then the difference, which is calculated by plugging in a formula into Excel.  This is the budget part.  You can give yourself a realistic (or not) amount you expect to spend on various expenses throughout the month and then track what you’ve actually spent by checking with your bank account online.  

There’ll be no need for random paper statements if you deploy this method.

To get started: Follow these steps to format a simple budget spreadsheet that works month-by-month.

1. Set up a column that list your projected monthly expenses, like the mortgage, your utilities, car payment, clothes, food, entertainment, etc. 

2. Set up a second column that defines how much money you want to allocate to each expense. This is your "budget" column.

3. Set up a third column that defines how much money you actually spent in each expense category. This is your "actual" column.

4. Set up a fourth column where you will put in formulas to pull totals from your expense tracking.

5. Set up the totals under the columns. You can then use a formula to auto-tally the total projected expenses and the total actual expenses.

In our example spreadsheet below, you'd use the formula =sum(C6:C18) in cell C21, then use =sum(E6:E18) in cell E21. To determine the differences between the two, you'd use the formula =sum(C6-E6) in the first row's cell (H6), =sum(C7-E7) in the second row's cell (H7), and so on down, then use =sum(H6:H18) in the final cell.

These categories will give you a clear picture of where you spend your money. 

Now, in order to know whether or not you're on budget, you've got to track all your expenses. So the second part of this spreadsheet is your monthly expense tracking. Every dollar you spend will be categorized and totaled. To do this, follow these steps.

1. Look to the right of the projected budget columns. Now, create a column to enter the name of every expenditure as you spend money during the month. These columns should match the labels from your budget section.   

2. Every entity that gets your money gets a row. Each row is money spent and where it was spent. In the example below, you'll see that cells M5-M37 all have different names, one for each place where money was spent.

3. Enter everything you spend on each row.

Putting everything you spend every month into this spreadsheet has a dual purpose: not only can you track these expenses to compare them with the budget, but you can ensure that every deduction is something you actually spent and not something that someone with your banking information spent for you.  With so many security concerns, it’s a good practice. 

These columns will track your expenses with a simple formula.

Assuming you have filled out all of your expenses, at the end of the month, your spreadsheet will total each of the categories and fill the columns of actual expenses back in your budget worksheet. Here's how that works:

1. You'll note which cell you're going to be entering the expenses into.

2. You can go back to the cell which will provide the actual expenses, then fill in. Using the mortgage expenses as an example: The amount spent for the month is entered in cell Q5. Back in the budget part of the worksheet, you'd also want to enter that in E6. But why should you do it twice? Just use a formula in E6 -- type =SUM(Q5), and when Q5 is filled in, so will be E6.

You can tally multiple cells too. If you look back at the expenses section, there are gas, electric and garbage bills entered in cells R6, R7 and R8. In the budgeting section, these are all counted under "utilities" in cell E7. So you'd fill in cell E7 with =SUM(R6:R8), and the sum will automatically tally as you update R6 through R8 with real numbers.

Filling out expenses will show you whether you’ve overspent against your budget or not.  Going forward you can either adjust the allocation of your money according to how you are actually spending or cut back in some areas.

Now how does it tie into taxes? Once you have all your sums, you can transfer the numbers into a section of the spreadsheet that totals the kind of expenses you can deduct on your taxes, such a charitable contributions, insurance and utilities (for home offices), or childcare expenses.  You’ll be glad you did when you're reviewing your standard IRS Form 1040 and Schedule A.  

And one final note: Each month, you can simply duplicate this worksheet within your Excel workbook. Then, at year's end, you can grab the numbers you need when you're tallying up your final expenses.

Here's what you can automate: While automation may be ideal, it’s tricky to make happen. If your bank or financial service like Intuit’s Mint allows you to export your banking information into a CSV format, you can then import it into Excel. However, you’ll have to play with the formatting to make it something that you want. 

An additional note: As an added security step, make sure your password protects your spreadsheet.  This is a simple process.  Just get ready to save your document and locate the browse box at the bottom of the folder.  Then click on options and you’ll be offered the opportunity to password protect.  

Here is where your password will protect your spreadsheet.

How this helps your taxes:  Depending upon your situation, you’ll want to set up a column for the months of the year and a row of labels for deductible expenses. As you incur deductible expenses throughout the month, fill the dollars in under the appropriate category. (Or set up formulae that will allow the cell to auto-fill.) 

At the end of the year, when you total these categories you’ll have the amounts that can be transferred directly to your 1040 and Schedule A, or you can print out this section. 

You’ll have a ready-made section of your spreadsheet that will greatly ease your tax preparation.

STEP TWO: IT'S TIME TO DO SOME SCHEDULE-FU

Why you'll want to do this: With a minimal amount of maintenance, you’ll not only be able to keep a constant eye on your budget but also build your tax-time records. 

How you'll do this: Now that your spreadsheet is up and running, here are a few strategies to make it a part of your daily workflow.

Daily, or at the very least weekly, check your bank account. You’ll need to transfer all expenditures or any credits from your online banking information to the expense tracking portion of your spreadsheet. If you maintain this routine, you will be able to watch your expenses add up and approach your budget. Hopefully, not going over. 

At the end of the month, move to your IRS 1040 and Schedule A expenses section and fill in any expenses that match your categories. Resist the temptation to just throw stuff anywhere: a few minutes of precise categorization can go a long way.

Here's what you can automate: Perhaps you’re the forgetful type, or just extremely busy like the rest of us and could use an occasional reminder. You can tap into one of many different options here: a repeating calendar reminder, a tool like Todoist or Wunderlist, or tie reminders to a specific date through Google’s handy Inbox. Either way, a slight nudge is what you need to follow this key procedure.

How it helps your taxes: By bringing in the key budget items you’ll have a complete record of your expenditures, income, and a picture of where you stand financially. This means tax time will be a matter of referencing your spreadsheet and much less time searching through stacks of statements or digging through your email archive.

STEP THREE: NOW DO YOUR TAXES

How your hard work will pay off: When it comes to tax time, your accountant will worship at your feet with this kind of record-keeping. You are bound to do far better on your taxes if you are able to maximize itemized deductions instead of just taking the standard option. For some people this may not make sense, but if you have a job, mortgage/rent, car payments, child expenses, and other adult-ish items then it’s worth brushing up on these skills. 

How you set up your routines for the next tax year: Now it’s time to think ahead to next year. You’ll want to get going early with the same type of setup. The best way to do this is to copy over the same columns into a new spreadsheet or to start a second tab within the same file. Get started right away with tracking your expenditures and income for a much easier time next year.

SOME FINAL ADVICE

The good news is you’ve done the most difficult legwork already. The key to ensuring that this will actually be useful for you is to, of course, update it regularly. You just won’t have the budget visibility if you get behind in your updating.

It gets overwhelming if you let things go for a few weeks and then try and catch up but it can be done.  With the method created here, it will truly take only a few minutes per day.