Financial
Split a contract payment or prepaid cost across calendar months (daily proration)
Allocate a hypothetical contract amount proportionally to inclusive active calendar days in each month.
Exact daily overlap allocation across partial start and end months
A $6,000 service contract running 60 inclusive days from April 16, 2026 through June 14, 2026 allocates $100/day under this assumed convention—not $2,000 in each of April, May, and June!
For any reporting month starting at PeriodStart (D2) and ending at mEnd = EOMONTH(D2, 0), active days inside that month equal MAX(0, MIN(ContractEnd, mEnd) - MAX(ContractStart, PeriodStart) + 1). Multiplying ContractAmt * OverlapDays / TotalDays allocates $1,500 to April (15d), $3,100 to May (31d), $1,400 to June (14d), and $0 outside the term.
| Month Start (D) | Active Window | Overlap Days | Allocated Amount ($100/d) |
|---|---|---|---|
| 2026-04-01 | Apr 16..Apr 30 | 15 | 1500 |
| 2026-05-01 | May 01..May 31 | 31 | 3100 |
| 2026-06-01 | Jun 01..Jun 14 | 14 | 1400 |
| 2026-07-01 | (outside term) | 0 | 0 |
Monthly prorated allocation formula with LET
Total allocated across April + May + June equals 1,500 + 3,100 + 1,400 = $6,000 exact.
=LET(mEnd, EOMONTH(D2, 0), totDays, C2 - B2 + 1, ovDays, MAX(0, MIN(C2, mEnd) - MAX(B2, D2) + 1), ROUND(A2 * ovDays / totDays, 2))Active days inside the reporting month
Inspect ovDays directly whenever auditing deferred revenue schedules.
=MAX(0, MIN(C2, EOMONTH(D2, 0)) - MAX(B2, D2) + 1)Inputs and interpretation
Use date-only numeric inputs with EndDate >= StartDate and PeriodStart on the first day of a month. This is a chosen daily-proration convention, not a revenue-recognition or tax rule. Confirm the contract’s inclusive/exclusive day policy. Independently rounded monthly amounts can leave a cent-level residual; reconcile the last period to the original total.
Try the example
Use the original values or change them to test your understanding.