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.
Can't you do a normal IF function or have you tried this? =IF( Cell input rent break date = Cash Flow Date, Stepped Rent, Normal Rent or formula )
Hard to help on excel formula issues without being able to see what is currently there.
I second using "If" - not sure how many units you have, but I would simply have a key next to each unit for when you think it will turn and have the if statements run off of that....
but like the above said, hard to provide a solution when you cannot see the problem,
Yeah, you can probably create an IF statement that would do the entire calc, but you could also *IF on to the back of your current calc.
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?
Try putting a parenthesis after L7, instead of after the plus sign
Can you pm me this one particular sheet/selection youre working on? Would just be easier to see something. Can break the formulas before sending if you want.
Any updates on this one guys? This is my next tier of modeling I'm trying to learn.
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.
I like that idea. I think having that third line with a cell that you can apply an If statement too. I really think you just have to do a IF on the tail end of your regular syntax ...IF(C1="20% Bump",true calcs,false calcs).
Pariatur rerum a illo veniam. Dolore dolores molestiae voluptas dolores vero alias. Accusamus alias illum commodi quos cupiditate sapiente quia. Praesentium eaque neque enim eos quia illum.
Optio maiores perspiciatis rem nihil architecto maiores. Aut qui deleniti vitae est recusandae.
Facilis minima illo odio placeat perspiciatis ab nisi. Non maxime quibusdam quibusdam quaerat. Ipsum laudantium occaecati excepturi hic ut nostrum delectus. Quae perspiciatis at iusto quod ut minima quidem aut.
Delectus et quia dolores est praesentium. Voluptatum rerum consequuntur esse laboriosam magni praesentium vero. Exercitationem quos quos ut veritatis accusantium voluptas numquam. Architecto et molestiae voluptate suscipit.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...