How do you decide what start date to use in your model? And how do you handle year 1 rent/expense inflation if the most recent operating statements don’t match up nicely with the timing of your model?
For example, say I’m underwriting a RE acquisition right now which realistically wouldn’t close for a few months, should “year 1” of the cash flow start in May 2017? And if so, say I’m underwriting off of 2016 year end financials, do I bother adding a little extra inflation in year 1 for the extra 5 months? This gets even more complicated if, say, I'm underwriting off a mix of 2016 actuals, a 2017 budget, maybe some Feb/March 2017 bills, etc...
What do you typically do?
Yeah I usually just start my models at the beginning of next month. Like I would put a venture start date of 3/1/2017 if I started a fresh model right now. If it's the only hardcoded date input it doesn't really matter because it's easy to update.
Generally you don't want to model inflation in the current year, most models tend to do 0% in yr 1 (current), some smaller portion (1-2%) in yr 2, then step into their true escalations, be it 2 or 3% or whatever - but this is more for global purposes, e.g. for new leases or capex.
If you have a rent roll you should know what the income side looks like. I would inflate the opex budget 2-3% if you're UW based on last year's operating statement, because you don't know the true numbers til the end of the year.
=today()+90
Agreed +90 makes sense (or starting next month as cre_questions said), but how would you handle year 1 inflation using a May/June start date if you're using 2016 financials or a 2017 budget?
Incorporating timing into model (Originally Posted: 08/21/2014)
Anyone have an idea of how you would incorporate a trigger into your timeline?
Say - if you have a 10 year projection, and income hits a certain level in year 3, it triggers the interest to change to a level that will be carried for 5 years (into year 7). Would you create a nested IF statement?
I dont believe you even need nested...on your debt service line just do =if(incomeline=X,pmt(blah),pmt(blahotherinterestrate))
There are some missing pieces of info that would make the formula better.
• Does this threshold have to be met by year 3 or is it anytime you reach that threshold (say year 4 or 5)? • If & when the threshold is met, is it fixed for 5 years? • What happens after the 5th year of fixed payments – does the interest revert to L + spread?
If you don't need the formula to be dynamic based on analysis year then I would go with what SHB said.
needs to be dynamic.
Does not need to happen year 3, can happen whenever. Fixed for 5 years, and then increases. I'm using countif (x>0), >5 within a if statement, but I don't know if it's working...
This is probably not the easiest/most elegant way to do it but I think it works. This is for a one-time trigger where the 5-year period will not continue to be extended if the income threshold is met.
Create a line that is basically your trigger switch. This line will be a IF(AND(Income>X,sumofpreviousyears1),1,0) -- This makes it so that a 1 will be displayed in the FIRST year that your income threshold is met and never again
Make a named cell that is TriggerYear that index/matches it (make sure it is iferror'd so that if it never happens you won't N/A yourself)
on your debtservice line do a IF like this IF(OR(TriggerYear=ThisYear,AND(ThisYear>=TriggerYear,(TriggerYear+4)>=ThisYear),NEWDEBTSERVICE,ORIGINALDEBTSERVICE)
Yes, the AND statements are nested inside the OR.
Corrupti quidem ratione esse omnis maxime natus odio aliquam. Distinctio explicabo sed dolores sit est tempora modi. Ad illo necessitatibus quis fugiat qui. Voluptatem et cumque vel eius nisi quos nihil. Vero repellat qui deserunt molestiae culpa et et.
Eos tempora consequuntur totam minima natus. Est hic dolorem facere.
Nam velit quas non ab consequatur et dolor. Ut voluptatem eius et laborum qui. Distinctio ipsum voluptates minima et. Ducimus praesentium non quibusdam. Labore totam tenetur pariatur quibusdam omnis. Excepturi consequatur quo consequuntur ipsum facere quasi. Iure similique velit consequuntur.
Possimus molestiae et incidunt consequatur iste ipsam. Numquam non velit itaque a. Odit aut consectetur eius quia est nulla. Mollitia perferendis impedit enim corrupti ipsum et. Velit et voluptas praesentium saepe alias. Explicabo est et vero maiores cupiditate non veritatis.
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...