EXCEL - UK income tax

[wibble32]

Hi

I have been trying to work out how to put an annual Gross income in A1 then have the either the Tax paid or Nett Income in another cell.

ie 10K tax free, 10000-27700 at 20% 27700 plus at 40%

I am not interested in the NI component.

Can anyone assist please with an equation?

I have tried using those posted on some accountant sites, but they seem to be "this equation is broken, oh I have fixed it" without posting what the fix was.

Thank you
Rgds

[Nickpicks]

I think it's got to be a nested IF statement.

=IF(A1<10000,0,IF(A1<27700,0.2*(A1-10000),(0.2*27700-10000)+(0.4*A1-27700)))

so, in english:
If A1 less than 10,000, zero, otherwise:
If A1 less than 27700, 20% of A1 minus 10k, otherwise:
20% of 17700 plus 40% of A1 minus 27700

[wibble32]

I think it's got to be a nested IF statement.

=IF(A1<10000,0,IF(A1<27700,0.2*(A1-10000),(0.2*27700-10000)+(0.4*A1-27700)))

so, in english:
If A1 less than 10,000, zero, otherwise:
If A1 less than 27700, 20% of A1 minus 10k, otherwise:
20% of 17700 plus 40% of A1 minus 27700

Thank you very much indeed
Should there be a 3rd nested IF statement for the 40% tax applicable above 27700?

It is accurate up to 27699

However if I put 35000 in A1 I get Tax of -18160

Thanks again
Rgds

[Edward3c]

Feel glat you don't live North of the boarder. We have 19%, 20%, 21%, 41% and 46%

[Nickpicks]

Thank you very much indeed
Should there be a 3rd nested IF statement for the 40% tax applicable above 27700?

No need for a 3rd nest - if it hasn't met the 1st or 2nd IF, then it must be the other (>27.7k)

It is accurate up to 27699

However if I put 35000 in A1 I get Tax of -18160

Thanks again
Rgds

Sorry, my mistake - it should be:

=IF(A1<10000,0,IF(A1<27700,0.2*(A1-10000),(0.2*(27700-10000))+(0.4*(A1-27700))))

I tested it for values in all 3 thresholds and found that error in line 3 of my testing spreadsheet and fixed it, but forgot to copy it back up to line 1 to then send to you.
(it was doing 20% then subtracting the 10k, rather than 20% of the difference to 10k)

[Nickpicks]

Are you sure you've got the right bands? I thought they were 20% at 12,570 and 40% at 50,271 (and 45% at 150k, which would need a further IF)

[Nickpicks]

A slightly more elegant way of doing it (in my opinion because I find nested IFs difficult to follow) is to use the IFS function (which basically does a series of [logical test],[value/formula if true]

=IFS(A1<10000,0,A1<27700,0.2*(A1-10000),A1>=27700,0.2*(27700-10000)+0.4*(A1-27700))

So the orange bit is if less than 10k,
blue is more than 10 but less than 27.7k
red is above 27.7k

[wibble32]

@Nickpicks

Thank you very much indeed I shall try those tonight. I knew of If and was going with IF AND, I did not know of the IFS function, very informative. As for the TAX bands seems I was living in the past?

Thank you again
Rgds

[Woz]

Someone else has done all the work for you

https://www.ecommerceaccountants.co.uk/resources/tax-calculator

[gpj]

Feel glat you don't live North of the boarder. We have 19%, 20%, 21%, 41% and 46%

In Wales we've got bands of
10% + 10%
30% + 10%
35% + 10%

The first figure is the discounted rate from Westminster and the +10% is controlled by Cardiff.

[wibble32]

Someone else has done all the work for you
https://www.ecommerceaccountants.co....tax-calculator

Woz, thanks but have been building a spredsheet to decide whether to take CETV from works pension and invest, less fund fees, plus Govt pensions etc for joint with Mrs W32 and Mrs W32 if I croak, so every line would have been a look up. Thanks again.
@Nickpicks cracked it
Rgds