## Saturday, March 12, 2016

### Analyzing a Property's Cash Flow Statement & How to Create a Real Estate Pro Forma (part 12 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. In part 12, we're going to perform a "Discounted Cash Flow" (DCF) calculation to help us understand what the property is really worth.

## What is a Discounted Cash Flow Analysis?

In part 11 we discussed the concept of the time value of money. The idea is that money today is a dollar today is worth more than a dollar at a future point in time. So just how much less is a dollar worth at a future point in time? Well, that's actually a very difficult question to answer, but let's work through a stripped down example to better understand the concept.

The example assumes we buy a bond for \$10,000 that yields 10% over 5 years and then matures, at which point the original \$10,000 investment is returned. The cash flow from the bond investment is summed in row 15. Our 0 period cash flow is -\$10,000, our bond purchase. Years 1-4 cash flow is .10 * 10,000 = 1,000. Year 5 is 1,000 + the return of our initial investment of \$10,000.

Calculating the cash flows from the investment is pretty easy and straight forward. However, now we want to take into account the time value of money. In order to factor in the time value of money, we need to make a very important assumption--that assumption is deciding what our "Cost of Equity" is.

## What is Cost of Equity?

Fully understanding the concept of cost of equity is beyond the scope of this blog. People write PHD papers on how to estimate cost of equity, just to give you an idea of how complicated of a subject it is. For our purposes, you can think of the cost of equity as what return you would expect on your money.

For example, let's think about when you receive your paycheck. You get the money, it's sitting in your bank account, what do you do? Most likely you pay some bills, maybe put some of it towards a vacation fund, and eventually you're left with some cash that isn't earmarked for anything in particular. As a prudent individual that plans for the future, you decide you're going to invest that money.

You might have a couple of safe, diversified ETFs you put the money into. Given what you know about historical stock market returns, you are expecting to receive a return of about 10% per year on that money you just invested. In this case, 10% is your cost of equity. Note that the stock market has a given risk level--our cost of equity needs to be thought of in terms of relative risk as well. So in this case, our cost of equity is 10%, but that's for a risk level of, say, X.

As a counterpoint, we could invest that money in municipal bonds and expect to receive a return of 7%. Municipal bonds, arguably, have a lower risk level than the stock market, so for this level of risk, Y, our cost of equity is only 7%.

To summarize, the cost of equity is the return we should expect on a given investment, but it will increase / decrease with the risk level of an investment.

## Calculating Discounted Cash Flows

In the DCF example, I've assumed we're buying a bond. As noted above, bonds have a relatively low risk profile, so I've assumed our cost of equity for this level of risk is about 7%. We can now calculate what our discount factor is for each year. Our discount factor tells us what a future cash flow is worth today. In this case, since our cost of equity is 7%, our discount factor for each year is calculated by taking 1 / (1.07^(year number)). In Year 1, our discount factor is 1 / 1.07^1 = 0.93. Year 5 is 1 / 1.07^5 = 0.71. To calculate the discounted cash flows, we take the cash flow for that year and multiply it by the discount factor.

Discounted cash flows can be used to calculate several interesting statistics. The first important stat is the "Net Present Value" (NPV). NPV is simply the sum of all the discounted cash flows. If the NPV is positive, then the investment should be pursued. If the NPV is negative, then it's generally not a good investment.

NPV is useful, but it's not really that descriptive. It's nice to be able to see quickly whether it's positive or negative, but that's all it really tells us. I mean, I don't really know what an NPV of 1,230 is, do you? To help give it some context, it's a good idea to calculate the DCF value. The DCF value is really easy to calculate. It's just the sum of the discounted cash flows, but without the 0 period cash flow of -10,000. This gives us a sum of 11,230. Now we can take that DCF value and divide it by the 0 period cash flow, which gives us a DCF multiple of 1.12x. Now we have some context on what this number means. If you're comparing two different investments, both might have a positive NPV. You can then look at the DCF multiple and whichever is higher is a better investment.

## Calculating a DCF in our CRE Pro Forma

We have everything we need to easily calculate the levered DCF value of our project in the pro forma. We already have our levered cash flows, so all we need to do is assume a cost of equity and then use the NPV formula in excel. I've assumed a cost of equity of 12%. In cell D16 on the "Valuation Metrics" tab, I then use the NPV formula in Excel on the levered cash flows, expect for the 0 period cash flow. Then in cell D17, I take the DCF value and divide it by the 0 period cash flow.

Remember how last time our IRR was a lowly 2%? I've gone ahead and adjusted the purchase price to make our return metrics more attractive. Now our IRR is 12.13%, and our DCF multiple is 1.01x. The investment looks much more attractive at this price. Given our cost of equity of 12%, we are achieving a 1.01x DCF multiple, which tells us we should do the deal at this price.