Apartment Rent Roll Cash Flow Formula

Not from England, however I have a 'Helper Columns' solution. After all this is a spread sheet and helper columns may be hidden for aesthetic purposes.
Added helper columns to show the start and end date of the second lease in columns M and N using the following formulas respectively:

Formula:

=IF(I5="","",DATE(YEAR(I5),MONTH(I5)+VALUE(LEFT(L5,2)),DAY(I5)))

Formula:

=IF(M5="","",DATE(YEAR(M5),MONTH(M5)+VALUE(LEFT(H5,3)),DAY(M5)))

Then two helper tables for calculating the monthly rent of the original and second leases using the following formulas respectively:

Formula:

=IF(OR(AZ$4>$I5,$G5=""),"",IF(AND($G5<=AZ$4,$I5>=EOMONTH(AZ$4,0)),$J5,IF(AND($G5<=EOMONTH(AZ$4,0),$I5>=EOMONTH(AZ$4,0)),(EOMONTH(AZ$4,0)-$G5)/DAY(EOMONTH(AZ$4,0))*$J5,IF(AND($G5<=AZ$4,$I5<=EOMONTH(AZ$4,0)),($I5-AZ$4+1)/DAY(EOMONTH(AZ$4,0))*$J5,""))))

Formula:

=IF(OR(CG$4>$N5,$M5=""),"",IF(AND($M5<=CG$4,$N5>=EOMONTH(CG$4,0)),$Q5,IF(AND($M5<=EOMONTH(CG$4,0),$N5>=EOMONTH(CG$4,0)),(EOMONTH(CG$4,0)-$M5)/DAY(EOMONTH(CG$4,0))*$Q5,IF(AND($M5<=CG$4,$N5<=EOMONTH(CG$4,0)),($N5-CG$4+1)/DAY(EOMONTH(CG$4,0))*$Q5,""))))

The original cash flow table uses a simple SUM function.
Let me know if you have any questions.

You're Welcome and thank you for the feedback. If the solution satisfies the query, please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.