Working out late payment interest in Excel or Google Sheets
For UK business-to-business invoices. Last checked against GOV.UK and legislation.gov.uk.
You can work out UK statutory late payment interest in Excel or Google Sheets with one formula. Statutory interest under the Late Payment of Commercial Debts (Interest) Act 1998 is simple interest at 8% a year over the Bank of England base rate, counted per day. The fiddly parts are picking the right base rate and getting the day count right. This guide gives formulas for both, plus the fixed £40, £70 or £100 compensation.
Set up the columns
Use one row per late invoice, starting in row 2:
- A, Amount: the invoice total in pounds, including VAT if you charged it. The VAT guide explains why the claim uses the VAT-inclusive total.
- B, Due date: the date payment was due. If your contract did not set one, the Act’s 30-day default applies, explained in statutory interest on unpaid invoices.
- C, Paid date: the date you were paid, or
=TODAY()if you still have not been. - D, Base rate: the Bank of England base rate on the reference date, entered as a percentage (type
4%, not4).
Format B and C as dates and D as a percentage. If a date is typed as text the formulas below return an error or a wrong number, so check that the cells right-align like numbers.
The formulas
- Days late (E2):
=MAX(0,C2-B2). Interest starts the day after the due date (section 4), and runs up to the day of payment, so subtracting the due date from the paid date gives the number of days.MAXstops an early payment showing negative days. - Statutory rate (F2):
=D2+8%. - Interest (G2):
=ROUND(A2*F2/365*E2,2). This is the yearly interest divided by 365, times the days late. GOV.UK uses the same method. - Fixed compensation (H2):
=IF(A2<1000,40,IF(A2<10000,70,100)). That is £40 under £1,000, £70 from £1,000 to £9,999.99, and £100 from £10,000 (GOV.UK). It is claimed once per invoice; see late payment compensation per invoice. - Total claim (I2):
=G2+H2, on top of the unpaid invoice itself.
Copy row 2 down for each invoice. All of these work the same in Excel and Google Sheets.
Finding the right base rate
The base rate is not the rate today, and it does not follow later changes. Under article 4 of the Rate of Interest (No. 3) Order 2002, it is the base rate on 31 December for interest that starts between 1 January and 30 June, and on 30 June for interest that starts between 1 July and 31 December. That rate then applies for the whole time the debt is unpaid.
To have the sheet show the reference date, add this in J2:
=IF(MONTH(B2+1)<=6,DATE(YEAR(B2+1)-1,12,31),DATE(YEAR(B2+1),6,30))
It looks at the day after the due date, which is when interest starts, and returns the 31 December or 30 June before it. Then read the base rate for that date from the table of statutory rates by period and type it into D2. If you would rather look it up in the sheet, the Bank Rate history page has a CSV download of every change.
A worked example
An invoice for £2,500.00 was due on 30 June 2026 and paid on 28 September 2026.
- A2 is 2500, B2 is 30/06/2026, C2 is 28/09/2026.
- Interest starts on 1 July 2026, so J2 gives 30/06/2026. The base rate that day was 3.75%, so D2 is 3.75%.
- E2 gives 90 days and F2 gives 11.75%.
- G2 gives £72.43 and H2 gives £70.00.
- I2 gives £142.43 to claim on top of the invoice.
These are the same figures the calculator gives for those dates, so you can use it to check a row in your sheet.
Mistakes that give the wrong figure
- Using today’s base rate for an invoice whose interest started in an earlier half-year.
- Compounding. Statutory interest is simple interest (section 1), so do not add the interest to the amount and charge interest on that.
- Dividing by 360 or by 12 months. Count actual days and divide the yearly figure by 365.
- Typing 4 instead of 4% in the base rate column, which makes the rate 100 times too high.
- Charging compensation per reminder. The fixed sum is once per invoice, not per letter you send.
- Using it for consumer invoices. The Act covers business-to-business contracts only (section 2).
- Ignoring your own terms. If your contract sets its own substantial late-payment remedy, that rate may apply instead; see setting your own interest rate.
Claiming it
GOV.UK says to send a new invoice if you decide to add interest to what you are owed. The late payment letter and interest invoice template gives wording you can use with the figures from your sheet.
Common questions
What is the Excel formula for late payment interest in the UK?
With the invoice amount in A2, the due date in B2, the payment date in C2 and the base rate on the reference date in D2, the interest is =ROUND(A2*(D2+8%)/365*MAX(0,C2-B2),2). The same formula works in Google Sheets.
Should the spreadsheet use 360 days or compound interest?
No. Statutory interest under the Late Payment of Commercial Debts (Interest) Act 1998 is simple interest, and GOV.UK works out the daily amount by dividing the yearly amount by 365. Do not add interest on top of interest.
Which base rate goes in the spreadsheet?
The Bank of England base rate on 31 December if interest starts between 1 January and 30 June, or on 30 June if it starts between 1 July and 31 December. Interest starts the day after the invoice was due. That one rate then applies for as long as the invoice stays unpaid.
How do I work out interest on an invoice that is still unpaid?
Use =TODAY() as the payment date. The figure then shows the interest owed so far and goes up each day until the invoice is paid.
General information, not legal advice