April 15th is right around the corner. Boy, am I glad I’m not a practicing CPA anymore.

Because the US Federal Tax rates are progressive, all of your income is not taxed at the same rate. To figure your tax liability in Excel, you can use VLOOKUP and a tax rate table with three columns. The example below shows how a single person might figure it.

You can see the formula in C5. Simply fill that formula down to C11. The SUM funtion is used in the formula so that the same formula can be used for all the cells in column C. It serves to ignore the text ‘Tax Table’ in the first row which would cause an error otherwise.

Cell A2 is where you enter your taxable income (Sorry, I can’t figure that one out for you – my hard drive isn’t big enough to hold the spreadsheet). The formula in B2 is:

=VLOOKUP(A2,B5:D11,2)+((A2-VLOOKUP(A2,B5:D11,1))*(VLOOKUP(A2,B5:D11,3)))

Because we omit the 4th argument in VLOOKUP, it finds the first row whose value does not exceed the lookup value (Row 9, in this case). The first VLOOKUP returns the tax liability on all the income that is below the next lower threshhold (68,800 results in a tax liability of 14,010). The second VLOOKUP returns the the upper limit of the next lower threshhold which is subtracted from our taxable income. The result of the subtraction is multiplied by the tax rate (the result of the third VLOOKUP), also called the Marginal Rate.

You are now qualified to work for the IRS. Don’t forget who helped you get there.

P.S. If you have a taxable loss in excess of $10 million, this formula will fail. You should be used to failure at that point, however.