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

Hi MZ,

To calculate a person's taxable income using the six different marginal tax rates, you can use the nested IF function. Here's an example formula that you can use:

1. =IF(D36<=8350,D36*0.1,
2. IF(D36<=33950,(D36-8350)*0.15+835,
3. IF(D36<=82250,(D36-33950)*0.25+4675,
4. IF(D36<=171550,(D36-82250)*0.28+4675+12000,
5. IF(D36<=372950,(D36-171550)*0.33+4675+12000+15087.6,
6. (D36-372950)*0.35+4675+12000+15087.6+93619.5)))))

This formula checks the income amount against each marginal tax rate and calculates the tax amount accordingly. The IF function is nested, meaning that there are multiple IF statements within each other.

To answer your second question, yes, you can convert a percentage to a fraction by dividing it by 100. For example, 15% is equal to 0.15 as a decimal or 15/100 as a fraction.

Create this table, say in a separate sheet (Sheet2):
A B
$0 10%
$8,350 15%
$33,950 25%
$82,250 28%
$171,550 33%
$372,950 35%
$372,950 35%

Then this formula returns tax rate for D36:
=VLOOKUP(D36,Sheet2!A:B,2)

--
Regards!
Stefi



€žMZ€� ezt Ã*rta:

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

Sorry, the last row is inintentionally and unneccesarily duplicated!
--
Regards!
Stefi



€žStefi€� ezt Ã*rta:

Create this table, say in a separate sheet (Sheet2):
A B
$0 10%
$8,350 15%
$33,950 25%
$82,250 28%
$171,550 33%
$372,950 35%
$372,950 35%

Then this formula returns tax rate for D36:
=VLOOKUP(D36,Sheet2!A:B,2)

--
Regards!
Stefi



€žMZ€� ezt Ã*rta:

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

Try

=SUMPRODUCT(--(D36{0;8350;39500;82500;171550;372950}),
(D36-{0;8350;39500;82500;171550;372950}), {0.1;0.05;0.1;0.03;0.05;0.02})

HTH

Bob


"MZ" wrote in message
...
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

And there is a typo in the correction: unintentionally
--
Regards!
Stefi



€žStefi€� ezt Ã*rta:

Sorry, the last row is inintentionally and unneccesarily duplicated!
--
Regards!
Stefi



€žStefi€� ezt Ã*rta:

Create this table, say in a separate sheet (Sheet2):
A B
$0 10%
$8,350 15%
$33,950 25%
$82,250 28%
$171,550 33%
$372,950 35%
$372,950 35%

Then this formula returns tax rate for D36:
=VLOOKUP(D36,Sheet2!A:B,2)

--
Regards!
Stefi



€žMZ€� ezt Ã*rta:

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

Or with the data arranged as mentioned by Stefi you can use LOOKUP()

=LOOKUP(D36,Sheet2!A:B)

Further you can add an IF() formula to validate the cell content

=IF(D36,LOOKUP(D36,Sheet2!A:B),"")

--
Jacob


"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

It's easy to deal with predetermined percentages such as 10%, 15% etc.
Simply express them as a decimal:
10% = .1
15% = .15
25% = .25
28% = .28
33% = .33
35% = .35
and multiply by that decimal value:
=8350/10
is same as
=8350 * .1
In fact, in some cases it's more accurate to do it that way:
=372950/3 [result is 124316.6667] (most people tend to think of 33% as 1/3
even though it really is not)
is not the same as
=372950*.33 [result is 123073.50]



"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

You don't need to convert 15% to 0.15. Just use 15% in your formula.
--
David Biddulph

"JLatham" wrote in message
...
It's easy to deal with predetermined percentages such as 10%, 15% etc.
Simply express them as a decimal:
10% = .1
15% = .15
25% = .25
28% = .28
33% = .33
35% = .35
and multiply by that decimal value:
=8350/10
is same as
=8350 * .1
In fact, in some cases it's more accurate to do it that way:
=372950/3 [result is 124316.6667] (most people tend to think of 33% as 1/3
even though it really is not)
is not the same as
=372950*.33 [result is 123073.50]



"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

Could you present a small exaple as - how per the Tax for an Income of $ 9,000
Is it: $ 1,350 OR $ 932.50 ?
Micky



"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

"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

Coiuld you, please, present the tax for an income of 9,000 /
Is it: 1,350 OR: 932.50 ?
Micky



"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

MZ,

I interpreted your question a bit dirrently than Stefi. Coming from an
accounting background, I understand your question to be, "what is the income
tax based on the taxable income entered into D36?"

