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. Partial credit is only possible if functions are used in all calculable cells.

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%. Use a discount rate of 9% when calculating the NPV

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:
   1. Tax rate is 30%
   2. Recapture tax rate is 20%
   3. Capital gain tax rate is 15%
   4. Going-out cap rate at the end of 10 years is 8%
4. Calculate the NPV and IRR of the property.