How to correctly calculate an amount due using dates in excel

Question

Hi,

I am trying to correctly calculate an amount due depending on when in the month the amount due normally falls. If the date is past the 28th and there are less days in the previous month than the amount due date then it will be the last calendar day, if the date is on or before the 28th then it will fall on that day. For example, if the due date is 30th, then for February I would expect 1 day from January and 28 days in Feb, so 29 days of (annual fee/365.25) paid. I attached an example file, I hope this makes sense.

I have added some clarity in the file - cannot upload excel file so have created a pdf instead.example file 03.03.2026.pdf

Many thanks in advance.

Answer 1

Use Excel’s date arithmetic to derive the “effective” due date each month, then multiply the number of days in the period by the daily rate (annual fee ÷ 365.25).

Assume:

• Annual fee in cell B2
• Nominal due day (e.g., 30) in cell C2
• Month and year for the period in cells D2 (month as number 1–12) and E2 (year)

1. Build the effective due date for the month (last calendar day if the nominal day does not exist in that month):

=MIN(DATE(E2,D2,C2),EOMONTH(DATE(E2,D2,1),0))

Explanation using the DATE function:

• DATE(E2,D2,C2) builds the nominal due date (e.g., 2026-02-30). If the day is too large, Excel automatically rolls it into the next month as described for the DATE function.
• EOMONTH(DATE(E2,D2,1),0) returns the last day of the month.
• MIN(...) forces the due date to be the earlier of the nominal date and the last day of the month, so for February with a due day of 30, the result is 28-Feb (or 29-Feb in a leap year).

2. Get the previous month’s effective due date:

=MIN(DATE(E2,D2-1,C2),EOMONTH(DATE(E2,D2-1,1),0))

3. Calculate the number of days in the period between the two effective due dates:

=EffectiveDueDateThisMonth - EffectiveDueDatePreviousMonth

If the effective due dates are in F2 (this month) and G2 (previous month):

=F2 - G2

4. Calculate the amount due for the period using 365.25 days per year:

=(B2 / 365.25) * (F2 - G2)

This matches the example: for a due date of 30th and February in a non-leap year, the effective dates will give 1 day in January plus 28 days in February, so 29 × (annual fee ÷ 365.25).

---

References:

• Add or subtract dates
• DATE function