First of all, you are correct in your title asking for the "marginal" income
tax, but this is a bit more complex than simply multiplying your taxable
income by the tax bracket.

For example: If a person has $50K of taxable income, the tax rate on the
first $8,349 is 10% ($835), the rate on the next $25,600 is 15% ($3,840), and
the rate on the ramaining $16,051 is 25% ($4,013). If you add up the
individual tax calculation, the total tax is $8,688 or an overall tax rate of
17.4%.

This can be done with a series of calculations set up as a table, but you
will end up with a very large and cumbersome formula if you try to put it all
in one cell.

rzink

"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

rzink,
I understood the question "your way" but I don't think that the hereunder
presented formula can be pronouns to be cumbersome and/or long complicated.
http://img691.imageshack.us/img691/622/nonamecz.png
Micky


"rzink" wrote:

MZ,

I interpreted your question a bit dirrently than Stefi. Coming from an
accounting background, I understand your question to be, "what is the income
tax based on the taxable income entered into D36?"

First of all, you are correct in your title asking for the "marginal" income
tax, but this is a bit more complex than simply multiplying your taxable
income by the tax bracket.

For example: If a person has $50K of taxable income, the tax rate on the
first $8,349 is 10% ($835), the rate on the next $25,600 is 15% ($3,840), and
the rate on the ramaining $16,051 is 25% ($4,013). If you add up the
individual tax calculation, the total tax is $8,688 or an overall tax rate of
17.4%.

This can be done with a series of calculations set up as a table, but you
will end up with a very large and cumbersome formula if you try to put it all
in one cell.

rzink

"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

Micky,

Your example is very nice. Thank you. Please note that my comment was not
directed is response to your comment, but someone else.

rzink



"מיכ�ל (מיקי) �בידן" wrote:

rzink,
I understood the question "your way" but I don't think that the hereunder
presented formula can be pronouns to be cumbersome and/or long complicated.
http://img691.imageshack.us/img691/622/nonamecz.png
Micky


"rzink" wrote:

MZ,

I interpreted your question a bit diffently than Stefi. Coming from an
accounting background, I understand your question to be, "what is the income
tax based on the taxable income entered into D36?"

First of all, you are correct in your title asking for the "marginal" income
tax, but this is a bit more complex than simply multiplying your taxable
income by the tax bracket.

For example: If a person has $50K of taxable income, the tax rate on the
first $8,349 is 10% ($835), the rate on the next $25,600 is 15% ($3,840), and
the rate on the ramaining $16,051 is 25% ($4,013). If you add up the
individual tax calculation, the total tax is $8,688 or an overall tax rate of
17.4%.

This can be done with a series of calculations set up as a table, but you
will end up with a very large and cumbersome formula if you try to put it all
in one cell.

rzink

"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

I was not "complaining" against whom your reply was intended to [you posted
it under MZs question and you even mentioned his 'Nick'].
What I was "complaining" against was the sentence [quote]:
"This can be done with a series of calculations set up as a table, but you
will end up with a very large and cumbersome formula if you try to put it all
in one cell".
Micky


"rzink" wrote:

Micky,

Your example is very nice. Thank you. Please note that my comment was not
directed is response to your comment, but someone else.

rzink



"מיכ�ל (מיקי) �בידן" wrote:

rzink,
I understood the question "your way" but I don't think that the hereunder
presented formula can be pronouns to be cumbersome and/or long complicated.
http://img691.imageshack.us/img691/622/nonamecz.png
Micky


"rzink" wrote:

MZ,

I interpreted your question a bit diffently than Stefi. Coming from an
accounting background, I understand your question to be, "what is the income
tax based on the taxable income entered into D36?"

First of all, you are correct in your title asking for the "marginal" income
tax, but this is a bit more complex than simply multiplying your taxable
income by the tax bracket.

For example: If a person has $50K of taxable income, the tax rate on the
first $8,349 is 10% ($835), the rate on the next $25,600 is 15% ($3,840), and
the rate on the ramaining $16,051 is 25% ($4,013). If you add up the
individual tax calculation, the total tax is $8,688 or an overall tax rate of
17.4%.

This can be done with a series of calculations set up as a table, but you
will end up with a very large and cumbersome formula if you try to put it all
in one cell.

rzink

"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

Which is exactly what my formula gives, not large, not cumbersome, and all
in one cell.


---
HTH

Bob Phillips

"rzink" wrote in message
...
MZ,

