Underwrite eSigning Template with airSlate SignNow

Underwrite esigning template

hi this is rob beardsley and today i'll be walking you through the underwriting spreadsheet and how to perform an underwriting analysis on a multi-family property so first i'll just give a quick run through of the spreadsheet specifically the inputs tab is where you'll be doing all of your inputs so none of these other tabs need to be played with as far as inputting data and making assumptions which is quite handy so for now i'll just stick to this uh tab and we'll go through the inputs so number one most importantly what we do is we get the whisper price we want to know what pricing the broker thinks the property will trade at or if it's an off-market property specifically what the seller's asking price is so and today we're going to be using surround a property in dallas as the example property and just to double check i will remind myself what the asking price is okay we'll assume it's it's this one so we'll put that as the as the whisper price now the whisper price input here it doesn't have a formula sourcing anything from it it's just a placeholder for us to know what the whisper price is and we can change the purchase price accordingly so if we're over 10 off from the whisper we might just walk away from the deal and choose not to pursue it because we think we'll just be too far off similarly these two inputs are also solely just for your own benefit they don't uh no formulas are built off of these inputs but it's nice to know what the physical vacancy is as well as the vintage so what we can do is we quickly go to the offering memorandum the om and we'll quickly find the vintage as well as the physical vacancy so i'm reading here i don't see it yet i'll keep going i don't see it yet so i might have to dig but nonetheless some of that information there is quite handy so here we go we have 1974 build and we still don't know the vacancy but that's all right but we'll take that information and we'll put it here um next thing i like to do is the unit mix so a quick way to do the unit mix is to find it already in the om if there is a consolidated unit mix already i will drag it and share the screen with the om as well as the spreadsheet and from here i will just basically copy the notes so here i see a1 unit type one bedroom one bath you could say a1 one by one a2 one by one b1 two by one b2 2x2 and b3 to one and a half and then number of units it's all the way in the far left 162 20 72 80 20. okay square footage is right here as well 619 756 869 920 and 1038 okay great now wrench is where it gets a bit trickier it's do you use the market rent do you use the in-place rent do you believe what the broker is saying and this is where the rent roll might come in handy but to start we'll just do it the easy way and we'll just assume that the current in-place rent is is the market rent and it's higher than the current market rent which doesn't really make any sense typically market rents are higher than average in-place rents due to lost lease but this here seems to be different so for now we'll just use the current in-place rent column to put in our rents and also to be overly simple we will also just take the pro formal rents directly from the om no this is not how we underwrite no this is not how we determine our pro forma rents but just for pedagogical purposes we're going to use the ones that we see here just to move along but obviously when putting together a true analysis a lot of time i mean even though there's all these inputs on this page here a lot of time is spent simply just focusing on these pro formal rents and then obviously substantiating them via comps okay so now you're lucky you're able to get the full unit mix right from the om which saved you a lot of time however what if it was an off-market deal and there is no om or what if the om doesn't seem to make sense or has errors in it well then we can go old school and go directly to the rent roll so here you see for this deal package i have the om which is always good to have but not necessary the rent roll which is necessary and the t12 which is also very necessary but the rent roll is probably the most important so typically you want these three things and then you can perform an underwriting so the rent roll now this is an unconsolidated raw look at every single unit in the property and what we can do if there is no om or if we want to do this on our own i will get rid of this extra information here and i will remove these extra rows and then create a filter on these top columns and now begin to filter by square footage so the first one is 619 and here if i sum if i sum the number of units of that are 619 i get 162. which luckily matches up here 162 for the 619 square foot units so looks like that's working out for us now if i look at the market rent i'll just make these well i guess that doesn't help much but so market rent if i look at it and i actually go like this i can see down at the bottom average count max and sum so average and max will be the most helpful here so and actually all in one shot we're able to see that there's 162 units average market rent is 726 and the max is 8.25 so i'm not as inclined to trust a max market rent because anyone can make any unit call at any market rent but what are they actually leasing up for so but it's nice to know the average is 7.26 however i can go one column over and look at the actuals and i can see that the max is 870. so it seems that they have a renovated unit that is receiving a premium up to 870 dollars in rent let's see if that matches up with the pro forma well the broker thinks you can get 960. so maybe it's only partially renovated and you can further renovate up to that 960 point or they're just being brokers and being a bit aggressive so for now i think what we'll do to be conservative considering that it's already a pretty big jump from what we have here as an average rent of 743 to 870 i think i'm going to switch my numbers around to reflect that so i'm going to do 743 to 870 okay and as you can see it's helpful that we're not solely relying on the om and that we're actually digging into the rent roll to find out what these true rents are and where you could potentially push rents so i go to the next unit type and i'm going to just go straight to the actuals and i'm going to see there's 20 of these units and they are 756 square feet so if we go here we see 20 units at 756 square feet and we can see that the actual lease is 813 and the max is 949. 49 so again our numbers are a little off here i think we'll we'll adjust we'll go 8 13 to 9 49 so it's 8 13 9 49 okay so by now you get the point you use filters on the top to then go and get the number of units for that specific unit type the square footage the current average market rent as well as if there are renovated units what's the max rent that they're achieving on the property and where can we push rents to which gives you a rough idea obviously if you do further renovations you can potentially raise rents beyond what is currently achievable on the property but that's a good baseline and for us that's a really good business strategy that we can get behind if we know that the premiums for underwriting to the premiers that are already on the property it's it's it's a much more compelling business plan okay so we'll stop there since you get the idea next after we fill out the unit mix we then go to our income assumptions and then our expenses so let's jump right in again if you stick to the om which you already see is potentially fraught um not fraud but fraught just that has a little more risk relying solely on the om however you can most oms will have some sort of underwriting or some sort of t12 and so here here they have a t12 broken out by month which isn't super helpful but here they do have a pretty helpful page with trailing 12 months six months income annualized with t12 expenses and then three months income with t3 expenses which is kind of the standard when underwriting this column here a 90 day income versus t12 expenses so the reason for this is because expenses have a bit more variability than income potentially so it's always wise to underwrite expenses on a t12 basis but if the property has seen a more recent uptick in occupancy or recent rent increases using the three months trailing of income numbers can help give a more accurate current picture of the property's performance so this here is a valuable tab to use to input our income and expenses so let's let's do just that so first what i like to do is i like to look at the market rent start at the top it's three five five four so here if i look it's in this formula here is calculating this it's just multiplying this with this and adding it and moving along and so here we're getting to 3.7 million nearly and the trailing 12 months shows me that they're only at 3.5 million with three and a half percent loss to lease so a true potential rent of three point oh i guess here it's actually they're saying a positive so what act actually these are below market rents and that explains why in the re in the unit mix it was showing higher in-place rents than market rents not the best example but we'll make it work so the gross potential rent then is 3.68 million 3.68 million looks pretty good to me if we look at the t3 3.725 so we're actually being a bit more conservative below the t3 which i like being conservative so we'll keep it at that and also i guess i forgot to we didn't fully go through the unit mix and update all the numbers so this could also change and not be quite as accurate so moving on we look at since loss to lease here is positive but we end up washing out we're just going to put zero since our gross potential run is taking into account not only the in place rents and the loss to lease okay and then we have vacancy which if we i'll use the t12 number since it's larger i tend for for income items or for for subtracting items always use the bigger number of any variables and then for expenses i'll always use the bigger number as well and then for income items positive items i'll use the smaller number so i'm basically just conservative all the way around so vacancy we're at 346 683 so i'll just put -347 i don't feel it's quite necessary to go exactly to the dollar especially on preliminary underwriting saves time hopefully it's less of a headache for you as well and at this point i'm just going to keep going back and forth reading these various numbers and so here i see concessions at 116 and then on the t3 concessions are 125 t6 130 i'll use the t3 so 125 minus 125 and then bad debt we have we'll say it's coming in at 25 thousand and then we have other income so we have utility reimbursements at 376 as well as other income at 139 so we'll go 376 plus 139 515 so i'll put 515 here and then i typically just bring that straight over as well to the stabilized assumptions but we'll deal with stabilized assumptions in a minute also another point to note is although it may be acceptable to jump around to the to the t3 t6 t12 for vacancy concessions bad debt loss to lease i think it's important to keep your utility reimbursement income your rubs income as well as all other income on a t12 basis because there's more variability in in that in those numbers and fannie and freddie don't let you get more aggressive and underwrite to more recent financials for those numbers anyway also bad debt is a bit more cyclical and discretionary so on a t12 basis it's best to keep bad debt to that as well okay so assuming we're done and happy here we obviously haven't tweaked with our stabilized assumptions but we're just going to move on to the expenses and try to be as quick as possible so here we have contract services repairs and maintenance pretty much the same line items and it's the same game just plug and chug we're just going to get all of these inputted in and on these i'm just doing the t12 because like i said expenses are best to be just on a t12 basis uh turnover can also be called make ready so just be aware of that uh same as marketing marketing can also be called leasing so whenever we see marketing or leasing we'll just put that in our line item here administrative at 74 000 and utilities total is 452 452 oops so insurance at 110 we'll call oops okay and so one thing you'll see is management fees yes they have them here and management fees are always a percentage of effective gross income so based on this number and so for us we don't really care that much what their management fee was because they could be using a friend as a property manager and paying them only two percent maybe one percent maybe maybe they're the self manager and they're not taking a management fee so there's not even a management fee to be found in the t12 the seller's financials um so for what we what we do is we automatically adjust the management fee based on what we would manage the property at so here's an assumption here are we going to manage or you know are we going to pay a third party property manager three percent three and a half percent four percent well whatever that number is it's we're going to automatically adjust the t12 and for that assumption because we care how expensive it would be for us to manage it we don't care what it costs them to manage it so we have a formula pre-built there now it might be helpful to note in the notes here what the management fee they have and the line item is so same thing for taxes we can note on a t12 basis 368 was their taxes so put 368. however you can see that in our t-12 we've automatically adjusted property tax as well for what our future taxes will be because again when we buy this deal we don't have the luxury of paying what they previously were for taxes we're going to be reassessed and have to pay more for taxes so to identify the true cap rate and income potential of the property we want to write off the bat adjust for taxes so that's what we do management adjust adjusted property tax adjusted t12 okay so now we'll assume all these numbers are good and assume we're not going to tinker with them on our stabilize just preliminarily we're just going to copy and paste them to our stabilized assumptions and how we're going to run the deal so look now we've gotten what our potential purchase price is as well as our income assumptions and our expense assumptions and of course we've compiled a unit mix with potential uh rent premiums and a handy way to double check what these rent premiums are is just to go to the summary tab and find this number here average rent premium 161 or 18.6 percent so that's a pretty hefty premium and i know we didn't go through the rest of the rent roll to actually identify what the real premiums on the property were but you can see that's a pretty aggressive premium just to give you an understanding so now that we've entered all the data now we can look at the assumptions and this is where things get a bit more challenging but we're almost done so for the most part we keep closing costs at one percent as a as a whole as we get closer and and we see the cost we can identify costs better that number is subject to change operating reserves is also a less tweaked assumption because simply we're taking the first months of expenses and we want to have that in cash day one so if we go to the monthly p l and go to the very beginning the first month has a hundred and seventy one thousand dollars of expenses we want to have that in the ca in the bank day one just to be safe and make sure we have operational reserves okay now here is these are the debt assumptions here so the first loan or the senior we can play around with here for now the standard is just 75 ltv we're going the full duration of the 10 years whether we're actually looking at the deal as a 10 year hold or not and then whatever the prevailing interest rates are and then we just you know typically we can get three years i o on 10 year paper 30 amortizations this is fannie fanning freddie debt this is just kind of our standard just to see how the deal stacks up now we might be doing bridge financing it might be floating and if it is floating a little tricky click on interest rate and you change it to libor plus and then you change this number to the spread not the all-in rate but we just want to see how a deal looks one on an unlevered basis and then two with just standard financing before we put on prep equity terms or mes mesdad or or assuming any supplementals or refi's during the life of the investment we just want to see how it looks on an unlevered as well as just a standard agency debt assumptions okay that's all i'll talk about there for now rehab assumptions so i have a formula here which is more of a placeholder not a strict formula guideline of how long it will take to stabilize the property and technically what we mean by stabilization at least within the framework of this underwriting is simply how long is it going to take us to get our rents from point a to point b obviously we can't buy the property and just increase rents by 100 or 150 dollars immediately and achieve it on every single unit especially when we're talking about 354 units so for this deal we're assuming based on my formula 27 months to essentially get from point a to point b now for some people they might think that's conservative some people might think that's aggressive you have to substantiate it but by who the team on the ground is going to actually be implementing and carrying out these uh unit interior renovations anyway so now the cost of doing so so typically our standard is just 5000 per unit that gets us pretty much where we need to be and then depending on the deferred maintenance or the exterior and amenity expenses that we want to do whether we want to install a playground or fix some of the parking lot or roof repairs foundation repairs um renovating the clubhouse that would be this line item so i believe in this deal we have a few already renovated units but as standard we're just assuming we're going to renovate all units at this price and then on top of it we're going to have a certain amount dedicated towards exterior and amenities as well as deferred maintenance also during our renovation period we're going to see higher vacancy as well as just lower income because higher vacancy because we're taking units off temporarily offline to renovate them and thus creating more turnover on the property and this higher vacancy assumption also helps to offset some of our more aggressive assumptions like getting lost lease burned off and reducing concessions here we're assuming we're going to reduce concessions so that's another way that lets us get comfortable and feel like we're underwriting more conservatively by again we can change this and we often do but as a standard this is 200 basis points above the stabilized vacancy assumption rate but we often make it 400 500 even 600 basis points above the stabilized so here we have seven bump it up to nine okay let's go to our stabilized assumptions i briefly mentioned the property management fee this is just who's your property manager what are they charging you plug it in it's good to get the tax rate from you can go to the county website or the oms will often say it if you're here for example and we look at pro forma notes here we have real estate taxes taxes are based on the 2018 tax assessment and a rate of 2.682 and we have 2.682 okay and then i won't get into how you assess the the future assessed value but this is very important but we just kind of have standard formulas here as long as you're seeing a modest bump from the t12 to where what we're going to be paying in taxes it's a good place to start next is the annual increase assumptions of of rent and expenses so this is what we have typically but sometimes we have to get more aggressive and use three percent sometimes we're in a market we feel less comfortable with or we feel like we just need a discount about the the underwriting and so we'll go two percent um so that's again more of a nuance and definitely important to the underwriting but in terms of building the foundation of the underwriting to then be modeled with and tweaked and worked on um that that's an important input next is vacancy rate again it's just knowing the market studying the market looking at the trailing occupancy and making our assumptions from there lost to lease something similar to that but we typically hold it standard at around two percent concessions and non-revenue units are very deal-specific bad debt is specific to the market so it's good to understand what deals are typically having in bad debt this reserves we rarely rarely change we put reserves above the line as well so it's in our expenses so it actually reduces our capitalization and our projected value of our properties when we exit as well as refinance so we feel that's just another way we're being conservative overall in our underwriting lastly our exit assumptions so as a placeholder we have the terminal cap rate widening by 100 basis points above the entry cap rate however that is just a completely terrible and oversimplified world view and we definitely do not abide by that rule uh in a in a hard and fast way at all this is just a placeholder so and again this is also a very nuanced input and something that is deliberated on a lot so not something just quickly input and move on and then these last ones we pretty much hold constant it's nice to see a deal play out on over five years up to over 10 years so we can or a shorter whole period even three but five is a good middle ground and then for longer term holds it's good to show investors a full 10-year term on an investment okay so i think that pretty much wraps it up on how to input the unit mix and the income and expenses one thing we didn't go over or at least show is the t12 so you know luckily we were able to use the om to grab t12 and t3 income and expense data however again we aren't very trusting of this all the time and it's very good to reference the raw numbers of the t12 and so here what i did is actually i cheated i've done this before is i go to view and i click freeze first column and now this first column here with all of the line items are is frozen and i can drag over and go to the total and more clearly see okay gross potential rent to this total and so on and so here are the raw numbers and from here i can take these totals for example total contract services take this copy and paste it over to our underwriting additionally what i did here is i'll do it again i go equals sum of the last three months multiplied by four so i annualized the trailing three months and then i click here and drag it all the way down to noi and now i can compare quickly the t12 versus t3 and make my assumptions and inputs accordingly okay so that is again a brief rundown of underwriting properties through our model and referencing the rent roll and the t12 thanks