hello i'm trying to make a wage calculator in excel that works out your tax
and NI ( UK ) i think i need to work a formula out on my topline but not
sure how to do this as my wages are diffrent each week.
please help

A UK tax calculator is quite complex, because as well as knowing all of the
tax bands and the rates, you have to take into account any tax free
deductions (such as pension payments), but worst of all, as I understand it
your tax is calculated based upon the assumption that whatever you earn this
month/week will be earnt every month/week for the rest of the year, so you
need tax paid to date, the number of months/weeks to go etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DarkNight" wrote in message
...
hello i'm trying to make a wage calculator in excel that works out your
tax
and NI ( UK ) i think i need to work a formula out on my topline but not
sure how to do this as my wages are diffrent each week.
please help

I pay an accountant to do it !!

Pete

But I bet you only do it once a year, this guy wants it once a week <g

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Pete_UK" wrote in message
ups.com...
I pay an accountant to do it !!

Pete

thanks guys for taking the time to respond to this post.
think i'm getting somewhere...
when i calculate my gross earnings for a week i then divide by a % this
however changes with diffrent amounts of pay.. but would like it to auto
calculate.
p.s. it dosent have to be 100% right just a rough estamate would be nice.
i've even been to the inland revenue site and there calculator gets me
somewhere near old payslips.

If approximate is close enough then do this

A1: earning for this month
B1: tax allowance (e.g. 4895)
C1: 10%
B2: 2090
C2: 22%
B3: 32400
C3: 40%
A2: =MAX(($A$1-SUM($B$1:B1)/12),0)*C1
A3: =MAX($A$1-B1/12-B2/12,0)*(C2-C1)
A4: =MAX($A$1-B1/12-B2/12,0)*(C2-C1)

and the answer in A5

=ROUND($A$1-SUM(A2:A4),2)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DarkNight" wrote in message
...
thanks guys for taking the time to respond to this post.
think i'm getting somewhere...
when i calculate my gross earnings for a week i then divide by a % this
however changes with diffrent amounts of pay.. but would like it to auto
calculate.
p.s. it dosent have to be 100% right just a rough estamate would be nice.
i've even been to the inland revenue site and there calculator gets me
somewhere near old payslips.

"Bob Phillips" wrote in message
...

B1: tax allowance (e.g. 4895)

Children grown up and left home then Bob? <g

--


Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk

I never got a tax allowance for my girls!

Actually, I had no idea what the number was, had to look it up on the IR
site <G

Bob

"Sandy Mann" wrote in message
...
"Bob Phillips" wrote in message
...

B1: tax allowance (e.g. 4895)

Children grown up and left home then Bob? <g

--


Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk

Thanks Bob it works out very well within £4.96 to £8.45 so thats great. Thank
You Very Much.
just 1 little thing A3 and A4 are the same is that suposed to be correct( it
works all the same )
and what are the values in b2 and b3
sorry to be a pain

"Bob Phillips" wrote:

If approximate is close enough then do this

A1: earning for this month
B1: tax allowance (e.g. 4895)
C1: 10%
B2: 2090
C2: 22%
B3: 32400
C3: 40%
A2: =MAX(($A$1-SUM($B$1:B1)/12),0)*C1
A3: =MAX($A$1-B1/12-B2/12,0)*(C2-C1)
A4: =MAX($A$1-B1/12-B2/12,0)*(C2-C1)

and the answer in A5

=ROUND($A$1-SUM(A2:A4),2)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DarkNight" wrote in message
...
thanks guys for taking the time to respond to this post.
think i'm getting somewhere...
when i calculate my gross earnings for a week i then divide by a % this
however changes with diffrent amounts of pay.. but would like it to auto
calculate.
p.s. it dosent have to be 100% right just a rough estamate would be nice.
i've even been to the inland revenue site and there calculator gets me
somewhere near old payslips.

No, sorry, A4 should be

=MAX($A$1-SUM($B$1,B3)/12,0)*(C3-C2)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DarkNight" wrote in message
...
Thanks Bob it works out very well within £4.96 to £8.45 so thats great.
Thank
You Very Much.
just 1 little thing A3 and A4 are the same is that suposed to be
correct( it
works all the same )
and what are the values in b2 and b3
sorry to be a pain

"Bob Phillips" wrote:

If approximate is close enough then do this

A1: earning for this month
B1: tax allowance (e.g. 4895)
C1: 10%
B2: 2090
C2: 22%
B3: 32400
C3: 40%
A2: =MAX(($A$1-SUM($B$1:B1)/12),0)*C1
A3: =MAX($A$1-B1/12-B2/12,0)*(C2-C1)
A4: =MAX($A$1-B1/12-B2/12,0)*(C2-C1)

and the answer in A5

