How to calculate rental cash flows with rent increasing at varying intervals

Hello first time post,
I am trying build a template that will calculate the IRR of a the cash flows from a commercial lease based on data from the lease including term, rental rate, square footage, etc. My issue comes with trying to project the lease increases which can start at varying months and increase at varying intervals. My sheet can identify the increase and its dollar amount based on whether the increase is in dollars psf or a percentage increase, what I need is a way for the sheet to calculate the next increase based upon the prior increased rent as opposed to the original amount and to do this based upon the increase interval times that have been input from the lease.

Any help would be appreciated and feel free to ask any questions.
I have attached what I have so far. Lease IRR calculations.xlsx

Again I am a new user so if this is a problem that has been solved elsewhere I apologize and would gladly be pointed in the direction of that thread, thank you for your time.

Hi,

Can you explain what calculation you want in which column. Any example would help me to resolve your query.

Thanks & Regards,
Prasad

I guess by this time you have figured that I overlook the obvious or am in fact mentally challenged thanks largely to prolonged exposure to chemicals

The files and formulas I submitted earlier do not show the rental cash flows from period 6 till period 24

I have reflected this change in the formula and all the cash flows look right this time

So the formulas would change as listed below

=IF( B15>=$E$5, IF(MOD(B15-$E$5,$E$6)=0, FV($E$3,INT((B15-$E$5)/$E$6)+1,0,-D15), FV($E$3,INT((B15-$E$5)/$E$6)+1,0,-D15)), D15)

=IF( B15>=$E$5, IF(MOD(B15-$E$5,$E$6)=0, FV($E$3/12,INT((B15-$E$5)/$E$6)+1,0,-D15), FV($E$3/12,INT((B15-$E$5)/$E$6)+1,0,-D15)), D15)

For reference, open and browse the attached Excel worksheet

Just to see what the rate of return is for the one who is leasing and found astonishing figures of 120% and 118% respectively based on monthly and annual percent increases in rates

See the last two columns where I have added in the 2 semi-annual Commission amounts to get the final cash flows for 12 years

Thank you for all of the help I really appreciate it I haven't been able to check the new sheets as i am out of town away from my computer but your help is appreciated thank you for your time sorry if it consumed to much of it lol