Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do you create a series based on a specific interval?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do you create a series based on a specific interval?
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do you create a series based on a specific interval?
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 "Sean Timmons" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do you create a series based on a specific interval?
Thanks, this is really neat!
"James Button" wrote: 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 In article , says... 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 "Sean Timmons" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel to recalculate a worksheet based on a time interval | Excel Worksheet Functions | |||
How dynamically create routing slip based on names in specific cel | Excel Discussion (Misc queries) | |||
i want to clculate the interval confidence of the 30 time series. | Charts and Charting in Excel | |||
How do I color specific data series based on location on data she | Charts and Charting in Excel | |||
create a data-series based on two different columns of data ? | Charts and Charting in Excel |