"MZ" wrote:
I am trying to make a single formula that will calculate
a person's taxable income assuming the six different
marginal tax rates that range from 10% to 35%, as
income increases

I presume you mean you are trying to calculate the tax, not the taxable
income [sic]. Of course, a person's taxable income has nothing to do with
the marginal tax rates.

For a single taxpayer, the marginal tax rates and the
associated income amounts a

First, it would be prudent to enter the entire tax rate schedule, namely in
A1:C7 (forgive me if the format is mangled):

0 0 0%
0 0 10%
8350 835 15%
33950 4675 25%
82250 16750 28%
171550 41475 33%
372950 108216 35%

The first column is the marginal taxable income, the second column is the
marginal minimum tax, and the last column is the marginal tax rate. (The
missing fourth column is the same as the first column.)

Note that I added one row of zeros first. That makes some formulas easier
to write. We really only need the 0% in column C.

Then the tax can be computed by either of the following formulas:

=ROUND(VLOOKUP(D36,A2:C7,2) +
VLOOKUP(D36,A2:C7,3)*(D36-VLOOKUP(D36,A2:C7,1)), 2)

or

=ROUND(SUMPRODUCT(--(D36A2:A7), D36-A2:A7, C2:C7-C1:C6), 2)

The SUMPRODUCT is more efficient, but it might be harder to understand. It
can be made more efficient by using constant arrays instead of a table. But
that might be more error-prone and harder to maintain from year to year.

Note that the additional "zero" row is not needed for the VLOOKUP formula.

Technically, the VLOOKUP formula is off by one. It could be corrected; but
it should work for correctly-designed marginal tax rate schedules. (One
year, the Georgia state tables were not designed correctly! I think that has
been corrected since.)

Finally, ROUND(...,2) could be changed to ROUND(...,0) if you want to round
to the dollar instead of to the penny.


----- original message -----

"MZ" wrote:
I am trying to make a single formula that will calculate a person's taxable
income assuming the six different marginal tax rates that range from 10% to
35%, as income increases

For a single taxpayer, the marginal tax rates and the associated income
amounts a

10% up to $8,350
15% up to $33,950
25% up to $82,250
28% up to $171,550
33% up to $372,950
35% over $372,950


I am using "IF" statements to test for each marginal tax rate.
I began the formula as follows, but it returns a value error.

Cell D36 is the taxpayer's income

=IF(D36<=8350,D36/10),IF(AND(D368350,D36<=D31),SUM(D36/10,D36/6.666))

Also, is the only way to multiply by a percentage to make a fraction with
the denominator the value of the percentage - e.g. 6.666 is 15%?

Thank you
--
MZ