How do I handle partial payments in the Excel calculation?

Recalculate on the reduced balance from the payment date forward. The clean way is a row per payment: one row per segment with start date, end date, and the outstanding balance during that segment, then sum the interest column.

Published October 4, 2026

How to Calculate Interest on an Overdue Invoice in Excel

Q: What is the Excel formula for interest on an overdue invoice?

For daily simple interest with the due date in B2, outstanding in C2, and annual rate in D2: =MAX(0,TODAY()-B2)*C2*D2/365. For a flat 1.5% monthly charge on the balance in C2: =C2*0.015*(TODAY()-B2)/30.

Q: Does late payment interest compound?

Almost never for invoices. Use simple interest. UK and EU statutory schemes are simple interest, and most US contract clauses are too. Compounding complicates the math and hands the client a legitimate argument.

The question of how to calculate interest on an overdue invoice in Excel comes up the moment a client is 30 days late and you want the number to be exact, not "about a hundred bucks." You need two formulas: one for daily simple interest when your contract states an annual rate, and one for the flat monthly percentage most small businesses actually use. Both fit in one cell each, and I will show you the traps before they cost you an argument.

Formula 1: daily simple interest from an annual rate

Suppose your contract says 12% per year. Put the due date in B2, the outstanding balance in C2, and the annual rate as a decimal (0.12) in D2:

=MAX(0,TODAY()-B2)*C2*D2/365

Three deliberate choices here. MAX(0,...) keeps the formula at zero before the due date passes, so your sheet does not show negative interest on invoices that are not late yet. TODAY() makes the number grow every day the client ignores you, which is exactly the psychological effect you want when you paste it into a reminder email. And the /365 divisor converts the annual rate to a daily one. The 360-day banker's convention exists, but 365 is the standard for invoices unless your contract says otherwise. Put the divisor in its own cell if you want the formula to be auditable.

Worked example: invoice for $4,800, due August 1, today is October 4, which is 64 days late, at 12% annual:

= 64 x 4800 x 0.12 / 365 = $100.93

Formula 2: the flat monthly percentage

Most freelancers do not think in annual rates. Their contracts say "1.5% per month on overdue balances," which is 18% a year and easier to explain to a client. With the due date in B2 and the balance in C2:

=C2*0.015*(TODAY()-B2)/30

Same $4,800 invoice, 64 days late:

= 4800 x 0.015 x 64 / 30 = $153.60

Notice the monthly version produces a bigger number ($153.60 vs $100.93). That is not a rounding difference: 1.5% a month is genuinely a steeper rate than 12% a year. If your contract says 1.5% monthly, use the second formula and do not apologize for it. You disclosed the rate when the client signed.

The partial-payment problem, solved with rows

This is where single-cell formulas die. A client pays $2,000 of the $4,800 on day 20, then nothing until day 64. You cannot apply one formula to the whole period because the balance changed mid-flight.

The clean fix is one row per segment. Lay out the columns as Segment Start, Segment End, Outstanding Balance, then compute each row's interest with =DAYS(End,Start)*Balance*Rate/365 and sum the column:

Day 1 to 20:  19 x 4800 x 0.12 / 365 = $29.99
Day 20 to 64: 44 x 2800 x 0.12 / 365 = $40.51
Total interest: $70.50

Each row is trivially checkable, which is the entire point. When the client disputes the number, you do not defend a formula. You show them the ledger.

My opinion: build the sheet once, stop doing this by hand

Every freelancer I know calculates overdue interest by hand the first time, argues about it the second time, and automates it the third. The sheet above takes ten minutes and ends the genre of conversation where both of you are doing mental arithmetic on a phone call. My one strong recommendation: freeze the rate convention in the spreadsheet (365 vs 360, simple vs compound, grace days) and make it match your contract wording exactly. The dispute that costs you real money is never about the arithmetic. It is about the client saying "that is not what we agreed." And honestly, for the actual number, skip Excel entirely and use the late payment interest calculator with your state's statutory rate; it is faster and the rate is cited to the law.

Frequently asked questions

What is the Excel formula for interest on an overdue invoice?

For daily simple interest: =MAX(0,TODAY()-B2)*C2*D2/365, with due date in B2, balance in C2, and annual rate in D2. For a monthly 1.5% charge: =C2*0.015*(TODAY()-B2)/30.

Should overdue invoice interest use 360 or 365 days?

Use 365 unless your contract says otherwise. The 360-day convention comes from commercial bank lending; invoicing contracts and statutory schemes overwhelmingly use 365.

How do I handle partial payments in Excel?

Split the overdue period into one row per segment, with the balance that was outstanding during each segment, compute interest per row, and sum them. Never apply one formula across a changing balance.

Does late payment interest compound?

Almost never. Use simple interest. Compounding complicates the math, looks aggressive, and gives the client something legitimate to argue about.

Calculate the exact interest on your overdue invoice.

Verified US state, UK, and EU statutory rates with the law cited for each.

Run your own numbers with the free calculator

I write one practical money guide a week. Subscribe free here.