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

 

[ExcelBeginner34]

Yes - in B5 I now have =VBAProject.Module1.IncomeTax(B1). However, this is providing a figure in B5 of £6,349.41 instead of the £5,629.50 shown on your table in post 6? Not sure what I am doing wrong.

 

[Dave Patton]

The 1dd is just the sheet name where I prepared an example.
You can place the table with Brackets and Rates wherever you want.
It is not essential to use named ranges; they can make the formula clearer.
Does the example provide the answer that you require?
What does you tax authority say the tax is for $150,000?

 

[Dave Patton]

I did not review or edit the UDF that you received above.

I prepared a quick UDF; an expert with VBA can edit and/or enhance it for you.

Function IncomeTax2(Amt As Double) As Double
If Amt <= 12500 Then IncomeTax2 = Amt * 0
If Amt > 12500 Then IncomeTax2 = 0 + (0.19 * (Amt - 12500))
If Amt > 14550 Then IncomeTax2 = IncomeTax2 + (0.01 * (Amt - 14550))
If Amt > 25000 Then IncomeTax2 = IncomeTax2 + (0.01 * (Amt - 25000))
If Amt > 43430 Then IncomeTax2 = IncomeTax2 + (0.2 * (Amt - 43430))
If Amt > 150000 Then IncomeTax2 = IncomeTax2 + (0.05 * (Amt - 150000))
End Function

 

[oldbrewer]

											col L				col P
													full band	cum full band
											0	0	0	0			row 4
					up to 1000 tax free					1001	0.1	0	0	5001
					1001 to 5000 10%					5001	0.25	400	400	20001
					5001 to 20000 25%					20001	0.4	3750	4150	75001
					20001 to 75000 40%				75001	0.5	22000	26150
					75001 upwards 50%					9999999	0.5					row 9
								10000	1649.75
							formula giving 1649.75
							=VLOOKUP(I14,mytable,4)+(I14-VLOOKUP(I14,mytable,1))*VLOOKUP(I14,mytable,2)
							mytable is L4:P9

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