On Sun, 08 Jul 2007 20:18:20 -0700, joeu2004 wrote:

On Jul 8, 5:49 pm, Ron Rosenfeld wrote:
Set up a table someplace on your worksheet:

I agree that the table lookup method is easier to change later. But
the trick is to understand the tax tables well enough to set up the
table correctly.

$ 0 $ 0.00 0%
$ 102 $ 0.00 10%
$ 389 $ 28.70 15%
$ 1,289 $ 163.70 25%
$ 2,964 $ 582.45 28%
$ 6,262 $1,505.89 33%
$13,525 $3,902.68 35%
[....]
You can use this formula:
=VLOOKUP(GrossIncome,BiWeeklySingle,2)+
(GrossIncome-VLOOKUP(GrossIncome,BiWeeklySingle,1))*
VLOOKUP(GrossIncome,BiWeeklySingle,3)

Technically, your formula does not follow the logic of the tax
tables. For example, it should assess 10% tax only on the amount
__over__ $102, whereas your formula ostensibly assesses 10% tax on the
amount equal to $102. (But not really. Continue reading....)

Of course, your formula works for the federal tables because the tax
computed for the lowest limit of one tax bracket is the same amount
computed for the highest limit of the previous tax bracket. For
example, (389-389)*15%+28.70, which your formula computes, is the same
as (389-102)*10%, which is the correct formula to use. (And
(102-102)*10% is the same as (102-0)*0%.)

IMHO (and I'm sure you would agree), that should be true of any
"reasonable" tax table. So it is easy to become complacent.

But a year or two ago, I discovered that the Georgia tables did not
have that nice property. Arrgghh!

Since then, I have been careful to ensure that the lookup table
follows the logic of the tax tables to the letter. This makes the
lookup table slightly more complicated and a tad error-prone to set
up.

(For the same reason, I eschew formulas that rely on the "delta
percentages" between the tax brackets, including those formulas that I
promoted in the past.)

The formula I wrote was designed to work with the Federal tax tables using the
logic with which they were constructed. It was NOT designed to also work with
the Georgia tax tables, or the NH tax tables, or any number of other untested
tables which may have been constructed using a different logic.
--ron