BiggerPockets

Too complex. I used to have one very similar to that. Read in the Rental Property forum about the "50%" rule.

Tax benefits are overstated. Unless your income is under $100K and you only plan on a few properties, you can't take the passive loss. Buy properties that cash flow with the 50% rule and let the tax benefits shelter the rental income.

Its really very simple. Gross monthly scheduled rent must be at least twice the P&I payment to break even.

Do your due diligence to make sure there's nothing going on with the property that will make expenses (which includes vacancy and capital items) more than 50% of the rent.

Looks good so far.

I would argue the opposite of Jon, that it is too simplistic.

While a real estate model is only as good as the assumptions behind it, with practice they can become hugely powerful and an additional tool to add to your arsenal.

You can always add an override function that allows you to use the "50% rule". I would never suggest to use the rule itself but there are times when being able to apply a single override can be very helpful.

Example: after you final expense A37 add a new row. Make row 38 your override row.

Add an "if statement" like the following to your expense sum cell D39. =if(B38>0,B38*D25,sum(B28:B37)). What this will allow you to do is enter a percentage and override any values you have entered in the expense. It will take the percentage in B38 and multiply it by your gross income to calculate a "bulk expense amount" based on your expense ratio. Hopefully this makes sense.

I've built many models and each one is used differently. If you plan on expanding on this particular model and adding things like sensitivity analysis or any type of simulation then I'd back track a bit and create separate input screens that then feeds your actual model. Having separate input screens will really help in the long run if you want to constantly add new components.

I always try to separate primary inputs (independent variables) from the actual model so that they are easier to organize and find if your model continues to grow. For example, on the first sheet you will only enter primary inputs (ie. purchase price, vacancy, discount rate etc). Then the second sheet is the actual model (Discounted cash flow, etc). The third sheet might be a sensitivity analysis or case scenario. The forth sheet can then be a printable report or summary.

The primary inputs will drive everything from the DCF to the sensitivity etc. If you inputs are all over the place, you end up having to go from page to page searching for the inputs. Additionally, with complex models you will constantly be fine tuning and there is nothing worse than deleting a primary input b/c you forgot that an independent variable was embedded in the model.

Let me know if this doesn't make sense and I can look through some old spreadsheets and try to find an example.

D.

The problem with a complex analysis is that you have to make too many guesses. With enough guesses about this future variable and that future variable, you can sex up any deal to make it look good. A simple analysis cuts through all the chaff.

If you're trying to pitch deals to people who have some finance knowledge, but don't know much about real estate, then use a complex spreadsheet.

If you're just trying to make a call if the deal is good or not, the simple analysis is sufficient. If it doesn't pass that test, but looks good with a complex analysis, then your complex analysis is most likely making overly optimistic predictions about the future.

I do agree with Dan's statements about separating inputs from outputs.

To say that financial modeling is nonsense is a pretty ignorant statement. Financial modeling is a very useful tool when used correclty. No different then any other real estate investment technique.

There are many investors who are very successful just looking at the basics (ie. a Warren Buffet). But there are those who only look at the complex algorithms & numbers (ie. Renaissance Tech.). To completely dismiss either is silly. Both are powerful and both have uses.

To say the "only thing that matters is making money" is stating the obvious and not very constructive. I've never met an investor that is trying to lose money. Stating that investing is simple is also not very constructive. Real estate varies in terms of complexity and completely depends on the circumstances.

I'd argue that financial modeling allows you to better understand the actual business and help enable an investor to reach the obvious goal of making lots of money. I've yet to meet someone who has a strong foundation in financial modeling who will take assumptions lightly. I bet if you ask a large subset of this forum what a typical operating expense is they will respond with 50%. Vacancy? 5%. But those are only "silly assumptions" if they make their way to a complex computer program.

Bottom line is financial modeling has a time and place. So does the 50% rule. To say one is worthless is narrow minded. Don't limit yourself, knowledge is power.

Also, for many individuals, excel is not a "complex computer program". Modeling a real estate investment is not equivalent to modeling the motion of a particle (ie brownian motion). A complex real estate model by comparison should be extremely straight forward & easy to follow. Additionally, I've yet to find a person who can "paper napkin" an investment with a HP12c or HP10B faster than someone with financial modeling experience. If modeling increases efficiency than it can also help them "makemoney".

