uk tax formula

[Adrian1]

This is a bit of a stab in the dark but does anyone have a formula that would work out UK Tax and NI deductions based on salary top line. I want the formula to be able to calculate my approx earnings after deductions based on how many hours I work, but it needs to also take into consideration extra earnings and higher tax as a result of working overtime. My tax code is 466L. Everything else on my spreadsheet is fine, I just need this formula as I can only get my gross at the moment and want net! (take home pay)

Cheers

 

[grambo]

Can somebody do this formula for the year 19-20

Thanks

This should work

=MAX(MAX(A1-150000,0)*0.45+MIN(MAX(A1-50000,0),150000-50000)*0.4+MIN(A1-12500,50000-12500)*0.2+MAX((MIN(A1,122000)-100000)/2,0)*0.4,0)

 

[ScoobsIL]

I thought I'd poke my nose in and add this. Note. All thresholds are based on 2023/24 tax year. Unless there's a policy change, there will be no changes before 2028.
Based on the formula to calculate the total tax =MAX(MAX(I4-(U$4),0)*0.45+MIN(MAX(I4-(U$5),0),(U$4-U$5))*0.4+MIN(I4-(U$6),(U$5-U$6))*0.2+MAX((MIN(I4,(U$4))-(U$7))/2,0)*0.4,0)
[modified from the formula =MAX(MAX(A1-150000,0)*0.45+MIN(MAX(A1-43000,0),150000-43000)*0.4+MIN(A1-11000,43000-11000)*0.2+MAX((MIN(A1,122000)-100000)/2,0)*0.4,0) originally posted here by Fivecases] I split it into monthly and separated the individual tax rates. It seems to work.

20% tax =IF(MIN(C4-(O$6),(O$5-O$6))*0.2>0,MIN(C4-(O$6),(O$5-O$6))*0.2,0)
40% tax =MIN(MAX(C4-(O$5),0),(O$4-O$5))*0.4+MAX((MIN(C4,(O$4))-(O$7))/2,0)*E$3
45% tax =MAX(MAX(C4-(O$4),0)*F$3)
Hidden column B4:B15 days in month formulae to calculate "Period" dates
"Taxable income" does not take into account allowable tax deductions like employee pension contributions or blind person allowance. Sadly, it only works for regular incomes. I've yet to find a way to make it work on accrued incomes. For example if one month you work overtime and it takes you into the 40% tax band followed by a couple of months on reduced income resulting in previous 40% tax overpayments refunded. Maybe one day, I'll succeed.
National Insurance =IF(C4<=I4,0,IF(C4<=J4,(C4-I4)*10%,(J4-I4)*10%+((C4-J4)*2%)))

Calculations_Income Tax_National Insurance.xlsx
	A		C	D	E	F	G	H			K	L	M	N	O
1	Monthly
2			Taxable		Tax Payable		Total				National	Total
3	Period		Income	20%	40%	45%	Tax	Check			Insurance	Stoppages	Remaining
4	06/Apr/2024		10,500.00	628.33	2,914.67	32.25	3,575.25	3,575.25			440.48	4,015.73	6,484.27	125140/12	10,428.33
5	06/May/2024		-	-	-	-	-	-			-	-	-	50270/12	4,189.17
6	06/Jun/2024		1,200.00	30.50	-	-	30.50	30.50			15.25	45.75	1,154.25	12570/12	1,047.50
7	06/Jul/2024		2,000.00	190.50	-	-	190.50	190.50			95.25	285.75	1,714.25	100000/12	8,333.33
8	06/Aug/2024		3,500.00	490.50	-	-	490.50	490.50			245.25	735.75	2,764.25
9	06/Sep/2024		2,500.00	290.50	-	-	290.50	290.50			145.25	435.75	2,064.25
10	06/Oct/2024		1,500.00	90.50	-	-	90.50	90.50			45.25	135.75	1,364.25
11	06/Nov/2024		5,000.00	628.33	324.33	-	952.67	952.67			330.48	1,283.14	3,716.86
12	06/Dec/2024		8,000.00	628.33	1,524.33	-	2,152.67	2,152.67			390.48	2,543.14	5,456.86
13	06/Jan/2025		7,000.00	628.33	1,124.33	-	1,752.67	1,752.67			370.48	2,123.14	4,876.86
14	06/Feb/2025		6,000.00	628.33	724.33	-	1,352.67	1,352.67			350.48	1,703.14	4,296.86
15	06/Mar/2025		6,000.00	628.33	724.33	-	1,352.67	1,352.67			350.48	1,703.14	4,296.86
16
17	Total		53,200.00	4,862.50	7,336.33	32.25	12,231.08	12,231.08			2,779.11	15,010.19	38,189.81
Partial Manual Calc

Cell Formulas
Range	Formula
D4:D15	D4	=IF(MIN(C4-(O$6),(O$5-O$6))*0.2>0,MIN(C4-(O$6),(O$5-O$6))*0.2,0)
E4:E15	E4	=MIN(MAX(C4-(O$5),0),(O$4-O$5))*0.4+MAX((MIN(C4,(O$4))-(O$7))/2,0)*0.4
F4:F15	F4	=MAX(MAX(C4-(O$4),0)*0.45)
G4:G15	G4	=MAX(MAX(C4-(O$4),0)*0.45 +MIN(MAX(C4-(O$5),0),(O$4-O$5))*0.4 +MIN(C4-(O$6),(O$5-O$6))*0.2 +MAX((MIN(C4,(O$4))-(O$7))/2,0)*0.4,0)
H4:H15	H4	=D4+E4+F4
K4:K15	K4	=IF(C4<=I4,0,IF(C4<=J4,(C4-I4)*10%,(J4-I4)*10%+((C4-J4)*2%)))
L4:L15	L4	=G4+K4
M4:M15	M4	=C4-L4
A5:A15	A5	=A4+B4
O4	O4	=125140/12
O5	O5	=50270/12
O6	O6	=12570/12
O7	O7	=100000/12
C17:H17,K17:M17	C17	=SUM(C4:C15)

 

