For many years my income and therefore my income taxes has varied significantly from year to year, and for many years I would pay in way too much in taxes one year only to follow it up the next year by having to write a big check on April 15^th.   In 1999 I finally tired of this routine and decided to do something about it.

In looking at my income tax returns, it dawned on me that all of the forms and verbiage notwithstanding, our income taxes are really nothing more than a mathematical equation.  It can be a very complicated equation with lots and lots and lots of options associated with it, but in the end it was still just an equation.  And because it was an equation, that meant I should be able to put together an Excel spreadsheet that could calculate my taxes for me.

So I set about creating this spreadsheet, and I discovered that my income taxes were not too terribly complicated.  The spreadsheet I needed to create was fairly simple- at least when it was compared to many of the spreadsheets I had created in my work.  My situation was as follows:  income came from salaries, bonuses, dividends and interest.  That income could be reduced for tax purposes by 401k, and Flexible Spending Account contributions.  Deductions came exclusively from Schedule A, which included mortgage interest, state income taxes, property taxes, other taxes, medical expenses and charitable contributions.

In addition to mirroring the tax calculation by using my income and deductions, I also needed to be able to take my actual information through any part of the year and project that out to the annual amounts that would be used to determine what my taxes were likely to be.  Finally, I needed to take this total projected tax bill and compare it to my total projected taxes that would be paid in via withholding and estimated payments.

All of this was fairly simple to achieve, and I have used this spreadsheet ever since to determine throughout the year precisely what my income taxes were going to be.  With this information, I was then able to change my withholding through the year to make it match up with what I projected my year-end taxes would be.

With this post I am now offering this spreadsheet for sale for $15 to anyone who would like to be able to track their own tax situation through the year.  Before purchasing it, however, you should understand that the spreadsheet is tied to a particular set of tax conditions that may not apply to your situation.  This spreadsheet will work perfectly as it is if your situation is married with one paycheck that is paid weekly, bi-weekly or semi-monthly, and that you only use either the standard deduction or Schedule A deductions.  If you do have an interest in using this spreadsheet, but it doesn’t fit your situation, I would be happy to modify it to match it to your situation for an additional $35.  I am also available to assist you with setting this up and showing you how to make it work for an additional fee.

The following things would be likely changes to the spreadsheet for many people:

• Schedule C – Business Income
• Schedule D – Capital Gains
• Schedule E – Rental Real Estate or Partnerships or S Corporations
• Two paycheck families
• Single status tax calculation

Following are step-by-step directions on how to go about using the spreadsheet as it is currently constituted (remember that if you don’t fit these circumstances you would need to have the spreadsheet modified to match your unique situation):

1. Set your standards:
   1. Choose your pay frequency and the gross pay you receive.
   2. Determine if you are going to use the standard deduction or Schedule A.
   3. Plug in the number of paychecks, quarters, and months you have tied to the YTD numbers you will be using.
   4. Plug in income withholding offsets for 401k, FSA, or Health Insurance.  The amount should be per paycheck.
   5. Plug in any quarterly estimated tax payments you are making.
   6. Plug in your current Federal and State withholding amounts per paycheck.
2. Input Income Tax Projection data YTD (any numbers in blue)
   1. Plug in the total salary you have received year-to-date (Y-T-D).
   2. Plug in the Y-T-D income offset withholding you have had.
   3. Put in any bonus you have received or expect to receive this year.
   4. Plug in any dividends or interest you have received year-to-date.
   5. Put in any other income you have received.
   6. Plug in Y-T-D estimated taxes paid in.
   7. Plug in federal withholding taxes paid in Y-T-D.
   8. If you are using the ‘Standard Deduction’ then make sure all Schedule A blue numbers are zero.  Otherwise plug in all Schedule A Deductions Y-T-D (all in blue).  The State Taxes and Medical Expenses are a formula and SHOULD NOT BE INPUT here.
   9. Plug in Y-T-D state estimated taxes paid in.
   10. Plug in state withholding taxes paid in Y-T-D.
   11. Lastly plug in your Medical Expenses paid in Y-T-D.

If the amounts you plug in year-to-date are in sync with all of the Standards you have set up then the Projection Tool will automatically tell you how much income tax you are projected to owe for the year for both federal and state, and it will tell you whether you will be owing any tax or expecting a refund and how much.

Finally, let me put in this disclaimer.  This spreadsheet is designed to be an estimating tool to help you determine what your likely tax situation will be each year.  It is not designed to actually ‘prepare’ your taxes for you.  If you need to have your taxes prepared, I can help you with that through association with a tax preparer I am working with, but you should not confuse this spreadsheet as tax preparation.  It will simply keep you from being surprised at year-end if used properly.