Best Spreadsheet to Analyze Real Estate Deals

In this video, I'm going to show you how you can actually take a property and plug it into the rental analysis and rehab form so that you could estimate what your cash flow would be year over year, and track to see how good of an investment this property would be.

Ariel Herrera 0:00

Are you looking to be a real estate investor or perhaps you already are, but you want to get organized to assess and compare different rental properties. Ultimately, as real estate investors, we want to be able to invest in properties that give us cash flow.

So there's expenses and income that we must record. In this video, I'm going to show you how you can actually take a property and plug it into the rental analysis and rehab form so that you could estimate what your cash flow would be year over year, and track to see how good of an investment this property would be. My name is Ariel Herrera with analytics aerial channel, we bridge the gap between real estate and technology. I believe in data driven results in order to assess the quality of your investments. And if you do to them, please subscribe to this channel. And like this video if you want to see more investment analysis via Google Sheets, so I know to make more of it. All right, let's get started. In the summer of 2020, when the pandemic hit, I decided it was going to be my move from the east coast up north down to the east coast by Florida. And I chose to live in a suburb relatively close to Tampa called river view. My whole goal was to be able to live in the property for a single year and then rent it out. And I wanted the property to be a turnkey property. So what that all means is I was looking for a property that barely had any work to be done. And I was going to be able to put only 5% down because I was going to live in the property as an owner occupied for just one year. Riverview. Florida, if you're not aware is a suburb town outside of Tampa, and Tampa is on the left hand side of Florida, directly across to the left of Orlando. So when looking at this town, I looked at many different factors which check out the Redfin video on how to analyze the market if you want to see more detail on how to look at different metrics. But overall, this was about two years ago and 2020. And if we see here on the right hand side for Riverview homes, were going for about 250k on median. since then there's been a huge growth as a lot of people have moved down over the last two years, where prices have almost doubled for houses and appreciation. Now, this property over here 3911 is very close to the property that I purchased, it was built in 2018 that sold and 2020 for 270k. And if we take a deeper look at this property, we could see that's pretty much turnkey, it has a driveway, a nice entrants, and none of the walls are stained or any issues, everything looks practically new. So in this instance, I was looking to analyze different properties within Riverview to see which one I was going to ultimately invest in. And for my criteria, one of the main things I was looking for those once I leave in a year, and I rent out to a tenant, I want to have positive cash flow. So my expenses that I have, including my mortgage insurance, property taxes, I wanted to make sure that what I'd be receiving from a tenant would be over that. But my question was, how do I find the right tool that's going to show me what my cash flow looks like, and also how that's going to look like not just year one. But year 30 as well. In my search, I found the single family home rental analysis by Jay Scott on bigger pockets, where he provides an Excel file that's super useful, so that you could actually plug in some of your metrics that have to do with the property such as the purchase price, downpayment, percentage monthly rent, and off of that there's formulas that are calculated so that you could see what everything is going to come out to and ultimately what your cash flow is going to be. I modified this spreadsheet a bit and ultimately created something on Google Sheets, which I think is a lot easier than using just an Excel so that you could use this too. So let's walk through the rental analysis sheet. Right now in this walkthrough. We're going to imagine that we're back in 2020 when I was originally purchasing my first out of state property to live in and how I went about entering in the right information so that I could see what my cashflow, income and expenses would look like year over year. So some factors that I noticed when I was looking at properties on sites like realtor and Zillow, what was the listed price, so here listed price. Imagine that that was there and it was on market. Then I also look to see what the square footage was as well as what the tax history was. Then I ultimately compared this listing to other similar listings to see what the rent was going for. And I use sites like rental meter to do that. So for example, we can copy this property address And we could enter into rental meter and paste it. Then we could select how many bedrooms and bathrooms there are, in this case, it's four beds. And if we go back, we could see it's three bath. So we'd enter that, over here that's over one and a half, we could select our look back period, building type, which here would be a house and then analyze this property rental meter basically takes different listings around the area and come up with an aggregate of what the median listing would be, as well as the average, I like to go more towards the median, because it is not sensitive to outliers. So for example, if there's a property that was pretty distressed, so maybe it's a little bit older, and wouldn't really get the best tenants, that property may be rented out for 1800. And it's an outlier, because most of these properties should be around 2400. So that's why I kind of lean away from average and go more towards the median. If you wanted to get more information on getting rental data and getting it programmatically please check out my prior videos on that. So let's go over the spreadsheet. Everything in yellow is what you need to populate. So under cost assumptions, you'll have your purchase price. So what you agree to pay to the seller, land value and building values typically 25% of the purchase price, which I already have some automatic calculations to get that then improvements. In this case, I was going for something that was turnkey, turnkey, meaning that it needed no fixes as of 2018 built home and 2020. I purchased it so it's practically brand new. However, if you wanted to do more of an analysis on improvements, maybe you're getting something that's older, then you could use a rehab estimate tab that has a selection of different types of expenses that you may occur for improvements, such as exteriors, interiors, and map systems. Some of these prices may be a bit outdated, depending on the cost of labor material, but it still could be really useful to work with next would be closing costs, which is typically about 3% of the purchase price. So I have this automatically calculated as well, you could always override this with your own number two. So there's three here fields that you populate. And the next would be your downpayment. So in my case, I was owner occupied the property, meaning that I was living in it as my primary home. And that case, I only needed to put 5% down. If you're an investor looking to get a property strictly as an investment, you will likely need to put at least 20% down, that's for a single family property. And for multifamily likely 25% or more. After that, we have the financing downpayment amount automatically calculated, and also you need to input your interest rate. So my case interest rate was 3.5%. And my mortgage was for 30 years. This total is to see what your cash outlay is going to be how much in total are you going to need to put down. So in total, I was able to purchase a property that was 270,000 plus additional closing costs. And I had to put down 21 by 21k. To do so. So not too bad. In comparison. If we look and change this to 20%, you'd see that I need to put $62,000 down, which could take a lot longer to save. And by the time I save it, this property would have already been over 400k Great. So the next thing you want to put is the monthly rent per unit. And that's what I specified for rental meter. You could also go to Zillow, and see what they have as the rent Zestimate. You could look at Realty mogul as well. And you could go to other sources like Facebook marketplace and search for your area what rents are going for as well as Craigslist. Then I have here the vacancy rate, which you could modify. Typically vacancy rate is about 8%. So we're thinking about one month or so every year, you're going to have to make up the mortgage yourself because tenants are turning over. And one of the main reasons that I wanted to modify Jays Jay Scott's original file was that it could also be handled for multifamily. So I have here where you could input number of units and the average tenant state in years. That way the vacancy rate could be adjusted off of that. Next we have square footage and per square foot per year insurance assumptions which I plan in the future to have the insurance automatically update based off of this. But as of right now that's manual. So before we touch on to the cashflow, and what these two different sections mean. Let's go through the last items that you have to input into this Excel file. So on the left hand side we have an assumption that our annual revenue increase will be about 2% every year and Next we have property taxes, which if we go back, we were able to get property taxes towards the bottom in our property history. Just note that, depending on your area, you may see property taxes, say at 7000. But once the property is sold, that may jump up to 14,000. It all depends on how long ago it was sold, and what the last assessed value was for the property. So please be aware of that. So after property taxes will be insurance, which is a manual input, next Maintenance Group and repairs annually, which may depend on the type of property that you have. And as well as the age after that is the variable costs of property management. So a property manager is an individual or entity hired by the property owner to oversee and manage the daily workings of the real estate investment. Basically, someone who doesn't want to take care of their own property or has other things going on will pay someone else to do so instead. And a typical fee is about 10% of the monthly gross rent. So if you wanted to include that here, you could talk to different property managers and then input what their percentage fee would be. Next tenant placement fee,

