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.

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

<tbody>
</tbody>

 

[dapper2012]

Would a table with vlookup and index match be better?