proforma rent roll

[rroudabush]

I am trying to create a 3 year proforma rent roll for an apartment community and am stuck. Here's what it looks like (only 2 apts shown).

2% 3% 3 1/1/2019 2/1/2019 3/1/2019 4/1/2019 5/1/2019 6/1/2019 7/1/2019 8/1/2019 9/1/2019 10/1/2019 11/1/2019 12/1/2019 1/1/2020 2/1/2020 3/1/2020 4/1/2020 5/1/2020 6/1/2020 7/1/2020 8/1/2020 9/1/2020 10/1/2020 11/1/2020 12/1/2020 1/1/2021 2/1/2021 3/1/2021 4/1/2021 5/1/2021 6/1/2021 7/1/2021 8/1/2021 9/1/2021 10/1/2021 11/1/2021 12/1/2021 Adjusted LE Rent 6/30/2019 $1,500 $ 1,500 $ 1,500 $ 1,500 $ 1,500 $ 1,500 $ 1,500 $ 1,530 $ 1,530 $ 1,530 $ 1,530 $ 1,530 $ 1,530 $ 1,530 $ 1,530 $ 1,530 $ 1,530 $ 1,530 $ 1,530 $ 1,530 $ 1,576 $ 1,576 $ 1,576 $ 1,576 $ 1,576 $ 1,576 $ 1,576 $ 1,576 $ 1,576 $ 1,576 $ 1,576 $ 1,576 $ 1,308 $ 1,308 $ 1,308 $ 1,308 $ 1,308 4/30/2017 $1,350 $ 1,377 $ 1,377 $ 1,377 $ 1,377 $ 1,377 $ 1,377 $ 1,377 $ 1,377 $ 1,377 $ 1,377 $ 1,377 $ 1,377 $ 1,418 $ 1,418 $ 1,418 $ 1,418 $ 1,418 $ 1,418 $ 1,418 $ 1,418 $ 1,418 $ 1,418 $ 1,418 $ 1,418 $ 1,461 $ 1,461 $ 1,461 $ 1,461 $ 1,461 $ 1,461 $ 1,461 $ 1,461 $ 1,461 $ 1,461 $ 1,461 $ 1,461 <colgroup><col style="mso-width-source:userset;mso-width-alt:2998;width:62pt" width="82"> <col style="mso-width-source:userset;mso-width-alt:1682;width:35pt" width="46"> <col style="mso-width-source:userset;mso-width-alt:2230; width:46pt" width="61" span="9"> <col style="mso-width-source:userset;mso-width-alt:2486; width:51pt" width="68" span="3"> <col style="mso-width-source:userset;mso-width-alt:2230; width:46pt" width="61" span="9"> <col style="mso-width-source:userset;mso-width-alt:2486; width:51pt" width="68" span="3"> <col style="mso-width-source:userset;mso-width-alt:2230; width:46pt" width="61" span="9"> <col style="mso-width-source:userset;mso-width-alt:2486; width:51pt" width="68" span="3"> </colgroup><tbody> </tbody> <colgroup><col><col><col span="9"><col span="3"><col span="9"><col span="3"><col span="9"><col span="3"></colgroup><tbody> </tbody> <colgroup><col><col><col span="9"><col span="3"><col span="9"><col span="3"><col span="9"><col span="3"></colgroup><tbody> </tbody>

<colgroup><col style="mso-width-source:userset;mso-width-alt:3072;width:63pt" width="84"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2706; width:56pt" width="74" span="20"> <col style="width:48pt" width="64" span="16"> </colgroup><tbody>
</tbody>

So, if the Adjusted LE date is less than the "current date" then the rent should be increased by the 2% (first year increase) it should then remain unchanged for 12 months. then when month 13 comes around it should be increased by the 3% (second year increase) and remain at that rate for 12 months, and so forth. Each apartment has a different Adjusted LE date and some are less that the start date of 1/1/2019 and some fall within the 1/1/2019 - 1/1/2021 range. I just can't seem to come up with a formula to make this happen.

Can anyone lend a hand?

Thanks,

 

[Rijnsent]

Hi Rroud,
=previous_rent*(1+percentage_increase) or =B5*(1+C1)
This assumes that the percentages row is empty or 0 for all the columns apart from the cell above the start of the year.
An alternative =IF(MONTH(date_cell)=1,previous_rent*(1+percentage_increase),previous_rent)
Cheers,
Koen