this is an excel doc i don't know if my numbers are right but please follow this format in excel

You are an employee of University Consultants, Limited, and have been given the following assignment. You are to present an investment analysis of a small retail income-producing property for sale to a potential investor. The asking price for the property is $1,330,000; rents are estimated at $170,240 during the first year and are expected to grow at 2.5 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. A fully amortizing 70 percent loan can be obtained at 6 percent interest for 30 years (total annual payments will be monthly payments * 12). The property is expected to appreciate in value at 2 percent per year and is expected to be owned for five years and then sold. Required: a. What is the first-year debt coverage ratio? b. What is the terminal capitalization rate? c. What is the investor's expected before-tax internal rate of return on equity invested (BTIRR)? d. What is the NPV using a 12 percent discount rate? e. What is the profitability index using a 12 percent discount rate? Complete this question by entering your answers in the tabs below. Required A Required B Required C Required D Required E What is the first-year debt coverage ratio? (Do not round intermediate calculations. Round your final answer to 2 decimal places.) Debt coverage ratio 1.71 A B C D 10.0% 35.0% 2.00% Term Payment Annual Payment Mortgage @ End E F. G H 30 5,581.82 66,981.78 502,771.26 Calculate the PV with only 10 years remaining on the loan. 5 1 2 3 4 5 9 Vacancies and collection losses (% of rent) 10 Operating Expenses (% of gross rent) 11 Property annual appreciation 12 Years owned 13 Sales Commission 14 15 Year 16 Rental Income 17 Vacancy & Collection Loss 18 Effective Gross Income 19 Operating Expenses 20 Net Operating Income 21 Debt Service 22 Before-Tax Cash Flow 170,240 17,024 153,216 53,626 99,590 66,982 174,496 17,450 157,046 54,966 102,080 66,982 35,098 178,858 17,886 160,973 56,340 104,632 66,982 37,650 183,330 18,333 164,997 57,749 107,248 66,982 40,266 187,913 18,791 % of rents from problem 169,122 59,193 % of gross income from problem 109,929 66,982 42, 1,468,427 Based on annual appreciation during ownership period Include sales commission 23 24 Cash Flow from Sale - Year 5 25 Sales Price 26 Sales Expenses 27 Mortgage Balance 28 Before-Tax Cash Flow 29 30 Before-Tax IRR 31 Year 32 33 34 35 Devt coverage ratio 36 terminal cap rate 37 Use Goal Seek to target the 15% BTIRR by changing the asking price 1 2 3 4 0 5 Be sure to include cash to purchase and net sales proceeds 1.486828209 or 1.71 You are an employee of University Consultants, Limited, and have been given the following assignment. You are to present an investment analysis of a small retail income-producing property for sale to a potential investor. The asking price for the property is $1,330,000; rents are estimated at $170,240 during the first year and are expected to grow at 2.5 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. A fully amortizing 70 percent loan can be obtained at 6 percent interest for 30 years (total annual payments will be monthly payments * 12). The property is expected to appreciate in value at 2 percent per year and is expected to be owned for five years and then sold. Required: a. What is the first-year debt coverage ratio? b. What is the terminal capitalization rate? c. What is the investor's expected before-tax internal rate of return on equity invested (BTIRR)? d. What is the NPV using a 12 percent discount rate? e. What is the profitability index using a 12 percent discount rate? Complete this question by entering your answers in the tabs below. Required A Required B Required C Required D Required E What is the first-year debt coverage ratio? (Do not round intermediate calculations. Round your final answer to 2 decimal places.) Debt coverage ratio 1.71 A B C D 10.0% 35.0% 2.00% Term Payment Annual Payment Mortgage @ End E F. G H 30 5,581.82 66,981.78 502,771.26 Calculate the PV with only 10 years remaining on the loan. 5 1 2 3 4 5 9 Vacancies and collection losses (% of rent) 10 Operating Expenses (% of gross rent) 11 Property annual appreciation 12 Years owned 13 Sales Commission 14 15 Year 16 Rental Income 17 Vacancy & Collection Loss 18 Effective Gross Income 19 Operating Expenses 20 Net Operating Income 21 Debt Service 22 Before-Tax Cash Flow 170,240 17,024 153,216 53,626 99,590 66,982 174,496 17,450 157,046 54,966 102,080 66,982 35,098 178,858 17,886 160,973 56,340 104,632 66,982 37,650 183,330 18,333 164,997 57,749 107,248 66,982 40,266 187,913 18,791 % of rents from problem 169,122 59,193 % of gross income from problem 109,929 66,982 42, 1,468,427 Based on annual appreciation during ownership period Include sales commission 23 24 Cash Flow from Sale - Year 5 25 Sales Price 26 Sales Expenses 27 Mortgage Balance 28 Before-Tax Cash Flow 29 30 Before-Tax IRR 31 Year 32 33 34 35 Devt coverage ratio 36 terminal cap rate 37 Use Goal Seek to target the 15% BTIRR by changing the asking price 1 2 3 4 0 5 Be sure to include cash to purchase and net sales proceeds 1.486828209 or 1.71