## Monday, March 7, 2016

### Analyzing a Property's Cash Flow Statement & How to Create a Real Estate Pro Forma (part 11 of pro forma building series)

Here's a list of everything we've covered so far:

Part 1 - Overview on how to calculate down to the NOI line item.
Part 2 - Intro to lease structures and calculating the gross potential revenue line item.
Part 3 - A look at reimbursement methods and how to calculate reimbursement income.
Part 4 - How to calculate the other income line item and an intro to income adjustments.
Part 5 - Rent abatements overview and calculation example.
Part 6 - Absorption and turnover vacancy explanation and intro to tenant improvements.
Part 7 - General vacancy allowance explanation and calculation example.
Part 8 - Operating expenses explanation.
Part 9 - Constructing a sources and uses table.
Part 10 - Building a debt schedule.
Part 11 - Calculating levered IRR.
Part 12 - DCF analysis.
Part 13 - Loan sizing.

Here's our simple example pro forma spreadsheet to follow along with as well.

At this point, we've projected all the relevant metrics we need to start valuing the property. Part 11 of the pro forma series will take the numbers we've calculated and pump out an IRR.

## Using IRR to Value Commercial Real Estate

The most common valuation metric used in commercial real estate is IRR. Investopedia defines IRR as:

Internal rate of return (IRR) is a metric used in capital budgeting measuring the profitability of potential investments. Internal rate of return is a discount rate that makes the net present value (NPV) of all cash flows from a particular project equal to zero. IRR calculations rely on the same formula as NPV does.

That definition might be a little confusing if you're new to finance--and understandably so. You can think of IRR as the average annual return on an investment, but keep in mind that this is a big simplification.

IRR has several properties that make it a popular metric. First, it takes into account the time value of money. Time value of money is the concept that a dollar today is worth more than a dollar at a future point in time because you have the ability to invest a dollar today and earn a return on it. Not all valuation metrics take into account the time value of money, but IRR does. Second, IRR can be compared to other investment opportunities. For example, if investment X has an IRR of 20%, investment Y has an IRR of 15%, you can say investment X has a better return profile than investment Y. Third, it is relatively easy to calculate with Excel. Excel has a built-in formula that will easily calculate an IRR if you give it the correct cash flows. Fourth, it's easy to make a quick judgement based on the IRR. If your return hurdle is 16% and your IRR is 10%, then you can quickly dismiss the investment opportunity.

## Problems with IRR

IRR as a valuation metric is certainly not without its flaws. For one, it assumes that cash received is reinvested at the IRR rate. So, if a project's IRR is 15% and you receive a lump of cash half way through the investment period, IRR assumes you are able to reinvest that lump of cash at 15% for the rest of the investment period. That's a pretty aggressive assumption, especially if you're analyzing an investment with an IRR of, say, 30%.

Another issue is IRR gets screwy when there are multiple negative cash flow periods. It's beyond the scope of this blog to fully explain why, but just keep in mind that an IRR calculation can't have negative cash flow periods mixed in with positive cash flow periods.

Next, IRR will favor high leverage deals over low leverage deals. A full explanation would get too granular at this point, but just note that the more leverage you use in a deal, the higher the IRR will get. However, using more leverage will increase the risk profile of an investment, so even though more leverage might increase the IRR, it increases the risk level. IRR doesn't show this trade off.

Finally, IRR will, perhaps more than it should, favor shorter term investments. For example, investment X might only be a year long and the IRR is 30%. Investment y might be 5 years long and have an IRR of 25%. IRR says investment X is the better investment decision, but what if you aren't able to find another good investment after that year? You could potentially keep reinvesting the money in worse projects that will give you a lower average return over 5 years than the 25% IRR. Of course, the opposite is true as well. You might find better and better projects that will increase the IRR past 30%. The point is, there's more uncertainty with the shorter term investment. The deal with a 25% IRR might actually be the better investment because you are removing 4 years worth of uncertainty.

## Levered Cash Flows in Our Pro Forma

As mentioned above, in order for Excel to calculate an IRR we need to feed it periodic cash flows. What we'll calculate in the pro forma is called a levered IRR. To get a levered IRR, we need to feed Excel levered cash flows. Levered cash flows are, simply, the cash equity investors invest and then receive throughout the investment period.

Initially in a real estate deal, an equity investor puts up the equity needed to buy the property. The equity investor then receives the net cash flow generated by the property. Finally, the equity investor will receive proceeds from the sale of the property after repaying the debt.

In our pro forma, at this point we have most of the pieces needed to calculate a levered cash flow. We have the equity needed for the deal and the net cash flow for each year. What we don't have is the sale proceeds after repaying the debt. In order to calculate this final piece, we need a projected sale price at Year 5. There's no exact science to forecasting a sale price in the future, but the most common method is to use an "exit cap rate" and apply it to the "exit NOI."

An exit cap rate is projected based on the market the property is in. If properties trade at a 6% cap rate in the market, then it would be reasonable to use a 6% exit cap rate. However, note that for the sake of conservatism, most pro formas will adjust the exit cap rate higher, especially investments that are projected to go 5+ years.

The exit NOI is also somewhat subjective. A conservative way to calculate an exit NOI is to take the final year's NOI. Another way is to take the final month's NOI and annualize it. Yet another way is to project a year's worth of NOI after the projected exit date. The point is there are many ways to calculate an exit NOI.

In our pro forma, I've assumed an exit cap rate of 6% and I've used Year 5's NOI as the exit NOI. Given these two assumptions, the estimated sales price is then 621,203 / .06 = 10,353,378. At the end of Year 5, our loan balance is 6,768,960. That means our sale proceeds available to equity investors is 10,353,378 - 6,768,960 = 3,768,304.

Now we have everything we need to calculate the levered cash flows and I've done so on the "Valuation Metrics" tab. First we have what's known as the "0 Period," or the time of investment. This is the moment equity investors actually commit their cash to the investment. In Years 1-4 the levered cash flow is simply the net cash flow for each period. Year 5 levered cash flow is the net cash flow and the sale proceeds available to equity investors.

Cell D15 is where the IRR is actually calculated. Note how simple the calculation is in Excel. Unfortunately, at this purchase price and exit cap rate, our investment is only generating a levered IRR of 1.54%. That's not very good. I could invest my money in a 5 year treasury and get a risk-free return of 1.4%.

To get the IRR up to an acceptable level, we need one of three things to happen:

1. Buy the asset for a cheaper purchase price
2. Get more aggressive with the exit cap rate
3. Increase the leverage

Our pro forma is flexible enough where you can play with the assumptions above and see how it affects the IRR.

We'll take a look at how to calculate more valuation metrics in the next part of the pro forma building series.

1. 1. 