Late Fee Formula in Excel: Days Overdue Times a Daily Rate

A late fee formula in Excel works out how many days a payment is late and multiplies that by a daily rate. The core is =MAX(0,C2-B2)*$G$1: paid date minus due date, never below zero, times the fee per day. This guide builds it step by step, then adds a grace period, a maximum fee and unpaid invoices. It is for anyone who tracks invoices, rent or bills in a spreadsheet. The fee amounts are examples only: the real terms come from your contract and the rules where you work, so this guide covers the spreadsheet, not legal advice.

The example data#

The examples use five invoices and a small settings area. Keep the fee settings in their own cells so you can change them without editing formulas:

Settings: G1 daily fee 2.50, G2 grace days 5, G3 maximum fee 50, G4 as-of date 31 Oct 2026. INV-104 has not been paid yet.

Step 1: Count the days overdue#

Excel stores dates as numbers, one per day, so subtracting two dates gives the days between them. To keep early payments at zero, wrap the subtraction in MAX:

=MAX(0,C2-B2)

MAX returns the larger of its numbers. For INV-101, C2-B2 is 18, so MAX returns 18. For INV-102, paid two days early, C3-B3 is -2, and MAX returns 0. The MAX function guide covers the syntax, and the article on days between dates explains DAYS and DATEDIF as other ways to count.

If the result shows as a date such as 18/01/1900, the cell has picked up date formatting. Change it to General or Number on the Home tab.

Check that both columns hold real dates, not text that looks like a date. A real date sits on the right of the cell by default, and =ISNUMBER(B2) returns TRUE for it. Text dates give a #VALUE! error when you subtract them, so convert them with DATEVALUE or Text to Columns first.

Step 2: Multiply by a daily rate#

Multiply the days overdue by the daily fee in G1:

=MAX(0,C2-B2)*$G$1

The $ signs lock G1, so the formula still points at the fee when you fill it down (Windows: Ctrl+D; Mac: ⌘D). Without them, the row below would look in G2.

You can write the same logic with IF: =IF(C2>B2,(C2-B2)*$G$1,0). It returns the same results; MAX is just shorter. The IF function guide and the article on how the IF function works show more two-outcome tests like this.

Add a grace period#

A grace period is a number of days after the due date when no fee applies. Subtract it inside MAX:

=MAX(0,C2-B2-$G$2)*$G$1

With 5 grace days, INV-101 is charged for 13 days: 32.50. INV-103, paid exactly 5 days late, now pays 0. INV-105 pays for 35 days: 87.50.

Some terms work differently: once the grace period passes, the fee counts from the original due date. For that rule, use IF: =IF(C2-B2>$G$2,(C2-B2)*$G$1,0). Read your terms carefully and pick the formula that matches them.

Cap the fee with MIN#

Wrap the fee in MIN to set a maximum. MIN returns the smaller of its numbers, so the fee can never pass the cap in G3:

=MIN($G$3,MAX(0,C2-B2-$G$2)*$G$1)

INV-105's fee of 87.50 is above the cap, so MIN returns 50. The MIN function guide explains the syntax. To set a minimum instead, for example a flat 10 for any late payment, use MAX on the outside.

Late fees on invoices that aren't paid yet#

When the paid date is blank, count up to an as-of date instead. IF chooses which date to use:

=MIN($G$3,MAX(0,IF(C5="",$G$4,C5)-B5-$G$2)*$G$1)

For INV-104, due 15 Oct and still unpaid on 31 Oct, that is 16 days minus 5 grace days, times 2.50: 27.50.

In a live tracker, replace $G$4 with TODAY() so the fee grows each day. In a report you send to someone, a fixed as-of date is safer, because the numbers won't change when the file is opened tomorrow. A bill tracker in Excel uses the same due date and paid date columns, and you can highlight overdue dates with conditional formatting so late items stand out.

Other late fee structures#

Not every fee is a fixed amount per day. Two other common shapes:

Flat fee plus a daily rate. Charge 25 once, then 2.50 per day:

=IF(C2>B2,25+(C2-B2)*$G$1,0)

INV-101 pays 25 + 18 × 2.50 = 70.

A yearly percentage of the amount. Charge a yearly rate, such as 12% in G5, for each day late:

=D2*$G$5/365*MAX(0,C2-B2)

INV-101 pays 1,200 × 12% ÷ 365 × 18 = 7.10 (rounded). Wrap it in ROUND, as in =ROUND(D2*$G$5/365*MAX(0,C2-B2),2), so the fee is in whole cents. Which structure, rate and cap you may use depends on your agreement and local rules. To group unpaid invoices by how late they are, build an aging report in Excel.

Practise it#

Try the exercise on this page: one formula combines the days overdue, the grace period and the cap. Then practise the IF function in a live spreadsheet and practise counting days between dates. Both lessons check your formula the moment you press Enter.