I'm doing the monthly version of this, because that's what I have source data to compare/validate against. I haven't examined what happens in the weekly/other version, thought the links have the data, so you could repeat my analysis.
Punchline is "it's f---ing horrible" ??
This is the Excel formula I've derived, to go from "the stated Personal Allowance, published by HMRC" to "the actual amount of pay that won't be taxed in PAYE calculations":
= IF([PersonalAllowance]=0, 0,
QUOTIENT([PersonalAllowance],5000) * (ROUNDUP(5000/12,2)*12)
+ ROUNDUP((MOD([PersonalAllowance],5000)+9)/12,2) * 12
Source for this calculation comes from: https://www.gov.uk/government/publications/tables-a-pay-adjustment-tables
I'll use the following terms/acronyms:
- NPA "Nominal Personal Allowance"
- This is the headline figure that HMRC publishes
- It's tied to your TaxCode - your tax code is your NPA divided by 10, plus a Letter Code signifying ... some stuff.
- For the mainline case, in England, in Tax Year 2022/23, this is value is £12,570 (link only valid during this tax year), which is why the default TaxCode is 1257L.
- APA "Actual Personal allowance".
- This is the number that I'm attempting to calculate.
- This is the amount of your income that is ignored, when calculating your Annual Tax for PAYE purposes.
- HMRC documentation appears to refer to this as you "Pay Adjustment"
Obviously in an ideal world we would have NPA == APA ... but if that were the case, this question wouldn't exist.
HMRC documents exactly how they derive APA from your TaxCode here:
- Doc 1: the first PDF document "Tables A: Payment adjustment tables"
- which provided instructions on how to use the tables, and the values in the tables themselves.
- Notably "How to work out free pay" on Page 3, and the "Month 12" table on Page 68.
- Doc 2: the 2nd Excel sheet "Tables A: Monthly pay adjustment", which has 1 Tab per month and (if you unhide some columns) exposes the formula that's used to populate the table.
- Notably the "month 12" tab.
There are 3 main factors that cause the discrepancy between APA and NPA:
- At one point the process takes your NPA and adds 9 to it, to reflect the fact that NPA is measured to the nearest 10 (presumably to store it as a 4 digit Tax Code)
- i.e. because the basic TaxCode is "1257" it treats it as "<£12,580" i.e. "£12,579"
- The process has some rounding at a monthly level baked into it. i.e. your allowance for a month gets calculated, rounded up to the next penny, and then you get 12 times that (so the rounding gets substantially compounded)
- The process is based around using lookup tables which only go up to 500 (= £5,000), so for any TaxCode value over 500, it has a complex (and slightly self-inconsistent) process for how to calculate that.
- It's even more weird if you're at an exact multiple of 500. ?????,??
Reproduce the Manual Lookup Process
So ... here's the process as documented in the "Doc 1" (rephrased for terseness). We're looking at Pages 3 and 68.
- Locate the correct table. [Ed: In our case Month 12, for EOY]
- Find the numeric portion of TaxCode in the 'Code' columns. The amount of free pay is shown to the right of the code
- If the code >= 501 ...
- Divide the code into units of 500 and note the number of
units and the amount left over – for example, for
code 1567 the number of units of 500 is 3 and the
remainder is 67
- Look up the amount of pay adjustment from the
main look-up table for the remainder (67 in this example).
- Multiply the figure given in the box marked
* at the
bottom of the page by the number of units of 500.
- Add these two amounts together.
For 2022/23, for TaxCode 1257L, this algorithm gives:
Lookup(257) + 2*SpecialLookupForUnitsOf500()
(Note that the units-of-500 value that gets multiplied is different from the Lookup value for exactly 500!) ?????
Doing the lookups for those figures then gives:
2579.04 + 2 * 5000.04 = 12579.12
Thus you can use these tables to lookup your APA.
Derive a first-principles calculation
However ... since HMRC also publishes the Excel sheet which is used to generate the lookup tables, we can actually see where the values in the lookup table come from, and thus arrive at the logic necessary to calculate APA directly from NPA!
So, examining Doc 2...
WARNING: There's actually a bug in the sheet as currently published. ?????
In cell X65 of sheet 'Month 12' it has value
0, and formula
='Month 12'!G2. It should have formula
=416.67*12 (consistent with other tabs) and thus value
5000.04 (consistent with page 68 of the PDF tables). If anyone knows how to contact HMRC to tell them, that'd be great!
Main Lookup table
We find that the main body of the lookup table consists of the following 2 steps of Excel Formula:
A9 is the initial Code being looked-up, and B9, is the address of the first formula.
It would seem that the steps of the logic are:
- Add 9. (To account for the £10-wide bands?)
- Divide by 12. (To account for monthly calculation?)
- Round UP to the next penny. (Presumably because exactly numbers are essential for lookup-tables.)
- Multiply by 12. (To map back to the EndOfYear)
Though also note that for TaxCode "0", the value is set to 'Nil' i.e. you don't get to add the "+9".
Units of 500
SpecialLookupForUnitsOf500 the logic is different (and simpler ... but still arrives at a different value from the 500 in the main lookup table)
That logic in each Monthly tab appears to just be
MOD to automate the splitting of the code into units of 500, would suggest that the final calculation to get from NPA to APA should be the following monstrosity:
= QUOTIENT([NPA],5000) * (ROUNDUP(5000/12,2)*12)
+ ROUNDUP((MOD([NPA],5000)+9)/12,2) * 12
QUOTIENT is the integer division function
Handling exact multiples of 500
There are special rules about exactly how this is done, which at first seemed very pointless ... but eventually I realised that they only exist to handle the discrepancy around "0", and when the +9 gets added.
The lookup value for 0 is just 0, you don't get the +9.
For, say, 367, you get the +9.
If your taxCode is exactly 500, then they do give you the +9.
If you taxCode is 501, then you get the +9 ... but only once!
Which means that the "500 unit" figure needs to not include the +9.
That's fine in general, but it would mean that if you interpretted 1000 as 2 * 500-unit + <Lookup for 0>, then you wouldn't be getting any +9 ... which would be wrong
The document solves this by declaring that, eg. 1500 is 2 * 500-unit + 1 * 500-lookup, thus you get the +9 once.
This could be achieved with a bunch of faffing around in the formula, however, it would be much easier to just say "0 in the lookup does include the +9 ... as long as NPA isn't == 0.
That's essentially what the formula above does, so we just need to special case "NPA == 0" and we're done:
= IF([NPA]=0, 0,
QUOTIENT([NPA],5000) * (ROUNDUP(5000/12,2)*12)
+ ROUNDUP((MOD([NPA],5000)+9)/12,2) * 12
I've tested this against the last 4 years of my personal payslips, and I was able to cross reference this against the HMRC PAYE tax calculator.
I may come back and provide some sample inputs later... it wasn't completely intuitive how to get it to test clear cases.