conditional calculation

[PKBrahma]

sir,
This is Income Tax calculation where for 1st 2,50,000 rupees no tax. For the next 2,50,000 rupees tax is 5%. Above 5 lakh but below 10 lakh tax, it is 20%. Above 10 lakh, tax is 30%.

if taxable income is 450000(4.5 lakh)
for 250000 nil tax
upto 500000 5% 10000
upto 1000000 20% 0
above 10 lakh 30% 0

if taxable income is 800000(8 lakh)
for 250000 nil tax
upto 500000 5% 12500
upto 1000000 20% 60000
above 10 lakh 30% 0

if taxable income is 1100000(11 lakh)
for 250000 nil tax
upto 500000 5% 12500
upto 1000000 20% 100000
above 10 lakh 30% 30000

All 3 columns are to be shown and 4 rows are to be shown in the tax form. How to write if condition (or anything or index/match) please advise. Thanks.

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

 

[etaf]

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}tr {mso-height-source:auto;}col {mso-width-source:auto;}br {mso-data-placement:same-cell;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {color:#222222; font-size:13.0pt; font-family:Verdana, sans-serif; mso-font-charset:0;}--></style>

you could use a nested IF or an index match

lets assume you write the amount in Cell D1

and have the other words in A Upto

and in B the thrreshold, C3 250,000 C4, 500,000, C5 100,000 , C6 100,000

and in C the % is in C3 0 C4 5% , C5 20% ,C6 30%

<tbody>
</tbody>

then in
D1 = Amount total
D2 = 0
D3 = =IF($D$1<b2,0,if(and($d$1>B2,$D$1<=B3),($D$1-B2)*C3,(B3-B2)*C3))
D4 = =IF($D$1<b3,0,if(and($d$1>B3,$D$1<=B4),($D$1-B3)*C4,(B4-B3)*C4))
D5 = =IF($D$1>B5,($D$1-B5)*C5,0)

BUT thats to match your numbers

dropbox copy
https://www.dropbox.com/s/ki92wrp4jvnm2t0/TAX Rates.xlsx?dl=0</b3,0,if(and($d$1></b2,0,if(and($d$1>

 

[shg]

Another way:

	A​	B​	C​	D​	E​
1​	Amt​	Rate​	Delta​	​	​
2​	0​	0%​	0%​		C2: =B2-N(B1)
3​	250,000​	5%​	5%​
4​	500,000​	20%​	15%​
5​	1,000,000​	30%​	10%​
6​
7​	Income​	Tax​
8​	450,000​	10,000​			B8: =SUMPRODUCT((A8 > $A$2:$A$5) * (A8 - $A$2:$A$5) * $C$2:$C$5)
9​	800,000​	72,500​
10​	1,100,000​	142,500​

 

[PKBrahma]

A

Taxable income	up to	Tax %
11,00,000

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[PKBrahma]

Sir,
Thanks. Calculation part is very good. But, I've to change the Amount in Taxable amount cell so that 5% amt., 20% amt. and 30% amt. details will be shown in three rows like

A B C D
Taxable amt free upto % to pay tax
7,50,000 2,50,000 0 0
5,00,000 5 12,500
10,00,000 20 50,000
above 10,00,000 30 0

Only A2 will be changed so that d column will show details automatically. Will it be possible sir? Please reply. Thank you.

 

[etaf]

did you see my post? and excel example linked?

 

[PKBrahma]

sir,
Please clarify the symbol missing below in the bold cells. Thank You. Hopefully, this will work. I will surely inform you when successful.

IF($D$1<b2,0,if(and($d$1 style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">B2,$D$1<=B3),($D$1-B2)*C3,(B3-B2)*C3))</b2,0,if(and($d$1>

 

[etaf]

the forum truncated
=IF($D$1 < B2,0,IF(AND($D$1 > B2,$D$1 < =B3),($D$1-B2)*C3,(B3-B2)*C3))
=IF($D$1 < B3,0,IF(AND($D$1 > B3,$D$1 < =B4),($D$1-B3)*C4,(B4-B3)*C4))
=IF($D$1 > B5,($D$1-B5)*C5,0)

but the dropbox version is correct if you opened the file

 

[PKBrahma]

Thank you so much. Fantastic working

 

[etaf]

you are welcome