=ROUND($A$1-SUM(A2:A4),2)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DarkNight" wrote in message
...
thanks guys for taking the time to respond to this post.
think i'm getting somewhere...
when i calculate my gross earnings for a week i then divide by a %
this
however changes with diffrent amounts of pay.. but would like it to
auto
calculate.
p.s. it dosent have to be 100% right just a rough estamate would be
nice.
i've even been to the inland revenue site and there calculator gets me
somewhere near old payslips.

Thanks for your time and help bob, just gonaa play about abit more with my
calculations then might even find out where i can post the finished version.
thanks again with all your help

"Bob Phillips" wrote:

No, sorry, A4 should be

=MAX($A$1-SUM($B$1,B3)/12,0)*(C3-C2)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DarkNight" wrote in message
...
Thanks Bob it works out very well within £4.96 to £8.45 so thats great.
Thank
You Very Much.
just 1 little thing A3 and A4 are the same is that suposed to be
correct( it
works all the same )
and what are the values in b2 and b3
sorry to be a pain

"Bob Phillips" wrote:

If approximate is close enough then do this

A1: earning for this month
B1: tax allowance (e.g. 4895)
C1: 10%
B2: 2090
C2: 22%
B3: 32400
C3: 40%
A2: =MAX(($A$1-SUM($B$1:B1)/12),0)*C1
A3: =MAX($A$1-B1/12-B2/12,0)*(C2-C1)
A4: =MAX($A$1-B1/12-B2/12,0)*(C2-C1)

and the answer in A5

=ROUND($A$1-SUM(A2:A4),2)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DarkNight" wrote in message
...
thanks guys for taking the time to respond to this post.
think i'm getting somewhere...
when i calculate my gross earnings for a week i then divide by a %
this
however changes with diffrent amounts of pay.. but would like it to
auto
calculate.
p.s. it dosent have to be 100% right just a rough estamate would be
nice.
i've even been to the inland revenue site and there calculator gets me
somewhere near old payslips.

Hi Dark Night,

I note that from the start of this tax year the income tax bands have
changed to the following

untaxed standard allowance first £5035
10% band next £2150
22% band next £31150

this means that to the nearest £ the weekly rates are as follows

£0-£97 0%
£97-£138 10%
£138-£735 22%
£735+ 40%

