PLEASE INCLUDE FULL CALCULATION/FUNCTION USED IN EXCEL. THANKS
You are an employee of University Consultants, Ltd., and have been given the following assignment. You are to present an investment analysis of a new small residential income-producing property for sale to a potential investor. The asking price for the property is $1,250,000; rents are estimated at $200,000 during the first year and are expected to grow at 3 percent per year thereafter. Vacancies and collection losses are expected to be 10 percent of rents. Operating expenses will be 35 percent of effective gross income. See the following chart: Year 1 5 23 4 206,000 212,180 218,545 Rent 200,000 225,102 Vacancy & Collection loss 20,000 20,600 21,218 21,855 22,510 Effective Gross Income 180,000 185,400 190,962 196,691 202,592 Operating Expenses 63,000 64,890 66,837 68,842 70,907 NOI 117,000 120,510 124 125 127,849 131,685 A 70 percent constant payment loan can be obtained at 11 percent interest for 30 years with monthly payment. The property is expected to appreciate in value at 3 percent per year and is expected to be owned for five years and then sold. a) How much is the mortgage payment per year? b) How much is the cash flow from sale of the property at the end of year 5? c) Please list the before-tax cash flow from year 0 to year 5 and compute the investor's expected before-tax internal rate of return on equity invested (BTIRR)? d) What is the first-year debt coverage ratio? You are an employee of University Consultants, Ltd., and have been given the following assignment. You are to present an investment analysis of a new small residential income-producing property for sale to a potential investor. The asking price for the property is $1,250,000; rents are estimated at $200,000 during the first year and are expected to grow at 3 percent per year thereafter. Vacancies and collection losses are expected to be 10 percent of rents. Operating expenses will be 35 percent of effective gross income. See the following chart: Year 1 5 23 4 206,000 212,180 218,545 Rent 200,000 225,102 Vacancy & Collection loss 20,000 20,600 21,218 21,855 22,510 Effective Gross Income 180,000 185,400 190,962 196,691 202,592 Operating Expenses 63,000 64,890 66,837 68,842 70,907 NOI 117,000 120,510 124 125 127,849 131,685 A 70 percent constant payment loan can be obtained at 11 percent interest for 30 years with monthly payment. The property is expected to appreciate in value at 3 percent per year and is expected to be owned for five years and then sold. a) How much is the mortgage payment per year? b) How much is the cash flow from sale of the property at the end of year 5? c) Please list the before-tax cash flow from year 0 to year 5 and compute the investor's expected before-tax internal rate of return on equity invested (BTIRR)? d) What is the first-year debt coverage ratio
