Need a formula to calculate tax

[myxlguru]

Sir,Need a formula to calculate tax


slabs:
Up to 2,50,000- Nil
[TABLE="width: 700"]
<colgroup><col width="35" style="width: 26pt;"><col width="268" style="width: 201pt;"><col width="197" style="width: 148pt;"><col width="200" style="width: 150pt;"></colgroup><tbody>[TR]
[TD="class: m_-8687281839508523251gmail-xl68, width: 700, colspan: 4"]2,50,000 TO 5,00,000 -5% OF THE AMOUNT EXCEEDING 2,50,000[/TD]
[/TR]
[TR]
[TD="class: m_-8687281839508523251gmail-xl67, colspan: 4"]5.00,000 TO 10,00,000- 20% OF THE AMOUNT EXCEEDING 5,00,000[/TD]
[/TR]
[TR]
[TD="class: m_-8687281839508523251gmail-xl67, colspan: 4"]ABOVE 10,00,000 30%


example:
for 11,00,000 Rs

11,00,000-2,50,000 nil ( upto 2.5 lac no tax)

next (bal amout after first slab)
8,50,000 - 2,50,000 5% slab tax

next (bal amout after second slab)
6,00,000 -5,00,000 20% slab tax

next (bal amout after third slab)
1,00,000 30% slab tax

i tried if && not succeed
so pls help on this



Thanks in Advance[/TD]
[/TR]
</tbody>[/TABLE]

 

[BrianJN1]

Could you please explain that table/ I get the basic idea but I don't understand some of your units.

I'm assuming that for the first "x" units there is a certain charge but that changes as the number of units increase.

 

[ARUNCHENNAI]

Try this
=IF(E27>1000000,30%,IF(E27>500000,20%,IF(E27>250000,5%,0)))

Sir,Need a formula to calculate tax


slabs:
Up to 2,50,000- Nil
[TABLE="width: 700"]
<tbody>[TR]
[TD="class: m_-8687281839508523251gmail-xl68, width: 700, colspan: 4"]2,50,000 TO 5,00,000 -5% OF THE AMOUNT EXCEEDING 2,50,000[/TD]
[/TR]
[TR]
[TD="class: m_-8687281839508523251gmail-xl67, colspan: 4"]5.00,000 TO 10,00,000- 20% OF THE AMOUNT EXCEEDING 5,00,000[/TD]
[/TR]
[TR]
[TD="class: m_-8687281839508523251gmail-xl67, colspan: 4"]ABOVE 10,00,000 30%


example:
for 11,00,000 Rs

11,00,000-2,50,000 nil ( upto 2.5 lac no tax)

next (bal amout after first slab)
8,50,000 - 2,50,000 5% slab tax

next (bal amout after second slab)
6,00,000 -5,00,000 20% slab tax

next (bal amout after third slab)
1,00,000 30% slab tax

i tried if && not succeed
so pls help on this



Thanks in Advance[/TD]
[/TR]
</tbody>[/TABLE]

 

[ARUNCHENNAI]

[TABLE="width: 108"]
<tbody>[TR]
[TD]=IF(E26>1000000,((E26-1000000)*30%)+100000,IF(E26>500000,((E26-500000)*20%)+25000,IF(E26>500000,((E26-500000)*20%)+12500,0)))
[/TD]
[/TR]
</tbody>[/TABLE]

 

[ARUNCHENNAI]

[TABLE="width: 108"]
<tbody>[TR]
[TD]=IF(E26>1000000,((E26-1000000)*30%)+100000,IF(E26>500000,((E26-500000)*20%)+25000,IF(E26>500000,((E26-500000)*20%)+12500,0)))
[/TD]
[/TR]
</tbody>[/TABLE]

 

[ARUNCHENNAI]

Tested & Working.
=IF(E26>1000000,((E26-1000000)*30%)+100000,IF(E26>500000,((E26-500000)*20%)+25000,IF(E26>250000,((E26-250000)*5%)+12500,0)))

 

[Swapnil Shah]

Sir,Need a formula to calculate tax


slabs:
Up to 2,50,000- Nil
[TABLE="width: 700"]
<tbody>[TR]
[TD="class: m_-8687281839508523251gmail-xl68, width: 700, colspan: 4"]2,50,000 TO 5,00,000 -5% OF THE AMOUNT EXCEEDING 2,50,000[/TD]
[/TR]
[TR]
[TD="class: m_-8687281839508523251gmail-xl67, colspan: 4"]5.00,000 TO 10,00,000- 20% OF THE AMOUNT EXCEEDING 5,00,000[/TD]
[/TR]
[TR]
[TD="class: m_-8687281839508523251gmail-xl67, colspan: 4"]ABOVE 10,00,000 30%


example:
for 11,00,000 Rs

11,00,000-2,50,000 nil ( upto 2.5 lac no tax)

next (bal amout after first slab)
8,50,000 - 2,50,000 5% slab tax

next (bal amout after second slab)
6,00,000 -5,00,000 20% slab tax

next (bal amout after third slab)
1,00,000 30% slab tax

i tried if && not succeed
so pls help on this



Thanks in Advance[/TD]
[/TR]
</tbody>[/TABLE]

Below formula will give you desired result , considering the income to be evaluated for tax is in cell C19.

=CHOOSE(MATCH(C19,{0,250000,500000,1000000},1),0,(C19-250000)*0.05,12500+(C19-500000)*0.2,112500+(C19-1000000)*0.3)

Thanks and Regards,
Swapnil Shah

 

[theBardd]

Cross-posted at ExcelGuru, where he was given the formula

=SUMPRODUCT(--(A1>{0;2500000;5000000;10000000}), (A1-{0;2500000;5000000;10000000}), {0;0.05;0.15;0.1})

which he says doesn't work, but he won't say how or why, or what he expects.

 

[Biz]

Try formula below

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Income	Tax																	Income is Greater Than or Equal To:	But Less Than or Equal to:	Tax Rate	Differential rate
2	1,00,000.00	0.00																	0.00	2,50,000.00	0%	0%
3	2,50,000.00	0.00																	2,50,000.01	5,00,000.00	5%	5%
4	2,51,000.00	50.00																	5,00,000.01	10,00,000.00	20%	15%
5	1,00,000.00	0.00																	10,00,000.01		30%	10%
6	1,00,000.00	0.00
7	1,00,000.00	0.00
8	1,00,000.00	0.00
India

?If you don't live in India then use custom format as below
[>=10000000]###\,##\,##\,##0.00;[>=100000]###\,##\,##0.00;##,##0.00

?

?