Please show work in excel.

Suppose you are considering the acquisition of a hotel. The building is currently leased for the next 5 years with annual (year-end) rents of $3,000,000. At the end of the current lease, you expect rents to increase to $3,500,000 (annually) for the foreseeable future. You anticipate selling the property five years from today, and the expected cap rate at that time is 8%. The transaction costs are 5% of the sales price. Market discount rate is currently 10%, but given the uncertainty surrounding future rental rates a 3% risk premium must be added to the discount rate. Your WACC is 10% and the current cap rate is 7.75%. The building to land ratio is 3:1 and the expected life of the property is 39 years. You contacted your banker and got the following underwriting standards: LTV of 80% and DSCR of 1.2. The mortgage loan details are: 7.5% 30 year monthly amortizing loan. The tax rates are as follows: 22% income tax, 25% depreciation recapture tax, 20% capital gains tax.

1. What is the market value of this property today?

2. What is the investment value to you?

3. What is the maximum loan your banker could provide you?

4. Calculate the annual interest expense in year 2

5. Calculate the cumulative depreciation in year 3

6. Calculate the taxable income in year 4

7. Calculate the depreciation recapture tax

8. What is the overall gain during the sale of the property?

9. What is the capital gain?

10. Calculate your IRR based on ATCF

11. What is the property`s IRR?