I interpreted your question a bit dirrently than Stefi. Coming from an
accounting background, I understand your question to be, "what is the
income
tax based on the taxable income entered into D36?"

First of all, you are correct in your title asking for the "marginal"
income
tax, but this is a bit more complex than simply multiplying your taxable
income by the tax bracket.

For example: If a person has $50K of taxable income, the tax rate on the
first $8,349 is 10% ($835), the rate on the next $25,600 is 15% ($3,840),
and
the rate on the ramaining $16,051 is 25% ($4,013). If you add up the
individual tax calculation, the total tax is $8,688 or an overall tax rate
of
17.4%.

This can be done with a series of calculations set up as a table, but you
will end up with a very large and cumbersome formula if you try to put it
all
in one cell.

rzink

"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

Bob,
If I may - this was one of the "brainstorming" thread I participated in
during the last couple of weeks.
Your formula is, as you stated, "Not large, not cumbersome, and all
in one cell" - however, with your permission I have ONE important comment.
It is not a good idea to type all the various "values of steps" WITHIN the
formula.
If you'll take a close look - it seems that you have had more than one TYPO
in what you presented.
In addition - whenever one needs to change one, or more, values - he would
have to edit the formula and copy it along the Salaries.
By using your suggestion in a different approach all mistakes and "extra
work" that can be eliminated.
http://img85.imageshack.us/img85/6707/nonameqh.png
Thank you,
Micky


"Bob Phillips" wrote:

Which is exactly what my formula gives, not large, not cumbersome, and all
in one cell.


---
HTH

Bob Phillips

"rzink" wrote in message
...
MZ,

I interpreted your question a bit dirrently than Stefi. Coming from an
accounting background, I understand your question to be, "what is the
income
tax based on the taxable income entered into D36?"

First of all, you are correct in your title asking for the "marginal"
income
tax, but this is a bit more complex than simply multiplying your taxable
income by the tax bracket.

For example: If a person has $50K of taxable income, the tax rate on the
first $8,349 is 10% ($835), the rate on the next $25,600 is 15% ($3,840),
and
the rate on the ramaining $16,051 is 25% ($4,013). If you add up the
individual tax calculation, the total tax is $8,688 or an overall tax rate
of
17.4%.

This can be done with a series of calculations set up as a table, but you
will end up with a very large and cumbersome formula if you try to put it
all
in one cell.

rzink

"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


.

Micky,

I agree that if the formula is used on many people a table would be the way
to go, on a single item, debatable.

However I just offered a solution, and it worked. I was responding to your
you where you showed a formula using INDIRECT, which is awful, and should be
avoided at all costs IMO. My solution worked, no INDIRECT, in one cell, not
cumbersome ... as I said.

Typos? I tested it and it seemed fine to me.

---
HTH

Bob Phillips

"????? (????) ?????" <micky-a*at*tapuz.co.il wrote in message
...
Bob,
If I may - this was one of the "brainstorming" thread I participated in
during the last couple of weeks.
Your formula is, as you stated, "Not large, not cumbersome, and all
in one cell" - however, with your permission I have ONE important comment.
It is not a good idea to type all the various "values of steps" WITHIN the
formula.
If you'll take a close look - it seems that you have had more than one
TYPO
in what you presented.
In addition - whenever one needs to change one, or more, values - he would
have to edit the formula and copy it along the Salaries.
By using your suggestion in a different approach all mistakes and "extra
work" that can be eliminated.
http://img85.imageshack.us/img85/6707/nonameqh.png
Thank you,
Micky


"Bob Phillips" wrote:

Which is exactly what my formula gives, not large, not cumbersome, and
all
in one cell.


---
HTH

Bob Phillips

"rzink" wrote in message
...
MZ,

I interpreted your question a bit dirrently than Stefi. Coming from an
accounting background, I understand your question to be, "what is the
income
tax based on the taxable income entered into D36?"

First of all, you are correct in your title asking for the "marginal"
income
tax, but this is a bit more complex than simply multiplying your
taxable
income by the tax bracket.

For example: If a person has $50K of taxable income, the tax rate on
the
first $8,349 is 10% ($835), the rate on the next $25,600 is 15%
($3,840),
and
the rate on the ramaining $16,051 is 25% ($4,013). If you add up the
individual tax calculation, the total tax is $8,688 or an overall tax
rate
of
17.4%.

This can be done with a series of calculations set up as a table, but
you
will end up with a very large and cumbersome formula if you try to put
it
all
in one cell.

rzink

"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


