Save content
Have you found this content useful? Use the button above to save it to your profile.
Tax data
iStock_Tax binary_fatido

HMRC Excel tax calculator signals policy weak spot


Knowing when to use the right tool for the job is one of the fundamental principles of data science. What a pity that HMRC failed to apply that rule to its tax calculators.

12th Aug 2021
Save content
Have you found this content useful? Use the button above to save it to your profile.

This week we recounted the sorry tale of erroneous P800 tax calculations for PAYE taxpayers going back several years. On closer examination, it emerged that HMRC has at least two internal tax calculators operated by different teams - PAYE and self assessment. With MTD looming in the background, there could even be more.

After a long-running campaign by Absolute Software director and tax lecturer Tim Good to ensure the HMRC specification applies the most favourable sequence of savings and dividend allowances for the taxpayer, the current version of the department’s 2020-21 self assessment calculator finally accommodated more flexible sequencing of the allowances.

But a comparison of this logic to the results presented in a sample of P800 calculations indicated that PAYE taxpayers were not being treated in the same way. When pressed, HMRC confirmed that the functionality of the two mechanisms was different, with the result that some taxpayers have been overpaying between £10-300 pounds in tax on calculations carried out since new savings and dividend allowances were introduced in 2016-17.

The original discrepancies came to light because HMRC circulates a massive (17-sheet) Excel workbook to software developers to help them compare the results they get for different test cases to the calculation carried out by HMRC’s mainframe system, which is programmed by following the same specification.

Cherchez le spreadsheet

Did someone mention a spreadsheet? The UK government has a long an ignominious track record of spreadsheet bungles going back to technology procurement errors (2006), the botched bidding process for the West Coast Main Line rail franchise contract (2012) and last year’s debacle over missing Covid-19 test results at Public Health England.

Surely HMRC and other departments would have learned from these episodes not to rely on an unwieldy and potentially error-prone tool to store its database of rates, reliefs and logical sequences that go into the vastly complex self assessment computation?

Actually, they don’t. The starting point for self assessment tax calcuations is a paper document (or Word file, to be precise) called the tax and NI specification document, explained David Forbes of Forbes Computer.

The spec interprets the conditions of tax law each year and sets out in a step-by-step sequence how tax should be calculated. “It’s devilishly complicated and there can be mistakes,” he said.

This includes someone writing in a default sequence for applying personal allowances (non-savings income-savings income-dividends) for the 2016-17 changes, even though the results could negate the legal requirement to allow the most favourable treatment for the taxpayer, as Good highlighted back in 2017.

The spreadsheet is produced as a by-product of that specification document, Forbes continued.

“It’s used more for testing scenarios than actually creating the code. Our software, the spreadsheet and the mainframe will generally come up with the same answer – and all make the same mistake if the specification is wrong.

 “We rely on Tim Good and our customers to say if the calculation is wrong, but it’s getting more and more complex every year.”

Version control

Software developers have numerous options to store and tweak their code, such as program libraries and databases. But in this instance, the civil servants responsible for the code cling to the tools they’re comfortable with, without appreciating the risks involved.

First, as Forbes noted, minor typos can creep into the spec or the spreadsheet to corrupt the calculation. These mistakes can be extremely difficult to spot and correct. It took five years and a loss at the first tier tax tribunal for HMRC to correct the personal allowance oversight.

The second flaw in this setup is our old friend version control. Information scientists have an acronym for this, SSOT, which stands for a single source of the truth. To borrow from Wikipedia, this involves creating “information models and associated data schema such that every data element is mastered in only one place. Any possible linkages to this data element are by reference only.”

The HMRC NI and self assessment tax calculator may now match the software specification. But all it takes is a tweak here or there for them to diverge. If another team does not have access to the definitive version of that logic, Murphy’s law dictates that something will go wrong. As we saw this week.

At least the spreadsheet checker gives people like Tim Good an opportunity to spot potential errors. As HMRC automates tax and takes it online, however, the calculations will be worked out centrally and fed back to taxpayers and agents via application programming interfaces (APIs) to their tax reporting software.

Under these conditions, how will taxpayers and their advisers  verify that they actually are paying the right amount of tax?

If nothing else, we hope this episode will prompt a review of HMRC’s tax calculation data strategy to build in better oversight, assurance and integrity into its systems. And perhaps a final farewell to Excel and Word as bastions of our national finances.

Replies (7)

Please login or register to join the discussion.

By Paul Crowley
14th Aug 2021 16:43

HMRC and IT or data technology
Do not have a clue how to operate it
Never will until they talk to accountants
Pigs will fly before HMRC think accountants are worthy of consultation

Did not used to be like that
HMRC used, in the old days, to consult

Thanks (2)
By Paul Crowley
14th Aug 2021 16:45

Date for Aweb live reruns still not shown
No idea how old any of this stuff is

Thanks (0)
By Tornado
16th Aug 2021 10:46

Perhaps the Government could consider simplifying tax. I am pretty sure that they have no idea as to the real consequences of their tinkering with the tax system and each new tinker adds to the confusion.

With MTD on the way it seems to me that we may have honour of having the most digitally advanced tax system in world that does not work.

Thanks (2)
Head of woman
By Rebecca Cave
17th Aug 2021 09:23

Its an absolute scandal that there is no independant checking of the word document which contains the tax and NI specification.
We need an independant Tax Software Standards body to check that the spec is in line with tax law, and that all the spreadsheets that software designers rely on also comply with tax law.
There have been numerous examples of where the tax software does not produce the legally correct answer, eg top slicing releif, losses set-off, the long list of exclusions from onlien filing for SA returns.
When MTD ITSA is mandated individuals and businesses will not be able to see how the tax is calculated, and this process will be entirely based on possibly faulty specifications. People will be taxed by a computer programmer and not by the law.

Thanks (1)
Replying to Rebecca Cave:
By Paul Crowley
23rd Aug 2021 12:04

Agree entirely
HMRC cannot by computer deal with best ordering
Needs a human to judge or less tinkering

Thanks (0)
By Kate Upcraft
18th Aug 2021 15:05

Another one we’ve spotted is that the tool to tell employers what tax code to use was wrong from 6th April to 25th May as it says codes from P45s with dates of leaving last year didn’t have to be updated this year, but they did as the PA went up. Last year of course they didn’t and whoever has ‘updated’ the tool didn’t realise last year was a bit unusual. As has been said if only they involved users in testing….

Thanks (2)
By Ben Alligin
20th Aug 2021 15:41

Here is another HMRC error to add to the list.

We all know that accountants were not to be trusted/consulted with SEISS grant applications, but as we have now discovered, we are responsible for policing them (ok the taxpayer signs the tax return, but essentially it falls to us).

I filed a 2020/21 tax return in May with HMRC for a client who had claimed 3 SEISS grants. Today I received a revised tax calculation from HMRC. They had unilaterally deducted the first SEISS grant since it had not been paid according to their system.

Wrong; client was previously registered with PD1 which proved a big enough headache for claiming the SEISS grant in the first place, because HMRC software didn't recognise that he existed due to the special status of PD1. Well we got over that first hurdle in getting the grant awarded, only to fall at the second since HMRC main body are now correcting his submitted tax return and don't have access to the full details.

Yet more time wasted sorting out their mistakes.

Thanks (1)