How to Build a Back of Envelope Model from Scratch 1 of 2

Symon He
A free video tutorial from Symon He
Author | Investor | Entrepreneur | Stanford MBA
4.5 instructor rating • 11 courses • 227,474 students

Lecture description

Looking at the core assumptions can help you quickly assess the viability of an investment.

In this lecture you will learn to build a robust, "back of the envelope" model from scratch to very quickly assess a rental income property.

**Model Included (Note that there are TWO models here - one is the same as the one in the video where for simplicity reasons, I've assumed that the renovations are financed.  The alternative assumes the renovations can be covered with cash).

Learn more from the full course

Fundamentals of Analyzing Real Estate Investments

Learn professional investment analysis techniques for real estate investing in residential and commercial properties

18:34:48 of on-demand video • Updated December 2020

  • Confidently Evaluate the Return Potential of Any Real Estate Investment Opportunity
  • Know a "Good" Investment from a "Bad" Investment
  • Use Professional Grade Investment Models to Evaluate Your Deals
  • Evaluate Rental Income Properties
  • Evaluate Fix & Flips
  • Evaluate Commercial Properties
  • Evaluate Airbnb Properties
  • Use Smart Investment Deal Structures With Business Partners
  • Understand and Use Professional Real Estate Investment Strategies & Techniques
English [Auto] All right now we're going to build an investment model for the rental income property from scratch almost from scratch. I have this model here that you're going to be able to download and you'll see this it's it has two worksheets one says eat blink at the bottom right here and then the other one says Bill Dutton's we're going to turn the blank one into something that looks like this. Now don't worry again if you don't understand what some of these things here mean and what these other things are. I mean they explain that as we build it right now and in the rest of the course we're going to really do a deep dive on how to do this. So this is for you to get a feel for how someone with some experience might be able to do a really quick investment analysis for rental income property. All right. Now let's get started. So when we do investment analysis we always want to look at assumptions especially assumptions that affect the cash flows. Since we're going to be measuring cash flow we want to look at what are the things that are relevant to looking at the cash flows of rental income property. Well let's start with the basics for rental income property you're collecting rents so we want to be able to account for the monthly rents. So maybe for this Vegas single unit rental it's in a decent area in Vegas and looks like rents are around 12 to 13:00 So let's put 13:00 there for now. But when you're holding a rental income property especially over a long period of time a lot of times you're not going to have a single tenant there during the entire duration of your project. What that means is you're going to probably have some turnover. And during the periods of turnover you're going to be vacant your unit isn't going to be collecting rents. Let's let's make an assumption about vacancy or how many weeks. The union is going to be empty during a given year in that area. There's a lot of what we call transient renters a lot of single working professionals who don't have families. They will go wherever their jobs are and so they tend to move pretty often in the area. And so it's a safe bet let's say eight weeks of vacancy. All right. And property taxes. Well every If you own a property you're going to have to pay property taxes. We're going to calculate that a little bit later. But in that area I believe it's about 1.1 to 1.2 percent of the assessed value. So why we're not going to put in the assumption now is we want to base the property taxes based on the actual price that you pay for the asset. So we'll get back to that. Now repair maintenance that's pretty straightforward. If you own a property things break down especially over time. And as a landlord you're going to have to fix things. So a good way to do that is simple way to do that is to set aside some sort of allowance every month for repairs to pipes to appliances for electrical whatever it is. Things will break down over the course of time and will be replaced so it's get to factor that in in your investment analysis. You don't want to assume zero because it's going to happen over the course of seven years. For something like this maybe something conservative would be putting in 100 bucks a month as an allowance. Now when you own a property you want to get insurance as well. Property insurance in case something happens to that to protect yourself. So for something in that area 450 is probably pretty conservative. You may be less given the house values but let's put 450 there and some other things that can kind of subtract away from the rents that you can collect. Right. Basically your cost of owning it are could be utilities. If Especially if you're giving utilities to a tenant in terms of an allowance just to entice people to move in. But there's a lot you know the rental market is pretty healthy. Let's assume that the zero. All right now let's get back to our example. Remember we said we're looking at something that's around a hundred twenty thousand in purchase price but purchase price although it is typically the biggest expense item or the biggest cost of getting a rental property. It's usually not the only thing. I mean especially even just that purchase you're going to have some closing costs involved so things like extra fees and such. So we're going to put aside some amount for that and this will vary depending on what state you're in. So if it's something there that will be reasonable to cover that. So let's go back to property taxes property taxes. Remember we said let's assume like one point two. Let's go one point two percent. I think it's one point to be one on one but what would just go and point to you to be conservative times the purchase price and that's the amount that we're going to be paying about a year. So we want to factor in property taxes because if you only factor in the rents that you're going to collect your artificially inflating your returns because these are costs and expenses that you're definitely going to be paying when you do. OK. So let's look at the next part. Well the next part here has to do with if you look at the headings here. There's a down payment a loan amount an interest rate and in the long term we're talking about a mortgage. We're going to revisit that later. Let's talk about the other costs items first. So recall that we're buying this rental income property and we're going to put some renovations in. Now when you buy a rental income property some times it can have more renovation or less renovation but there's really kind of two main times where you can do your renovation either. Right. When you buy it and there's something wrong with it you need to renovate it in order to bring it to the market level so you can attract the tenants and get the highest rental rates or and or do some renovations right before you sell it so that you can fetch top dollar in the market. So here we have two headings here we're going to say let's let's set aside time to do renovations for both the beginning and the end. OK. And we said in the beginning we're going to put 5000 renovations you can put something more substantial. When we sell it maybe. All right. And now with the investment analysis putting all these numbers in it doesn't mean these are the right numbers. We just want to put something in that you think it's reasonable as a starting point and then we look at where the analysis takes us and then see what it's more sensitive to and see what assumptions. If you're wrong could have a significant impact on the analysis. So we're putting all these numbers here right now just to have a baseline just have a starting point. OK. Now one more thing about the rents is that if you're holding it we're pulling for seven years. Right. So for seven years the rent that you're likely to collect later on is going to be higher than the rent that you're going to collecting years wanted to rents tend to go up. So we want to account for that as well. And one of the simple ways to do that is to assume some sort of a rental growth rate that grows on an annual basis. Kind of like an interest rate. If it's $100 and it grows at 3 percent then in year two it's going to be $103. OK. So some conservative assumptions for rental growth rate might be 3 percent a year. OK. Now you don't get to have your returns you can't complete an investment analysis unless you also know what you're going to sell it at. Right. If you sell it for less than you bought it and you may lose some money especially if you're not collecting rents for a long time. But if you sell it for more than what you bought it and you got some appreciation there and that will how your returns in addition to the rents that you collect during that period. So we need to make an assumption about what you're going to be able to sell it. And if you bought this set a hundred twenty thousand you'll see that in later analysis there's different ways you can try to think about the sale price. So one way to look at that is well do you think it's going to appreciate on an annual basis what kind of appreciation would you want for it to appreciate. Or do you believe is going to happen. Right. This market has experienced double digit growth in recent years but I think it's going to slow down a little bit in the coming year. So maybe it will grow at it will grow at maybe 8 percent. So the way to do that is I'm going to go 1.2 0 8 so that's kind of like 8 percent a year. Right. And I'm going to raise atus seven years in and then I'm going to multiply it by the first year. So basically what this is doing is saying let's start with the first year let's grow it by 8 percent. But seven years in a row at 8 percent. Now we should get some value that's higher. 8 percent might be aggressive it's kind of high for that market. You know I think I want to be a little bit more conservative. I'm going to put it up 5 percent. All right so now if I put it at five it's going to be starting at 120 and it's going to grow 5 percent a year for seven years and that will take it to about 170000. That sounds kind of more reasonable to me. And now just like when you buy a closing cost when you're selling it as a seller. Usually you want a broker to help you market the property and have them look for buyers and try to help you get top dollar. So typically it will cost somewhere between five to six percent for commissions for the broker. So you want to account for that as well. I'm going to do 6 percent times the sale price now now actually we have it 10 percent. So just leave it in percent and account for that later in the models. OK. So just say six percent is the cost of sale and then sale price under 60. So you can use a formula like. Like I had like this to kind of get a sale price to get you some kind of in the ballpark. And then what you can do is you can just overwrite it with a value because 1 6 8 8 5 2 is really exact We're just trying to get a ballpark maybe Barbara it would just be 170000. OK. So now that we have all these numbers in here for kind of what would establish cash flows right. You're the initial cash flow out is related to your purchase price. Now imagine right now you're buying it all cash then you're going to be putting in 100 24 22000 Abiud you put in another five grand in the beginning to renovate it and then later you're going to be spending 10 grand towards the end. So those are all the cash flow items. But then there's cash coming in. Right. Rents minus all these adjustments over on a monthly basis over seven years you're collecting all those rents and then you're getting the difference in terms of the profit that you have when you sell it as well. So those are all the cash flow coming in. And if all those cash will come in is more than what is going out to a degree that is reasonable over that period of time. Then you may have a good investment. So the key there is how big that difference is over what period of time. OK. Now we have those set up. There's one more thing we want to account for in this analysis and that's whether it's going to take a mortgage or not. OK most of the time as an investor even if you're buying a rental property or cash you probably want to refinance out a portion a good portion of it with what's called a permanent loan or a mortgage so that you can keep collecting cash flow but you're not tying your capital it's not locking your money on this property. So if you bought it all cash in order to get a good deal then you take a refinance it take the cash out so that you can use most of that cash again to find another deal. Once you find a tenant for this month. So we're going to make that assumption as well. OK so for that down payment we're going to look at the percentage that you're going to pay down. So let's just say 35 percent OK. And in the loan amount it's going to be the difference between what you need minus the cost. OK. So we're we're going to roll for simplicity here we're going to roll the cost of the renovations into the loan. OK. So that you get a bigger loan so and so nice you can do that with lenders sometimes you can't. But for example we're going to assume that you can. OK so we're going to say the total loan amount is going to be the sum of the purchase price. The closing costs the renovation. OK. And let's let's borrow for the later renovation also. Typically wouldn't do that but for this example for simplicity let's just add up all the costs. OK. And then we're going to subtract the down payment. Now we have a total loan amount right now for a permanent loan for investment property depending on your credit and where you're at. You can get anywhere from like 4 to 6 percent. So let's get five percent interest rate in a permanent loan is usually 25 30 years. So let's do 25 years. Right now. So now that we have all of these assumptions in here we got to start looking at well what does the cash flows look like. On an annual basis. So let's start with the first ones you see at the bottom here there's laid out the headers for seven years and then there's the investment part. So this is to capture when you actually may be investment and then each of the numbers for each of the corresponding years will help us capture the value of all of those line items like how much rent you're collecting how many expenses for each of those items are there and when you're getting the proceeds from your sales and when you're spending renovations. And then we can find a total cash flow line in the calculations. OK so let's start with the rental income for us. OK. You're not collecting rents when you're investing so we're going to start with year one in year one where you are going to collect the total rents of 3300 times 12 months. So that's kind of gross for. That's what you could collect. Right now we could collect that much but we're going to have eight weeks of vacancy. So what's eight weeks of vacancy eight weeks a vacancy for the year will be eight divided by 52 weeks. That's how much is vacant. Times The rent out so that so that gives you the percentage of the year times the rent that you're going to lose. And because this is something that we're going to be losing let's make sure we designate that by making a negative. OK so out of a possible 1300. Fifteen thousand six hundred dollars in total rent. If eight of those weeks are vacant then you're going to lose 2400 in in rents. OK. Now property taxes for the year we already calculated that. So we're just going to make it equal to what we calculated. But again it's a it's a minor it's a subtraction right. It's something that we're going to be taking away just visually that will we'll make sure we can see that. OK. You learned quite a bit about setting up an investment model but we're not quite done yet in the next lecture we're going to finish the rest of the calculations and show you how we use those figures to calculate the measures of return metrics that we discussed earlier. And see this investment makes sense or not.