Financial
Trigger Fixed Payment Every N Columns or Months (MOD + COLUMN)
Place a fixed amount every N equal-width period columns, choosing either the first or last column of each cycle. Includes interval validation and a six-period experiment.
Choose the interval and the phase of a repeating schedule
A2 is a positive integer interval and B2 is a numeric amount. C:H represent six consecutive, equally spaced periods. Negative amounts represent outflows; zero is valid.
End-of-cycle payments fall in periods 3 and 6 when N=3. Start-of-cycle payments fall in periods 1 and 4. The formulas count column positions, not dates or the numbers in row 2. Skipped or irregular periods need a date-based model.
This is a hypothetical schedule, not a rule for tax due dates, bond terms or actual invoice dates.
| Model Column | 1-Based Month # | Quarterly ($A$2 = 3) | Bimonthly ($A$2 = 2) |
|---|---|---|---|
| Col C (M1) | 1 | $0 (MOD = 1) | $0 (MOD = 1) |
| Col D (M2) | 2 | $0 (MOD = 2) | $4,500 (MOD = 0!) |
| Col E (M3) | 3 | $4,500 (MOD = 0!) | $0 (MOD = 1) |
| Col H (M6) | 6 | $4,500 (MOD = 0!) | $4,500 (MOD = 0!) |
End-of-Cycle Payment Every Nth Column (Months 3, 6, 9...)
Enter this formula in C3 and fill right through H3. For N=3, E3 and H3 contain the amount.
=IF(COUNT($A$2,$B$2)<>2,"Enter numeric interval and amount",IF(OR($A$2<1,$A$2<>INT($A$2)),"Interval must be a positive integer",IF(MOD(COLUMN(C3)-COLUMN($C$3)+1,$A$2)=0,$B$2,0)))Start-of-Cycle Advance Payment Every Nth Column (Months 1, 4, 7...)
Enter this formula in C4 and fill right through H4. For N=3, C4 and F4 contain the amount.
=IF(COUNT($A$2,$B$2)<>2,"Enter numeric interval and amount",IF(OR($A$2<1,$A$2<>INT($A$2)),"Interval must be a positive integer",IF(MOD(COLUMN(C4)-COLUMN($C$3),$A$2)=0,$B$2,0)))Try the example
Use the original values or change them to test your understanding.