Income Tax Payable from table with escalating marginal percentage rates.

I am relatively new to formulas & functions and am stumpted by the
following:
I have a problem I cannot solve. I want to input a "Taxable
Income" (to the dollar) in cell A2 and have an output cell A3 which
displays "Combined Fed + Prov Tax Payable" (again , to the dollar).
This output would be derived from the table below after inputting a $
amount in A2. Thanks in advance for any help offered .... Bosko


(Cell A4:Tax Bracket) From To (Cell_D4:Combined Marg Fed+Prov TaxRate)

1 $0.00 $8,012.00 0.00%
2 $8,013.00 $35,000.00 22.05%
3 $35,001.00 $60,000.00 31.50%
4 $60,001.00 $65,000.00 32.98%
5 $65,001.00 $70,000.00 39.39%
6 $70,001.00 $110,000.00 43.40%
7 $110,001.00 and above 46.40%

Hi Bosko

You should put your table as

0 0.00%
8,013.00 22.05%
35,001.00 31.50%
60,001.00 32.98%
65,001.00 39.39%
70,001.00 43.40%
110,001.00 46.40%

(ie you don't need the 'To' column) in eg, range C20:D26

and use a VLOOKUP formula, eg =IF(A2<8013,0,A2*VLOOKUP(A2,$C$20:$D$26,2,1))

The IF bit avoids getting a #DIV/0! error when the tax percentage is zero

The 1 at the end of the VLOOKUP function tells it to find the nearest match
rather than an exact match (0)

HTH
Best rgds
Chris Lav



"Bosko" <[email protected]> wrote in message
news:[email protected]...
> I am relatively new to formulas & functions and am stumpted by the
> following:
> I have a problem I cannot solve. I want to input a "Taxable
> Income" (to the dollar) in cell A2 and have an output cell A3 which
> displays "Combined Fed + Prov Tax Payable" (again , to the dollar).
> This output would be derived from the table below after inputting a $
> amount in A2. Thanks in advance for any help offered .... Bosko
>
>
> (Cell A4:Tax Bracket) From To (Cell_D4:Combined Marg Fed+Prov TaxRate)
>
> 1 $0.00 $8,012.00 0.00%
> 2 $8,013.00 $35,000.00 22.05%
> 3 $35,001.00 $60,000.00 31.50%
> 4 $60,001.00 $65,000.00 32.98%
> 5 $65,001.00 $70,000.00 39.39%
> 6 $70,001.00 $110,000.00 43.40%
> 7 $110,001.00 and above 46.40%
>

Try...

=SUMPRODUCT(--(A2>{8012,35000,60000,65000,70000,110000}),A2-{8012,35000,6
0000,65000,70000,110000},{0.2205,0.0945,0.0148,0.0641,0.0401,0.03})

The above method is described in the following link...

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

Hope this helps!

In article <[email protected]>,
"Bosko" <[email protected]> wrote:

> I am relatively new to formulas & functions and am stumpted by the
> following:
> I have a problem I cannot solve. I want to input a "Taxable
> Income" (to the dollar) in cell A2 and have an output cell A3 which
> displays "Combined Fed + Prov Tax Payable" (again , to the dollar).
> This output would be derived from the table below after inputting a $
> amount in A2. Thanks in advance for any help offered .... Bosko
>
>
> (Cell A4:Tax Bracket) From To (Cell_D4:Combined Marg Fed+Prov TaxRate)
>
> 1 $0.00 $8,012.00 0.00%
> 2 $8,013.00 $35,000.00 22.05%
> 3 $35,001.00 $60,000.00 31.50%
> 4 $60,001.00 $65,000.00 32.98%
> 5 $65,001.00 $70,000.00 39.39%
> 6 $70,001.00 $110,000.00 43.40%
> 7 $110,001.00 and above 46.40%

Hi Chris
Thanks for your swift reply. I tried the method out you sent me and
found it did not work. When I enter $8012 in A2 I get Tax Payable in
A3 as $0, which is correct. But when I enter $8013 in cell A2 I
should get 22.05 cents diplayed in A3 but get $1,766.87 (this is3013 x
22.05%). But really appreciate your help anyway (maybe it only
requires a little tweak) Any way, the formula given to me by Domenic
originally from the mcgimpsey.com site works perfect for all test
income inputs ... THX again ... Bosko

Hi Domenic
Thanks for the help. I did several test income inputs in A2 and they
all equal results obtained by working out the Tax Payable manually. I
dont know how this works by looking at the the sumproduct formula
(percentages dont look right for my problem) but as I say it works
flawlessly. So thanks to you Domenic & J.E Mc Gimpsey. Cheers ...
Bosko

Hi Bosko

>(percentages don't look right for my problem) but as I say it works
It does seem a little illogical at first, but the formula is taking the
lowest rate of tax against ALL qualifying income, plus the marginal extra
tax due on the whole of each successive sum in each successive band as
appropriate.

22.05%,31.50%,32.98%,39.39%,43.40%,46.40%
0.2205,0.315 ,0.3298,0.3939,0.4340,0.464
so the incremental change for each band is as follows
0.2205,0.0945,0.0148,0.0641,0.0401,0.03

and these are the values that Domenic used in his formula.

Regards

Roger Govier


Bosko wrote:
> Hi Domenic
> Thanks for the help. I did several test income inputs in A2 and they
> all equal results obtained by working out the Tax Payable manually. I
> dont know how this works by looking at the the sumproduct formula
> (percentages dont look right for my problem) but as I say it works
> flawlessly. So thanks to you Domenic & J.E Mc Gimpsey. Cheers ...
> Bosko
>