We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
We're aware that some users are experiencing technical issues which the team are working to resolve. See the Community Noticeboard for more info. Thank you for your patience.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!
Excel Formula for UK tax system ??
Options
Comments
-
There are, as has been said, many tax calculators available; however all but one of them that I have seen make assumptions and so while being often about correct sometimes they are way off.
The one that uses exactly the same system as PAYE is here....
https://www.gov.uk/guidance/work-out-an-employees-income-tax
This one takes account of the way PAYE calculates each payday the tax due on taxable earnings to date up to that point in the tax year, and from that deducts tax paid so far this tax year to get a tax due on that payday.
0 -
One other thing I'd add is that if wanting to work it out in Excel, it's probably overambitious to try to encapsulate it all in a single formula spitting out one number - I have knocked up relatively simplistic ones (not of a sharable quality!) that mirror the HMRC presentation, e.g. one row per band with the top and bottom values and the rate in between, to then work out how much tax is chargeable within each. Apart from anything else, this makes it easier to reconcile with HMRC's version of events!0
-
Hi, I don't want to use an online tax calculator. I want to embed the equation for working out the tax due on a given sum in my own spreadsheets hence me asking if anyone has the equation/formula to hand. No I haven't been given money - I just invented that sum to demonstrate an example of covering all the tax bands in the UK tax system,.
I know how to work it out manually, I was seeing if anyone had the excel formula because it's quite complex.1 -
MetaPhysical said:
I know how to work it out manually, I was seeing if anyone had the excel formula because it's quite complex.0 -
I'll develop a way to do it with emdedded Python script.0
-
MetaPhysical said:I'll develop a way to do it with emdedded Python script.Enter gross amount: £xHere's the net figure: £y
A customer enters a pet shop.
Mr. Praline: 'Ello, I wish to register a complaint.
(The owner does not respond.)
Mr. Praline: 'Ello, Miss?
Owner: What do you mean "miss"?
Mr. Praline: (pause)I'm sorry, I have a cold. I wish to make a complaint!
Owner: We're closin' for lunch.
Mr. Praline: Never mind that, my lad. I wish to complain about this parrot what I purchased not half an hour ago from this very boutique.
Owner: Oh yes, the, uh, the Norwegian Blue...What's,uh...What's wrong with it?
Mr. Praline: I'll tell you what's wrong with it, my lad. 'E's dead, that's what's wrong with it!
Owner: No, no, 'e's uh,...he's resting.
[etc]
4 -
Here you go. I asked ChatGPT for the script.
A1 is the cell holding the gross amount to be taxed. It works perfectly.=IF(A1<=12570, 0, IF(A1<=50270, (A1-12570)*0.2, IF(A1<=125140, (50270-12570)*0.2 + (A1-50270)*0.4, (50270-12570)*0.2 + (125140-50270)*0.4 + (A1-125140)*0.45)))
1 -
And some screenshots:
0 -
MetaPhysical said:Here you go. I asked ChatGPT for the script.
A1 is the cell holding the gross amount to be taxed. It works perfectly.=IF(A1<=12570, 0, IF(A1<=50270, (A1-12570)*0.2, IF(A1<=125140, (50270-12570)*0.2 + (A1-50270)*0.4, (50270-12570)*0.2 + (125140-50270)*0.4 + (A1-125140)*0.45)))
0 -
The formula to take account of the loss of the PA would be:=IF(A1<=12570, 0,IF(A1<=50270, (A1 - MAX(0, 12570 - MAX(0, (A1 - 100000) / 2))) * 0.2,IF(A1<=125140, (50270 - MAX(0, 12570 - MAX(0, (A1 - 100000) / 2))) * 0.2 + (A1 - 50270) * 0.4,(50270 - MAX(0, 12570 - MAX(0, (A1 - 100000) / 2))) * 0.2 + (125140 - 50270) * 0.4 + (A1 - 125140) * 0.45)))0
Confirm your email address to Create Threads and Reply

Categories
- All Categories
- 350.8K Banking & Borrowing
- 253.1K Reduce Debt & Boost Income
- 453.5K Spending & Discounts
- 243.8K Work, Benefits & Business
- 598.7K Mortgages, Homes & Bills
- 176.8K Life & Family
- 257.1K Travel & Transport
- 1.5M Hobbies & Leisure
- 16.1K Discuss & Feedback
- 37.6K Read-Only Boards