Here you can download a macro-less spreadsheets for doing UK tax related calculations.
Please leave a comment below if you find this useful or have any feedback!
Please also consider making a donation towards my time and costs.
Personal Allowance, Income Tax, Class 1 National insurance and Student Loan Repayments.
Tax year 2017/2018 (From 6th April 2017 to 5th April 2018)
Tax year 2016/2017 (From 6th April 2016 to 5th April 2017)
Tax year 2015/2016 (From 6th April 2015 to 5th April 2016)
Tax year 2014/2015 (From 6th April 2014 to 5th April 2015)
Tax year 2013/2014 (From 6th April 2013 to 5th April 2014)
Tax year 2012/2013 (From 6th April 2012 to 5th April 2013)
Monthly PAYE versions.
Tax year 2011/2012 (From 6th April 2011 to 5th April 2012)
Now with
pensions, age related allowances, student loans and blind persons
allowance!
Tax year 2010/2011 (From 6th April 2010 to 5th April 2011)
(Including new 50% rate and personal allowance taper)
Tax year 2009/2010 (From 6th April 2009 to 5th April 2010)
Corporation Tax, Tax on Dividends, Class 1 NI, Employers NI, Class 2 and 4 NI (For Sole traders), Salary vs Dividends
Tax year 2014/2015 (From 6th April 2014 to 5th April 2015)
Tax year 2013/2014 (From 6th April 2013 to 5th April 2014)
Tax year 2012/2013 (From 6th April 2012 to 5th April 2013)
Please note I am unable to answer any questions about tax nor am I able to offer advice. For those things, please consult an accountant. Thank you for your understanding.
Thank you for your comment. This comment is awaiting moderation.
Hey Paul, Great spreadsheets! But I think I've noticed an error, due to the fact that spreadsheets start off "Protected". If you open the 2017/18 excel spreadsheet and change the Student Loan Payment (in Cell C11) to either Plan 1 or 2, I think you'll see "#NAME?" errors. I think this is because of an error in Cell C57. If a user unprotects the worksheet, a clicks within the formula bar of Cell C57, the problem resolves. I've check this for the excel spreadsheets for 2017/18 and 2016/17 Just thought I'd let you know.
How about a version that caters for PAYE earnings plus other earnings from commercial rental income? Now that would be great... if you can help please let me know.
Scrolling through the comments I think what many people are asking for is a self assessment s/sheet, giving tax computation for multiple income sources.
Think my formulae below work with ROUND function Regards
Also my rounding does not seem to work properly. Can I send you an ods file somehow?
@Paul - sorry (Im a bit lazy). Complete simple formula should read IF(A1=0,0,(taxcode*10+9)/52*weekno) to achieve equivalence with the prescribed method which I believe attempts to emulate the method with manual freepay table. This convoluted method is per final note in Section 4.3.1 of the specification ( Web address in my earlier post).
@MadKenny - I don't understand what you're trying to say but a quick glance at the two formulas would suggest they're likely not equivalent!
s/sheet model for calculating freepay allowance per section 4.3 of hmrc technical specs is as follows: Let A1 = taxcode, B1 = week no, C1=ROUNDDOWN(((QUOTIENT(A1-1,500)*£96.16)*B1)), D1 = ROUNDOWN((((MOD(A1-1,500)+1)*10)+9))/52*B1, E1 = IF(A1=0,0,C1+D1) The above is the spec for a computer based routine. I have tested this and the result always equates to IF(A1=0,0,(Taxcode*10)+9) which is much simpler!
Would be useful if there was an input parameter for other income - e.g. Pension
Hi paul, thanks again for your time with the tax calculator, any plans for 2018-2019 one?
Hi Paul, Excellent spreadsheet but would be improved with addition of dividend option as mentioned previously. One issue I noticed is that the calculated student repayment amount uses the repayment interest rate from Plan 1 for both Plans 1 & 2. It doesn’t affect me but may affect some of the younger users! Cheers, Brian
Very interested to see your spreadsheets and extremely pleased to see the open office versions. In the spirit of open source may I point you to hmrc document providing formulae for calculating free pay allowances from tax code. https://www.gov.uk/government/publications/payroll-technical-specifications-income-tax Hope this will be of help to you Regards Ken
Hello. This 2017-18 PAYE looks great -- it is clearly the best spreadsheet of its kind I have seen. One point though: Often HMRC provide a tax code for calculating tax & allowances, especially where an employee has more than one income source. How can this be incorporated, please? And should a tax code of 257L (fictional example) be counted as £2570 or £2579 please? And does it just replace the basic tax allowance? My thanks to you, Ray Williamson.
Very good. Looking forward to the 2018/19 update. Thank you for your hard work in setting these up.
Dear Paul Your tax spreadsheet is interesting but very similar to others in the internet. However, if you added in savings interest and dividend income it would be the only one avalible online. There is virtually no information online about how to calculate if you are a higher rate taxpayer in such circustances. Kind regards Bob
Hi How to change protected....
For those below struggling with Student Loan: This cell C11 should be entered with (one of) the following values { 0 ; 1 ; 2 } (ie 0 or 1 or 2) C57 may still have ?NAME error in which case select it, press F2 and Enter (sequentially, not simutaneously) and that sorted it for me. If you can't edit the cells you need to unlock the sheet. This is done via Review > Unprotect Sheet in the top bar.
Hi, I'm so sorry but I'm not sure what info I should put for the Student Loan Repayment. I know I should put 'Plan 1' or 'Plan 2' into cell C11 (which works fine) but then #NAME appears in cells C6-C8 (and C57). Please help!
Greetings I trade shares and these days it is a requirement to calculate various profit/ losses for the year when the same share has been bought and sold several times (pooling) and also 30 day rule, all under 285 ? Just wonder if you have anything like a programme to address this? Kind regards Bryan
Hi, is there any chance you can add a weekly tax ammount, you have in cell c49 total weekly n.i.
just what I was looking for- having started to create it in a very crude format, I discovered your download. donation on the way !
Great Calculator Suggestions For Improvement 1.Could you include salary sacrifices - Cyle To Work and Share Options? 2. If you receive an additional bonus say 0.5% of annual salary in one month how is NIC and Tax Calculated such that monthly payment is correct for that month? 3.Where you have an option for cashing in SAYE before the qualifying period, Income Tax and NIC is payable. Calculation?
Fabulous calculator. Could you add company car & dividends to the personal calculations? Many thanks
Hi Paul! Very useful spreadsheet. Have you considered adding a section for charitable donations? As I'm sure you know, these are tax deductible. And it would be nice to remind people that they're an option, and make it clear that giving £1 to a charity only costs them 80p/60p/55p depending on their tax rate. More info... https://www.gov.uk/donating-to-charity/donating-straight-from-your-wages-or-pension https://www.givingwhatwecan.org/get-involved/tax-deductibility/united-kingdom/
hi hope you can help. just wondered where i am going wrong in my spreadsheet am multiplying hourly x rate x week hours x 52.18 months per year then devide by 12 for monthly minus 11000 income tax (code s1100L cumulative) remainder /=100*20, and nat ins minus 8000 remainder /100*12 am i missing something? monthly pay statement tax+nat ins figures dont match!
Hi bit of a late starter with regards to spreadsheets etc, my question is how do I unprotected your spreadsheet for 20016-17 tax return. Many thanks for taking the time to create the spreadsheet and to answering my query Kind regards Brian Walker
Excellent sheet all though do you have a Sole Trader version for 2017/18 to reflect the current personal allowance? Thanks,
excellent formulae. I need to add 'other income' rows. Thanks
Do you have a spreadsheet where you can input all info to do with your personal tax so it is easier to give to the accountant including 1. property income / expend. investment and bank income, income from your limited business. I have done one but think it could be a lot better. I could send you a copy to see what i mean if you want.
Thank you so much for this. Have you got Company / Sole trader for Tax year 2015/2016 (From 6th April 2015 to 5th April 2016) Kind Regards,
Hi On the most recent PAYE spreadsheet when I try and put in "Plan1" for student loan it comes out with an error. Can you help?
i have made a more complex and dynamic tax model for UK individual tax liability, which incorporate all the clauses and guidelines as required in ACCA paper F6 Taxation (UK), i can share if you want...
I want to thank you so much for these spreadsheets Paul - you have helped me so uch as we have transitioned back to the UK I wanted to ask if you have considered drawng up a student loan calcuator. I have just built a very simple sheet to illustrate to my daughter what is going to happen to her significant loan. The websites are woefully silent on this calculator. Everyone tells me I am wrong to think of it as a debt - but RPI +3% - RPI at best, I am afraid for those ignoring the truth ! Anyway, happy to send what I have but I think your are brilliant ! Please feel free to ontact me Finally - I would be really gratefu if you could update then earier in the year (accepting things can change!) as I use it to manage pension contributions Thank you
Fantastic spreadsheet, saved me making my own! Thanks for your help, I've made a donation.
Great Stuff, sheets calculations are all in line with the respective years. Love the the latest 2016/2017 spreadsheet and the Monthly PAYE spreadsheet. Just a quick request, is there a setup that would allow you to do bi-weekly calculations similar to the Monthly PAYE spreadsheet? instead of monthly running breakdowns a weekly and 2 week option at all?
I've donated. Any chance you could update the Company/Sole Trader sheets for 2016/2017 as well?
DO you have an excel spreadsheet to calculate dividend tax payable for 2016/17 under the new rules? I would be happy to pay something for this.
HI I wonder would, you release one that allows me to put in quarterly bonuses as earned to show the PAYE tax implications of the sudden increase on that months salary to understand how the TAX will right it self over the year
Is it just me or is there an issue with the student loan box on the spreadsheet in the 16/17 PAYE calculator? Great help these sheets though
Any news on the Dividend calculator? Happy to pay for it if you need a wee bit of funding.
Thanks for the spreadsheet - it will help me stick it to the taxman for another year. Donation sent
It is really nice of sharing this valuable spreadsheet, highly appreciated
Hi, great tool and will be making a donation today - in the next version could we have a box to input 'deductions after tax' so that you can get a true match to what's on your paylsip?
Do you have the most recent year available as a monthly calculation?
Brilliant, I was really scratching my head about how to build something like this, but you've done it for us. Thanks.
Brilliant work - very well played. I also second a reqeust for Interest income Dividend income Other income e.g. Rental income.
Hi great spreadsheet however just a note - for future editions, the student loan is calculated monthly as a rounded down figure then multipled by 12 to get the yearly deduction which is slightly different to the total salary above the threshold * the rate
Paul, excellent work, would love to see a spreadsheet for 2015/2016 as the current one helped me and helped a few Aussie friends over working in NHS. Hope all is well Charles
This is a very useful tool, but I can't seem to get the exact tax deduction that is applied by the company without adjusting my given tax code and even then its never the same twice, also my NI is never the same, its £10 to £20 + or- each month, am I missing something or is this normal Thanks Dan
Hi, spreadsheet looks easy to use. Couldn't use it though unless i overlooked a few bits. A couple of points: Would be more usable if it had a section for interest from savings ( where you can input details) Also, a section or lines for Income from other sources ( property,rent)
Can the PAYE spreadsheet be modified to include car allowance calculations as well as other benefits such as fuel through a fuel card?
Great work - thank you! Would be good to add a mixed income spreadsheet, i.e. Salary plus Dividend plus Investment Income.
Thank you for a wonderful tool. Small donation made. Wish it could be more.
Great, very useful, donation sent.
Hi Paul, This is great! Thank you so much. Will you be releasing a Company / Sole trader version for 2015/2016? I so I'll for sure donate! Phil
This is brilliant Paul thank you. I was just about to try and create this myself on my days off work - you have just bough me more holiday, I love you If you ever want too add a box to add into monthly commission (no idea how this would work) i would love you even more Thanks again
Hi, can you please include childcare and also car allowance calculations, so i can work out exact tax implications.
I have rental income + bank interest as well as PAYE. Great if you could include this in the calculation
SuperMan..Life saver..Appreciate your effort..Perfect and simple..
Fantastic calculator, I've just used it to determine whether to accept a job at a different employer. Have you considered adding the ability to include expenses and benefits from a P11D in your calculator? Great work!
Bloody fantastic, well done sir, this is a truly helpful tool! If you see this please could you tell me when the next update will be available? For tax yeayear 2015/2016 Thanks.
Thanks for these. They will be very useful so I have donated. I have 1 question. On the Company/Sole trader Excel spreadsheet what is the meaning of the value in C81 on the UK Dividend worksheet? Should this be included in the top section? Otherwise I seem to pay no NI at all Richard
Thanks Paul for the excellent UK PAYE sheet. It will help me immensely with my ongoing battle with HMRC. You should hire yourself out to them as it seems that most of their "advisors" don't understand their own rules. Top tip everyone - check everything HMRC tell you!
Thanks, this was very useful and saved me a lot of time trying to build one myself! Thanks! Ali
The ability to show childcare vouchers which are a tax free deductible would be helpful.
Excellent work... have you ever seen anywhere that you can add monthly figures (i.e if someone does Overtime, Oncall) that vary monthly, annual Bonus and private tax relief deductions?
These are extraordinarily useful spreadsheets with accurate calculations - thank you for this, it makes self assessment a complete breeze. Much appreciated indeed.
This has been a great help today, can't thank you enough for doing this and sharing
Great job on this spreadsheet. May I know when the 14/15 would be out? Thanks!
Super job thank you. Very useful. It seems that the company/sole trader sheet is slightly more difficult to use than the personal tax sheet because of the numerous cross references. Just a suggestion - can you perhaps highlight more clearly the boxes where the user can input their data. Alternatively just include some instructions on which boxes to edit. Thanks again.
Many thanks, the only problem is I earn commission that varies from month to month. Is there any way this could be included so I could add my commission value in manually and calculate tax, NI etc...?
I have a toolbar which has a lot of links to all the best sites, I would like to add your site if you don't mind. the excel sheet is great and a lot of the office staff that use my toolbar would make great use of the sheet you have designed. John
Superb tool, many thanks. As someone else has requested, any chance of a tax code option. and or an BIK allowance cell to help balance etc... I've donated too and an incentive to add the above element. Cheers
A couple of people pointed out the taper deduction error. I believe this can be rectified by changing cell C22 to: =IF((C3-G23)<0,0,IF(((C3-C13)-G23)/2>C20,C20,((C3-C13)-G23)/2)) Can somebody with a few more expertise verify this please? :) PS love this spreadsheet, thanks for putting it together!
Nice work. When is the 2013/2014 will be available? Thank you.
Love the sheet but the taper deduction is incorrect. See http://www.hmrc.gov.uk/incometax/adjusted-net-income.pdf for details.
This sheet is wrong. it does not calculate the tapper of personal allowance correctly. The formula is based on Gross Earnings, but should be net of Pension.
This calculator is great.
Great workbook. Would be useful if you could enter your individual tax code too.
thank you very much for this - very useful and thank you for making it availabe to everyone. if i use it a lot i will of course contribute.
Thank you very much. Awesome. I have been looking for this for a long time. Thanks to Google as well for directing to your portal.
Great work. Any chance of adding in a bit for dividend income on shares?
Absolutely brilliant, thanks for sharing... cheers!
Brilliant - thanks!
A very useful and easy to use spreadsheet but there is no mention of married couples allowances
Hi Paul- really good calculator, thanks. Do you by any chance have one for pension incioome (i.e. without NI) Many thanks
@Glen, See above.
Hi Pual did you get around to doing a monthly version of the spreadsheet as asked in post 17?
Andrew, this is now in the 2011 and 2012 versions. You just need to change the age field. Let me know if that works for you!
Will you be able to add a field for over 65s and over 75s to take account of their increased allowances?
Many thanks for this and for making it available for free. Suggestions for future enhancements - Interest income Dividend income Other income e.g. Rental income.
Is there any way this could be updated to specify an age, pension contributions, blindness and student loans? I've tried to do it myself but it is a bit confusing!!
Remember to add pension contributions (and other allowable expenses) to the allowances (RHS of the sheet). Though please correct me if I'm wrong.
thanks for the old years. I tried HMRC software and it does not allow old years.
Thank you so much for this. I was getting really frustrated attempting just half of what you've given us!
Really useful calculator. It would be good to see the basis of the calculations as cell comments. Thanks for posting FOC
Really helpful, thank you.
Thanks, that's awesome! My only suggestion is to amend the sheet so you can put in multiple gross salaries so you can do a quick comparisons. I've cheated and used a macro to provide the net annual/monthly/weekly salaries for all salaries from 50k to 150k in increments of 5k. Wilkes
Thanks. Really helpful. Appreciate the time you have put into this and for making it available.
Many thanks for sharing your spreadsheet.
Just wanted to say "thanks" - very useful to crosscheck my tax deduction...i had a feeling i was paying more....
Googled and came here - thank you. And I have added a couple of lines which enable me to enter gross salary then the pension which it take out as a salary sacrifice before tax. I had not quite realised how much tax that was saving me!
Ideal - Just what I wanted. I was just about to make one, but you've done it much better ! Have you considered combining historical years onto seperate sheets in the same book ? Could even have a year by year summary sheet ! I don't suppose you have any previous years ? Thanks.
Getting my annual updated version! Thanks yet again for such a helpful tool
Is it possible for you to make a monthly tax calc so I can add in bonus's and overtime pay etc?
Just wanted to say "thanks" for the 2011/2012 Tax Calculation - shows me how much worse off I'll be in 2011/12 though!
Excel version 2011/2012 Thanks great but will you be updating it with maybe a Overtime column or row most grateful
Started to prepare a spreadsheet myself but then realised that it isn't as straightforward as you first think. Then thought, someone must have done this already and a quick Google search reveals that you have!!! :-) Thanks very much for sharing your hard work and great to see the OpenOffice version.
Back again for 2011-2012 version. Thank you very much!
Thanks!! :) Very good sheet...
Thanks so much for sharing your tax spreadsheets! You are an absolute star and saved me a ton of time!!
Great work! Useful for future budgeting... :o)
Many thanks! Excellent work. Digging the openoffice version. Hate excel. Thanks again!
The PAYE calculator is great. I do hope you are planning to update for 2011-2012!
excellent stuff keep up the good work!
Thank you very much for sharing this, goes well with my home made spreadsheet for calculating income and expenses. Great the see an OpenOffice Version too.
Thanks for putting this together and allowing others to use it for no charge. It could be very poplular with the PAYE tax underpayments/overpayments suggested by HMRC.
Thanks for this! It's really helpful!
Leave a comment