Hello all,
First post for me, so thank-you in advance for any help.
I am creating a cashflow model model for a rental business. One area I am struggling with, although I have a formula that works, is the area of rental growth. The formula I have seems unwieldy and I was wondering if anyone had any ideas on how to simplify. Unfortunately I can not code using VBA (need to learn how to!).
We have a series of properties in a building that are let on three year terms. I look up in another sheet the number of properties let in the building at a given date and assume that the properties are let in ID order (i.e. property 1, then property 2, then property 3 and so on until all properties are let). When a property is let I need to look up that property's rent and use it as the start point. The rent will then grow in two ways: There is a rental growth after 12 months of the property being let (i.e. after 12 months of income) in line with CPI at say 1.6% and then there is also rental growth at the end of the three years (i.e. after 36 months of rental income have been received), when a new tenancy is granted, in line with a notional growth rate of 5.5% per annum.
The structure of the income sheet is a series of month start dates in cells D1 through to FC1 and below that month end dates in D2 through to FC2. Below this are three rows with a factor for Income inflation (row 3) for starting residential rental inflation, retail rental inflation (row 4) and Cost inflation (row 5). Below this are a further 3 rows containing Month Number, Quarter Number and Financial Year respectively.
Property IDs are listed in column A starting at A15 down to An, where n is the number of properties in the building. Column B contains the sum of columns D:FC. Column C is blank and Columns D:FC then contain the income flow for the Property.
Assumptions!$B$4 is the rental growth occurring throughout the property life which needs to be applied at the renewal of tenancy (every 36 months).
Assumptions!$B$5 is the rental growth in line with CPI at 1.6% every 12 months.
Formula in D15 is as follows:
=IF(AND(INDEX('Absorption Calculations'!$E$7:$FD$61,MATCH($A$1,'Absorption Calculations'!$B$7:$B$61,0)+4,MATCH(D$6,'Absorption Calculations'!$E$3:$FD$3,0))>=COUNTA($A$15:$A15),C15=0),INDEX('Unit Information'!$H$2:$H$1440,MATCH($A15,'Unit Information'!$A$2:$A$1440,0))*D$3/12,C15)*IF(AND(MOD(COUNTIF($C15:C15,">0"),36)=0,COUNTIF($C15:C15,">0")>0),(1+Assumptions!$B$4/12)^COUNTIF($C15:C15,">0"),IF(AND(MOD(COUNTIF($C15:C15,">0"),12)=0,COUNTIF($C15:C15,">0")>0),(1+Assumptions!$B$5),1))
if I simplify this to remove the index and match for number of units let (red) and the property IDs associated rent (blue) then I have the following:
=if(and(number of properties let at given time>=COUNTA($A$15:$A15),C15=0),property rent*D$3/12,C15)*IF(AND(MOD(COUNTIF($C15:C15,">0"),36)=0,COUNTIF($C15:C15,">0")>0),(1+Assumptions!$B$4/12)^COUNTIF($C15:C15,">0"),IF(AND(MOD(COUNTIF($C15:C15,">0"),12)=0,COUNTIF($C15:C15,">0")>0),(1+Assumptions!$B$5),1))
What I was wondering is whether there was a way of having a simpler formula to do the same?
Sorry for the long first post. I hope I have been sufficiently clear to explain my problem, but please do let me know if I can clarify in anyway. You can appreciate that uploading the example may prove difficult given the links to other sheets.
Thanks
D
First post for me, so thank-you in advance for any help.
I am creating a cashflow model model for a rental business. One area I am struggling with, although I have a formula that works, is the area of rental growth. The formula I have seems unwieldy and I was wondering if anyone had any ideas on how to simplify. Unfortunately I can not code using VBA (need to learn how to!).
We have a series of properties in a building that are let on three year terms. I look up in another sheet the number of properties let in the building at a given date and assume that the properties are let in ID order (i.e. property 1, then property 2, then property 3 and so on until all properties are let). When a property is let I need to look up that property's rent and use it as the start point. The rent will then grow in two ways: There is a rental growth after 12 months of the property being let (i.e. after 12 months of income) in line with CPI at say 1.6% and then there is also rental growth at the end of the three years (i.e. after 36 months of rental income have been received), when a new tenancy is granted, in line with a notional growth rate of 5.5% per annum.
The structure of the income sheet is a series of month start dates in cells D1 through to FC1 and below that month end dates in D2 through to FC2. Below this are three rows with a factor for Income inflation (row 3) for starting residential rental inflation, retail rental inflation (row 4) and Cost inflation (row 5). Below this are a further 3 rows containing Month Number, Quarter Number and Financial Year respectively.
Property IDs are listed in column A starting at A15 down to An, where n is the number of properties in the building. Column B contains the sum of columns D:FC. Column C is blank and Columns D:FC then contain the income flow for the Property.
Assumptions!$B$4 is the rental growth occurring throughout the property life which needs to be applied at the renewal of tenancy (every 36 months).
Assumptions!$B$5 is the rental growth in line with CPI at 1.6% every 12 months.
Formula in D15 is as follows:
=IF(AND(INDEX('Absorption Calculations'!$E$7:$FD$61,MATCH($A$1,'Absorption Calculations'!$B$7:$B$61,0)+4,MATCH(D$6,'Absorption Calculations'!$E$3:$FD$3,0))>=COUNTA($A$15:$A15),C15=0),INDEX('Unit Information'!$H$2:$H$1440,MATCH($A15,'Unit Information'!$A$2:$A$1440,0))*D$3/12,C15)*IF(AND(MOD(COUNTIF($C15:C15,">0"),36)=0,COUNTIF($C15:C15,">0")>0),(1+Assumptions!$B$4/12)^COUNTIF($C15:C15,">0"),IF(AND(MOD(COUNTIF($C15:C15,">0"),12)=0,COUNTIF($C15:C15,">0")>0),(1+Assumptions!$B$5),1))
if I simplify this to remove the index and match for number of units let (red) and the property IDs associated rent (blue) then I have the following:
=if(and(number of properties let at given time>=COUNTA($A$15:$A15),C15=0),property rent*D$3/12,C15)*IF(AND(MOD(COUNTIF($C15:C15,">0"),36)=0,COUNTIF($C15:C15,">0")>0),(1+Assumptions!$B$4/12)^COUNTIF($C15:C15,">0"),IF(AND(MOD(COUNTIF($C15:C15,">0"),12)=0,COUNTIF($C15:C15,">0")>0),(1+Assumptions!$B$5),1))
What I was wondering is whether there was a way of having a simpler formula to do the same?
Sorry for the long first post. I hope I have been sufficiently clear to explain my problem, but please do let me know if I can clarify in anyway. You can appreciate that uploading the example may prove difficult given the links to other sheets.
Thanks
D