National insurance (assuming you're not in a company pension scheme)
works like this

£0-£97 0%
£97-£645 11%
£645+ 1%

As Bob says, there are complications with the way Income Tax is
calculated but given these rates, if your gross earnings are in cell A1
this formula gives your approximate Income Tax

=SUMPRODUCT(--(A1{0,97,138,735}),A1-{0,97,138,735},{0,0.1,0.12,0.18})

and this your National Insurance

=SUMPRODUCT(--(A1{0,97,645}),A1-{0,97,645},{0,0.11,-0.1})

Clearly if you deduct these two amounts from your gross pay you should
get
your net pay

...or you can combine the above two formulas in one to give your total
deductions

=SUMPRODUCT(--(A1{0,97,138,645,735}),A1-{0,97,138,645,735},{0,0.21,0.12,-0.1,0.18})


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537126

Thanks daddylonglegs, i've adjusted the tax bands.
and tryed the income tax and national insurance but for some reason its way
out £35, which is a real shame.
if i use Bobs example and add together cells a2:a4 it gets somewhere near
tax and cell a2 gets me somewhere near Ni.
also would it be possible for you to post the site where you got the tax
bands, i've been looking and non of it makes any sence.


"daddylonglegs" wrote:


Hi Dark Night,

I note that from the start of this tax year the income tax bands have
changed to the following

untaxed standard allowance first £5035
10% band next £2150
22% band next £31150

this means that to the nearest £ the weekly rates are as follows

£0-£97 0%
£97-£138 10%
£138-£735 22%
£735+ 40%

National insurance (assuming you're not in a company pension scheme)
works like this

£0-£97 0%
£97-£645 11%
£645+ 1%

As Bob says, there are complications with the way Income Tax is
calculated but given these rates, if your gross earnings are in cell A1
this formula gives your approximate Income Tax

=SUMPRODUCT(--(A1{0,97,138,735}),A1-{0,97,138,735},{0,0.1,0.12,0.18})

and this your National Insurance

=SUMPRODUCT(--(A1{0,97,645}),A1-{0,97,645},{0,0.11,-0.1})

Clearly if you deduct these two amounts from your gross pay you should
get
your net pay

...or you can combine the above two formulas in one to give your total
deductions

=SUMPRODUCT(--(A1{0,97,138,645,735}),A1-{0,97,138,645,735},{0,0.21,0.12,-0.1,0.18})


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537126

Hi Dark Night,

I think you mentioned earnings for the week, so my formulas are based
on you inputting your gross weekly earnings whereas Bob's approach
assumes monthly earnings.

Assuming you enter the weekly rate my formulas should work give or take
a few pence, they certainly work for my earnings, otherwise you could
adjust for monthly pay (i.e. 12 times a year) giving this formula for
NI

=SUMPRODUCT(--(A1{0,422,2804}),A1-{0,422,2804},{0,0.11,-0.1})

although the rates are slightly different if you are in a company
pension scheme.

This site gives details of bands and allowances for both UK Income Tax
and NI

http://www.hmrc.gov.uk/rates/it.htm


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537126

sorry to be a pain,
a1=gross pay
b2=payment frequency (52 weekly, 13 montly 26 fortnightly)
how would i implament your formula to work this out
just trying to make the calculator very universal so it can follow me to my
next job which may be montly pay or weekly, at the moment i'm on fortnightly.
and thanks again for taking the time to respond.


"daddylonglegs" wrote:


Hi Dark Night,

I think you mentioned earnings for the week, so my formulas are based
on you inputting your gross weekly earnings whereas Bob's approach
assumes monthly earnings.

Assuming you enter the weekly rate my formulas should work give or take
a few pence, they certainly work for my earnings, otherwise you could
adjust for monthly pay (i.e. 12 times a year) giving this formula for
NI

=SUMPRODUCT(--(A1{0,422,2804}),A1-{0,422,2804},{0,0.11,-0.1})

although the rates are slightly different if you are in a company
pension scheme.

This site gives details of bands and allowances for both UK Income Tax
and NI

http://www.hmrc.gov.uk/rates/it.htm


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537126

Assuming B2 just contains a number try this for NI

=SUMPRODUCT(--(A1{0,5044,33540}/B2),A1-{0,5044,33540}/B2,{0,0.11,-0.1})

and this for income tax

=SUMPRODUCT(--(A1{0,5035,7185,38335}/B2),A1-{0,5035,7185,38335}/B2,{0,0.1,0.12,0.18})

If you wish you could include the figures in a table on your worksheet
and amend the formulas to reference these, then you only have to amend
the rates and bands each time they cahnge, without altering the
formulas.


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537126

that works a treat, thanks for you hard work.
1 little daft question the bands in your refering to are they 5035, 7185,
38335 NI
and 5044, 33540 for income tax ?
if so how come there diffrent?

"daddylonglegs" wrote:


Assuming B2 just contains a number try this for NI

=SUMPRODUCT(--(A1{0,5044,33540}/B2),A1-{0,5044,33540}/B2,{0,0.11,-0.1})

and this for income tax

=SUMPRODUCT(--(A1{0,5035,7185,38335}/B2),A1-{0,5035,7185,38335}/B2,{0,0.1,0.12,0.18})

If you wish you could include the figures in a table on your worksheet
and amend the formulas to reference these, then you only have to amend
the rates and bands each time they cahnge, without altering the
formulas.


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537126

DarkNight Wrote:
that works a treat, thanks for you hard work.
1 little daft question the bands in your refering to are they 5035,
7185,
38335 NI
and 5044, 33540 for income tax ?
if so how come there diffrent?



Yes, those bands, although you have them the wrong way round, 5044 and
33540 are for NI.

Possibly the 5044 and 5035 should be the same but it depends how you
calculate the number of weeks in a year! I'm not quite sure how the
Inland Revenue do that.....

I don't believe the bands have ever been co-ordinated as such, as they
currently stand there's a discrepancy in that the combined rate of NI
and income tax actually drops (at £33540 per year) to 23% and then goes
back up again when 40% band kicks in


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537126

thanks again for you help daddylonglegs, i've made a table like you surgested
so i'm not changing the formulas, but getting an error message
the formula you typed contains an error....
any ideas how i can solve this problem please
i'm draging information from another sheet called "user settings"
p.s i can find all the bands on the web site you gave me apart from 7185


"daddylonglegs" wrote:


DarkNight Wrote:
that works a treat, thanks for you hard work.
1 little daft question the bands in your refering to are they 5035,
7185,
38335 NI
and 5044, 33540 for income tax ?
if so how come there diffrent?



Yes, those bands, although you have them the wrong way round, 5044 and
33540 are for NI.

Possibly the 5044 and 5035 should be the same but it depends how you
calculate the number of weeks in a year! I'm not quite sure how the
Inland Revenue do that.....

I don't believe the bands have ever been co-ordinated as such, as they
currently stand there's a discrepancy in that the combined rate of NI
and income tax actually drops (at £33540 per year) to 23% and then goes
back up again when 40% band kicks in


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537126

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(--(A1C3:C5/B2),A1-C3:C5/B2,D3:D5-D2:D4)

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537126

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(--(A1C3:C5/B2),A1-C3:C5/B2,D3:D5-D2:D4)

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.

"daddylonglegs" wrote:


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(--(A1C3:C5/B2),A1-C3:C5/B2,D3:D5-D2:D4)

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537126

Thanks daddylonglegs Ive 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%


"DarkNight" wrote:

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(--(A1C3:C5/B2),A1-C3:C5/B2,D3:D5-D2:D4)

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.

"daddylonglegs" wrote:


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(--(A1C3:C5/B2),A1-C3:C5/B2,D3:D5-D2:D4)

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537126