Formula to calculate federal income tax

[mikeburg]

I am trying to come up with the best way (formula not vba) to lookup & calculate income tax from the following two columns A & B when the taxable amount is entered in C1:


ColA Col B
1 10% 14,300 (1st 14,300 taxed at 10%)
2 15% 58,100 (1st 14,300@10% + over 14,300 to 58,100 taxed at 15%)
3 25% 117,250 (1st 14,300@10% + 43,800@15% + 58,100 to 117,250@25%)
4 28% 178,650 (1st 14,300@10% + 43,800@15%+120,550@25%+over 117,250 to 178,650@28%)
5 33% 319,100 (1st 14,300@10%+43,800@15%+120,550@25%+ 61,400@28%+over 178,650 to 319,100@33%)
6 35% over 319,100 (1st 14,300@10%+43,800@15%+120,550@25%+61,400@28%+140,450@33%+all over 319,100@35%)
Using only two columns A & B are preferred and have the ability to change the column A percents & the column B amounts without having to change the formula.

Example: The tax on 49,000 entered in cell C1 would be 6,635 (14300 X 10% + (49000-14300 X 15%)).

Any ideas?

Thank you for your help. mikeburg

[Don Guillett]

look here
http://tinyurl.com/djxy9

--
Don Guillett
SalesAid Software
[email protected]
"mikeburg" <[email protected]> wrote in
message news:[email protected]...
>
> I am trying to come up with the best way (formula not vba) to lookup &
> calculate income tax from the following two columns A & B when the
> taxable amount is entered in C1:
>
>
> ColA Col B
> 1 10% 14,300 (1st 14,300 taxed at 10%)
> 2 15% 58,100 (1st 14,300@10% + over 14,300 to 58,100 taxed at 15%)
> 3 25% 117,250 (1st 14,300@10% + 43,800@15% + 58,100 to 117,250@25%)
> 4 28% 178,650 (1st 14,300@10% + 43,800@15%+120,550@25%+over 117,250 to
> 178,650@28%)
> 5 33% 319,100 (1st 14,300@10%+43,800@15%+120,550@25%+ 61,400@28%+over
> 178,650 to 319,100@33%)
> 6 35% over 319,100 (1st
> 14,300@10%+43,800@15%+120,550@25%+61,400@28%+140,450@33%+all over
> 319,100@35%)
> Using only two columns A & B are preferred and have the ability to
> change the column A percents & the column B amounts without having to
> change the formula.
>
> Example: The tax on 49,000 entered in cell C1 would be 6,635 (14300 X
> 10% + (49000-14300 X 15%)).
>
> Any ideas?
>
> Thank you for your help. mikeburg
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=399496
>