which I have as a strict value of 650, annual tune up inspection, any HOA dues which in my case, I had an HOA do sewer service, monthly lease renewal water and lawn care. Once that's complete, we're able to see our total expenses for year one, as well as the expenses as a percentage of the gross income and our noi, we have our mortgage, which is extrapolated by 12 months. And then we have our total cash flow, which in this case, our total cash flow for the year would be $3,178. And in order to calculate our cash on cash return, what we do is we take our cash flow divided by our total investment. And in this case, our cashflow is 14%, almost 15%, which is huge. Then we could look at how much equity we accrue year over a year and see our total return and total ROI. If we focus on our total cash flow, we could see this number increasing year over year. And you might be thinking, Well, how is this continuously increasing, and it's because we're assuming that our property is able to be rented for a higher rate every time we have a tenant turnover. In addition, I added to the top here, two different scenarios. One is if we outsource the property to a property manager to manage. So say here in the cell where we have property management, let's say we did have someone else look after the property. Once we rent it, we can put say 10% here, and we'll see now that we have some high costs. And if we look, our cash flow went down to 2.58%, which would not be a good deal. Our annual cash flow would be $500, which is not good. But if we actually manage this property on our own, our cashflow is at about 18%. This is because we add back the cost of the property manager the tenant placement fee inspection in this case, assuming that maybe I'm doing an inspection on doing some tuneups and the lease renewal fee, I do plan to eventually turn this into a programmatic worksheet where you'd be able just to enter maybe the address and how much down payment you expect to put down. And automatically the spreadsheet would generate, if this is something that you're interested in. And please leave comments below so that I could prioritize this ahead of some other tools that I have in the pipeline, as well if you have your favorite spreadsheets that you'd like to send my way so I can help automate that as well. Then please let me know. either email me at Ariel Herrera analytics erielle.com or leave it in the comments below too. If you haven't already, please subscribe. Thanks