Use Excel to create all solutions and submit an excel document for final HW submission. Use the mathematical functions within excel to make all calculations.

You purchase a brand-new property that had an NOI of $12 million last year (year 0). NOI is expected to grow at 5% a year. In order to maintain this growth, you hire a management team to manage the property and they charge 3% of NOI per annum. The risk-free rate is 1%.

1. You go to bank ABC to take out an amortized loan. The going-in cap rate for the property is 6%. They are willing to lend you 75% of the fair market value of the property. The term of the loan is 25 years, the interest rate = 4% above the risk-free rate. What is the maximum amount of debt you can take?

2. Assume you take the maximum amount of debt that bank ABC offered. Your accountant deems that 30% of the property value is attributed to land while 70% is to the building. The building will depreciate for 39 years. Show the value of the building, the land, and annual depreciation.

3. Prepare a 10 year pro forma for the property, ending with ATCFs. Include the following assumptions: a. Tax rate is 30% b. Recapture tax rate is 20% c. Capital gain tax rate is 15% d. Going-out cap rate at the end of 10 years is 8%

4. Calculate the NPV and IRR of the property.

* Net Income is not given.

* Fair Market Value is not given.