Excel Formula for UK tax system ??

Thorndorise

Jumping in here, admittedly without reading every post in this thread...but would something like this be useful?

Cheers

double_dutchy

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)))

In Excel 365 this formula will give you the same result

=LET(LB,{0,12570,50270,125140},SUM((A1>LB)*(A1-LB)*{0,20,20,5}%))

The {0,20,20,5}% part is the % differences between each band, i.e. 20% difference between 0% and 20%, 20% difference between 20% and 40% etc.

Your "PA reduction" formula doesn't get the correct results, I think. Don't forget that when the personal allowance threshold is reduced you are also effectively lowering the 40% threshold. Your formula for "PA reduction" gives £37,432 tax on £120,000 earnings when the correct amount should be £39,432 as confirmed by various online calculators.

This formula will give you correct results in Excel

=LET(LB,{0,12570,50270,125140},PA,MEDIAN(0,(A1-100000)/2,12570),LBB,IF(LB-PA>100000,LB,LB-PA),SUM((A1>LBB)*(A1-LBB)*{0,20,20,5}%))

Dazed_and_C0nfused

double_dutchy said:

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)))

In Excel 365 this formula will give you the same result

=LET(LB,{0,2570,40270,115140},SUM((A1>LB)*(A1-LB)*{0,20,20,5}%))

The {0,20,20,5}% part is the % differences between each band, i.e. 20% difference between 0% and 20%, 20% difference between 20% and 40% etc.

Your "PA reduction" formula doesn't get the correct results, I think. Don't forget that when the personal allowance threshold is reduced you are also effectively lowering the 40% threshold. Your formula for "PA reduction" gives £37,432 tax on £120,000 earnings when the correct amount should be £39,432 as confirmed by various online calculators.

This formula will give you correct results in Excel

=LET(LB,{0,12570,50270,125140},PA,MEDIAN(0,(A1-100000)/2,12570),LBB,IF(LB-PA>100000,LB,LB-PA),SUM((A1>LBB)*(A1-LBB)*{0,20,20,5}%))

That is beyond my limited excel formula knowledge but is it calculating the Personal Allowance based on the income?

When it needs to be adjusted net income.

Nomunnofun1

Thorndorise said:

Jumping in here, admittedly without reading every post in this thread...but would something like this be useful?

Cheers

So - you have deducted the personal allowance from the income of £51750 to arrive at £39180. 

You have then deducted the personal allowance again to arrive at £26610 taxable. 

I can see a flaw or am I missing something?

MeteredOut

Here's a function I wrote a while back based on a combination of various bits I found on the web. I didn't really use it in the end as i use a different way to model my retirement plan, so I can't say its fully tested, but it might help someone.

It's based on Scottish tax rates (but anyone who understands it should be able to amend it to make it work for the simpler bands used elsewhere) and also provide the facility to apply an inflation effect to the calculations. 

Note, as per above conversations, it does not cover all scenarios/circumstances.

Function calculateTax(taxableIncome As Double, _
    Optional rateYearOffset As Integer = 0, Optional rateYearFactor As Double = 0.02, _
    Optional personalAllowance As Double = 12570, _
    Optional starterRate As Double = 15398, _
    Optional basicRate As Double = 27492, _
    Optional intermediateRate As Double = 43663, _
    Optional higherRate As Double = 75001, _
    Optional advancedRate As Double = 125141)

    If rateYearOffset > 0 Then
        personalAllowance = personalAllowance * (1 + rateYearFactor) ^ rateYearOffset
        starterRate = starterRate * (1 + rateYearFactor) ^ rateYearOffset
        basicRate = basicRate * (1 + rateYearFactor) ^ rateYearOffset
        intermediateRate = intermediateRate * (1 + rateYearFactor) ^ rateYearOffset
        higherRate = higherRate * (1 + rateYearFactor) ^ rateYearOffset
        advancedRate = advancedRate * (1 + rateYearFactor) ^ rateYearOffset
    End If
 
    calculateTax = WorksheetFunction.Max(WorksheetFunction.Max(taxableIncome - advancedRate, 0) * 0.48 + _
                        WorksheetFunction.Min(WorksheetFunction.Max(taxableIncome - higherRate, 0), advancedRate - higherRate) * 0.45 + _
                        WorksheetFunction.Min(WorksheetFunction.Max(taxableIncome - intermediateRate, 0), higherRate - intermediateRate) * 0.42 + _
                        WorksheetFunction.Min(WorksheetFunction.Max(taxableIncome - basicRate, 0), intermediateRate - basicRate) * 0.21 + _
                        WorksheetFunction.Min(WorksheetFunction.Max(taxableIncome - starterRate, 0), basicRate - starterRate) * 0.2 + _
                        WorksheetFunction.Min(taxableIncome - personalAllowance, starterRate - personalAllowance) * 0.19 + _
                        WorksheetFunction.Max((WorksheetFunction.Min(taxableIncome, 100000 + (personalAllowance * 2)) - 100000) / 2, 0) * 0.42, 0)
  
End Function

Thorndorise

Nomunnofun1 said:

Thorndorise said:

Jumping in here, admittedly without reading every post in this thread...but would something like this be useful?

Cheers

So - you have deducted the personal allowance from the income of £51750 to arrive at £39180. 

You have then deducted the personal allowance again to arrive at £26610 taxable. 

I can see a flaw or am I missing something?

Nope, just a typo on my behalf, it was a first try - just needs that PA being zeroed out (in fact it's in there for negative PA), good shout