[ScoobsIL]

I thought I'd poke my nose in and add this. Note. All thresholds are based on 2023/24 tax year. Unless there's a policy change, there will be no changes before 2028.
Based on the formula to calculate the total tax =MAX(MAX(I4-(U$4),0)*0.45+MIN(MAX(I4-(U$5),0),(U$4-U$5))*0.4+MIN(I4-(U$6),(U$5-U$6))*0.2+MAX((MIN(I4,(U$4))-(U$7))/2,0)*0.4,0)
[modified from the formula =MAX(MAX(A1-150000,0)*0.45+MIN(MAX(A1-43000,0),150000-43000)*0.4+MIN(A1-11000,43000-11000)*0.2+MAX((MIN(A1,122000)-100000)/2,0)*0.4,0) originally posted here by Fivecases] I split it into monthly and separated the individual tax rates. It seems to work.

20% tax =IF(MIN(C4-(O$6),(O$5-O$6))*0.2>0,MIN(C4-(O$6),(O$5-O$6))*0.2,0)
40% tax =MIN(MAX(C4-(O$5),0),(O$4-O$5))*0.4+MAX((MIN(C4,(O$4))-(O$7))/2,0)*E$3
45% tax =MAX(MAX(C4-(O$4),0)*F$3)
Hidden column B4:B15 days in month formulae to calculate "Period" dates
"Taxable income" does not take into account allowable tax deductions like employee pension contributions or blind person allowance. Sadly, it only works for regular incomes. I've yet to find a way to make it work on accrued incomes. For example if one month you work overtime and it takes you into the 40% tax band followed by a couple of months on reduced income resulting in previous 40% tax overpayments refunded. Maybe one day, I'll succeed.
National Insurance =IF(C4<=I4,0,IF(C4<=J4,(C4-I4)*10%,(J4-I4)*10%+((C4-J4)*2%)))

Calculations_Income Tax_National Insurance.xlsx
	A		C	D	E	F	G	H			K	L	M	N	O
1	Monthly
2			Taxable		Tax Payable		Total				National	Total
3	Period		Income	20%	40%	45%	Tax	Check			Insurance	Stoppages	Remaining
4	06/Apr/2024		10,500.00	628.33	2,914.67	32.25	3,575.25	3,575.25			440.48	4,015.73	6,484.27	125140/12	10,428.33
5	06/May/2024		-	-	-	-	-	-			-	-	-	50270/12	4,189.17
6	06/Jun/2024		1,200.00	30.50	-	-	30.50	30.50			15.25	45.75	1,154.25	12570/12	1,047.50
7	06/Jul/2024		2,000.00	190.50	-	-	190.50	190.50			95.25	285.75	1,714.25	100000/12	8,333.33
8	06/Aug/2024		3,500.00	490.50	-	-	490.50	490.50			245.25	735.75	2,764.25
9	06/Sep/2024		2,500.00	290.50	-	-	290.50	290.50			145.25	435.75	2,064.25
10	06/Oct/2024		1,500.00	90.50	-	-	90.50	90.50			45.25	135.75	1,364.25
11	06/Nov/2024		5,000.00	628.33	324.33	-	952.67	952.67			330.48	1,283.14	3,716.86
12	06/Dec/2024		8,000.00	628.33	1,524.33	-	2,152.67	2,152.67			390.48	2,543.14	5,456.86
13	06/Jan/2025		7,000.00	628.33	1,124.33	-	1,752.67	1,752.67			370.48	2,123.14	4,876.86
14	06/Feb/2025		6,000.00	628.33	724.33	-	1,352.67	1,352.67			350.48	1,703.14	4,296.86
15	06/Mar/2025		6,000.00	628.33	724.33	-	1,352.67	1,352.67			350.48	1,703.14	4,296.86
16
17	Total		53,200.00	4,862.50	7,336.33	32.25	12,231.08	12,231.08			2,779.11	15,010.19	38,189.81
Partial Manual Calc

Cell Formulas
Range	Formula
D4:D15	D4	=IF(MIN(C4-(O$6),(O$5-O$6))*0.2>0,MIN(C4-(O$6),(O$5-O$6))*0.2,0)
E4:E15	E4	=MIN(MAX(C4-(O$5),0),(O$4-O$5))*0.4+MAX((MIN(C4,(O$4))-(O$7))/2,0)*0.4
F4:F15	F4	=MAX(MAX(C4-(O$4),0)*0.45)
G4:G15	G4	=MAX(MAX(C4-(O$4),0)*0.45 +MIN(MAX(C4-(O$5),0),(O$4-O$5))*0.4 +MIN(C4-(O$6),(O$5-O$6))*0.2 +MAX((MIN(C4,(O$4))-(O$7))/2,0)*0.4,0)
H4:H15	H4	=D4+E4+F4
K4:K15	K4	=IF(C4<=I4,0,IF(C4<=J4,(C4-I4)*10%,(J4-I4)*10%+((C4-J4)*2%)))
L4:L15	L4	=G4+K4
M4:M15	M4	=C4-L4
A5:A15	A5	=A4+B4
O4	O4	=125140/12
O5	O5	=50270/12
O6	O6	=12570/12
O7	O7	=100000/12
C17:H17,K17:M17	C17	=SUM(C4:C15)

Edit
National Insurance = =MAX(MAX(C4-O$6,0)*2%+MIN(MAX(C4-O$2,0),(O$6-O$2))*10%,0)