Prepaid expense for one month (IF + date window)
The problem
Problem statement
A prepayment of B1 is amortised evenly over B3 months starting B2. Report the expense for the month containing F2, in the Expense cell D2, rounded to two decimals.
Outside the amortisation window the expense is 0 — not the same monthly amount forever.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | F | |
|---|---|---|---|---|
| 1 | Prepaid total | 12000 | Expense for month | Month to report |
| 2 | Start | 2026-01-15 | 2026-03-15 | |
| 3 | Months | 12 |
Notes
The window is [start, start + months) — the start month belongs to the schedule, the month after the last one does not. Building the end date with DATE(YEAR(start), MONTH(start)+months, 1) avoids the EDATE-on-text problem, because DATE works from numbers.
How to approach
Your answer goes in D2.
- Target cell is D2
- Monthly charge is total ÷ months
- Test F2 is on or after start and before the end
- Shape: =IF(AND(?, ?), ROUND(?, ?), ?)
Function guide
FAQ
How do I amortise a prepayment by month?
Monthly charge = total / months, and only inside the window: date >= start and date < start + months.
Why is the last month counting twice?
The window was built inclusively at both ends. Use < the first day after the final month, which is what DATE(YEAR(start), MONTH(start)+months, 1) gives you.