Hi All! I created a single family rental model because I'm interested in creating some rental income down the road. My model is attached. It's annual rather than monthly, and my assumptions are noted in the cells. The red text contains the input variables. Tear it apart. Constructive criticism, or just criticism. I want to make sure I include everything that will move the needle in the model before taking into account demographic/geographical considerations.
| Attachment | Size |
|---|---|
| rental_income_analysis.xls 74.5 KB | 74.5 KB |
Everything looks good/fine. Not super dynamic and everything is on one page. Create a separate amort page for debt, include ability to pay back principal. Make the layout the size of the computer screen, what I mean is have your inputs going more horizontal and not vertical, easier to see for the user. Include Price Per Square foot ratio.
Yeah, you use a 30-year horizon. Even companies who hold buildings forever use a 10-year time horizon max. I couldn't easily see how the deal pencils out. Where was the IRR, COC, Equity Multiple returns?
This was just what came to mind within a minute, and sorry it wasn't in too much detail.
Thanks for the suggestions; I really appreciate it! I'll make those changes.
-Change inputs to blue. -I think pretty much all of your inputs are optimistic (rent especially, I'd think it'd close to $1200 per month max). -Would build out a debt schedule showing that shows how much principal is paid down vs. interest. You have some formula looking at equity gained or something, that's principal paid I think. You're not really gaining equity, you're just paying down principal. -Model in some exit assumptions ie exit at a reasonable cap rate at whatever year you want. Make that year dynamic. -There's no returns analysis. Add IRR, NPV, COC, etc.
To further clarify the point on repaid principal (which you are displaying as accrued equity), this "accrual" of unrealized equity should not count in your return calculations until they are realized (i.e. upon sale or refi). Think of it this way - sure you are gaining theoretical equity (RE guys really dont think of it this way though), but really you are using cash to pay for those principal paydowns so your accrual is worthless until the back end. When you sell the property (or refinance) and gain the difference back, that's when you actually have the cash, and therefore when it should affect your returns. Otherwise echo others comments.
Thanks for the advice brosephstalin! I liked the idea of including "theoretical equity" as you called it because if I know my equity, I can use that to pull out additional capital to fund future acquisitions. Thanks again for the input.
Gotcha, thanks for the suggestions. I wasn't planning an exit strategy - more so just building a portfolio of single family rentals. I really appreciate all the suggestions! I'd definitely incorporate them into my revisions.
I understand the need for a downpayment amount - and that some people actually require these in deals. However, I've seen over $300m in RE development deals and we've never had a downpayment once for one. Again, might be needed at some point but I generally would stick $0 in there as an assumption and probably wouldn't even include that line in there.
Just don't close a deal or take the DD period for 30 days to 65 or 90 days so that you have enough time to line up your equity and your financing.
Seriously? That's insane and makes me really concerned about a possible CRE bubble.
How the hell is that possible? Seems like anyone could do any RE deal as long as they a strong case that their NOI can cover debt service.
Um, I agree with Greg,
Now I'm really confused. Downpayment is equity, and you are saying you have never done a deal where you put down equity to acquire a property. Even if it's ground up, the inputed equity in the land will only be counted so much.
I'm not gonna pretend to know anything about the SFR market but a 15% in-place yield seems too good to be true.
Agree with some of the other suggestions above about results, exit value, etc.
Would also suggest making a bit more dynamic, not a single IF function in there. Add in a lease breaks every 2-3 years, depending on input, with a void or something. Also when modelling never list your period as text (Year 1, Year 2, etc.) as you generally use these cells as drivers for a lot of different factors and is better to have them as numbers.
Awesome, thanks!
First off, you should open your search to 2-4 Units. Better returns depending on the market. Multiple sources of cash flow. Financed as residential properties and appraised based on comparables rather than income. With SFR if you have vacancy you're going to lose money for sure. I'm currently looking at SFR and 2-4 units daily so here's my thought.
Years: No need to look 30 years ahead. Look Years 1-5 or 10 and base your analysis. 30 years down the road is too far out to be accurate.
Improvements: Might be a good idea to have another sheet where you can quickly input improvements such as kitchen, bath, paint, lighting fixtures, doors, drywall, etc to get a property to rent condition. And there may be some sort of turnover expense every year
Income - Should you be basing income off a per bedroom & bath number/comparable rents in neighborhood/psf? Different towns call for different measures
Other income - Not sure in SFR but in 2-4 unit you might have laundry.
Vacancy - Assume 8-12% or base your vacancy on 'Days vacant per year'. SFRs take longer to rent. Have you thought about who rents in your neighborhood?
Property Tax - What state are you? How are taxes calculated? If you purchase, do taxes reset at your purchase price? Will they go up every year in reassessments? Did you count taxes for school, municipalities, county, and city prop. tax?
PM - Always assume property management (if you self-manage, your time is worth money). They will probably take one month rent or 1/2 month rent + 8-12% monthly off rented income. They will charge $50-100 to do a repair as simple as turning off the water on a toilet that's leaking. Assume something like 10-13% from effective income
Utilities - Break out sewer/water/gas/electric/misc. costs. Different areas of town require landlords to pay for different utilities and allow tenants to have the burden. If you have to pay for Util., beware because they could skyrocket.
Repairs - How much deferred maintenance? Dishwasher/no dishwasher? Other items that could break? Age of plumbing, roof, furnace, water heater? Do you need to budget 5% of monthly income or 10% because the house is older and needs maintenance
Insurance - Are there things about the house that will become uninsurable? Make insurance more expensive? For instance, are there railings that are too short/inadequate where the house can't get insurance? What about flood insurance if you're in a flood hazard zone? Fire insurance?
Other expenses - Snow removal? Lawn care? etc
NOI - Good
CapEx - You forgot capex. I'd breakout a separate sheet with all the big ticket items on a house. One column for current age, another for cost, another for the expected life in years. Should calculate the per month(per year) $ to set aside to replace items like roof, furnace, boiler, water heater, etc etc.
Mortgage - I've always wanted to build a good model which allows me to input Year 0 purchase at ### mortgage at % LTV, but year 2, 3, 4, etc refinance at a higher appraised $$$ at a higher % so you can pull equity out.
Equity Accrued - BS number. You don't see it until the end like someone else mentioned. You may 'gain' equity, but property values move all the time. That $2-3k a year could mean nothing if your value goes down.
Cash on Cash - Shouldn't this adjust every year based on how much (cash outlay year 0 + year "X" capex) goes into the property every year?
Returns - Include IRR
Sale price - Comp out other properties and made a judgement on value increase (decrease) at year 5 or 10. Or 30 if you really want to go that far.
Have a amortization table. I like the loan payoff section.
From a modeling perspective you basically just want to know
That's all really. You could just use a pen and napkin really.
This is solid advice. In general on the expense side I would not model these based on % of PP, as the seller should be able to provide you with Taxes, Insurance, Utility Bills, and repairs/maintenance from the prior year. I would model based on these actual expenditures to get the most realistic scenario/returns.
Wow, thanks for all of that! Super helpful!
Can i have a copy of your model
Is IRR calculated based on Cash Flows (after debt service) or NOI? Sorry if that's a very simple question.
Where are you getting that Mortgage rate and 90% LTV. Unless you plan on living there for 6 month and getting a traditional mortgage your not getting anything close to that. The best i have seen is mid 70% at low to mid 5% Rate for cash flow producing SFRs.
I also prefer making my years = numbers i.e. F32/F36 would both = 1 rather than "Year 1" and then I would custom format the number to display as "Year 1" - would make your HLOOKUP formula's a little quicker to type (no "&" needed)
Concur, and use edate as well.
Random question, i'm lazy and haven't created a 2-4 unit model since I more so keep it simple or use biggerpockets calculators. Does anyone have a 2-4 unit model buy & hold, buy, rent, and flip or SFR flip model they've come across or made themselves?
your debt+equity in this model only adds up to the purchase price... what about the closing costs and 'improvements'. you do not have enough to purchase the property.
I also noticed that your formulae is not consistend across the rows. How is the model looking now?
This is for personal investing, correct? If so, this is a pretty good model. I get a chuckle out of the people who tell you how to make it look more presentable, inputs in blue, assumptions tab, etc... This isn't an IB pitch, formatting doesn't make him more money. (my favorite might be the criticism that the model doesnt include any IF statements)
While the model is good, you've gotten some good feedback on the assumptions likely being optimistic. I don't know what market you're looking at, but where I'm familiar with $1,875 is a lot of rent on a $150k sfh. Also, you'll probably need 20-25% down payment and slightly higher interest rate if going conventional.
You DEFINITELY need a vacancy assumption. I suggest 8.3% (1 month per year), but that would hopefully be conservative.
Once you're looking at actual properties, you should be refining those expenses, especially property taxes. You probably already planned on doing that, but I just wanted to be sure.
My only other recommendation is that if cash is going to be tight you might need to do the first 2-3 years monthly. If you start vacant and needing repairs you need to be prepared to absorb that.
The blue font is for inputs and helps everyone remember(including the analyst) which inputs effect the outputs. I've never has a senior guy retool my model, they just ask me to make changes on assumptions so the blue helps the analyst not the boss.
These changes should be made for his own editing ease, nothing else. Also when the model is dynamic it is good to see which changes take effect on the same page, like how the return metrics differ when you change the debt terms, and you do this without having to scroll.
I understand that. My assumption is that this is for personal use, which, if that's the case, who cares if its blue or red? As long as it works for him and helps him screen properties it's serving it's function.
Vacancy rate of 0?
Are you sure you created this model? I've been using the same one since 2013, which I received from a forum member on Bigger Pockets.
Positive. I found similar models online and combined 3 or 4 of them to make this one, but it's clearly still a work in progress with all the feedback I've been receiving.
I can tell you for a fact that for a 150k property you'll be getting 1000-1200 a month in gross potential rent, MAYBE, depending on your area. What MSA will determine this, and even further, proximity toward central cities, schools, highways, shopping centers - I'd do more research on your rental assumptions. And definitely only go out 10 years.
Omnis assumenda ullam deleniti exercitationem nobis ad nemo. Voluptatum sit sunt praesentium sapiente aperiam commodi. Et non aperiam quo. Aut labore et aut nisi nobis tenetur consequatur.
Voluptatem non amet dolorem. Aut rem reprehenderit error ut omnis et vitae. Adipisci aut accusantium rerum.
Nemo dolores qui sed ut delectus autem. Voluptates ut in dolor blanditiis officia et. Fugiat voluptas voluptatem voluptatem accusamus similique aperiam quisquam neque.
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...
Porro deleniti voluptate itaque doloribus atque expedita deleniti. Ad eveniet odio cumque. Rerum nihil labore sit consectetur incidunt aut in.
Commodi ut numquam magnam. Facere enim id a modi quo assumenda et. Fugit qui et rerum vel. Eaque fugiat aliquam aliquid necessitatibus inventore molestias et. Impedit ut similique officia quo rerum. Enim autem maiores hic similique quis.