HMRC Excel tax calculator signals policy weak spotby
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.
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.”
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.
You might also be interested in
AccountingWEB’s Editor at large has been with the site since 1999, rising from news editor to editor in chief, global editor and head of insight. As a roving editor, he continues to investigate the profession's use of technology around the world. He devotes his spare time to technology history and an oddball collection of stringed instruments...