Use Excel to answer all questions. Use the mathematical functions within excel to make all calculations.

A university bought an office building for $6,500,000 5 years ago with the purpose of renting as a residential area. The assumptions are:

• First year potential gross income of $1,200,000 with a 3.2% annual growth rate
• Vacancy & collection losses equal to 12% of PGI
• Operating expenses:
  • First year insurance of $145,000 with a 2.5% annual growth rate
  • First year Utilities of $137,000 with a 2% annual growth rate
  • First year Maintenance Expense of $75,000 with a 1.5% annual growth rate
• Capital expenditures = 4% of EGIwith a .5% annual escalation
• 70% LTV at 6%
• Mortgage will be amortized over 25 years
• Total up-front financing costs:
  • 2 points of the loan amount
  • $1000 appraisal fee

1. Build a 25 yearamortization table.

1. Calculate After Tax Cash Flows including After Tax Equity Reversion taking into account the following assumptions:

• 80% of the original cost is allocated to depreciable real property. The cost recovery period is 27.5 years.
• The university is in the 30% tax bracket on ordinary income
• If the university were to sell the property at the end of year 5, assume the sale price equals the year 6 NOI capitalized at 10% and selling costs equal 5% of the sale price
• Capital gains tax rate = 20%
• Depreciation recapture tax rate = 25%

1. Calculate After Tax NPV and IRR. Similar properties use a 6.5% discount rate.

• Did the university make a good choice by investing in this property?