Real Estate Investment Valuation (Spreadsheet Inside!)

by JT McGee

Use a DCF analysis to find a good valuation for investment real estate.An investor who gets the entry right can forget the rest, especially when it comes to real estate. Purchased for cash flow, nothing matters more than valuation—you can’t afford to overpay for the future value of cash flows.

Thanks to a reader, Brad, I’ve a great real estate valuation spreadsheet to share. I can’t say enough about how awesome this utility is.

Discounted Cash Flow Valuation

Since real estate is all about the cash flow, there’s no better valuation methodology than a discounted cash flow analysis. In a DCF model, an investor projects the future cash flows and then discounts their value back to the present.

There are five steps to a solid discounted cash flow valuation:

Step 1. Project free cash flow for the period we can reasonably forecast.

Step 2. Determine a discount rate, or the rate at which we discount a future dollar back to a present dollar. Future dollars are worth less than current dollars.

Step 3. Discount the projected free cash flows to the present and sum the total.

Step 4. Calculate the perpetuity value and discount it to the present.

Step 5. Add the values from Steps 3 and 4. If you were to do a DCF for a company, you would then divide by the number of shares outstanding so as find a DCF value on a per-share basis.

Discounting the value of future cash flows from a real estate transaction is fairly easy. Finding the perpetuity value is a little more complex, but fairly simple, as well. The perpetuity value is found by the following equation:

(CFn x (1+ g) ) / (R – g)

CFn is Cash flow from our last forecast period; in most cases I use the fifth year.
g is the long-term growth rate of our investment
R is our discount rate, or cost of capital. In this case, I’d use the cost of mortgage debt.

DCF Spreadsheet vs. Doing it by Hand

You can see why doing a DCF analysis by hand is a pain in the rear. With Brad’s spreadsheet, all you have to do is enter important values for your investment. It’s incredibly intuitive, which makes for exceptional accuracy in valuing real estate with a DCF model. Here are the key inputs, just to show the complexity and power of the spreadsheet:

Property
Purchase Price ($)
Annual Appreciation (%)

Loan
Down Payment ($)
Loan Interest Rate (%)
Loan Term (yrs)
Monthly PMI ($)
Additional Monthly Pmnt ($)

Transaction Costs
Closing Costs at Purchase ($)
Realtor Fees at Sale (%)
Other Selling Costs (%)

Income
Monthly Rent ($)
Occupancy Rate (%)
Annual Rent Increase (%)

Recurring Expenses
Annual Maintenance ($)
Annual Property Taxes ($)
Annual Insurance ($)
Monthly HOA ($)
Monthly Utilities ($)
Other Annual Expenses ($)
Expense Inflation (%)

Taxes
Marginal Tax Rate (%)
Capital Gains Tax Rate (%)
Depreciable Value (%)

Other
Duration of Analysis (yrs)
Opportunity Cost of Capital (%)

I’m not a real estate investor, so I use a cash flow to equity model for stocks that is WAY simpler than the spreadsheet I link at the bottom of the article. However, Brad’s real estate valuation spreadsheet is absolutely phenomenal for the purposes of finding a good value for a real estate investment.

Take a look at this spreadsheet and do an analysis for your own home, or even your own investment property if you have one. With rates at record lows, the value of future cash flows from your home should be very, very high—and that’s a good thing!

Hopefully each investment property is a beautiful piece of real estate, but most importantly, we should hope that it provides a vehicle in which we can buy a future dollar for less than it’s real cost. That’s how investors make money in any investment. It’s the name of the game.

Here’s the link for the spreadsheet. Let Brad and me know what you think in the comments. Is your current property meeting your goals? If you don’t have investment property, but own your home, is the NPV of your home worth more than what you paid? If so, you’re doing just fine!

Click here to download the valuation spreadsheet! We owe Brad a beer!

{ 10 comments… read them below or add one }

Sal October 24, 2011 at 11:06

This spreadsheet is very helpful! What is the MIRR number at the end?

Reply

Brad October 24, 2011 at 13:07

MIRR is the after-tax rate of return for the investment, given the assumptions you use as inputs. MIRR (Modified Internal Rate of Return) is typically a more accurate rate of return calculation than IRR because it assumes that any cash flows generated by the investment are reinvested at your opportunity cost of capital (IRR assumes cash flows generated are reinvested at the project’s internal rate of return).

Similar to the NPV, it tells you how attractive the investment is. It is relative to your opportunity cost of capital. For example, if the NPV of the project is zero, the MIRR will equal your after tax cost of capital (cost of capital * (1-tax rate)). What this means is that the rate of return generated by the project is exactly the same as the rate you could realize from an alternative investment with similar risk and duration. Therefore, your NPV is zero relative to the alternative investment.

I hope that makes sense. I’m willing to bet that JT can explain it more eloquently.

Reply

Sal October 24, 2011 at 13:39

Thanks for the help and this calculator.

I will run the numbers again. We want to buy investment property. We aren’t investment people and we like to avoid the math in the investment. We’re not into stocks or mutual funds either since we got burned by tech stocks and banks in the past 10-15 years.

I will change the opportunity cost area for the mortgage interest rate. We have debt that we want to get pay off before buying property as an investment. Now I see that we should stop paying our mortgage so quickly and buy another home as an investment. The worst case scenario is better than paying down more debt. We don’t have that much debt anyway but it seemed like a really safe investment without the risk.

Reply

JT McGee October 24, 2011 at 20:33

I’m willing to bet, too, since I can’t. 😉 Great description, and thanks a billion for sharing this. There were quite a few downloads today.

Reply

cardsfan October 24, 2011 at 20:28

I bought my townhome in 2009 to get a first time homebuyers tax credit. My home is underwater…this says I got a good deal…

Reply

JT McGee October 24, 2011 at 20:34

In the long haul, I’ll bet on the spreadsheet’s call. The numbers on real estate are so attractive that even I’m turning my head toward RE–and I’m not even a real estate fan!

Reply

20's Finances October 25, 2011 at 10:17

As someone who is already making plans to purchase his first property, I love this. I had created a very simple spreadsheet, but this does wonders! Thanks for sharing a great resource.

Reply

retirebyforty October 25, 2011 at 12:25

Thanks for the spreadsheet. This one is a lot nicer than what I made.

Reply

PKamp3 October 26, 2011 at 09:31

Brad, good stuff! Are you considering adding additional output fields? Something like break-even would be interesting, just the point at which NPV = 0 (or you could do it like a target, where ‘point that NPV = x’).

I’m running the numbers on my home, and from what I was seeing in the worst-case scenario, I better stay for at least ten years!

Reply

JT McGee October 30, 2011 at 20:46

I’m going to make a few revisions if time permits. I think some expense amortization would be really cool to get a 100% accurate picture (assuming your assumptions about roofing, appliances, etc are accurate.)

Reply

Leave a Comment

*

Previous post:

Next post: