Tax calculation formula

[randy]

I need a formula for cell D22 which calculates the tax amount, based on a
value in B6. The tax rules are: 0.5% of the dollar amount from $0.01 to
$55,000.00 PLUS 1.00% of the dollar amount from $55,000.01 to $250,000.00
PLUS 1.50% of the dollar amount from $250,000.01 to $400,000.00 PLUS 2.0% of
the dollar amount from $400,000.01 and up.

The values in B6 will be all over the map, and so far, I have not yet
figured out how to write a formula which ignores calculation of the higher
percentages, when using a value within a lower threshold, ie. if I use
$350,000, I would need the formula to calculate the 0.5%, the 1.00% and the
1.50%, but obviously not the 2.0%, since under the $400,000 threshold.

Anyone have any ideas or a snippet of formula syntax that would work?

 

[Jim Cone]

randy,

The tax on...
55,000 = 275
250,000 = 2225
400,000 = 4475

So you calculate the tax on the amount above the bracket level and
add the amount for the lower level..

=IF(B6<=55000,0.005*B6,IF(B6<=250000,275+0.01*(B6-55000),IF(B6<=400000,2225+0.015*(B6-250000),4475+0.02*(B6-400000))))

Jim Cone
San Francisco, USA


"randy"
<[email protected]>
wrote in message

I need a formula for cell D22 which calculates the tax amount, based on a
value in B6. The tax rules are: 0.5% of the dollar amount from $0.01 to
$55,000.00 PLUS 1.00% of the dollar amount from $55,000.01 to $250,000.00
PLUS 1.50% of the dollar amount from $250,000.01 to $400,000.00 PLUS 2.0% of
the dollar amount from $400,000.01 and up.

The values in B6 will be all over the map, and so far, I have not yet
figured out how to write a formula which ignores calculation of the higher
percentages, when using a value within a lower threshold, ie. if I use
$350,000, I would need the formula to calculate the 0.5%, the 1.00% and the
1.50%, but obviously not the 2.0%, since under the $400,000 threshold.

Anyone have any ideas or a snippet of formula syntax that would work?

 

[Guest]

One possibility

=CHOOSE(MATCH(B8,{0.01;55000.01;250000.01;400000.01},1),B8*0.5%,275+((B8-55000)*1%),2225+((B8-250000)*1.5%),4475+((B8-400000)*2%))

 

[Ron Rosenfeld]

I need a formula for cell D22 which calculates the tax amount, based on a
value in B6. The tax rules are: 0.5% of the dollar amount from $0.01 to
$55,000.00 PLUS 1.00% of the dollar amount from $55,000.01 to $250,000.00
PLUS 1.50% of the dollar amount from $250,000.01 to $400,000.00 PLUS 2.0% of
the dollar amount from $400,000.01 and up.

The values in B6 will be all over the map, and so far, I have not yet
figured out how to write a formula which ignores calculation of the higher
percentages, when using a value within a lower threshold, ie. if I use
$350,000, I would need the formula to calculate the 0.5%, the 1.00% and the
1.50%, but obviously not the 2.0%, since under the $400,000 threshold.

Anyone have any ideas or a snippet of formula syntax that would work?

Set up a table someplace on your worksheet that looks like:

$0 $0 0.5%
$55,000 $275.00 1.0%
$250,000 $2,225.00 1.5%
$400,000 $4,475.00 2.0%

and NAME it TaxTable.


The values in column 2 are the base amount to be paid for the value in column
1. If I understand you correctly, if you have 55,000, you will be paying
0.5%*55000 = $275. For $250,000 you would be paying $275 + 1%*(250000-55000) =
2225.00. And so forth.

Your formula is then:

=VLOOKUP(B6,TaxTable,2)+
(B6-VLOOKUP(B6,TaxTable,1))*
VLOOKUP(B6,TaxTable,3)
--ron

 

[randy]

Exactly what I was looking for. Much appreciated

randy