Excel Override

Hi Gang,

I'm running a typical 10-yr DCF for an owner. I have run into a problem that I can solve but would take a lot of man hours in the way I figured. I was hoping to get a shortcut from anyone here.

I have a rent stabilized building in nyc and have the whole cash flow figured out, returns, waterfall, etc..

What I need to put in is instead of some apartments growing at year0*(1+growthrate)^(year#-1) is some years we have assumed that we need to manually input a jump in rent of 20%. This is purely for internal deal vetting so it doesn't need to be pretty, e.g.) instead of month 13 rental income with the assumption growth rate, is there a way to either manually input a numerical value while keeping intact a formula for growth.

Simpler, I have a $1700 rent in nyc for a rent stablized apt, i have made a probabalistic assumption that in year 5 the tenant will turn over and I can raise the rent legally by 20%. I want to be able to manually input that and keep the growth rate in my assumptions in the years after to reflect the jump I manually inputted in year 5.

 
Best Response

Thanks for the inputs. I think this helps me. Here is the whole problem, maybe i was just really tired last night.

This is the monthly statement per unit. =$F4*(1+IF($D4="FM",Escl_FM,Escl_RS))^(K$3-1), where F4 is the rental rate from year 0, D4 denotes whether unit if Free Market or Rent Stabliized it returns a specified growth rate, and K3 is the year applied to the growth rate. Let's say in year 2 we forecast that a RS unit will turn over and therefore increase by 20%. Is there a way to manually input an input value and a formula value in the same cell that can be toggled by a data validaiton switch, like a choose function? Perhaps keep the row below empty and group it to hide a switch?

Just thought of something in line with keeping a grouped cell below each unit row, if, cell below is = 0, so then if input is manual it will return the manual value, but if not it will return the formula value. But then i would need to adjust every column's formula as they are all based on year 0 specific unit cash flow. So year 3 would grow at the old rental rate.

I was hoping there was a cleaner/easier way to do this.

Thank you everyone for your input.

 

Think you just answered your own question, but as you said you can just use the two rows above/below your formula for manual inputs:

Row 1 > Override switch (1 or 2) Row 2> hard coded growth rates for overrides (your 20%) Row 3> your current formula

Just change your current formula to run off of a choose function...=choose(override switch cell, your current formula, your current formula but with replaced with the overridden growth rate). And copy and paste it over the hold period. Should be a simple drag...

 

thanks, Excel is actually giving me a hard time. for some reason year 1 doubles, whiie year 2-10 does not have the growth rate. I don't get it, the formula makes sense mathamtically.

=IF(L7>0,L71+(IF($D6="FM",Escl_FM,Escl_RS)^(L$3-1)),$F6(1+IF($D6="FM",Escl_FM,Escl_RS))^(L$3-1))

where L7 is the row beneith, L3 is the year(e.g. 1,2,3,4,5,6) in the top column.

what if it is in year 5-10 out of a 10yr dcf?

 

Check adventures in CRE for their construction draw and interest calculation model. They did something related to development costs that allowed for a manual override. It's not exactly what you're looking for but it might help.

Another option is if you're manually assuming de-rent control why not just have an input for the time period and amt. Have your growth rate override if it it's that quarter.

 

Hey, can't you just insert a line under your growth line and add in the same exact line as above instead, modify it so it can jump 20% in year 5. Then you can simply put a choose function under your main line ( that affects the whole model) and input a drop button to choose between those two lines.

Hope it has helped you.

 

Laudantium iusto quasi enim sequi harum fugiat. Eos itaque illo deserunt commodi perferendis voluptas.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
BankonBanking's picture
BankonBanking
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
Secyh62's picture
Secyh62
99.0
5
kanon's picture
kanon
98.9
6
DrApeman's picture
DrApeman
98.9
7
dosk17's picture
dosk17
98.9
8
GameTheory's picture
GameTheory
98.9
9
CompBanker's picture
CompBanker
98.9
10
Jamoldo's picture
Jamoldo
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”