Using Excel to calculate income tax

[ExcelBeginner34]

Hi,

I am trying to use Excel to calculate income tax. The tax system has a number of rates applied to each band as follows

Lower Limit (H22) Upper Limited (I22) Rate (J22)
Tax Free Allowance (G23) £0 £12,500 0%
Starter Rate (G24) £12,500 £14,549 19%
Basic Rate (G25) £14,549 £24,999 20%
Intermediate Rate (G26) £24,999 £43,430 21%
Higher Rate (G27) £43,430 £150,000 41%
Top Rate (G28) £150,000 £250,000 46%

So if the Salary was £40,000 before tax:

• The first £12,500 is tax free
• The next £2,049 (14,549 - 12,500) is taxed at 19%
• The next £10,450 (24,999 - 14549) is taxed at 20%
• and so on until you reach the total salary.

I am trying to calculate each bracket i.e £2,049*0.19 = 389.31 in a separate cell

• Starter Rate E22
• Basic Rate E23
• Intermediate Rate E24
• Higher Rate E25
• I don't need the top rate as salary is unlikely to ever go above £150k.

The taxable income i.e gross salary less tax free allowance of £12,500 is currently in cell E20. So for example at £40k this would be £27,500.

I have tried using the IF function but I am just a beginner and not having much luck. Any help would be much appreciated.

Many thanks

 

[silentwolf]

Hi,
maybe a UDF like the below can help you.

Function IncomeTax(ByVal dblIncome As Double) As Double
    Dim Tax01 As Double
    Dim Tax02 As Double
    Dim Tax03 As Double
    Dim Tax04 As Double
    Dim Tax05 As Double
    
    Select Case dblIncome
        
        Case 0 To 12500
            IncomeTax = (dblIncome / 100) * 0
        Case 12500 To 14549
            Tax01 = dblIncome - 12500
            IncomeTax = (Tax01 / 100) * 19
        Case 14550 To 24999
            Tax01 = (14549 - 12500) / 100 * 19
            Tax02 = (24999 - 14550) / 100 * 20
            IncomeTax = Tax01 + Tax02
        Case 25000 To 43430
            Tax01 = (14549 - 12500) / 100 * 19
            Tax02 = (24999 - 14550) / 100 * 20
            Tax03 = (43430 - 25000) / 100 * 21
            IncomeTax = Tax01 + Tax02 + Tax03
        Case 43431 To 150000
            Tax01 = (14549 - 12500) / 100 * 19
            Tax02 = (24999 - 14550) / 100 * 20
            Tax03 = (43430 - 25000) / 100 * 21
            Tax04 = (150000 - 43430) / 100 * 41
            IncomeTax = Tax01 + Tax02 + Tax03 + Tax04
        Case 150001 To 250000
            Tax01 = (14549 - 12500) / 100 * 19
            Tax02 = (24999 - 14550) / 100 * 20
            Tax03 = (43430 - 25000) / 100 * 21
            Tax04 = (150000 - 43430) / 100 * 41
            Tax05 = (dblIncome - 150000) / 100 * 46
            IncomeTax = Tax01 + Tax02 + Tax03 + Tax04 + Tax05
        Case Else
        
        End Select
        
End Function

you need to check if the rates are correct in the function but something down those lines could get you started.
I guess you only like the result rahter then all the necessary steps.

HTH

 

[ExcelBeginner34]

Hi,
maybe a UDF like the below can help you.

Function IncomeTax(ByVal dblIncome As Double) As Double
    Dim Tax01 As Double
    Dim Tax02 As Double
    Dim Tax03 As Double
    Dim Tax04 As Double
    Dim Tax05 As Double
    
    Select Case dblIncome
        
        Case 0 To 12500
            IncomeTax = (dblIncome / 100) * 0
        Case 12500 To 14549
            Tax01 = dblIncome - 12500
            IncomeTax = (Tax01 / 100) * 19
        Case 14550 To 24999
            Tax01 = (14549 - 12500) / 100 * 19
            Tax02 = (24999 - 14550) / 100 * 20
            IncomeTax = Tax01 + Tax02
        Case 25000 To 43430
            Tax01 = (14549 - 12500) / 100 * 19
            Tax02 = (24999 - 14550) / 100 * 20
            Tax03 = (43430 - 25000) / 100 * 21
            IncomeTax = Tax01 + Tax02 + Tax03
        Case 43431 To 150000
            Tax01 = (14549 - 12500) / 100 * 19
            Tax02 = (24999 - 14550) / 100 * 20
            Tax03 = (43430 - 25000) / 100 * 21
            Tax04 = (150000 - 43430) / 100 * 41
            IncomeTax = Tax01 + Tax02 + Tax03 + Tax04
        Case 150001 To 250000
            Tax01 = (14549 - 12500) / 100 * 19
            Tax02 = (24999 - 14550) / 100 * 20
            Tax03 = (43430 - 25000) / 100 * 21
            Tax04 = (150000 - 43430) / 100 * 41
            Tax05 = (dblIncome - 150000) / 100 * 46
            IncomeTax = Tax01 + Tax02 + Tax03 + Tax04 + Tax05
        Case Else
        
        End Select
        
