# tax calculation

#### shamsu203

##### Board Regular
Hi
I want to create a work sheet to calculate income tax a follows
TAX RATE SLAB TAX YEAR
0% 0-300,000 2019
5% 300001-50000
20% 500001-1000000
30% 1000000-

income

354000
Besides using if conditions is there any other excel functions other then if which could be used. The tax rate or slab may change for subsequent
years ie 0% slabs may change to higher level or tax rate may change up or down
Reagrds
Shamsu

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
try this
adjust tax rates in Col B, and 0 in C2

Book1
ABCDEF
1TAX RATEincometax
200%0%125,0000
3300,0015%5%354,0002,700
4500,00120%15%750,00060,000
51,000,00130%10%1,500,000260,000
Sheet1
Cell Formulas
RangeFormula
F2=SUMPRODUCT((E2 > \$A\$2:\$A\$5) * (E2 - \$A\$2:\$A\$5) * \$C\$2:\$C\$5)
C3=B3-B2

Hi

Attention: shamsu

You may want to check how you specify the brackets.
The example below shows a very small difference.
A manual calculation is also shown.

Excel 2010
ABCDEF
1IncomeTaxBracketsRaterDiff
21,500,000.00260,000.000.000%0%
3354,000.002,700.00300,000.005%5%
4500,000.0020%15%
51,000,000.0030%10%
6
7Manual calculation
80.00to300,000.000%0.00
9300,000.00500,000.005%10,000.00
10500,000.001,000,000.0020%100,000.00
111,000,000.001,500,000.0030%150,000.00
12260,000.00
13
4a
Cell Formulas
RangeFormula
B2=SUMPRODUCT(--(A2>rB),A2-rB,rDiff)
B3=SUMPRODUCT(--(A3>rB),A3-rB,rDiff)
Named Ranges
NameRefers ToCells
rB='4a'!\$D\$2:\$D\$5
rDiff='4a'!\$F\$2:\$F\$5

Tks could you let me have the worksheet to work on
Regrds
Shamsu

Name the Brackets and the Rate Difference or use the relevant ranges in your formula.
Copy the formula in the cell that you choose.
If you do not want to use the ranges, use

=SUMPRODUCT(--(A2>{0;300000;500000;1000000}),A2-{0;300000;500000;1000000},{0;0.05;0.15;0.1})

HI
tks got it much appreciated

Replies
8
Views
989
Replies
2
Views
773
Replies
3
Views
1K
Replies
13
Views
4K
Replies
9
Views
958

1,203,620
Messages
6,056,335
Members
444,861
Latest member
B4you_Andrea

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back