can anyone complete this project? The instructions and template is attached.

RE 3010 | Final Project Spring 2017 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 A-1 A-2 A-2A B-1 B-2 B-2A B-2B C-1A C-1B 70 72 4 24 30 4 4 3 3 1 Bd / 1Ba 1 Bd / 1.5 Ba 1 Bd / 1Ba 1 Bd / 1 Ba + D 2 Bd / 2 Ba 2 Bd / 2Ba 2 Bd / 2Ba 3 Bd / 2 Ba 3 Bd / 2 Ba Area (sqft per unit) 688 800 846 971 1,113 1,139 1,251 1,500 1,511 Monthly Rent per unit $700 $800 $865 $925 $1,030 $1,050 $1,140 $1,500 $1,500 Some additional sources of revenue are described below: Additional Revenue Garages: Direct Access Assignable Detached Storage Units: Carports: Other: 25 22 40 106 112 272 garages at garages at garages at units at carports at units at $125 $100 $75 $30 $34 $25 per month per month per month per month per month per month An overall vacancy rate of 7% is anticipated in all rentable units (including apartments, garages, etc.). 1 RE 3010 | Final Project Spring 2017 Operating expenses are described as below, on annual basis: Operating Expenses: Per square foot 0.15 1.90 0.19 0.24 0.39 0.56 0.85 0.45 0.15 Administration Property Taxes Advertising & Promotion Insurance Repairs & Maintenance Management Fee Personnel Expense Utilities Reserves 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 70 700 A-1 1 Bd / 1Ba A-2 1 Bd / 1.5 Ba A-2A 1 Bd / 1Ba -- -- -- -- -- -- -- -- -- 688 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.0% of the market value, i.e. the capitalization rate is 7.0%. 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 inhouse team at ATREIT; thus they are virtually costless for this analysis. ATREIT CFO advises you that she may approve up to $5 million of equity from the cash reserves to acquire this property. The remaining cash must come from external sources. She is unwilling to raise 2 RE 3010 | Final Project Spring 2017 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.75%. 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 4.75% 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 7.5%.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. 3 RE 3010 | Final Project Spring 2017 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). Submit the report (MS Word) and analysis (MS Excel) files for grading. Make sure you name your files with your last name (and that of any partner). 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 year0. The remaining cash flows should be shown as year1, year2...., year5 3 You apply the NPV formula on the cash flows from year1 to year5 and add this quantity to the initial (negative) investment value to reach at the final NPV. 4 Rent Roll Unit Unit Type Description A-1 1 BR/1BA A-2 1 BR/1.5 BA A-2A 1 BR/1 BA Total Number of Area Per Units Unit (sqft) 70 688 Monthly Rent/Unit 700 Total Area Monthly Gross Rent/sqft (sqft) Monthly Gross Rent Annual Gross Rent 1.0 Additional Revenue Sources # of Units Monthly Rent/Unit Garages Direct Access Assignable Detached Storage Units Carports Other Total Additional Revenue Total Annual Rent 2.1 Annual PGI & EGI Rental Revenue Additional Revenue PGI Vacancy Loss EGI Total Monthly Rent Operating Expenses PSF 0.15 1.9 Administration Property Taxes Advertising & Promotion Insurance Repairs & Maintenance Management Fee Personnel Expense Utilities Reserves Per/Year 2.2 Total Operating Expenses 2.3 NOI = Property Value Going In Cap Rate NOI Market Value 3.0 Loan Term Frequency of payments per year Annual Interest rate years LTV Method LTV Value Maximum Loan Amount by LTV Method 4.1 DCR Method NOI DCR Maximum allowed Debt Service Maximum allowed monthly payment Now, for the mortgage loan: PMT N I Therefore, PV= Maximum Loan Amount by DCR Method 4.2 months 4.3 Amount to be approved Answer: 4.4 Annual Debt Service 4.5 Assumptions PGI growth Rate Op Ex growth rate Going out cap rate Selling costs per year per year Cash Flow From Operations Year-0 Year-1 PGI Vacancy Loss EGI Operating Expenses NOI ADS Cash Flow frpm Operations Cost of Acquisition Bank Loan Equity Investment Future Sale Price Selling Costs Loan Balance Cash Flow from Sale BTCF IRR NPV @ loan rate NPV @ IRR 6: Synopsis & Recommendation 5.a. 5.b. 5.c. Year-2 Year-3 Year-4 Year-5 Year-6