if formula help for - reduce £1 for every £2 you earn over £100,000

[Data_Alter]

I am doing a PAYE calculation and do not know how to build this in my formula(highlighted in bold below). Due to this I am unable to correctly calculate the tax for people whose salary is above 100,000

Note that your personal allowance will reduce by £1 for every £2 you earn over £100,000

I have attached the workflow and data. The full rules are below for reference

Tax rate 2022-23

Personal allowance 0%	£0-£12,570
Basic-rate (20%)	£12,571-£50,270
Higher-rate (40%)	£50,271-£150,000
Additional-rate (45%)	£150,001+

[ArtApa]

Hi @Data_Alter - I was not able to achieve exactly the same result as in your Excel, but I think the logic is correct. The formula is the following:

if
[Annual Salary]<=12570 then 0 elseif
[Annual Salary]<=50270 then ([Annual Salary]-12570)*0.2/12 elseif
[Annual Salary]<=150000 then ([Annual Salary]-50270)*0.4/12 + (50270-12570)*0.2/12 else
([Annual Salary]-150000)*0.45/12 + (150000-50270)*0.4/12 +
(50270-12570 +
iif([Annual Salary]>100000,
iif(([Annual Salary]-100000)/2>12570,
12570,
([Annual Salary]-100000)/2),
0))
*0.2/12
endif

The way I understood the requirement is the following. If someone earns more than 100,000. Then the 0% tax is applied on a smaller amount. I increased the amount which is taxable by 20%, but the increase cannot be more than 12,570. However, if all thresholds need to slide down then iif should be applied on all levels. Hope that helps.

[Data_Alter]

Hi @ArtApa 

Thank you so much.

The personal allowance 12,570 ( on which we have 0% tax) will reduce by £1 for every £2 increase

For eg. the personal allowance for a person with salary of 100,002 will be 12,569.

The calculation for 112,000 will be

[50270 - 12570 + ((112000-100000) /2) ] * 20%   =  8740

 +

 [(112,000 - 50270) * 40% ] = 24692

Total = 33,492

Monthly = 2,786

I am getting a different number through your formula

[ArtApa]

Hi @Data_Alter - Then, are you happy with the solution? 

[Data_Alter]

Sorry i meant I am getting different number. I am getting 2686 from your formula

[ArtApa · Accepted solution]

Hi @Data_Alter - Fixed.