Add Underwriter Company with airSlate SignNow

Add underwriter company

[Music] hey and thanks for watching in this video I'll be showing you how to model a hypothetical value-add office investment using the all-in-one model for underwriting real estate acquisition and development deals now the genesis of this tutorial is a question we received in the all-in-one support forum asking specifically how to model a value at office deal with no debt and some non stabilized in place rent roll operation etc and so while I don't have the specifics for the deal that the questioner is posing I know enough where I can come up with some hypothetical assumptions and then using those hypothetical assumptions model that this hypothetical deal in the all-in-one now before I get into it I should mention there are two methods for underwriting value-add deals using the all-in-one now the first method would be you use the development module which involves construction financing or you might consider that a bridge loan during the non stabilized period during the renovation period and the lease up and at the point of stabilization that bridge loan is taken out by a permanent loan and then there's an opera stabilized operating period from then through to the end of the whole period and that first method you'd likely use in cases where you have a complex budget timing schedule where you really want to model out in detail the renovation cash flows there's not going to be a lot of operating income during that renovation period and it's more for kind of redevelopment type value-add deals the second method is you buy an existing building maybe it's empty if it's under occupied you put some money in it over a period of time during the lease up and then at the end of kind of that renovation and Lisa period you hit stabilization and either you sell or you hold through to the end of some hold period and in the second method instead of using the development module you use the acquisition module and then you model out the renovation cash flows in the other capex line and that's what I'm gonna show you right now so let's go ahead and get started the first thing you'll need are the assumptions if you want to follow along you can download those in the post attached to this video if you're watching this video on YouTube look in the comments below and there'll be a link to go over and find these assumptions and they're both in PDF format or appended to the end of the post and with these assumptions we'll then open the most recent version of the all-in-one in this case I'm using version 5.5 and I'm gonna start here on the summary tab now the first thing is I want to I want to activate the modules that I need well I don't need the permanent financing financing module there'll be no debt on this deal I will need to set the property type to office and then make the Ori tabs visible and now the now the model is set up with all of the tabs that I need and I'm just gonna fill out first the description so I'll pause the video fill out the description come back and with this description complete I'm gonna move up here and this is only a three-year hold so there will be renovation in years 1 & 2 then I'll have one full year of stabilized occupancy and that'll just help season the operation so I can sell at the end of the third year the analysis start date being january 1st 2018 with my growth begin date being 12 months following now again I'm leaving the development length at zero which means this will be modeled as an acquisition without using the development module the next thing we see over here on our assumptions we're assuming a market and a y cap rate of 6% with no growth in that cap rate through to our exit and then we're assuming 2% in selling costs at reversion we're also assuming we can buy this building in place at three point two five million with about seventy-five thousand dollars in acquisition costs let me come back here again we set this cap rate to 6% no annual change in the cap rate three to five oh four the acquisition price and then due dilligence seventy five thousand now you'll notice blue font cells are required inputs whereas orange font cells are optional in these orange font cells there are formulas that are recommended but don't necessarily always apply and in this case it's certainly the case so in this case the orange was Drott was drawing from the present value or whatever value this model is spitting out for the acquisition price and sometimes that may be the case but here we will be buying it below it's it's stabilized value that's some in place value and in this case three point two five million thus we change that orange font cell and we're going to do that a couple times in this particular value add deal lastly and this isn't too important but I'm going to set a discount rate of 8% here and all that's doing is it's driving some present value of the deals cash flows discounted at that 8% that'll just give us a feel for what the present value at stabilization for for this opportunity would be and with that we can move to model out the operation so we're going to go to the Ori - rent roll tab and we're going to first drop in this rent roll now there are six tenants you can see the rent roll here and so I'm just going to pause the video I'm going to drop in these six tenants and the various assumptions for these tenants and I'll be right back I've modeled out the rent roll now you'll notice that I don't make mention of the future tenant assumptions and the reason is this tutorial isn't meant to teach you how to use the office retail industrial rent roll you can find that on the retail development walkthrough in this case I'm more talking about just how to model out a value-add deal so with the rent roll in place I now want to move to my RI - opstat tab or my operating statement tab and I have some assumptions for income so for a first other income I have a 3% growth rate on each one of those items with no parking income 15,000 a year in antenna income which is fixed meaning it starts as of analysis begin date miscellaneous income of 7,500 which is 100% variable 0% fixed which means it's it's the 7,500 is tied to physical occupancy again though these are growing by 3% thus in year 3 our first stabilized year it's 77 82 versus our 7,500 as of today next we are going to drop in our operating expenses first our growth rates here are 2 percent for each one of these items then in terms of expenses we have here payroll at a dollar seventy-five a foot general and administrative at fifty cents a foot utilities at 225 a foot etc gonna pause I'm gonna drop these in I'll pause the video and I'll be right back and then in terms of percentage fixed you'll see that here all are zero percent fix except for taxes and insurance which are at 100 percent for insurance seventy five percent for taxes so we can just set these to zero taxes 275 insurance at 100 and then our non-reimbursable is that half a percent of egi so I just go ahead and change this to half a percent so now we've modeled to Noi now you'll notice that this is negative for our for our stabilized year well that doesn't make any sense and the reason is again you'll see another orange font cell here this is calculating the estimated first stabilized year and in the case of value-add that needs to be manually entered so this case we again assumed two years of renovation and Lisa so the third year is our first full year of stabilized cash flows and you'll see as soon as I do that our stabilized Noi now becomes what we expect it to be finally let's get into capex and this is where we model out the renovation cost so you can ignore the tenant improvements and Leasing commissions those are orange font cells and and if if you were to cap so if when you're doing your direct cap valuation currently it's defaulted to do a direct clap off of Noi if you do a direct cap off of cash flow from operations you'll want to fine tune your T is and LCS on the stabilized basis to be kind of the average per year in this case that's not necessary because we're capping in a Y and then in terms of capital reserve 30 cents a foot so 0.3 ups equals 0.3 times and alright 6360 actually it should be 35 cents of foot let's change it and now we get to the fun part so we have a budget for renovations right here we've got some lobby expenses we're gonna upgrade the bathrooms do some exterior upgrades there's some deferred maintenance that needs to be covered and there's soft costs involved with that in all we're gonna spend five hundred and forty thousand dollars to bring this building up tomorrow get and so how do we account for that well we're gonna come again to this the Ori operating statement tab and down below in the monthly operating statement section you'll notice this line other capex these are orange font cells again the default is you drop some value into here 50 thousand as an example and then down below again orange font it just automatically takes that 50,000 straight lines at each month and that becomes the other capex but instead we're gonna zero it out up top here and we're going to hard-code in the values oh I'll show you how not technically a hard code but we're going to custom model or renovation cash flows for this five hundred and forty thousand in and upgrades and the like and to do that I'm just gonna come and I'm going to create a new tab again the the beauty of Excel is we can do this custom modeling and with that I'm then going to model out 24 months to years of renovation cash flows so I'll start with a header go out 24 months Simone's there okay and then again I have five budget items lobby bathrooms exterior deferred maintenance and then finally soft costs and I'll have a total line I'm gonna set all of these to hide this hide this great okay and then I'm gonna have two columns here and this first column is my budget and then I'm going to do an air check column and what this air check column is going to do it's just gonna take the sum of all of the cash flows that I put out into there to confirm that it matches the budget okay I'm gonna underline that this sum up of that this becomes the sum row all right so these are these are summing each one of these columns I'm just gonna drop in the budget right here 250 for Lobby 50,000 for bathrooms hundred thousand for exterior deferred maintenance 75,000 and 65,000 for soft costs my total that up and convert that to numbers and now I need to model these cash flows well the lobby is going to be a 12-month project I'm just gonna straight line that so what I'm gonna do is I'm gonna select all 12 of these cells and I'm gonna write equals 250 divided by 12 and then I'm going to hit control enter and when I hit control enter it's going to it's going to write that formula in all 12 of the selected cells and then my check equals my budget bathrooms it's a similar thing right equals 50,000 divided by 12 and then exterior is 24 months and that is 100,000 equals 100,000 divided by 24 deferred maintenance and soft costs are also 24 months so deferred maintenance mi-24 and soft costs 65 divided by 24 and with that my check equals my budget right and I've now modeled out my renovation cash flows okay this is what I'm going to spend on improving the property and I'm gonna come back to my operating statement and here's month one of my other capex and I'm just going to link this month to month one total here there's thirty-five thousand and then I'm going to copy over through to the end of month 24 copy as formulas right or I'm going to be linking each one of those formulas over and then all I'm going to do is to confirm I'm gonna select all of those cells and the down below here some 540 so I know all of those have come over you can also see them now as we scroll up to the annual 420 and year 1 1 20 and year 2 gives us the 540 thousand and with that done we're pretty much ready now there is no partnership in this particular hypothetical and so I'm going to come to the equity cash flow tab and just set the sponsor to 0% and all that does is it turns off the partnership waterfall and now we can come to the property cash flow report we can see the cash flows in each year again 1 year 1 2 & 3 which is our entire analysis period and here's the unlevered cash flow right we have three point three to five million in time zero that's our acquisition that's three point two five million plus our acquisition costs in year one we have a net cash flow of - almost six hundred thousand year - and then year three we have a combination of some positive operating cash flow plus our residual or our sales price less are 2% selling costs give us a positive cash flow in year three of 6.2 million that gives us an unlevered IRR in an annual basis of fourteen point eight five percent at an equity multiple of almost one and a half times we can also see that summarized here on the summary tab and we see here returns on a monthly and annual basis and with that we've used the all-in-one a model to underwrite a office value add investment if you have questions feel free to reach out to me and thanks for your time