DavidOverton.com
This site is my way to share my views and general business and IT information with you about Microsoft, IT solutions for ISVs, technologists and businesses, large and small.  
UK 2024/25 Excel formulas to work out take home, NI and income tax.

I know most people don't need this sort of information, but since I just entered it into a spreadsheet and found the information wrong in Co-Pilot, I thought I'd share the correct information.

In the UK, NI rates can be found here and income tax rates can be found here.

The formula to calculate NI is as follows.  I've used the LET function to make it easier to read. This presumes the annual salary is in cell A1. If you want a monthly figure, you will need to divide PT and UEL by 12 (directly in the formula eg “12570/12”)


=LET(
    salary, A1,
    PT, 12570,
    UEL, 50270,
    rate1, 0.08,
    rate2, 0.02,
    IF(salary <= PT, 0,
        IF(salary <= UEL, (salary - PT) * rate1,
            (UEL - PT) * rate1 + (salary - UEL) * rate2
        )
    )
)

The UK Tax calculation would look like this. It has a lot of variables.


=LET(
    salary, A1,
    pension_rate, 5%,  
    pension_contrib, salary * pension_rate,
    adjusted_salary, salary - pension_contrib,
    base_PA, 12570,
    BR, 50270,
    HR, 125140,
    rate_basic, 20%,
    rate_higher, 40%,
    rate_additional, 45%,
    reduced_PA, MAX(0, base_PA - MAX(0, (adjusted_salary - 100000) / 2)),
    taxable_income, MAX(0, adjusted_salary - reduced_PA),
    basic_tax, MIN(taxable_income, BR - reduced_PA) * rate_basic,
    higher_tax, MAX(0, MIN(taxable_income - (BR - reduced_PA), HR - BR) * rate_higher),
    additional_tax, MAX(0, taxable_income - HR) * rate_additional,
    basic_tax + higher_tax + additional_tax
)

I hope these are of interest if you needed them.

To calculate your take home, it is  salary – NI – tax – salary*pension_rate, which would look like this if all entered into a single formula, although you could replace the whole of the NI and tax calculations with a cell reference if you already have used the formulas above.


=LET(
    salary, A1,
    pension_rate, 5%,

    tax, LET(
        pension_contrib, salary * pension_rate,
        adjusted_salary, salary - pension_contrib,
        base_PA, 12570,
        BR, 50270,
        HR, 125140,
        rate_basic, 20%,
        rate_higher, 40%,
        rate_additional, 45%,
        reduced_PA, MAX(0, base_PA - MAX(0, (adjusted_salary - 100000) / 2)),
        taxable_income, MAX(0, adjusted_salary - reduced_PA),
        basic_tax, MIN(taxable_income, BR - reduced_PA) * rate_basic,
        higher_tax, MAX(0, MIN(taxable_income - (BR - reduced_PA), HR - BR) * rate_higher),
        additional_tax, MAX(0, taxable_income - HR) * rate_additional,
        basic_tax + higher_tax + additional_tax
    ),

    ni, LET(
        PT, 12570,
        UEL, 50270,
        rate1, 8%,
        rate2, 2%,
        IF(salary <= PT, 0,
            IF(salary <= UEL, (salary - PT) * rate1,
                (UEL - PT) * rate1 + (salary - UEL) * rate2
            )
        )
    ),

    take_home, salary - ni - tax - salary * pension_rate,
    take_home
)


Posted Thu, Aug 29 2024 10:55 AM by David Overton
Filed under: ,

Add a Comment

(required)
(optional)
(required)  
Remember Me?

(c)David Overton 2006-23