Hello everyone!

If anyone can assist with the below it would be greatly appreciated:

I am attempting to build a 10 year pro-forma that incorporates rent roll-over and existing vacancy. I have written a formula that works, but does not capture the whole picture.

=-SUMPRODUCT(--(YEAR('Pro Forma '!C$4)>=YEAR(RENTROLL!$S$9:$S$20)),RENTROLL!$F$9:$F$20,RENTROLL!$T$9:$T$20*('Pro Forma '!$C$115))

Dates: C4

RentRoll - Column S: Lease Maturity Dates (incorporates existing vacancy as having a maturity date preceding the C4)

RentRoll - Column F: Square Footage

RentRoll - Column T: YearFrac intra-year vacancy (i.e. if a lease matures in April, 0.67 of the year the space is vacant

C115: value of vacant square foot, in my model it is $19 per square foot

What is missing is duration to backfill the space (this is a high vacancy market and we assume it will take approximately 3 years, roughly 1/3rd per year, to backfill the space)

Additionally, we assume that new tenants backfilling the space will sign for 5 year leases, which means that the formula needs to "vacate" again during the investment period, and again backfill over an additional 3 years.

Ideally, I would like to improve the formula without adding additional rows.

Additional assumption cells for "Backfilling:"

C116: New lease term (5 years, as noted above)

C120: Vacant Inventory Duration (3 years as noted above)