tough excel problem!

[Outlawdevil]

how to you put the formula in column D by using "If" and "Min" function



I want excel to calculate
15% of net income before taxes,zero to $50,000.00
Plus 25% of net income before taxes,$50,000.00 to $75,000.00
Plus 34% of net income before taxes,$75,000.00 to $100,000.00
Plus 39% of net income before taxes,$100,000.00 to $335,000.00
Plus 34% of net income before taxes over $335,000.00
--------------------------------------------------------------------------
example
federal income before tax:$131,135.27

50,000*.15= $7500
plus 25,000*.25= 6,250
plus 25,000*.34= 8,500
plus 31,135.27*.39= 12,142.76

 

[JE McGimpsey]

Take a look here:

http://www.mcgimpsey.com/excel/variablerate.html

 

[Guest]

You can try this formula:

=IF(B9="","",IF(B9<=50000,B9*0.15,IF(B9<=75000,B9*0.25,IF(B9<=100000,B9*0.34,IF(B9<=335000,B9*0.39,IF(B9>335000,B9*0.34))))))

 

[JE McGimpsey]

Unfortunately, that won't work, since only the amounts between $50,000
and $75,000 are taxed at 25% (the first $50,000 being taxed at 15%).

When you next reply, check your formula against the OP's problem
statement. The OP's example gives a tax of $34,392.76 for an income of
$131,135.27. Your formula gives a tax of $51,142.76

And, just FYI, your last IF(B9>335000, B9*0.34) is redundant - the only
time it will execute is if B9 is not less than or equal to 335000 (from
the previous IF() statement). Using B9*0.34 would work just as well.

The formula I come up with, which has better variants at the site I
mentioned is

=SUMPRODUCT(--(B9>{0,50000,75000,100000,335000}),
(B9-{0,50000,75000,100000,335000}), {0.15,0.1,0.09,0.05,-0.05})

 

[Outlawdevil]

Thanks JE your formula works fine. Is that possible to create a kind o
formula to fill in the D column like D3 to be 7500 then add all the
columns together for total taxes

 

[JE McGimpsey]

Dunno - what's in your other columns?

If you're looking for the tax from each bracket (but why?) there are
better approaches. Here's a very simple way, though I don't know if your
data matches up:

A1: <total income>
A3: 50000
A4: 75000
A5: 100000
....
B3: 15%
B4: 25%
B5: 34%
....
D3: =MIN(A1,A3)*B3
D4: =MAX(0,MIN(A$1,A4)-A3)*B4
D5: =MAX(0,MIN(A$1,A5)-A4)*B5
....

 

[Outlawdevil]

thanks, that's what I want ! I saw some one has Min function in thei
formula that's why I posted this thread