Homework Assignment #9 Due via CANVAS using the Excel template. Recognize that the template already has some formulas built in. Take care not to write over those formulas. Both the formulas and the hard-coded cells are there to assist you. Question 1 (39 points) You are analyzing the potential acquisition of a small residential income-producing property. The asking price for the property is $8,500,000; gross potential rents are estimated to be $895,000 during the first year and are expected to grow at a rate of 2.5% per year thereafter. Vacancies and collection losses are expected to be 5% of potential gross income. Operating expenses are estimated to be 40% of effective gross income. You will finance the acquisition with a partially amortizing loan for 70% of the purchase price. The annual interest rate will be 3.00%, and the payments will be based on a 30-year term for amortization. An amortization table has been provided for your use. Your sales value at the end of year 5 is based on year 6 NOI and an assumed exit cap rate that is 0.50% (50 basis points) higher than your purchase cap rate. Using the Excel template, answer each of the questions below. Round all returns to two decimal places: A) What is the "exit" cap rate of the investment based on the spread you're your purchase cap rate? B) What is the loan amount that you will receive from the lender based on the proposed LTV (Loan to Value)? C) What is the annual debt service payment (sum of 12 monthly payments) based on the loan parameters above? D) What is your initial equity invested in year 0 (time of acquisition)? E) Project the Effective Gross Income for the property for each of the next 5 years. F) Project the Net Operating Income for the property for each of the next 5 years. G) Project the cash flow after debt service for each of the next five years. H) Calculate the gross sales proceeds (residual/terminal value) for the property at the end of year 5 based on your exit cap rate assumption. Homework Assignment #9 Due via CANVAS using the Excel template. Recognize that the template already has some formulas built in. Take care not to write over those formulas. Both the formulas and the hard-coded cells are there to assist you. Question 1 (39 points) You are analyzing the potential acquisition of a small residential income-producing property. The asking price for the property is $8,500,000; gross potential rents are estimated to be $895,000 during the first year and are expected to grow at a rate of 2.5% per year thereafter. Vacancies and collection losses are expected to be 5% of potential gross income. Operating expenses are estimated to be 40% of effective gross income. You will finance the acquisition with a partially amortizing loan for 70% of the purchase price. The annual interest rate will be 3.00%, and the payments will be based on a 30-year term for amortization. An amortization table has been provided for your use. Your sales value at the end of year 5 is based on year 6 NOI and an assumed exit cap rate that is 0.50% (50 basis points) higher than your purchase cap rate. Using the Excel template, answer each of the questions below. Round all returns to two decimal places: A) What is the "exit" cap rate of the investment based on the spread you're your purchase cap rate? B) What is the loan amount that you will receive from the lender based on the proposed LTV (Loan to Value)? C) What is the annual debt service payment (sum of 12 monthly payments) based on the loan parameters above? D) What is your initial equity invested in year 0 (time of acquisition)? E) Project the Effective Gross Income for the property for each of the next 5 years. F) Project the Net Operating Income for the property for each of the next 5 years. G) Project the cash flow after debt service for each of the next five years. H) Calculate the gross sales proceeds (residual/terminal value) for the property at the end of year 5 based on your exit cap rate assumption