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
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,sumofpreviousyears=TriggerYear,(TriggerYear+4)>=ThisYear),NEWDEBTSERVICE,ORIGINALDEBTSERVICE)
Yes, the AND statements are nested inside the OR.
Consequuntur voluptatem quaerat et aut officiis. Omnis facere qui quaerat soluta.
Deserunt vel eum enim nostrum rerum neque in. Quo dicta aspernatur perspiciatis sit. Omnis inventore sint quo molestiae quas repellendus ut. Maxime nihil debitis eveniet molestias ea odit.
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...