Real Estate Rent Roll

[dapper2012]

I am having difficulty trying to figure out the most efficient and cleanest way to project and roll up for future cash flow modeling a large list of retail tenants. Each tenant has different lease start and expiration dates, rent-able SF, rent price, renewable options, and rent escalations. Below are three example tenants of a list of roughly 50. I am trying to figure out the best way to model this for a 5 year projection. My initial thought was to model them out monthly by tenant using a nested IF statement and than roll up the gross rental revenue using SUMPRODUCT to arrive at the annual cash flows.

I really appreciate some help.

[TABLE="width: 1841"]
<tbody>[TR]
[TD]Ste. #[/TD]
[TD]Tenant[/TD]
[TD][/TD]
[TD]Credit Rating[/TD]
[TD]Type Lease[/TD]
[TD]Rentable
SqFt[/TD]
[TD]Rent/SF Year[/TD]
[TD]Current Rent / Year[/TD]
[TD]Since[/TD]
[TD]Lease Start[/TD]
[TD]Firm Term Expiration[/TD]
[TD]Lease Expiration[/TD]
[TD]Lease Term[/TD]
[TD]Years Remaining[/TD]
[TD]Firm Term Years Remaining[/TD]
[TD]Renewal Options[/TD]
[TD]Rent Incr Freq /Years[/TD]
[TD]Next Adjustment Date[/TD]
[TD]Next Adjustment Amount[/TD]
[TD]Reneweal Proability[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Applebees[/TD]
[TD][/TD]
[TD][/TD]
[TD]NNN[/TD]
[TD]7,000[/TD]
[TD]$16.32[/TD]
[TD]$114,240[/TD]
[TD]2003[/TD]
[TD]5/1/2015[/TD]
[TD]7/31/2020[/TD]
[TD]7/31/2020[/TD]
[TD]5.3[/TD]
[TD]4.1[/TD]
[TD]4.1[/TD]
[TD]Two - 5 yr
Option 1: $18.48/SF
Option 2: $20.33/SF[/TD]
[TD]5[/TD]
[TD]8/1/2020[/TD]
[TD]None[/TD]
[TD]75%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]China King Buffer[/TD]
[TD][/TD]
[TD][/TD]
[TD]NNN[/TD]
[TD]1,400[/TD]
[TD]$18.28[/TD]
[TD]$25,596[/TD]
[TD]2006[/TD]
[TD]10/1/2010[/TD]
[TD]9/30/2017[/TD]
[TD]9/30/2017[/TD]
[TD]7.0[/TD]
[TD]1.3[/TD]
[TD]1.3[/TD]
[TD]None[/TD]
[TD]5[/TD]
[TD]10/1/2017[/TD]
[TD]None[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Chase Bank[/TD]
[TD][/TD]
[TD]B+[/TD]
[TD]NNN[/TD]
[TD]1,840[/TD]
[TD]$12.00[/TD]
[TD]$22,080[/TD]
[TD]2013[/TD]
[TD]8/6/2013[/TD]
[TD]8/31/2017[/TD]
[TD]8/31/2017[/TD]
[TD]4.1[/TD]
[TD]1.2[/TD]
[TD]1.2[/TD]
[TD]One - 4 yr
Option 1: $13.00/SF[/TD]
[TD]4[/TD]
[TD]9/1/2017[/TD]
[TD]None[/TD]
[TD]75%[/TD]
[/TR]
</tbody>[/TABLE]

 

[dapper2012]

Would a table with vlookup and index match be better?