.

Well..., for a salary of $ 50,000:
My formula returns: $ 8,687.50 and yours: $ 8,132.50
Would you be so kind to calculate the Income Tax step by step...?
Micky


"Bob Phillips" wrote:

Micky,

I agree that if the formula is used on many people a table would be the way
to go, on a single item, debatable.

However I just offered a solution, and it worked. I was responding to your
you where you showed a formula using INDIRECT, which is awful, and should be
avoided at all costs IMO. My solution worked, no INDIRECT, in one cell, not
cumbersome ... as I said.

Typos? I tested it and it seemed fine to me.

---
HTH

Bob Phillips

"????? (????) ?????" <micky-a*at*tapuz.co.il wrote in message
...
Bob,
If I may - this was one of the "brainstorming" thread I participated in
during the last couple of weeks.
Your formula is, as you stated, "Not large, not cumbersome, and all
in one cell" - however, with your permission I have ONE important comment.
It is not a good idea to type all the various "values of steps" WITHIN the
formula.
If you'll take a close look - it seems that you have had more than one
TYPO
in what you presented.
In addition - whenever one needs to change one, or more, values - he would
have to edit the formula and copy it along the Salaries.
By using your suggestion in a different approach all mistakes and "extra
work" that can be eliminated.
http://img85.imageshack.us/img85/6707/nonameqh.png
Thank you,
Micky


"Bob Phillips" wrote:

Which is exactly what my formula gives, not large, not cumbersome, and
all
in one cell.


---
HTH

Bob Phillips

"rzink" wrote in message
...
MZ,

I interpreted your question a bit dirrently than Stefi. Coming from an
accounting background, I understand your question to be, "what is the
income
tax based on the taxable income entered into D36?"

First of all, you are correct in your title asking for the "marginal"
income
tax, but this is a bit more complex than simply multiplying your
taxable
income by the tax bracket.

For example: If a person has $50K of taxable income, the tax rate on
the
first $8,349 is 10% ($835), the rate on the next $25,600 is 15%
($3,840),
and
the rate on the ramaining $16,051 is 25% ($4,013). If you add up the
individual tax calculation, the total tax is $8,688 or an overall tax
rate
of
17.4%.

This can be done with a series of calculations set up as a table, but
you
will end up with a very large and cumbersome formula if you try to put
it
all
in one cell.

rzink

"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


.



.

I've been watching this part of the discussion, and it reminded me of dialog
between Dumb and Dumber (before Bob jumped in). I held my tongue because
everyone is entitled to post their opinions, no matter how misguided they
might.


"מיכ�ל (מיקי) �בידן" <micky-a*at*tapuz.co.il wrote:
Well..., for a salary of $ 50,000:
My formula returns: $ 8,687.50 and yours: $ 8,132.50

You are correct: $8687.50 is the right answer.

But you don't need to throw out the baby with the bath water. You might
have simply noted that Bob had written 8950 and 39500 instead of 8350 and
33950.


"מיכ�ל (מיקי) �בידן" <micky-a*at*tapuz.co.il wrote previously:
By using your suggestion in a different approach all mistakes and
"extra
work" that can be eliminated.
http://img85.imageshack.us/img85/6707/nonameqh.png

Which now looks suspiciously like one of the solutions that I presented in
this thread two days ago, complete with relying on a row with 0%.


Bob wrote:
I was responding to your
you where you showed a formula using INDIRECT, which is awful

You (Micky) fail to acknowledge the point that Bob is trying to educate you
about, namely: INDIRECT is a volatile function, which carries a huge cost
in terms of worksheet preformance. Your first solution at
http://img691.imageshack.us/img691/622/nonamecz.png was indeed unduly
cumbersome and complicated.


Micky wrote previously:
with your permission I have ONE important comment.
It is not a good idea to type all the various "values of steps"
WITHIN the formula.

There is nothing wrong with Bob's approach. For my money, it is
error-prone, and it contains "simplifications" that deserved explanation;
but it is indeed the most efficient formulation, I believe. I can see why
it might have been a complete mystery to you, given that you did not even
know how US marginal tax rate tables work just 2 days ago.

I, for one, am getting sick and tired of your misguided sermonizing. It
reflects badly on you more than on the people you try to criticize. But if
you are going to berate people publicly, you should be a mensh and admit
your own mistakes publicly and up-front.

And do follow your own advice, misguided as it might have been in the
original context, namely:

``I would dare to say that 30-40% of the replies are Superfluous. I think
it will be a good idea that every supporter will adopt the "habit" of
pressing [F5] BEFORE(!) replying.``

