Cashflow forecast based on start dates and either monthly, bi-monthly, quarterly, annually

[hro5e]

Hello and thank you for your time:

I have a table of overheads.

Each overhead has a value in column D

Each value is paid monthly, bi-monthly, quarterly or annually stated in column F

Each overhead has a start date for when the first payment is due in column G

Column H is the start date of the year 2024, for example H1 = 01/01/2024

Each column following H is the next day for example I1 = 02/01/2024, J1 = 03/01/2024 and so on until you have 365 columns representing each day of the year

I am trying to create a formula that will display the value from column F into each matching cell based on the overheads criteria.

For example:

	A	B	C	D	E	F	G	H	AJ
1	Overhead	Cost	When	Starting	01/01/2024	02/01/2024	03/01/2024	...	01/02/2024
2	Rent	500.00	Monthly	01/01/2024	500.00				500.00

As you can see the rent first payment starts 01/01/2024 and shows the value under the matching date, now because rent is "monthly" the formula should know that the next payment is 1 month later on 01/02/2024. therefore the value 500.00 is displayed under the date.


The formula I have at the moment (which does not work) is:

=IF(AND($F2 = "Monthly", H$1 = EDATE($G2, COLUMN(H$1)-COLUMN($H$1:H$1))), $D2,
 IF(AND($F2 = "Bi-Monthly", H$1 = EDATE($G2, 2*(COLUMN(H$1)-COLUMN($H$1:H$1)))), $D2,
  IF(AND($F2 = "Quarterly", H$1 = EDATE($G2, 3*(COLUMN(H$1)-COLUMN($H$1:H$1)))), $D2,
   IF(AND($F2 = "Annually", H$1 = EDATE($G2, 12*(COLUMN(H$1)-COLUMN($H$1:H$1)))), $D2, ""))))

 

[hro5e]

=IF($F2 = "Monthly", IF(DAY($G2) = DAY(H$1), $D2, ""),
 IF($F2 = "Bi-Monthly", IF(AND(MOD(MONTH(H$1) - MONTH($G2) + 12 * (YEAR(H$1) - YEAR($G2)), 2) = 0, DAY($G2) = DAY(H$1)), $D2, ""),
  IF($F2 = "Quarterly", IF(AND(MOD(MONTH(H$1) - MONTH($G2) + 12 * (YEAR(H$1) - YEAR($G2)), 3) = 0, DAY($G2) = DAY(H$1)), $D2, ""),
   IF($F2 = "Annually", IF(AND(MONTH(H$1) = MONTH($G2), DAY($G2) = DAY(H$1), YEAR(H$1) >= YEAR($G2)), $D2, ""), ""))))

This seems to work well.

I need to test further.

 

[felixstraube]

Assuming that you have excel 365, how about?:

=LET(
c,$D2,
f,XLOOKUP($E2,{"Monthly";"Bi-Monthly";"Quarterly";"Annually"},{1;2;3;12}),
s,$F2,
d,G$1,
r,EDATE(s,SEQUENCE(12/f,,0,f)),
SUM((r=d)*1)*c
)