In regards to assumptions, this is always the main pitfall of any financial model. Garbage in Garbage out. However, if you are entering "silly assumptions" than you do not understand the underlying investment or market and are destined to act upon and base your decision on inaccurate information. No real estate investment can be made without assumptions being made. Not entering assumptions into a financial model doesn't protect you from a poor investment decision. I've seen a huge amount of developers sponsor huge projects based on "silly assumptions". The only difference is that the developers never put the silly assumptions in a model, they simply vocalized them. Perhaps putting them in a model would have allowed them to realize how "silly" the assumption really was.

I do use a slightly more complex model than the price, rent, 50%, PITI model I often use when someone asks "is this a good deal". That's because that very simple model can answer that basic question, but can't incorporate my other personal factors.

Here's my more complex model. This one allows me to adjust the 50% ratio (because I do management myself and am willing to do it for free) and allows me to adjust the desired cash flow (also not an immediate factor for me.) This model also lets me incorporate estimated rehab expense. It spits out a max price.

In addition, as a second level of analysis, this sheet adds the ability to consider a conventional loan with a down payment. It recalculates expected cash flow and cash on cash return.

I actually have a yet more complex model I've not stuffed into Google docs yet. This one incorporates the cost of using hard money to buy and rehab a property and then refinancing it into a permanent loan. It also considers the ARV and calculates the max price from both the ARV and the rent and gives you the lower of those two.

Notice what's NOT in this model though. There are no assumptions about appreciation. The are no inflation assumptions. No future adjustments to rent or expenses.

None of those values are knowable. Anything you put in for those values is nothing more than a guess. So any model that incorporates those factors is a useless model.

Yes, I've built models like that. Once in a while I still do. But its really just for amusement.

The values I input to the model may be wrong. The ARV could be off. The rent could be off. Rehab could be higher. But the values I plug in are ones I can actually determine with some confidence. I don't include factors like appreciation, inflation or what rents will be in five years. That is, values I cannot determine with any confidence.

These complex models do have two uses.

One is that if you're recruiting investors or speaking with bankers or other financial institutions, these are expected. So, you have to pick some values for these unknowables and generate a plan.

The other use is for scenario planning. Rather than saying "I think inflation will be 3% and it will drive both rents and expenses", you say "what if" and determine how the deal works if that set of assumptions comes to pass. Then, you must try many different assumptions and evaluate the deal in many possible futures. If the deal only works with a small set of possible futures, then you know its dodgy, and will only make you money if very specific things happen. If it works with many possible futures, you know its robust, and will be profitable under a wide range of conditions.

I rarely see such an exercise being done for real estate deals. All you ever see is some set of rosy assumptions that makes a bad deal look good sometime in the future. The unstated piece is "if those assumptions come to pass."

A very simple model, really, just a set of calculations based on known values, is all it takes to tell you if it works right now.

MikeOH,

When selling property to investors who live thousands of miles away, they want to see numbers in a financial model to see how everything is going to break down. Now Im guessing you do not sell property at all or you do not sell anything to anyone out of your local area because if you did I would imagine they would need more than just your word! Even if they did take your word, how much is that actually worth? Or anyones word at that.

[REMOVED] Todays investors are more tech savvy then in the last several years. All my clients like seeing the financial model broken down for them to understand.

It is more important to have these for the new investor. They are the ones who need to have their hand held through the beginning.

Numbers for a deal are great. Essential, in fact. But I know when I'm presented with a five year profit and loss projection that has assumptions about appreciation, inflation and future expenses I'm being fed a pile of poo. When I was first starting to look at investments, I saw a lot of such spreadsheets. I can understand them in detail, and could easily (and did) construct them myself. I just eventually came to understand that number over in the lower right hand corner - the after tax cash flow five years from now - was totally fictitious. The number I needed, the "is this a good deal" number, - first year before tax cash flow - was over to the left, and usually about halfway down. And it was only valid if the expense projections, always broken down into five, six or 10 separate numbers, were accurate.

It basically seems to me that everybody has their own way of figuring out if an investment is right for them. As someone previously stated with spreadsheets, "garbage in garbage out." Doing your homework and gathering as much data as possible can help minimize the errors in ones calculations. I'm the type of person who likes to try to look at all aspects if possible before I make a decision. I think using spreadsheets will help me. I probably need to make sure that I don't get "paralysis by analysis" though.

Very true. It seems to me that common sense plays a huge part in any type of business. If it's to good of a deal, it probably isn't true.