Need a formula to calculate tax

Sir,Need a formula to calculate tax


slabs:
Up to 2,50,000- Nil

2,50,000 TO 5,00,000 -5% OF THE AMOUNT EXCEEDING 2,50,000

5.00,000 TO 10,00,000- 20% OF THE AMOUNT EXCEEDING 5,00,000

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

Try this

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

not succed

Bob Phillips said:

Try this

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

Click to expand...

myxlguru said:

not succed

Click to expand...

here i mentioned example of 11 lakh amount

for 11,00,000 Rs

11,00,000-2,50,000 nil ( upto 2.5 lac no tax) ==> no tax paid able for first 2.5 lac amount

next (bal amout after first slab) ==> after minus from 11 lakh - 2.5 lakh bal 8.5 lakh cut first slab tax i.e 5% for 2.5 lakh
8,50,000 - 2,50,000 5% slab tax

next (bal amout after second slab) ==> after minus from 8.5 lakh - 2.5 lakh bal 6 lakh cut first slab tax i.e 20% for up 5 lakh
6,00,000 -5,00,000 20% slab tax

next (bal amout after third slab) ==> after minus 1 lakh cut first slab tax i.e 30% for up 10 lakh
1,00,000 30% slab tax

Repeating what you said the time before in the same way doesn't really add to the sum of human knowledge does it?

I tried it and I got 1,425,000, which looks right to me. What are you getting?

Bob Phillips said:

Repeating what you said the time before in the same way doesn't really add to the sum of human knowledge does it?

I tried it and I got 1,425,000, which looks right to me. What are you getting?

Click to expand...

Sir,

here i am calulating only tax

by spliting as per slabs

Slabs are here:
Up to 2,50,000- Nil
2,50,000 TO 5,00,000 -5% OF THE AMOUNT EXCEEDING 2,50,000
5.00,000 TO 10,00,000- 20% OF THE AMOUNT EXCEEDING 5,00,000
ABOVE 10,00,000 30%


if amout up to 2.5 lack no tax
then upto 5 lac - 5% tax
up to 10 lac -20 % tax
more then 10 - 30 % tax

11 lac - 2.5 lac ( no tax first slab 0% tax )= bal 8.5 lac

then bal 8.5 lac - 2.5 lac ( as per second slab 5% tax) to 2.5 lac

now bal 6 lac - 5 lac ( as per third slab 2.5 to 5 lac tax is 20 % ) to 5 lac

now bal is only 1 lac ( as per fourth slab above 10 lac it is calculated with 30 % tax ) to remaining 1 lac

As I said ... Repeating what you said the time before in the same way doesn't really add to the sum of human knowledge does it?

As I said ...I tried it and I got 1,425,000, which looks right to me. What are you getting?

Bob Phillips said:

As I said ... Repeating what you said the time before in the same way doesn't really add to the sum of human knowledge does it?

As I said ...I tried it and I got 1,425,000, which looks right to me. What are you getting?

Click to expand...

Ok thank You