The tax man loves his little games!

[Montanes]

Hi,

I know i can get this but i have been looking for too long and i need a fresh pair of eyes...

In the UK we have different tax brackets applied to work out the total amount to be deducted from a salary and paid to the nice tax man. So, if your annual salary was £150k, you wouldn't pay any tax for the first £12,570, but then pay 19% on the portion between £12,571 and £14,732, the pay 20% on the portion between £14,733 and £25,688, 21% on the portion between £25,689 and £43,662, 42% on the portion between £43,663 and £125,140 and finally 47% on anything above £125,141.

I have set it out as attached where i can enter a salary figure in A4 and hopefully B4:G4 will work out the different tax portions with a total sum in H4. The salary figure can be anything between £20k to £150k so some calculations wont be needed if a salary figure is below the threshold and for a salary of £20k, only £5,267 will be subject to the 20% tax bracket.

If you're from the UK you will already get this, but i accept help from everywhere. Thanks in advance.

 

[etaf]

you may have taken this into account with the info above , so apologies

So, if your annual salary was £150k, you wouldn't pay any tax for the first £12,570,

isn that wrong now - I understood , that over 100K , you started to lose the allowance - at £1 lost for every £2 you earn -= so at 122K you lost the lot
or is that again different in scotland ???

Income over £100,000​

Your Personal Allowance goes down by £1 for every £2 that your adjusted net income is above £100,000. This means your allowance is zero if your income is £125,140 or above.

Income Tax rates and Personal Allowances

Personal Allowance, Income Tax rates, bands and thresholds.

www.gov.uk

Also the tax rates for 23/24 - seem slightly different to the rates you quoted or is this in scotland.

Income Tax rates and Personal Allowances

Personal Allowance, Income Tax rates, bands and thresholds.

www.gov.uk

Income Tax rates and allowances for current and previous tax years

www.gov.uk

 

[Montanes]

you may have taken this into account with the info above , so apologies


isn that wrong now - I understood , that over 100K , you started to lose the allowance - at £1 lost for every £2 you earn -= so at 122K you lost the lot
or is that again different in scotland ???

Income Tax rates and Personal Allowances

Personal Allowance, Income Tax rates, bands and thresholds.

www.gov.uk

Also the tax rates for 23/24 - seem slightly different to the rates you quoted or is this in scotland.

Income Tax rates and Personal Allowances

Personal Allowance, Income Tax rates, bands and thresholds.

www.gov.uk

Income Tax rates and allowances for current and previous tax years

www.gov.uk

You're absolutely correct, you lose the personal allowance if your salary is over £125,140. I should have pointed that out. Well done for recognising that us Scots like to complicate things in the UK. lol

 

[etaf]

i have had a play and setout a spreadsheet as below
its also on dropbox - but only for a fewdays

Salary Tax- Scotland - ETAF.xlsx

Shared with Dropbox

www.dropbox.com

I'm still working on the loss of £1 allowance for every £2 earned - though i had it but it was wrong - and have to go out now

I used this website to calculate the tax due 2023/24 for scotland and seems to give correct results

Income Taxc Calculator for Intermediaries | Scottish BS

www.scottishbs.co.uk

which seems to work up to 100,000

Salary Tax.xlsx
	A	B	C	D	E	F	G	H	I	J
1		£ -	£ 12,571.00	£ 14,733.00	£ 25,689.00	£ 43,663.00	£ 125,141.00
2		£ 12,570.00	£ 14,732.00	£ 25,688.00	£ 43,662.00	£ 125,140.00	£ 1,000,000.00
3	Enter salary below	0%	19%	20%	21%	42%	47%
4	£ 100,000.00	£ -	£ 2,162.00	£ 10,956.00	£ 17,974.00	£ 56,338.00	£ -
5	Over 100,000
6	£ -	£ -	£ -	£ -	£ -	£ -	£ -
7									TOTAL TAX
8	TOTAL	£ -	£ 410.78	£ 2,191.20	£ 3,774.54	£ 23,661.96	£ -		£ 30,038.48
9
10
11
12
13
14
15
16									https://www.scottishbs.co.uk/intermediary-hub/intermediary-support/income-tax-calculator
17
18									99000
19									$29,618.48	correct
20
21
22									15000
23									464.38	correct
24
25									22000
26									£1,864.38	correct
27
28									38000
29									$5,187.50	correct
30
31									66000
32									$15,758.48	correct
33
34									100000
35									$30,038.48	correct
Sheet1

Cell Formulas
Range	Formula
B4	B4	=IF(A4>100000,"",0)
C4:G4	C4	=IF(A4>100000,"",IF($A4>B$2,MIN((C$2-B$2),($A4-B$2)),0))
A6	A6	=IF(A4>100000,A4,0)
B6	B6	=IF($A6=0,0,MIN(B$2,(((A6-100000))*2)-B$2))
C6:G6	C6	=IF($A6>B$2,MIN((C$2-B$2),($A6-B$2)),0)
B8	B8	=IF($A4<=100000,B4*B3,B6*C3)
C8:G8	C8	=IF($A4<=100000,C4*C3,C6*C3)
I8	I8	=SUM(B8:G8)

 

