This is a classic spreadsheet problem, and the fact that you're asking about it means you already understand the core issue: HMRC interest rates move, and your tool needs to move with them. The right approach isn't a single formula that tries to do everything; it's a structure that lets you swap rates in and out as they change. That distinction matters, because it shifts the burden from remembering rate history to simply updating a table.
Here is the practical method. Build a small reference table with two columns: the effective date of each rate change and the corresponding annual percentage rate. Then, in your main calculation, break the overdue period into chunks defined by those dates. For each chunk, calculate the number of days at that specific rate, apply the daily rate (annual rate divided by 365), and sum the results. The key insight is that you are not writing one giant formula; you are writing a series of smaller, date-bounded calculations that a lookup function like XLOOKUP or INDEX-MATCH can stitch together. This turns a messy variable-rate problem into a clean, repeatable process.
What makes this approach effective is that it future-proofs your spreadsheet. When HMRC announces another rate change next quarter, you add one row to your reference table and your calculations update automatically. You never have to dig into nested IF statements or manually adjust date ranges again. The same logic applies whether you are dealing with two rate changes or twenty. It is the difference between building a calculator and building a framework.
We think you should stop trying to solve this with a single cell and start constructing a small, modular solution. Your spreadsheet will become something you trust, not something you second-guess every time the Bank of England meets. That is the real win: not a one-time answer, but a structure that handles the inevitable next change without requiring you to rebuild the whole thing.