You have recently graduated from University and have started your new job at Rang Consulting LLC. You have been given the following assignment. You are to present an investment analysis of a new residential income producing property an investor is considering purchasing. The asking price for the property is $1,200,000; rents are estimated at $201,000 during the first year and are expected to grow at 3.5% per year thereafter. Vacancies and collection losses are expected to be 11 percent of rents. Operating expenses will be 35% of effective gross income. A 70% loan can be obtained at 11% interest for 30 years. The property is expected to appreciate in value at 3% per year and will be sold in 5 years. You determine that the building represents 90% of value and would be depreciated over 39 years (use 1/39th per year). The potential investor indicates that she is in the 36% tax bracket and has enough passive income from other activities so that any passive losses from this activity would not be subject to any passive activity loss limitations. Capital gains from price appreciation will be taxed at 20% and depreciation recapture will be taxed at 25%. The discount rate is 14%.

Please answer in excel

What is the investor’s expected before-tax internal rate of return on equity invested (BTIRR)? What is the investor’s expected after-tax internal rate of return on equity invested (ATIRR)? What is the first year debt coverage ratio? What is the terminal capitalization rate?