[etaf]

i may not be following this correct
As i cannot seem to find a scotland calculator that will give me the full break down for each threshold !!!!! - all seem to just use the 20%
I know i'm correct upto 100,000 with the link i used

Income Taxc Calculator for Intermediaries | Scottish BS

www.scottishbs.co.uk

if you earn 110,000 =
then the calculator - says
TAX £36,338.48
and based on other sited , which have confirmed

TaxCalc - UK taxation software

Integrated tax return and accounts production - fast, efficient and affordable software for practices, businesses and self-assessment.

kb.taxcalc.com

The general rule when working out a reduction in personal allowance is that if your income is greater than £100,000, your allowance will be reduced by £1 for every £2 over £100,000.
For example, if your income is £110,000, your personal allowance will be reduced by £5,000.

if thats reduced by 5000 - then the personal allowance become
12,570 - 5000
=7,570

so you would pay 19% tax on that 5000
At least that whats i thought
or maybe the tresholds all change and reduce by 5000 now - so instead of 19% upto 14,732 - its now only upto 9,732 etc etc
But that seems strange thing to do ........ as i say , i cannot find a breakdown by each threshold for scotland 23/24 year to see what happens

so in the 0-12570
you pay an extra 19% on the 5000 = 950
and you also pay 42% on the extra 10,000 = 4,200

add those together = 5,170

100,000 = £30,038.48
plus 5,170
= 35188.48
and NOT
£36,338.48

so i'm doing something wrong ......

as i say going out now

 

[etaf]

worked it out , using a different website now

which also provides a breakdown at each tax amount

The Salary Calculator - Take-Home tax calculator

The Salary Calculator tells you monthly take-home, or annual earnings, considering UK Tax, National Insurance and Student Loan. The latest budget information from April 2024 is used to show you exactly what you need to know. Hourly rates, weekly pay and bonuses are also catered for. Why not find...

www.thesalarycalculator.co.uk

The loss of the threshold allowance is taxed at the 42% figure

so for the 5000 in the 110,000 example , the 5000 is NOT at the 19% - BUT at the 42%

tested with other figures and it matches -


I have added the spreadsheet to dropbox

Dropbox - File Deleted - Simplify your life

www.dropbox.com

as i say only there for a few days

How it works

you ONLY enter the salary into 1 cell - that is A5
if the salary is 100,000 or less - then the amount you would pay TAX on is shown in row 5 , for each threshold

ROW 10 - TOTAL , then calculates the tax due , and the total due is shown in I10

NOW if you enter OVER 100,000
A8 automatically populates - and then ROW 8 shows all the amounts due for each threshold - BUT The 0 - 12570 column B is taxed at 42%

ROW 8 shows the amount tax is payable on
Then ROW 10 - still shows the tax - as before - BUT B10 is at the rate of 42%
then cell F11 shows the total tax at 42% - so can be compared with any online calculator breakdown

Total is given in cell I10

XL2BB for an example below 100,000

Salary Tax- Scotland - ETAF2.xlsx
	A	B	C	D	E	F	G	H	I
1	2023/24 tax year	£ -	£ 12,571.00	£ 14,733.00	£ 25,689.00	£ 43,663.00	£ 125,141.00
2	Thresholds	£ 12,570.00	£ 14,732.00	£ 25,688.00	£ 43,662.00	£ 125,140.00	£ 1,000,000.00
3	Tax %	0%	19%	20%	21%	42%	47%
4	Enter salary below
5	£ 50,000.00	£ -	£ 2,162.00	£ 10,956.00	£ 17,974.00	£ 6,338.00	£ -
6
7	Calc if Salary is over 100,000	42%	<--- loss of the allowance over 100,000 is taxed at the 42% rate
8	£ -	£ -	£ -	£ -	£ -	£ -	£ -
9									TOTAL TAX
10	TAX Due by threshold	£ -	£ 410.78	£ 2,191.20	£ 3,774.54	£ 2,661.96	£ -		£ 9,038.48
11	Total at the 42% rate				loss of the allowance over 100,000 is taxed at the 42% rate --->	£ 2,661.96
Sheet1

Cell Formulas
Range	Formula
B5	B5	=IF(A5>100000,"",0)
C5:G5	C5	=IF(A5>100000,"",IF($A5>B$2,MIN((C$2-B$2),($A5-B$2)),0))
A8	A8	=IF(A5>100000,A5,0)
B8	B8	=IF($A8=0,0,MIN(B$2,(A8-100000)/2))
C8:G8	C8	=IF($A8>B$2,MIN((C$2-B$2),($A8-B$2)),0)
B10	B10	=IF($A5<=100000,B5*B3,B8*B7)
C10:G10	C10	=IF($A5<=100000,C5*C3,C8*C3)
I10	I10	=SUM(B10:G10)
F11	F11	=B10+F10

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5	Expression	=A5<=100000	text	YES
A8	Expression	=A8>0	text	YES

an example above 100,000 = 128,000