[From: "מיכ�ל (מיקי) �בידן" <micky-a*at*tapuz.co.il, Newsgroups:
microsoft.public.excel.misc, Sent: Thursday, December 31, 2009 10:35 AM,
Subject: excel decimal rounding down all numbers.]


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

"מיכ�ל (מיקי) �בידן" <micky-a*at*tapuz.co.il wrote in message
...
Well..., for a salary of $ 50,000:
My formula returns: $ 8,687.50 and yours: $ 8,132.50
Would you be so kind to calculate the Income Tax step by step...?
Micky


"Bob Phillips" wrote:

Micky,

I agree that if the formula is used on many people a table would be the
way
to go, on a single item, debatable.

However I just offered a solution, and it worked. I was responding to
your
you where you showed a formula using INDIRECT, which is awful, and should
be
avoided at all costs IMO. My solution worked, no INDIRECT, in one cell,
not
cumbersome ... as I said.

Typos? I tested it and it seemed fine to me.

---
HTH

Bob Phillips

"????? (????) ?????" <micky-a*at*tapuz.co.il wrote in message
...
Bob,
If I may - this was one of the "brainstorming" thread I participated in
during the last couple of weeks.
Your formula is, as you stated, "Not large, not cumbersome, and all
in one cell" - however, with your permission I have ONE important
comment.
It is not a good idea to type all the various "values of steps" WITHIN
the
formula.
If you'll take a close look - it seems that you have had more than one
TYPO
in what you presented.
In addition - whenever one needs to change one, or more, values - he
would
have to edit the formula and copy it along the Salaries.
By using your suggestion in a different approach all mistakes and
"extra
work" that can be eliminated.
http://img85.imageshack.us/img85/6707/nonameqh.png
Thank you,
Micky


"Bob Phillips" wrote:

Which is exactly what my formula gives, not large, not cumbersome, and
all
in one cell.


---
HTH

Bob Phillips

"rzink" wrote in message
...
MZ,

I interpreted your question a bit dirrently than Stefi. Coming from
an
accounting background, I understand your question to be, "what is
the
income
tax based on the taxable income entered into D36?"

First of all, you are correct in your title asking for the
"marginal"
income
tax, but this is a bit more complex than simply multiplying your
taxable
income by the tax bracket.

For example: If a person has $50K of taxable income, the tax rate
on
the
first $8,349 is 10% ($835), the rate on the next $25,600 is 15%
($3,840),
and
the rate on the ramaining $16,051 is 25% ($4,013). If you add up
the
individual tax calculation, the total tax is $8,688 or an overall
tax
rate
of
17.4%.

This can be done with a series of calculations set up as a table,
but
you
will end up with a very large and cumbersome formula if you try to
put
it
all
in one cell.

rzink

"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


.



.

PS....

I wrote:
"מיכ�ל (מיקי) �בידן" <micky-a*at*tapuz.co.il wrote previously:
http://img85.imageshack.us/img85/6707/nonameqh.png

Which now looks suspiciously like one of the solutions that I presented in
this thread two days ago, complete with relying on a row with 0%.

Actually, your table is different in design: it is limited to incomes of
less than $10 million, whereas mine is not. Not a big limitation,
admittedly; but an unnecessary one.


There is nothing wrong with Bob's approach [...] it is indeed the
most efficient formulation, I believe.

I meant the most efficient formulation presented so far in this thread.

Some years ago, I was posting a formula using MAX. I suspect it is more
efficient, although I am not taking the time to test the theory. I still
use the MAX formula sometimes for quick-and-dirty implementations. But I
have adopted a table-driven design for responses because I believe it is
less error-prone and easier to maintain (change from year to year). It is
certainly easier to remember ;-).


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

"Joe User" <joeu2004 wrote in message
...
I've been watching this part of the discussion, and it reminded me of
dialog between Dumb and Dumber (before Bob jumped in). I held my tongue
because everyone is entitled to post their opinions, no matter how
misguided they might.


"מיכ�ל (מיקי) �בידן" <micky-a*at*tapuz.co.il wrote:
Well..., for a salary of $ 50,000:
My formula returns: $ 8,687.50 and yours: $ 8,132.50

You are correct: $8687.50 is the right answer.

But you don't need to throw out the baby with the bath water. You might
have simply noted that Bob had written 8950 and 39500 instead of 8350 and
33950.


