Formula for expressing an annual growth rate in monthly terms?

[jordantgeller]

Hello,

I am working on a dynamic real estate financial model and have hit a roadblock trying to write a formula that I can copy over to the multiple periods that I am attempting to model. I have attached the model for your consideration.

Please refer to cell M18 on the "Monthly CFs" Tab. The formula in that cell is the base year's expense ($4,000) divided by the number of periods (12) to get arrive at the monthly expense. I want to model the base year's expense of $4k to grow 4% annually for the next four years, but to express the number at a monthly rate for each 1 month period.

In cell Y18 I attempted to write a formula that would grow the expenses at the 4% annual growth rate expressed as a monthly amount. However when I copy that formula horizontally to the right, it shifts the summed range to the right by one cell, capturing the wrong range of cells. To see, look at the formula copied into the cell in Z18, it shows the range to be N18:Y18 when the desired range is still M18:X18. How do I keep the range from shifting one cell to the right every time the formula is copied one cell to the right? Is there a variation on the formula that I should be using or perhaps a different function all together?

My end goal is to to create one formula that I can copy across the entire model but will spit out the figure of $346.67 from period 13 to 24 (X18:AJ) and $360.5 for period 25 to 36 (AK:AV) and so on and so forth.

Any thoughts, suggestions or tips would be greatly appreciated.

Best regards,
Jordan

[Shareez Saleem]

IF(YEAR(M6)=2017,$F$24/12,IF(YEAR(M6)=2018,$F$24/12*1.04,IF(YEAR(M6)=2019,$F$24/12*1.08,IF(YEAR(M6)=2020,$F$24/12*1.12,IF(YEAR(M6)=2021,$F$24/12*1.16,IF(YEAR(M6)=2022,$F$24/12*1.2,""))))))

Copy and past the above formula in M18 to CF18.

[FDibbins]

1. correct the spelling in L18
2. Use the exact same words in L as you have in B (leave off the year 1 etc in B and keep the spaces in L if you must, but otherwise use the same terms)
3. Use real dates row 6, this will allow you to use many of excel's built-in date functions (you can format this to look however you want)
4. In fact, if you enter a real date in F5, then you can use this in M2 and copy across...
=EOMONTH($F$5,COLUMNS($M1:M$10)-1)
This will make the whole sheet dynamic - change the date in F5 - all the dates change for you

OK on to your question...
M18=IFERROR((INDEX($F:$F,MATCH(TRIM($L18)&"*",$B:$B,0))/12)*(1+(YEAR(M$6)-2017)*0.04),"")
copied down and across