Salary Tax- Scotland - ETAF2.xlsx
	A	B	C	D	E	F	G	H	I
1	2023/24 tax year	£ -	£ 12,571.00	£ 14,733.00	£ 25,689.00	£ 43,663.00	£ 125,141.00
2	Thresholds	£ 12,570.00	£ 14,732.00	£ 25,688.00	£ 43,662.00	£ 125,140.00	£ 1,000,000.00
3	Tax %	0%	19%	20%	21%	42%	47%
4	Enter salary below
5	£ 128,000.00
6
7	Calc if Salary is over 100,000	42%	<--- loss of the allowance over 100,000 is taxed at the 42% rate
8	£ 128,000.00	£ 12,570.00	£ 2,162.00	£ 10,956.00	£ 17,974.00	£ 81,478.00	£ 2,860.00
9									TOTAL TAX
10	TAX Due by threshold	£ 5,279.40	£ 410.78	£ 2,191.20	£ 3,774.54	£ 34,220.76	£ 1,344.20		£ 47,220.88
11	Total at the 42% rate				loss of the allowance over 100,000 is taxed at the 42% rate --->	£ 39,500.16
Sheet1

Cell Formulas
Range	Formula
B5	B5	=IF(A5>100000,"",0)
C5:G5	C5	=IF(A5>100000,"",IF($A5>B$2,MIN((C$2-B$2),($A5-B$2)),0))
A8	A8	=IF(A5>100000,A5,0)
B8	B8	=IF($A8=0,0,MIN(B$2,(A8-100000)/2))
C8:G8	C8	=IF($A8>B$2,MIN((C$2-B$2),($A8-B$2)),0)
B10	B10	=IF($A5<=100000,B5*B3,B8*B7)
C10:G10	C10	=IF($A5<=100000,C5*C3,C8*C3)
I10	I10	=SUM(B10:G10)
F11	F11	=B10+F10

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5	Expression	=A5<=100000	text	YES
A8	Expression	=A8>0	text	YES

 

[Dave Patton]

The payable can be calculated with Sum or Sumproduct.
The tax credit can be included in the formula.
We can do the above with a Lambda function.

I do not have information on your tax system.
Tiered taxes can be calculated as follows.
You can edit the brackets and rates in the table or in the Lambda function.
Please advise if you want the Lambda; it doesn't show automatically with the post.
If you use Sum or SumProduct, you can name the bracket and rate differential information.

LAMBDA.xlsm
	A	B	C
1	Income	Tax Payable	Tax Payable
2	140,000.00	28,117.61	28,117.61
3
4
5	Brackets	Percentage
6
7	0	15.0%
8	50,197	20.5%
9	100,392	26.0%
10	155,625	29.0%
11	221,708	33.0%
4f

Cell Formulas
Range	Formula
B2	B2	=F_Tax22(A2)
C2	C2	=SUM((A2>A7:A11)*(A2-A7:A11)*(B7:B11-B6:B10))

 

[Montanes]

I am going to try some of these now but here is a link to the thresholds on government website Income Tax in Scotland
If i get something to work i will mark accordingly.
Thanks to everyone for your help

 

[Dave Patton]

Please advise how the loss of threshold is calculated; the calculations below set a ceiling for the extra tax.
Please review the brackets, rates, and calculation of total tax.
The first example does not require the tables.

LAMBDA_2.xlsm
	A	B
1	Income	Tax Payable
2	128,000.00	47,821.48
3	190,000.00	83,651.48
4f

Cell Formulas
Range	Formula
B2:B3	B2	=TaxU_23(A2)

LAMBDA_2.xlsm
	A	B	C
1	Income	Tax Payable	Tax Payable
2	128,000.00	47,821.48	47,821.48
3	190,000.00	83,651.48	83,651.48
4
5
6	Bracket	Percentage
7
8	0	0.00%
9	12,570.00	19.00%
10	14,732.00	20.00%
11	25,688.00	21.00%
12	43,662.00	42.00%
13	125,140.00	47.00%
4f

Cell Formulas
Range	Formula
B2:B3	B2	=TaxU_23(A2)
C2:C3	C2	=SUM((A2>$A$8:$A$13)*(A2-$A$8:$A$13)*($B$8:$B$13-$B$7:$B$12))+(A2>10^5)*MIN(12570,(A2-10^5)/2*0.42)

 

[Dave Patton]

I edited the formula re the tax on the 12570.
No information on how the amount is calculated.
You can check the formula with a range of Taxable Incomes.

LAMBDA_2.xlsm
	A	B	C
1	Income	Tax Payable	Tax Payable
2	128,000.00	47,220.88	47,220.88
3	125,140.00	45,876.68	45,876.68
4	200,000.00	81,060.88	81,060.88
5
4f

Cell Formulas
Range	Formula
B2:B4	B2	=TaxU_23(A2)
C2:C4	C2	=SUM((A2>$A$8:$A$13)*(A2-$A$8:$A$13)*($B$8:$B$13-$B$7:$B$12))+(A2>10^5)*MIN(5279.4,(A2-10^5)/2*0.42)