uk tax calculator

[Guest]

thanks for getting back to me,
still having problems getting #Div/0!
think its the way i've constructed the table?
A1 = Gross earnings
B2 = frequency of pay ( in my case 26 for fortnightly)
C1 = tax band 5035
C3 = 0
C4 = 5044
C5 = 33540
D3 = 0%
D4 = 11%
D5 = 1%
A7 = =SUMPRODUCT(--(A1>C3:C5/B2),A1-C3:C5/B2,D35-D24)

just incase i get same problem with income tax can you help with that aswell

p.s. i can now see how 7185 band is worked out thank you very much.

when all is working i'd like you to have a look a the calculator if possible
is there any way i can send you it?

then you can see what i'm trying to do, and would welcome any feed back on it
theres probably an easer way to do what i've done but just learning, thats
why all the questions.

 

[Guest]

Thanks daddylonglegs I’ve found my problem now...
A great job done by you and Bob Phillips for helping me on this task.
Much to your relief i now consider the calculator working 100%

thanks for getting back to me,
still having problems getting #Div/0!
think its the way i've constructed the table?
A1 = Gross earnings
B2 = frequency of pay ( in my case 26 for fortnightly)
C1 = tax band 5035
C3 = 0
C4 = 5044
C5 = 33540
D3 = 0%
D4 = 11%
D5 = 1%
A7 = =SUMPRODUCT(--(A1>C3:C5/B2),A1-C3:C5/B2,D35-D24)

just incase i get same problem with income tax can you help with that aswell

p.s. i can now see how 7185 band is worked out thank you very much.

when all is working i'd like you to have a look a the calculator if possible
is there any way i can send you it?

then you can see what i'm trying to do, and would welcome any feed back on it
theres probably an easer way to do what i've done but just learning, thats
why all the questions.

Hi Dark Night

Which formula gives an error?

If you put your NI bands and percentages in a table like this:

band percentage

0 0%
5044 11%
33540 1%

[top left of table, "band" is in C1 - bottom right "1%" in D5

you can use the formula

=SUMPRODUCT(--(A1>C3:C5/B2),A1-C3:C5/B2,D35-D24)

you can accomplish something similar using a table for Income Tax. Note
C2 & D2 should be blank

Income tax is paid at 10% for the first £2150 of taxable earnings but
because the first £5035 is tax free this means that 10% is paid for all
earnings between £5035 and (£5035+£2150=£7185) hence the £7185 band