Real Estate Project

RE 3010.010 | Final Project Fall 2015

1

PeachTreeAgain Communities Acquisition Analysis

You work as an acquisition strategist with ATREIT, an Atlanta based apartment real estate investment trust (REIT). ATREIT, by regulation, is obligated to pay most of its NOI to its shareholders. However, it is always under pressure from its shareholders to “grow” its property portfolio. ATREIT has limited amount of cash reserves which challenges its potential to enrich its portfolio by acquiring properties. However, ATREIT enjoys good rapport with local banks which are willing to lend money to the company so that it can pursue acquisitions.

Your team has identified a multifamily property “PeachTreeAgain Communities” (PTAC) in suburban Atlanta that is available for sale by its current owners. However, they have not actively listed the property yet. The property characteristics match the property holdings of ATREIT and the C-suite is quite interested. You have been assigned to analyze the acquisition’s feasibility. The leasing agents at PTAC are cooperative. From them, you have collected comprehensive property-related information that is displayed below:

Unit Type Unit Description Number of Units Area (sqft per unit)

Monthly Rent per unit

A-1 1 Bd / 1Ba 72 688 750 A-2 1 Bd / 1.5 Ba 76 800 850 A-2A 1 Bd / 1Ba 4 846 915 B-1 1 Bd / 1 Ba + D 24 971 975 B-2 2 Bd / 2 Ba 40 1,113 1,080 B-2A 2 Bd / 2Ba 4 1,139 1,100 B-2B 2 Bd / 2Ba 4 1,251 1,190 B-3 2 Bd / 2 Ba 36 1,152 1,110 C-1A 3 Bd / 2 Ba 3 1,500 1,550 C-1A-Bay 3 Bd / 2 Ba 3 1,668 1,700 C-1B 3 Bd / 2 Ba 3 1,511 1,550

Some additional sources of revenue are described below:

Additional Revenue Garages: Direct Access 26 garages at $125 per month Assignable 22 garages at $100 per month Detached 36 garages at $75 per month Storage Units: 106 units at $35 per month Carports: 112 carports at $35 per month Other: 272 units at $20 per month

An overall vacancy rate of 6% is anticipated in all rentable units (including apartments, garages, etc.).

RE 3010.010 | Final Project Fall 2015

2

Operating expenses are described as below, on annual basis:

Operating Expenses: Per square foot

Administration 0.12 Property Taxes 1.95 Advertising & Promotion 0.17 Insurance 0.25 Repairs & Maintenance 0.39 Management Fee (3.5%) 0.44 Personnel Expense 0.85 Utilities 0.45 Reserves 0.15

1. Rent Roll: Prepare a Rent Roll table outlining the rentable income in the following format

Unit Type

Unit Description

# Units Area per unit (sq ft)

Monthly Rent

Total Area

Gross Rent (per

sqft)

Monthly Gross Rent

Annual Gross Rent

A-1 1 Bd / 1Ba 72 688 750 A-2 1 Bd / 1.5 Ba A-2A 1 Bd / 1Ba -- -- -- -- -- -- -- -- -- Total

2. Net Operating Income

2.1 Prepare a table (in new tab [2]) for revenue (annual) from other sources. Calculate Annual PGI & EGI

2.2 Prepare a table for Operating Expenses (in new tab [3]). 2.3 Calculate the NOI.

3. Property Value (Cost): Your market analysis suggests that currently for such properties, the NOI is around 7.8% of the market value, i.e. the capitalization rate is 7.8%. Estimate the value of PTAC (in tab [3]) - This is the price you will pay to acquire the property.

Since PTAC management is in good terms with ATREIT executives, no brokerage services will be required. All legal and other expenses related to the acquisition will be provided by the in- house team at ATREIT; thus they are virtually costless for this analysis. ATREIT CFO advises you that she may approve up to $7 million of equity from the cash reserves to acquire this property. The remaining cash must come from external sources. She is unwilling to raise

RE 3010.010 | Final Project Fall 2015

3

additional equity shares at the moment. So you only have one option - to get a loan from the bank.

Your bank loan officer agrees with your evaluation of the market value of PTAC and its revenue potential and advises you that the bank will consider two metrics to determine the loan amount: A maximum Loan-to-Value (LTV) ratio of 80% or a maximum Debt-Service-Coverage Ratio (DCR) of 1.25. The final loan amount will be the minimum of those suggested by these two metrics (LTV or DCR). The loan term will be 30 years (with monthly payments) at an annual interest rate of 4.5%.

4. Loan Analysis: 4.1. What is the maximum loan amount based on the LTV metric (in new tab [4])? 4.2. What is the maximum allowed annual loan payment (from ATREIT to the bank) based on the DCR metric? Divide this amount by 12 to find the maximum allowed monthly payment. 4.3. What is the maximum loan amount based on the DCR metric? Which of the two loan amounts you calculated will be selected by the bank? 4.4. Is the acquisition feasible based on financing? Why? 4.5. Based on borrowing the maximum amount allowed by the bank, what is the annual debt service (ADS)?

5. Future Projections & Pro-forma Development: You hire a real estate consultant to evaluate how the project will perform moving forward. The consultant subscribes to various databases and periodically consults real estate experts to remain up-to-date with market movements and trends. Here are some numbers he suggests for the first five years: 1. PGI will increase at a rate of 5% per year with a strong marketing campaign 2. Operating expenses will increase at 4% per year 3. At the end of five years, the going out capitalization rate will be 8%.1 4. Selling costs (e.g. brokerage) at the end of the fifth year will be 6% of the sale price

ATREIT plans to own and operate this property for five years. At the end of five years, it will sell the property. Thus, the cash flow for the fifth year will be the sum of the cash flow from operations (rentals) and the cash flow from sale.

1 Note that the Market value at the end of five years is based on the property’s potential NOI in the following sixth year. Also, assume that market value is the “Sale” price of the property.

RE 3010.010 | Final Project Fall 2015

4

Based on the information provided above, develop the BTCF (before tax cash flow) series for PTAC for the five years (in new tab [5]). As a standard practice, yearly cash flows are arranged column-wise. Then:

a. Calculate the IRR of the BTCF2 b. Calculate the NPV of the BTCF discounted at the loan interest rate3 c. Calculate the NPV of the BTCF discounted at the calculated IRR

6. Synopsis & Recommendation In 1-2 paragraphs, provide a synopsis of your analysis. Make it succinct yet informative so that a busy executive from the C-suite can understand your research and recommendation. Provide key data and findings in your report and make a recommendation regarding the acquisition. Create a separate MS Word document titled ‘Executive Summary’ to accompany your excel file.

Present your answers (in the sequence of questions 1-5) in a neatly formatted MS Excel file (template provided). Conduct all your analyses in a single MS Excel file (using multiple worksheets). Name your tabs accordingly. Submit the report (MS Word) and analysis (MS Excel) files for grading. Make sure you name your files with your last name.

Further hints and instructions:

• Use cell referencing in the Excel spreadsheets • Format your report well and include a cover page • Use the financial functions of Excel. Do not type numbers calculated externally (e.g. with

a calculator). Any numbers in the spreadsheet cells for which I am unable to find your calculation method (if they are to be calculated) will be marked incorrect even if they are correct.

• Where needed, use your own assumptions. However, clearly mention the assumptions you use.

2 Remember, the initial investment should be treated as a negative cash flow at year-0. The remaining cash flows should be shown as year-1, year-2…., year-5 3 You apply the NPV formula on the cash flows from year-1 to year-5 and add this quantity to the initial (negative) investment value to reach at the final NPV.