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
245

Comments

  • chrisbur
    chrisbur Posts: 4,248 Forumite
    Part of the Furniture 1,000 Posts Photogenic Name Dropper
    edited 6 June at 4:19PM
    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.


  • eskbanker
    eskbanker Posts: 36,987 Forumite
    Part of the Furniture 10,000 Posts Name Dropper Photogenic
    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!
  • MetaPhysical
    MetaPhysical Posts: 449 Forumite
    100 Posts First Anniversary Photogenic Name Dropper
    edited 6 June at 5:06PM
    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.
  • Bookworm225
    Bookworm225 Posts: 393 Forumite
    100 Posts Name Dropper

    I know how to work it out manually, I was seeing if anyone had the excel formula because it's quite complex.
    yes it would be, hence people do not do it via spsheet
  • MetaPhysical
    MetaPhysical Posts: 449 Forumite
    100 Posts First Anniversary Photogenic Name Dropper
    I'll develop a way to do it with emdedded Python script.
  • eskbanker
    eskbanker Posts: 36,987 Forumite
    Part of the Furniture 10,000 Posts Name Dropper Photogenic
    I'll develop a way to do it with emdedded Python script.
    Enter gross amount: £x

    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]

    Here's the net figure: £y
              
  • MetaPhysical
    MetaPhysical Posts: 449 Forumite
    100 Posts First Anniversary Photogenic Name Dropper
    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)))
    
    
  • MetaPhysical
    MetaPhysical Posts: 449 Forumite
    100 Posts First Anniversary Photogenic Name Dropper
    And some screenshots:


  • eskbanker
    eskbanker Posts: 36,987 Forumite
    Part of the Furniture 10,000 Posts Name Dropper Photogenic
    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)))
    
    
    It works perfectly in certain circumstances - if they're the ones you're interested in then great, but it wouldn't recognise, for example, the loss of personal allowance if earning the £150K in your original scenario.
  • MetaPhysical
    MetaPhysical Posts: 449 Forumite
    100 Posts First Anniversary Photogenic Name Dropper
    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)))

Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

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

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.