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.




[TABLE="width: 230"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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>[TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl63, width: 87"]you could use a nested IF or an index match[/TD]
[/TR]
[TR]
[TD="class: xl63"]lets assume you write the amount in Cell D1[/TD]
[/TR]
[TR]
[TD="class: xl63"]and have the other words in A Upto[/TD]
[/TR]
[TR]
[TD="class: xl63"]and in B the thrreshold, C3 250,000 C4, 500,000, C5 100,000 , C6 100,000[/TD]
[/TR]
[TR]
[TD="class: xl63"]and in C the % is in C3 0 C4 5% , C5 20% ,C6 30%[/TD]
[/TR]
</tbody>[/TABLE]

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:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Amt​

[/td][td="bgcolor:#F3F3F3"]

Rate​

[/td][td="bgcolor:#F3F3F3"]

Delta​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

0​

[/td][td]

0%​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][td][/td][td="bgcolor:#CCFFCC"]C2: =B2-N(B1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

250,000​

[/td][td]

5%​

[/td][td="bgcolor:#CCFFCC"]

5%​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

500,000​

[/td][td]

20%​

[/td][td="bgcolor:#CCFFCC"]

15%​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

1,000,000​

[/td][td]

30%​

[/td][td="bgcolor:#CCFFCC"]

10%​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td="bgcolor:#F3F3F3"]

Income​

[/td][td="bgcolor:#F3F3F3"]

Tax​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

450,000​

[/td][td="bgcolor:#CCFFFF"]

10,000​

[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]B8: =SUMPRODUCT((A8 > $A$2:$A$5) * (A8 - $A$2:$A$5) * $C$2:$C$5)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

800,000​

[/td][td="bgcolor:#CCFFFF"]

72,500​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

1,100,000​

[/td][td="bgcolor:#CCFFFF"]

142,500​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[PKBrahma]

A
[TABLE="width: 309"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Taxable income[/TD]
[TD] up to[/TD]
[TD]Tax %[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11,00,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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