Home 
Search 
Today's Posts 
#1




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% 
#2




Income Tax Payable from table with escalating marginal percentage rates.
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 C2026 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" wrote in message oups.com... 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% 
#3




Income Tax Payable from table with escalating marginal percentage rates.
Try...
=SUMPRODUCT((A2{8012,35000,60000,65000,70000,110000}),A2{8012,35000,6 0000,65000,70000,110000},{0.2205,0.0945,0.0148,0.0 641,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 .com, "Bosko" 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% 
#4




Income Tax Payable from table with escalating marginal percentage rates.
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 
#5




Income Tax Payable from table with escalating marginal percentage rates.
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 
#6




Income Tax Payable from table with escalating marginal percentagerates.
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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
display data as a percentage of a subtotal in excel pivot table  Excel Discussion (Misc queries)  
How do I manipulate pivot table report to include percentage of?  Excel Discussion (Misc queries)  
Vlookup and Indexing in excel  Excel Worksheet Functions 