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.
Blanditiis ratione et ab eligendi. Est quo quo recusandae consectetur cupiditate suscipit eaque. Deleniti nihil nostrum ipsa dolorem. Sed repellat maxime at ex ea autem.
Unde et corporis unde. Ea accusantium quia id totam. Est cupiditate in repellat repellendus reprehenderit. Impedit sit et ut.
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...