calculate annual tax 1

[AzizKamal]

I need to calculate annual tax based on a tax slab. To calculate the tax, I had to use sumproduct formula twice and one vlookup formula. I am not sure whether my approach is fine. May be it is a lengthy formula and it can be achieved with one formula??

Tax Computation Procedure is as follows:

Where the taxable income does not exceed Rs.400,000, Rate of tax is 0%.
Where the taxable income exceeds Rs.400,000 but does not exceed Rs.750,000, Rate of tax is 5% of the amount exceeding Rs. 400,000.
Where the taxable income exceeds Rs.750,000 but does not exceed Rs.1,500,000, Rate of tax is 17,500 + 10% of the amount exceeding Rs. 750,000.
Where the taxable income exceeds Rs.1,500,000 but does not exceed Rs.2,500,000, Rate of tax is Rs.92,500 + 15% of the amount exceeding Rs.1,500,000.
Where the taxable income exceeds Rs.2,500,000, Rate of tax is Rs. 242,500 + 20% of the amount exceeding Rs.2,500,000

To accomplish this, I have four columns from A to D.
A1=Minimum Taxable Income
B1=Maximum Taxable Income
C1=Fixed Amount
D1=Rate of Tax

Column A has the following values from A2 to A6:
1
400,001
750,001
1,500,001
2,500,001

Column B has the following values from B2 to B6:
400,000
750,000
1,500,000
2,500,000
99,999,999

Column C has the following values from C2 to C6:
0
0
17,500
92,500
242,500

Column D has the following values from D2 to D6:
0
5
10
15
20

Annual Taxable Income is entered in cell B11. Annual Tax is calculated in cell B12 using the following formula:

=SUMPRODUCT((B11>=A2:A6)*(B11<=B2:B6)*((B11-IF(ISNA(VLOOKUP(B11,B2:B6,1,1)),0,VLOOKUP(B11,B2:B6,1,1)))*D2:D6%))+SUMPRODUCT((B11>=A2:A6)*(B11<=B2:B6)*(C2:C6))

 

[combo]

It's simpler if one notice, that the equivalent rule is:
5% of the amount above 400000
+
additional 5% of the amount above 750000
+
additional 5% of the amount above 1500000
+
additional 5% of the amount above 2500000

So a shorter formula:
=5%*MAX(0;B11-400000)+5%*MAX(0;B11-750000)+5%*MAX(0;B11-1500000)+5%*MAX(0;B11-2500000)


combo

 

[AzizKamal]

Thanks a lot combo.

It is working perfectly and the formula is so short and easy to comprehend.

Thank you so much.

I replaced semicolon with comma in the MAX formula:
=5%*MAX(0,B11-400000)+5%*MAX(0,B11-750000)+5%*MAX(0,B11-1500000)+5%*MAX(0,B11-2500000)

 

[SkipVought]

Hi,

Your LOOKUP table
[tt]
Amt Rte
0 0
400001 0.05
750001 0.1
1500001 0.15
2500001 0.2
99999999 0.3
[/tt]
Your formula to return the rate
[tt]
=INDEX(Rte,MATCH(B11,Amt,1),1)
[/tt]
to return the rate applied to the ATI
[tt
=(INDEX(Rte,MATCH(B11,Amt,1),1)+1)*B11
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oops TAX...
[tt]
=INDEX(Rte,MATCH(B11,Amt,1),1)*B11
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AzizKamal]

Thanks Skip.

The formula:
=INDEX(Rte,MATCH(B11,Amt,1),1)*B11

does not subtract previous slab amount and also does not add fixed amount.

For example, if I enter 760,000 in cell B11, the result shown is 76,000 (760,000 * 0.1).

It needs to be calculated as:
17,500 (fixed amount) + 10% of the amount exceeding 750,000
17,500 + 10% of 10,000
17,500 + 1,000
Hence the desired result is 18,500.

The formula:
=5%*MAX(0,B11-400000)+5%*MAX(0,B11-750000)+5%*MAX(0,B11-1500000)+5%*MAX(0,B11-2500000)
shows the result 18,500.

 

[SkipVought]

Sorry, I missed that

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}