Please help

Operating Expenses The following table breaks out historical operating expenses for the property as well as projected increases over the holding period. *PRI is potential rental income. Reserves for Replacement In addition to the above operating expenses there will be a reserve for capital improvements of $250 per unit. Depreciation and Taxes Depreciation is straight-line over 39 years with no mid-month adjustment. Capital gains are taxed at 20 percent; depreciation recapture is taxed at 25 percent. The Harrison family is in the 35 percent tax bracket, and they will have no passive loss limitations. Sales Price and Cost of Sale The projected sale price is estimated by applying a conservative 3% annual growth rate to the acquisition price of the property over the 5-year holding period. Additionally, a 6% cost of sale is factored into the net sales proceeds to account for selling costs. Acquisition Costs In addition to the $17,800,000 purchase price, an additional $50,000 is factored in to account for closing costs. Discount Rate Harrison's required rate of return, based on alternative investments in their portfolio, is 15%. Julia is charged with completing the following analyses to help with the decision to invest: 1. Forecast the before-tax cash flows (operating and sale) over a 5-year holding period for the 150 -unit apartment building. 2. Given the above assumptions about tax rates, estimate the after-tax operating cash flows and after-tax cash flows on the sale. 3. Calculate the maximum supportable loan amount based on the debt service coverage ratio and the loan-to-value ratio. 4. Calculate the cash-on-cash return. 5. Calculate the debt service coverage ratio. 6. Complete a discounted cash flow analysis to determine the levered and unlevered internal rate of return (IRR) and net present value (NPV), beforeand after-tax. 7. Stress test the vacancy rate to analyze how it impacts the debt service coverage ratio (i.e., sensitivity analysis). 8. Stress test the loan interest rate to analyze how it impacts the debt service coverage ratio (i.e., sensitivity analysis). 9. Stress test the assumed increases in rental rates, the vacancy rate, and the loan interest rate to analyze how they impact the IRR and NPV, levered and unlevered, before- and after-tax (i.e., sensitivity analysis). Upon completing the analysis, Julia must draft a memorandum (maximum five single-spaced pages, not including exhibits and tables) to inform the investment decision of the Harrison family. This memo should address the implications of the sensitivity analyses for the decision, always keeping in mind the Harrison family's required return. Julia must also supply the Excel file used to conduct the analysis and to develop the exhibits and tables. Julia Gates is a real estate analyst with the Harrison Family Office (Harrison). Harrison is considering buying an apartment building in Lexington, KY geared toward retired University of Kentucky alumni. The property has 150 units and is offered for sale at $17,800,000. The building has the following unit mix: Julia is charged with developing a five-year pro forma and other analyses, with the following assumptions: Vacancy and Credit Loss In the current market, total vacancy and credit losses are running at 9%. Due to the improving market conditions as well as Harrison's prior experience leasing and operating multifamily buildings, it's expected that vacancy and credit losses will steadily decline over the next 5 years to 5%. Potential Rental Income Potential rental income (PRI) is based on the above unit mix. The 1bedroom and studio rental rates are expected to increase at 2% annually. The rent for 2 -bedroom units is also expected to increase at 2% annually. Financing After a preliminary discussion with a relationship manager at a local bank it's determined that a loan can be extended based on the lesser of a 1.25x debt service coverage ratio or 80% loan to value. Additionally, assuming the underwriting process doesn't reveal any red flags, it's expected that the loan will be based on a 20 -year amortization and a 6% interest rate. However, it is possible that loan interest rates could rise before a decision is made on the investment given forecasted economic conditions. 1