How do you create a series based on a specific interval?

[Steve Hickman]

I am setting up the following as inputs to forecast rental costs across a
range of property:

Annual rental
Rent review frequency
Rent increase at review
Life of lease

I am struggling to put together a formula that will include the rent review
frequency. e.g. in the below example the rent review is after two years and
is 2.0%. However I need to include a formula so that x years after 2009 (and
x years after that etc with all x's the same length of period) the rent
increase escalator figure will be included based upon the rent review
frequency included in the inputs.

Year Base cost Escalator Total
2009 500,000 500,000
2010 500,000 500,000
2011 500,000 2.0% 510,000

Using Excel 2007

 

[Sean Timmons]

I would think it's easiest to just take the two rows you have and paste them
down the column. So, base cost would be = previous row's total. Total
=total*(1+escalator) If it was 3 years per escalator, just enter the
escalator at the 3rd month and copy/paste the 3 rows all the way down the
column...

HTH

 

[Steve Hickman]

Thanks. What I really need to do though is insert a formula so that if I
selected an interval of three years, the cell would immediately insert 2%
after the initial three years and every three years thereafter. Equally, if
I wanted an annual interval, the formula would insert 2% in each cell each
year. Any ideas? Never come across the need for this before but I would
have expected this to be part of normal financial modelling/scenario planning
given the need to flex different variables

 

[James Button]

Consider -
row() gives you the row number
mod(row(),$A$1) gives you a count of the row number divided by the
(interval) from A1

Compare the value for the current row, and the prior row (Row(-1)

that gives you = inf the row is for the same interval
and <> for a new interval entry

= is True, which can be treated as 1
<> is False, which can be treated as 0

so, if the compare result is 1, then don't add the review %
and if the compare result is 0, then do add the review %
=prior col *(1+Increment)

JimB

 

[Steve Hickman]

Thanks, this is really neat!

Consider -
row() gives you the row number
mod(row(),$A$1) gives you a count of the row number divided by the
(interval) from A1

Compare the value for the current row, and the prior row (Row(-1)

that gives you = inf the row is for the same interval
and <> for a new interval entry

= is True, which can be treated as 1
<> is False, which can be treated as 0

so, if the compare result is 1, then don't add the review %
and if the compare result is 0, then do add the review %
=prior col *(1+Increment)

JimB