End Function

you need to check if the rates are correct in the function but something down those lines could get you started.
I guess you only like the result rahter then all the necessary steps.


Thanks I'll give this a try.

 

[oldbrewer]

The first £12,500 is tax free				Basic Rate (G25) £14,549 £24,999 20%
The next £2,049 (14,549 - 12,500) is taxed at 19%				Intermediate Rate (G26) £24,999 £43,430 21%
The next £10,450 (24,999 - 14549) is taxed at 20%				Higher Rate (G27) £43,430 £150,000 41%
and so on until you reach the total salary.				Top Rate (G28) £150,000 £250,000 46%
							M Y T A B L E
				0						max	cum max
			0	0	-17500	12500	12500		0	0	0
			0	0	-15451	2049	14549		19	389.31	389.31
			0	0	-5001	10450	24999		20	2090	2479.31
			1	1	13430	18431	43430		21	3870.51	6349.82
			0	1	120000	106570	150000		41	43693.7	50043.52
		let salary be	30000				current band tax	1050.21
		3529.52
		obtained by
		=VLOOKUP($D$22,mytable,5)+$J$22

<colgroup><col><col><col><col span="10"></colgroup><tbody>
</tbody>

 

[MARK858]

@ExcelBeginner34, see posts 5 & 6 in the link below and adjust to your figures.

https://www.mrexcel.com/forum/excel-questions/1047938-vba-tax-schedule-making-dynamic.html

 

[Dave Patton]

Please check
- brackets
- rates
- calculation with "allowance"

A variety of solutions for your consideration

Excel 2010
	A	B	C	D	E	F	G	H
1	Amount	40,000.00		Brackets	Rate	Rate_Diff		Income Tax
2	Fee	5,629.50		0	0%	0%	0	0
3		5,629.50		12,500	19%	19%	389.50	389.50
4		5,629.50		14,550	20%	1%	2,090.00	2,479.50
5		5,629.50		25,000	21%	1%	3,150.00	5,629.50
6				43,430	41%	20%	0.00	5,629.50
7				150,000	46%	5%	0.00
1dd

Cell Formulas
Range	Formula
F2	=E2-N(E1)
G3	=MAX(0,MIN($B$1-D3,D4-D3)*E3)
H3	=H2+G3
B2	=SUMPRODUCT(--(B1>D2:D7),B1-D2:D7,F2:F7)
B3	=SUMPRODUCT(--(B1>Brackets),B1-Brackets,Rate_Diff)
B4	=(B1>D3)*(B1-D3)*F3+(B1>D4)*(B1-D4)*F4+(B1>D5)*(B1-D5)*F5+(B1>D6)*(B1-D6)*F6+(B1>D7)*(B1-D7)*F7
B5	=Incometax(B1)

Named Ranges
Name	Refers To	Cells
Brackets	='1dd'!$D$2:$D$7
Rate_Diff	='1dd'!$F$2:$F$7

 

[ExcelBeginner34]

Thanks - I have set the table out as shown, however, in B3 and B5 I am just getting #ref and #name . I think this is to do with the use of Brackets and Rate Diff in the formula. I have set up the defined names (I think!) but where do I put the 1 dd? When I set up the defined names it looked like excel was trying to get me to open a file but I am not sure what I am supposed to do here. Any thoughts? Sorry as the name suggests this is all very new to me.
Thanks

 

[ExcelBeginner34]

Thanks

 

[ExcelBeginner34]

Hi again - I have made some progress and now only have the #NAME showing in cell B5. NOt sure where to go from here?

 

[MARK858]

Do you still have the UDF silentwolf provided in the file?