Charge interest on overdue invoices. Compute days late, apply a monthly or annual rate, and add the fee to the balance — with a floor so paid-on-time invoices owe nothing extra.
The example
$2,000, 1.5%/mo, 45 days late.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | 45 days late | — |
| 3 | Fee | → $45.00 |
The formula
The formula:
How it works
How it works:
- Compute days late:
MAX(TODAY() - due_date, 0)— zero until overdue. - Apply the monthly rate prorated by days:
balance × rate × days/30. - For a flat annual percentage, use
balance × annual_rate × days/365. - Add the fee to the balance for the amount now due.
Late-fee caps are regulated. Many jurisdictions limit the interest rate you can charge on overdue invoices, and your contract should state the rate up front. Use the formula for the math, but confirm the allowable rate — and that your agreement specifies it — before billing interest.
Try it: interactive demo
Balance, monthly rate, days late.
Variations
Days late
From the due date:
Annual-rate version
APR basis:
Flat late fee
Fixed charge:
Pitfalls & errors
Floor days late. Use MAX(...,0) so on-time invoices owe no fee.
Rate basis. Decide monthly (÷30) vs annual (÷365) and be consistent.
Know the legal cap. Late-fee rates are often regulated — confirm and state in the contract.
Practice workbook
Frequently asked questions
How do I calculate a late fee on an invoice in Excel?
How do I use an annual interest rate instead?
Can I charge any late-fee rate?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class