"מיכ�ל (מיקי) �בידן" <micky-a*at*tapuz.co.il wrote previously:
By using your suggestion in a different approach all mistakes and
"extra
work" that can be eliminated.
http://img85.imageshack.us/img85/6707/nonameqh.png

Which now looks suspiciously like one of the solutions that I presented in
this thread two days ago, complete with relying on a row with 0%.


Bob wrote:
I was responding to your
you where you showed a formula using INDIRECT, which is awful

You (Micky) fail to acknowledge the point that Bob is trying to educate
you about, namely: INDIRECT is a volatile function, which carries a huge
cost in terms of worksheet preformance. Your first solution at
http://img691.imageshack.us/img691/622/nonamecz.png was indeed unduly
cumbersome and complicated.


Micky wrote previously:
with your permission I have ONE important comment.
It is not a good idea to type all the various "values of steps"
WITHIN the formula.

There is nothing wrong with Bob's approach. For my money, it is
error-prone, and it contains "simplifications" that deserved explanation;
but it is indeed the most efficient formulation, I believe. I can see why
it might have been a complete mystery to you, given that you did not even
know how US marginal tax rate tables work just 2 days ago.

I, for one, am getting sick and tired of your misguided sermonizing. It
reflects badly on you more than on the people you try to criticize. But
if you are going to berate people publicly, you should be a mensh and
admit your own mistakes publicly and up-front.

And do follow your own advice, misguided as it might have been in the
original context, namely:

``I would dare to say that 30-40% of the replies are Superfluous. I think
it will be a good idea that every supporter will adopt the "habit" of
pressing [F5] BEFORE(!) replying.``

[From: "מיכ�ל (מיקי) �בידן" <micky-a*at*tapuz.co.il, Newsgroups:
microsoft.public.excel.misc, Sent: Thursday, December 31, 2009 10:35 AM,
Subject: excel decimal rounding down all numbers.]


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

"מיכ�ל (מיקי) �בידן" <micky-a*at*tapuz.co.il wrote in message
...
Well..., for a salary of $ 50,000:
My formula returns: $ 8,687.50 and yours: $ 8,132.50
Would you be so kind to calculate the Income Tax step by step...?
Micky


"Bob Phillips" wrote:

Micky,

I agree that if the formula is used on many people a table would be the
way
to go, on a single item, debatable.

However I just offered a solution, and it worked. I was responding to
your
you where you showed a formula using INDIRECT, which is awful, and
should be
avoided at all costs IMO. My solution worked, no INDIRECT, in one cell,
not
cumbersome ... as I said.

Typos? I tested it and it seemed fine to me.

---
HTH

Bob Phillips

"????? (????) ?????" <micky-a*at*tapuz.co.il wrote in message
...
Bob,
If I may - this was one of the "brainstorming" thread I participated
in
during the last couple of weeks.
Your formula is, as you stated, "Not large, not cumbersome, and all
in one cell" - however, with your permission I have ONE important
comment.
It is not a good idea to type all the various "values of steps" WITHIN
the
formula.
If you'll take a close look - it seems that you have had more than one
TYPO
in what you presented.
In addition - whenever one needs to change one, or more, values - he
would
have to edit the formula and copy it along the Salaries.
By using your suggestion in a different approach all mistakes and
"extra
work" that can be eliminated.
http://img85.imageshack.us/img85/6707/nonameqh.png
Thank you,
Micky


"Bob Phillips" wrote:

Which is exactly what my formula gives, not large, not cumbersome,
and
all
in one cell.


---
HTH

Bob Phillips

"rzink" wrote in message
...
MZ,

I interpreted your question a bit dirrently than Stefi. Coming
from an
accounting background, I understand your question to be, "what is
the
income
tax based on the taxable income entered into D36?"

First of all, you are correct in your title asking for the
"marginal"
income
tax, but this is a bit more complex than simply multiplying your
taxable
income by the tax bracket.

For example: If a person has $50K of taxable income, the tax rate
on
the
first $8,349 is 10% ($835), the rate on the next $25,600 is 15%
($3,840),
and
the rate on the ramaining $16,051 is 25% ($4,013). If you add up
the
individual tax calculation, the total tax is $8,688 or an overall
tax
rate
of
17.4%.

This can be done with a series of calculations set up as a table,
but
you
will end up with a very large and cumbersome formula if you try to
put
it
all
in one cell.

rzink

"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


.



.

I'm wondering what an active dispute is in process while the OP didn't
respond at all!
--
Regards!
Stefi



€žMZ€� ezt Ã*rta:

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