## Wednesday, March 30, 2016

### Analyzing a Property's Cash Flow Statement & How to Create a Real Estate Pro Forma (part 13 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 13, we're going to look at loan sizing, which helps us determine what kind of loan we can get on the property.

## What is Loan Sizing?

As we've seen in other parts of this pro forma building series, debt plays a very important role in real estate. The interest rate, interest only periods, and actual loan amount have a big impact on the cash flows and return metrics. Before, we just assumed a loan size. There wasn't anything scientific about it, I just picked the number because it seemed reasonable. In reality, we should attempt to calculate how much debt we could get on the property by looking at it from a lender's perspective. This process of trying to predict how much debt a lender will provide on a property is called loan sizing.

## How a Lender Sizes a Loan

There are 4 calculations lenders rely on to size a loan. They are:
1. Debt Service Coverage Ratio (DSCR) - DSCR is calculated by taking the (NOI - CapEx Reserve) / Total Debt Payment. A lender will specify what month this test will occur. The lender might also have specific ways the NOI, CapEx Reserve, and Total Debt Payment are calculated. For example, if the month of the DSCR test is month 36, they might define NOI as the trailing 3 months NOI annualized, which in this case would be months 34-36 NOI multiplied by 4. They might define Total Debt Payment as the total payment amount, but assuming an interest rate of 6.5% and 25 year amortization period, even if the actual loan terms are a 4% interest rate and 30 year amortization period.
2. Debt Yield - Debt yield is calculated by taking the (NOI - CapEx Reserve) / Outstanding Loan Balance. Again, a month for the test is specified and NOI can be defined however the lender would like.
3. Loan to Cost (LTC) Ratio - LTC is calculated by taking the Loan Amount / Total Project Cost. Note that Total Project Cost includes most costs associated with acquiring the property, such as the loan origination fee, cost of 3rd party reports, etc...
4. Loan to Stabilized Value (LTV) Ratio - LTV is calculated by taking the Loan Amount / Stabilized Property Value. Stabilized Property Value is usually provided in the appraisal, which is always required for loans, and is the appraiser's opinion of the property's value once it is fully stabilized.
These statistics can be used to see how big of a loan the lender is willing to provide. For example, if the lender has a DSCR test of 1.12x in month 36, then we know that the NOI must be at least 1.12x the total debt payment. If it's not, then we need to reduce the size of the loan until the payment is small enough to satisfy the 1.12x DSCR test.

Similarly on the debt yield test, if a lender has a 9.1% debt yield hurdle in month 36 then we need to make sure our projected NOI is at least 9.1% yield on the outstanding loan balance. If it's not, then we need to reduce the loan size or increase the amortization on the loan so that we meet the test minimum in month 36.

The LTC and LTV tests are a lot easier and more straightforward. LTC is usually defined by the lender by saying "loan amount not to exceed 80% of project costs" in the term sheet, for example. Likewise, LTV is defined by the lender by saying "loan amount not to exceed 70% of stabilized property value" in the term sheet.

## How to Size the Loan in the Pro Forma

Now that we've reviewed the important lender stats and how they can be used to size a loan, let's look at how to actually size the loan for our pro forma. Notice that I've added rows 34-41 on the General Assumptions tab with some inputs that will help us size the loan. For the pro forma, I've assumed our debt yield and DSCR tests happen at the end of year 3.

The debt yield test is actually pretty simple. We know that our year 3 NOI is \$586,507. Now we can back into what our loan size can be based on that NOI and our debt yield test assumption (9.1% in the pro forma). To caclulate the max loan size given the debt yield test, we just take the NOI and divide it by the debt yield test assumption of 9.1%. This gives us a max loan amount of \$6.4M based on the debt yield test.

The DSCR test is a bit trickier. To calculate the max loan size given a DSCR test, we need to utilize Excel's PV formula. The PV formula has a couple of inputs. First is the interest rate. Second is the amortization periods. Last is the total payment amount. We know the interest rate and amortization periods--these are just assumptions we made earlier. The total payment amount is the tricky part. Basically, we calculate what our max payment amount could be given the DSCR constraint and our year 3 NOI. Our year 3 NOI, as stated above, is \$586,507. Our DSCR test is 1.12x. That means, given our year 3 NOI, the max loan payment amount that still satisfies our DSCR test is 586,507 / 1.12 = \$523,667. We then use that calculated amount as the total payment amount in the PV formula. This gives us a max loan size of \$8.5M.

LTC and LTV tests are pretty easy. For the LTC, our max loan size is just the LTC assumption (75% in the pro forma) and multiply it by the total project cost which is calculated lower on the same sheet. For the LTV, our max loan size is the LTV assumption (70% in the pro forma) multiplied by a stabilized value assumption. For our pro forma, I've just used our exit price as the stabilized value.

Now that we've calculated the max loan size that satisfies each lender test we can see what our max loan size is. The debt yield test is the lowest max loan size, so based on all the tests our max loan size is the \$6.4M.

Loan sizing is a surprisingly difficult but important part of the pro forma. Hopefully this exercise helped explain the concept and make it a bit more clear.