Calculate Tax Amount from Tax Table

L

lardog

New Member
Joined
Oct 30, 2002
Messages
8
I am trying to develop a formula that will calculate the appropriate tax amount based on a tax table. The distribution amount is used to calculate the tax amount. The tax table gives the distribution amount range and then the applicable tax rate is applied for that tier; in addition an additional tax amount may apply. For example if the distribution amount is $50,000, the tax amount is $6,544.50 (((50,000 - 49,900)*.27)+6,517.50).

Here is the tax table I am using.
Over Under Rate Addtnl Tax
- 6,450.00 0% -
6,450.00 49,900.00 15% -
49,900.00 105,200.00 27% 6,517.50
105,200.00 171,200.00 30% 21,448.50
171,200.00 302,050.00 35% 41,248.50
302,050.00 9,999,999.99 38.60% 87,046.00

I appreciate any assistance I can get. thank you
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
If you name the ranges in your tax rate table
as under, over, rate and addtnl as you have in your post, then the following function, array entered (ctrl-shift-enter) will give the correct figure.
In the formula below I have used the name amount to indicate the taxable amount that the tax is calcualted on.
=SUM((amount>Over)*(amount<=Under)*(Rate*(amount-Over))+(amount>Over)*(amount<=Under)*(Addtnl))

HIH

Kieran
 
Upvote 0
Or try VLOOKUP. See example,
Book2
ABCDEFG
1AmountRateExtraAmountRateExtra
2(6)0%302,049.0035%41,248.50
345015%
449,90027%6,517.50
5105,20030%21,448.50
6171,20035%41,248.50
7302,05039%87,046.00
Sheet1


Richard
 
Upvote 0
I will show two alternatives.

With amount (say taxable income) in A2

1. With the ranges mentioned above and the
entire table named rT

=(A2-VLOOKUP(A2,rT,1))*VLOOKUP(A2,rT,3)+VLOOKUP(A2,rT,4)


2. An array formula that must be entered with Ctrl+Shift+Enter (CSE)

With tax brackets in just one column and
rates in an adjacent column.

0
6,450
49,900
105,200
171,200
302,050
9,999,999,999

First to second last named r_1
Second to last named r_2
Percentages named Rate

=SUM((A2>r_1)*(IF(A2>r_2,r_2-r_1,A2-r_1))*Rate)
This message was edited by Dave Patton on 2002-11-01 14:31
 
Upvote 0
<HTML><HEAD><Script Langage JavaScript><!---
function CopyToClipBoard() {
var HtmlSource;
HtmlSource = document.all.ForSubmit.innerHTML;
HtmlSource=RetDeleted(HtmlSource);
window.clipboardData.setData("Text",HtmlSource);
alert('Html source of above imagennhas been copied to your clip boardnnJust paste it into Message Body');
}
function RetDeleted(targetstring) {
if (targetstring.indexOf(unescape('%0D%0A')) > -1) rcode = unescape('%0D%0A')
else if (targetstring.indexOf(unescape('%0A')) > -1) rcode = unescape('%0A')
else rcode = unescape('%0D');
i = 0;
p = '';
while (targetstring.indexOf(rcode,i) != -1) {
m = targetstring.indexOf(rcode,i);
p += targetstring.substring(i,m);
i = m + rcode.length;
}
p += targetstring.substring(i,targetstring.length);
return p;
}</Script></HEAD><BODY BGCOLOR=#E0F4EA><CENTER><FONT COLOR=#339966 SIZE=5>[HtmlMaker 2.20]</FONT></CENTER><HR><SPAN id='ForSubmit'>
Book25
ABCDEFG
2AddtnlTaxLookup:
3Lessthan6,450.000.0%-50,000.00
46,450.0049,900.0015.0%-
549,900.00105,200.0027.0%6,517.50
6105,200.00171,200.0030.0%21,448.50Excess27.00
7171,200.00302,050.0035.0%41,248.50Addt.Tax6,517.50
8302,050.009,999,999.9938.6%87,046.00
9Total$6,544.50
10
Sheet1
</SPAN><CENTER><HR></BODY></HTML>
 
Upvote 0
You must log in or register to reply here.

Similar threads

charlesstricklin
  • Question
Calculate federal withholding rates from tax table?
2
Replies
10
Views
11K
joeu2004
J

Forum statistics

Threads
1,214,396
Messages
6,119,268
Members
448,881
Latest member
Faxgirl

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top