Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
I pay an accountant to do it !!
Pete |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
"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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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. |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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. |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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. |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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 |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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 |
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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 |
#15
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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 |
#16
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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 |
#17
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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 |
#18
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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 |
#19
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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 |
#20
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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 |
#21
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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 |
#22
Posted to microsoft.public.excel.newusers
|
|||
|
|||
uk tax calculator
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to design microsoft Excel subnet calculator | Excel Worksheet Functions | |||
Excel vs. calculator multiplication product discrepancy... | Excel Worksheet Functions | |||
calculator | Excel Discussion (Misc queries) | |||
Payroll calculator template | Excel Discussion (Misc queries) | |||
loan calculator cells, P&I formulas do not copy to second cloumn | Excel Worksheet Functions |