tax calculation

[shamsu203]

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

 

[AlanY]

try this
adjust tax rates in Col B, and 0 in C2

Book1
	A	B	C	D	E	F
1		TAX RATE			income	tax
2	0	0%	0%		125,000	0
3	300,001	5%	5%		354,000	2,700
4	500,001	20%	15%		750,000	60,000
5	1,000,001	30%	10%		1,500,000	260,000
Sheet1

Cell Formulas
Range	Formula
F2	=SUMPRODUCT((E2 > $A$2:$A$5) * (E2 - $A$2:$A$5) * $C$2:$C$5)
C3	=B3-B2

 

[shamsu203]

Hi
Tks for the prompt reply.

 

[Dave Patton]

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
	A	B	C	D	E	F
1	Income	Tax		Brackets	Rate	rDiff
2	1,500,000.00	260,000.00		0.00	0%	0%
3	354,000.00	2,700.00		300,000.00	5%	5%
4				500,000.00	20%	15%
5				1,000,000.00	30%	10%
6
7	Manual calculation
8	0.00	to	300,000.00	0%	0.00
9	300,000.00		500,000.00	5%	10,000.00
10	500,000.00		1,000,000.00	20%	100,000.00
11	1,000,000.00		1,500,000.00	30%	150,000.00
12					260,000.00
13
4a

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(--(A2>rB),A2-rB,rDiff)
B3	=SUMPRODUCT(--(A3>rB),A3-rB,rDiff)

Named Ranges
Name	Refers To	Cells
rB	='4a'!$D$2:$D$5
rDiff	='4a'!$F$2:$F$5

 

[shamsu203]

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

 

[Dave Patton]

Copy the data from the post into your spreadsheet.
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})

 

[shamsu203]

HI
tks got it much appreciated