Sunday, February 21, 2016

Analyzing a Property's Cash Flow Statement & How to Create a Real Estate Pro Forma (part 10 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.

Click the button below to download the spreadsheet:

Next Step: Modeling Debt

One of the things that makes real estate an attractive asset class is the ability to utilize leverage, and lots of it. Many property owners choose to put debt on their properties as a way to juice their returns. Just how debt actually juices returns is a bit complicated but will become clearer as we continue building out our pro forma. For now, let's do a high level overview of the different types of commercial real estate debt and then see how to model a loan.

Senior Debt

Senior debt is a loan that is backed by the actual property. It is "senior" to all other parts of the capital stack (equity, preferred equity, junior loans, etc...). Being "senior" means the loan payments must be paid before equity holders or junior loans. It also means if the trustee can't pay the debt, the senior lender has the right to foreclose and take ownership of the property.

Senior lenders have the lowest risk in the capital stack and, subsequently, are the cheapest part of the capital stack. Senior lenders, as of now, are offering silly low interest rates. Not only are they offering low interest rates, but they're lending at high LTV's.

LTV is short for "Loan to Value" and refers to the amount of the loan as a percentage of the property's estimated value. For example, if a lender is willing to lend $7M on a property they estimate is worth $10M, then the LTV is 70%. In the current lender environment, I've seen lenders willing to go up to 80% on certain assets, which seems extreme. Keep in mind that in the bubble years (early 2000's), lenders were out of control and lending up to 100% of the property's estimated value (which was grossly over-estimated making matters worse). So even though an 80% LTV seems high, it's still well below the previous peak.

Another important note is there are different types of senior debt, namely variable and fixed rate. Variable rates will have lower interest rates initially, but carry the risk of increasing. Variable rate loans will typically have more flexible prepayment options as well. Fixed rate loans have the advantage of having a fixed interest rate that won't change regardless of what happens with market interest rates. However, the rate will typically be higher initially and prepaying the loan can be more difficult and expensive.

Junior / Unsecured Loan

Junior debt is subordinate to senior debt. Because it doesn't have the first lien (senior lender has the first lien), junior debt is often called unsecured. Debt service on a junior loan is only paid after the debt service on the senior loan. Likewise, if the senior loan isn't paid, the senior lender has the option to foreclose on the property and leave the junior lender with nothing.

Clearly junior loans (also often called Mezz debt) are much riskier and, subsequently, their interest rates are much higher than senior loans. So why would a property owner ever want a junior / mezz / unsecured loan given the high interest rate? Mezz debt will typically be used to take the LTV beyond what a senior lender would provide. For example, if a senior lender will provide a 75% LTV loan, a mezz lender might provide an additonal 10% LTV at a high interest rate to take the total project LTV to 85%.

Interest Only Periods

Senior and junior loans alike might have interest only periods. Interest only periods are exactly what they sound like--periods where the property owner only pays interest on the loan and doesn't pay any amortization. In general, the longer the interest only period, the better for the property owner and worse for the bank.

Modeling a Loan in our Pro Forma

To model a loan in our pro forma, we need to add a couple of assumptions to our "General Assumptions" tab. We need the number of amortization periods (in years), the yearly interest rate, the number of interest only months, the LTV, and finally when the loan matures. Note that we also changed how the debt amount in our "sources" table is calculated. Before it was a hard input--now we calculate the amount by taking the LTV assumption and multiplying it by the acquisition price. I've assumed a 70% LTV loan and since our purchase price is $10M, the loan amount is $7M.

The next step is to create a tab where we model out the loan. I've named the tab "Debt Schedule." First we need to calculate what the total monthly payment is (amortization and interest). I do the calculation in cell D8. To get the correct amount, use the PMT function in excel, but note that you need to use a monthly interest rate (just take the yearly rate and divide by 12) and need to convert the amortization periods to months (just multiply by 12).

Second, I like to create some rows with binary signals (0 or 1) that dictate whether or not we're in an interest only period, amortization period, and when we hit the loan maturity date. In rows 11-13 I've laid out the actual months where each period starts and ends and the month the balloon payment will happen. Rows 15-17 then have the actual binary signals.

Third, we create the actual debt schedule. For each period, we first need the loan balance at the start of the period (row 20). Then we calculate the interest owed for the period, which is calculated by taking the monthly rate and multiplying it by the beginning balance. Then, if we're in an amortization period, we need to calculate the amount of amortization, which is just the monthly payment (cell D8) minus the interest payment in the period. I like to have the amortization actually be negative. Then we need the ending loan balance for the period, which is just the beginning balance minus the amortization amount. The end balance then becomes the next period's beginning balance.

Fourth, we have a row (23) where we zero out the loan balance at the maturity date. The formula says if the the month is also the maturity date, then we pay a balloon payment of whatever is left on the loan balance, which is the period's beginning balance minus the amortization paid in the period.

Fifth, and the last step, is to link the debt schedule to the "Yearly Pro Forma" tab. On our pro forma tab, we have an interest expense (row 39) and amortization amount (row 40) that need to be populated. Notice that our debt schedule is monthly, but our pro forma tab is yearly. The tricky part is putting the monthly data from the debt schedule into the yearly format on the pro forma. I do this with a "sumif" formula. I'd recommend doing it that way and learning how to use the formula--it's one of the most useful in Excel--but you could just manually sum the appropriate cells as well.

That's it for this part of the series. Now that we've finished the sources and uses tables and schedule we're getting close to being able to pump out some interesting analysis!

No comments:

Post a Comment