Real Estate Rent Roll Question - Return a value from a date range

I have a rent roll for a retail center which has three tenants and the analysis start date is 1/1/2016. There are 3% CPI increases on each year, and each tenant has a different start date. One suite is vacant thus it will not have any increases or start date but will have income.

I am having trouble finding a formula to use to find the per square foot rental rate for at a given date. The information for the tenants is below:

Tenant A A 1,152sf 12/01/08 11/30/18 - Base Rental Rate in 12/1/2008 is $3.25 with 3% escalations annually.
Tenant B B 2,540sf 05/14/14 05/13/19 - Base Rental Rate in 5/14/2014 is $3.25 with 3% escalations annually.
Tenant C C 1,800sf - Vacant with projected income.

how do i return the correct per square foot rental rate value for 1/1/2016 for each tenant through a formula in excel? I have tried; =if(1/1/2016<=..... but i get stuck. Is it a Vlookup Hlookup via a table, or a SUMPRODUCT / SUMIFS?

Thank you !

take a look at the formulas

OK, understood... Fdibbins

RCM thank you for that. I am trying to be as clear as possible but Im not sure how to explain it. I have attached my workbook for you all. I would like to enter a formula into the cell 'Rent Roll'F8 and F9 to automatically populate with the correct price per square foot value from the Rent Roll detail sheet or any other sheet, given the Analysis Start Date (1/1/2016) and the period in which Tenant A & Tenant B are in respectively. There may not be a need for another rent roll detail sheet, and maybe a formula solving for the period with the correct adjustments would work.

When i input that formula into F8 on 'Rent Roll' the result is still $3.25. I would prefer to omit Rent Roll Detail (3) and utilize 'Rent Roll Detail' and 'Rent Roll Detail (2)' - the correct number should be $4.00 / sf / month.

I did see the #4 but that was a response to the moderator. Im not sure if its coming up on my thread feed here. All I see is reference to 'Rent Roll (3)' - IM sorry for the confusion.

Im sorry, a sheet to me is a tab/worksheet within a workbook - i.e. 'Rent Roll' or 'Rent Roll Detail' etc Thank you.

i want a function if i change the analysis date to automatically update the appropriate per square foot rate based on the period of the lease term.

Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
3.25 3.35 3.45 3.55 3.66 3.77 3.88 4.00 4.12 4.24

year 8 of the total lease term would be 1/1/2016 - or $4.00 / sf / month. - or $4.00 / sf / month until 12.1.16.

by way of: =($A$10*(1+$C$10)^(K13-1))*IF(K13>$B$4+$B$5,0,1) with information derived from 'Rent Roll'

I have attached the V2 instead of the former.

D12 should read the beginning date of the lease, which is 12/1/2008. The next rental increase would be 12/1/2009; a CPI or rental increase of 3% on the $3.25 / per month. 3.25*(1+.03)^period-1 - where the period is the year, and 12/01/2008 through 11/30/2008 is year 0.

When I put in your formula it is referring back to the square footage of the space. Was this supposed to refer to the per square foot rental rate?

I have re-attached the spreadsheet with the formula in, and C8 as D8 instead which is what the increase would be referring to.

Understood. I just tried your formula again but it didnt work "N/A?" - and i just used this formula =INDEX('Rent Roll Detail'!D14:M14, SUMPRODUCT(--('Rent Roll Detail'!C1>='Rent Roll Detail'!D12:M12))) to get the correct value for a date after 1/1/2016 however I am now having an issue when trying to calculate a point in the year when the rate increases and accounting for the rate between the 12/1/2015 increase and the 12/1/2016 increase - effectively taking 11 months of $4.00 rent / sf, and one month of $4.12 / sf.

Same with Tenant B which has increases in June 2016, or a split year of $3.61 for the first 6 months of 2016, and $3.71 of the last 6 months of 2016.

File attached - could you send a file back again?