tax calculation

[Excel_Amateur]

<TABLE style="WIDTH: 158pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=210 border=0><COLGROUP><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7680" width=210><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 158pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=210 height=20>Hi,</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Could somebody pl help with the following tax formula?</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 493pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=657 border=0><COLGROUP><COL style="WIDTH: 149pt; mso-width-source: userset; mso-width-alt: 7277" width=199><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 122pt; mso-width-source: userset; mso-width-alt: 5924" width=162><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 149pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=199 height=20>Amount (“A”)</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 93pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=124>Tax</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 122pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=162>Minimum tax payable</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 129pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=172>Maximum tax payable</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Upto 100000</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">0.1% of A</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">25</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">100</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Between 100000 and 1000000</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">100 + 0.05% of A</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">100</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">550</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Above 1000000</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">550 + 0.01% of A</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">550</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">5000</TD></TR></TBODY></TABLE>


thanks

 

[Andrew Poulsom]

Look here:

http://www.mcgimpsey.com/excel/variablerate.html

 

[Richard Schollar]

Try this:

<b>Excel 2002</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"> 50,000.00 </td><td style="text-align: right;;">50</td><td style="border-left: 1px solid black;;">Threshold</td><td style=";">Rate</td><td style=";">Differential Rate</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"> 150,000.00 </td><td style="text-align: right;;">125</td><td style="text-align: right;border-left: 1px solid black;;"> - </td><td style="text-align: right;;">0.10%</td><td style="text-align: right;;">0.10%</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"> 1,500,000.00 </td><td style="text-align: right;;">600</td><td style="text-align: right;border-left: 1px solid black;;"> 100,000.00 </td><td style="text-align: right;;">0.05%</td><td style="text-align: right;;">-0.05%</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-left: 1px solid black;;"> 1,000,000.00 </td><td style="text-align: right;;">0.01%</td><td style="text-align: right;;">-0.04%</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">A1>$H$2:$H$4</font>)+0,A1-$H$2:$H$4,$J$2:$J$4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J2</th><td style="text-align:left">=I2-N(<font color="Blue">I1</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Excel_Amateur]

Sorry Guys,
but its not helping.
I have a data with large number of rows. I have to calculate tax for each row as per the above table.
I could calculate the formula for tax slabs. but not able to build in the minimum and maximum tax payable.

I have made the following formula:
=IF(A18<100001,A18*0.1%,IF(AND(A18>100000,A18<=1000000),(A18*0.05%)+100,(A18*0.01%)+550))

how do i build in minimum and maximum tax payable?

 

[Richard Schollar]

Hi

You just need to wrap a Min and a Max around the SUMPRODUCT formula I gave:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td width="25px" style="color: #161120;text-align: center;">1</td><td width="63px" style="font-weight: bold;;">Salary</td><td width="63px" style="font-weight: bold;;">Tax due</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">2</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">50,000.00</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">50</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;"></td><td width="63px" style="background-color: #FFFFFF;;">Threshold</td><td width="63px" style="background-color: #FFFFFF;;">Rate</td><td width="63px" style="background-color: #FFFFFF;;">Differential Rate</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">3</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">150,000.00</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">125</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;"></td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">0</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">0.10%</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">0.10%</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">4</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">1,500,000.00</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">600</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;"></td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">100,000.00</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">0.05%</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">-0.05%</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">5</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">10000</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">25</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;"></td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">1,000,000.00</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">0.01%</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">-0.04%</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">6</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">100,000.00</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">100</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">7</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">1,000,000.00</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">550</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">8</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">100,000,000.00</td><td width="63px" style="text-align: right;background-color: #FFFFFF;;">5000</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=MAX(<font color="Blue">25,MIN(<font color="Red">5000,SUMPRODUCT(<font color="Green">(<font color="Purple">A2>$D$3:$D$5</font>)+0,A2-$D$3:$D$5,$F$3:$F$5</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F3</th><td style="text-align:left">=E3-N(<font color="Blue">E2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F4</th><td style="text-align:left">=E4-N(<font color="Blue">E3</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F5</th><td style="text-align:left">=E5-N(<font color="Blue">E4</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Excel_Amateur]

Superb.
that works.
Thanks a ton.