Update and Tidy Up Income Tax Formula

[techfreak]

Hi all,

So I've created a spreadsheet that calculates someones net income (for the U.K. less tax and national insurance contributions).

However, as the government like to tinker with things all the time the way the personal allowance (tax free amount you are allowed to earn before paying any tax) works has changed rendering my formula useless.

I'm sure I could figure it out but at the moment my brain is fried looking at all of spreadsheets/numbers so wondered if someone here could help?

I'm sure my formula could also be simplified?

the current way it works is B4 = Gross Salary, E4 = B4 less personal allowance of £10,600.

E5= formula to calculate tax paid - currently it's a nested IF as follows:

=IF(B4>150000,(31785*20%)+(150000-31785)*40%+($B$4-150000)*45%,IF(E4>100000,(31785*20%)+($B$4-31785)*40%,IF(E4>31785,(31785*20%)+(($B$4-10600)-31785)*40%,E4*20%)))

The way the personal allowance now works is between £100,000 and £121,200 of income the personal allowance of £10,600 decreases by £1 for every £2 earned until at £121,200 at which point for any income above this the personal allowance is reduced to £0 and you are liable to income tax on all of your income.

Please tell me there is an easy way to formulate this in excel?

 

[Special-K99]

Does this work?

Personal allowance = 10600-(AND(B4>=100000,B4<=121200)*(B4-100000)/2+(B4>121200)*10600)

 

[Eric W]

Definitely tricky, since it is government work! I think I figured out a reasonably simple set of formulas. Just to be clear, the personal allowance is taken off the top, then the tiered rates are 0-31785=20%, 31785-15000=40%, and over 150000=45%?

E4: =B4-MAX((10600-ABS((B4-100000))/2),0)
E5: =MAX(E4-150000,0)*.45%+MAX(MIN(150000,E4)-31785,0)*.40%+MIN(E4,31785)*.20%

You need to test these well, since my tests don't have actual data. But I get back what I think I should! Good luck!

Edit: wait on E4

 

[Eric W]

Sheesh . . . I feel a little silly, but I think I have it now:

E4: =B4-MAX(10600-MAX((B4-100000)/2,0),0)
E5: =MAX(E4-150000,0)*0.45%+MAX(MIN(150000,E4)-31785,0)*0.4%+MIN(E4,31785)*0.2%

 

[techfreak]

Thanks Special K and Eric. Eric, your understanding of the tiered tax system is correct.

I've tried special K's formula first and got it working so not tried yours Eric but will have a play about with it later as it's a good learning exercise.

I had to modify Special K's to work for me as follows so E4=

=IF(B4>10600,B4-(10600-(AND(B4>=100000,B4<=121200)*(B4-100000)/2+(B4>121200)*10600)),B4)

Where B4 = Gross Salary entered

Reason being this the value used to calculate the tax in E5 =

=IF(E4>=150000,(31785*20%)+(150000-31785)*40%+(E4-150000)*45%,IF(AND(E4<150000,E4>31785),(31785*20%)+(E4-31785)*40%,IF(AND(E4<31785,B4>10600),(E4*20%),0)))

Tested and this gives me the results I was expecting.

I'll try your formula out Eric as it seems a lot simpler to read than mine above and report back later.