Calculate Tax Amount from Tax Table

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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

kieran

Active Member
Joined
Oct 27, 2002
Messages
429
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
 

RichardS

Well-known Member
Joined
Feb 16, 2002
Messages
761
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
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
<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>
 

Forum statistics

Threads
1,144,052
Messages
5,722,242
Members
422,418
Latest member
Chipsy

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
Top