Calculating UK HMRC late payment interest across multiple interest rate changes

I'm currently trying to create an Excel spreadsheet to calculate HMRC interest on late-paid UK income tax liabilities.

The inputs are the payment due date, the payment date, and the amount due. The challenge is that HMRC interest rates can change while the liability is outstanding, so the calculation needs to handle multiple rates at different dates across the period.

What would generally be the best approach for this kind of problem.

I’ve included the interest rate changes below just in case it helps.

Many thanks! :)

https://www.gov.uk/government/publications/rates-and-allowances-hmrc-interest-rates-for-late-and-early-payments/rates-and-allowances-hmrc-interest-rates