Income Tax Payable from table with escalating marginal percentage rates.

B

Bosko

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%
 
C

Chris Lavender

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
 
B

Bosko

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
 
B

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
 
R

Roger Govier

Hi Bosko
(percentages don't look right for my problem) but as I say it works
Click to expand...
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
 
Top