Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 3rd 05, 07:56 PM
Bosko
 
Posts: n/a
Default 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   Report Post  
Old November 3rd 05, 09:16 PM
Chris Lavender
 
Posts: n/a
Default 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   Report Post  
Old November 3rd 05, 10:05 PM
Domenic
 
Posts: n/a
Default 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   Report Post  
Old November 3rd 05, 11:09 PM
Bosko
 
Posts: n/a
Default 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   Report Post  
Old November 3rd 05, 11:21 PM
Bosko
 
Posts: n/a
Default 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   Report Post  
Old November 4th 05, 09:20 AM
Roger Govier
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
display data as a percentage of a subtotal in excel pivot table Fl pivot user Excel Discussion (Misc queries) 2 March 26th 05 12:24 PM
How do I manipulate pivot table report to include percentage of? KRUEMJ-Needs some help Excel Discussion (Misc queries) 2 January 31st 05 01:06 AM
Vlookup and Indexing in excel CLSCHWIES Excel Worksheet Functions 2 December 4th 04 01:57 AM


All times are GMT +1. The time now is 03:10 AM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017