Help with a string of IF statements

Apologies for the confusion I only meant to attach one sheet not the whole book and the stressed case was not meant to be included. I have attached one sheet now and here's a better outline of where I'm at:


Expected results:

1. When the current date (H2:AA2) exceeds either the "Lease Break" date (column D) or "Lease Expiry" (Column E) I want the rental values to drop off (or expire as it were).

2. If the rental values have dropped off and the cell = 0 i want it to display the word "Void" for the amount of quarters specified in column G.

3. Finally, I want the cashlows/rent to kick back in (at the level it was previously) after the specified amount of "Void" periods (per Column G) have elapsed.


So I have tried to tackle each of these one at a time as follows:

1. IF(H2>$E$4, 0, $F$4/4) & IF(H2>$D$4, 0, $F$4/4)

2. & IF(H4=0, "VOID", "")

3. IF(D4:G4="VOID", $F$5/4, "")


Just to note the model takes the annual rental sum and divides it into quarterly amounts.


I have attempted each element of the above separately in rows 4-7:

1. Rows 4 & 5: when the current date exceeds the lease break date or lease expiry date I want the cell to revert to 0, I have these working separately.

2. Row 6: I can get this working to an extent but I don't know if it is the delimiting or what but the text keeps disappearing (even though I've delimited cells).

3. Row 7: This appears to be working okay.

So I have tried (and failed) to merge all of these rules together and it's just not happening for me, any advice would be greatly appreciated.