(Comprehensive) Final Case 2 - Commercial Real Estate Valuation, Return & Risk Analysis Background Information You consider the possible purchase of an office building by an investor for $13 million. The office building has three floors of rentable space with a single tenant on each floor. The property is expected to appreciate in value at 5.5 percent per year and is expected to be owned for five years and then sold. You will obtain a fully amortizing 70 percent loan with 8.5 percent annual interest for 20-year term. The building represents 90 percent of value and would be depreciated over 39 years (use 1/39 per year). Estimated tax brackets in the following: Income Tax 35% Tax on price appreciation 20% Tax on accumulate depreciation 25% The first floor has 60,000 square feet of rentable space and is currently renting for $14 per square foot. Three years remain on the lease. The lease has an expense stop at $4 per square foot excluding management. The second floor has 15,000 square feet of rentable space and is leasing for $14.5 per square foot and has four years remaining on the lease. This lease has an expense stop at $4.25 per square foot excluding management. The third floor has 15,000 square feet of leasable space and a lease just signed for the next five years at the rental rate of $16 per square foot, which is the current market rate. The expense stop is at $4.5 per square foot. Management expenses are expected to be 6.0 percent of effective gross income and are not included in the expense stop. Market Rent The current market rent for an equivalent property is $16 per square foot. The market rent will go up by $0.75 using the step-up approach. The respective market rent will be used to define the base rent for the new tenant agreement and/or the contract renewal agreement. When a lease is renewed, it will have an expense stop equal to operating expenses per square foot during the first year of the lease. To account for any item that may be necessary to find new tenants after current leases expire and new leases are made, vacancy is estimated to be 10 percent of EGI for the last two years (year 4 & year 5). Estimated operating expenses for this coming year include the following: Property taxes $ 120,000 Increase 2% per year Insurance $ 20,000 Increase 3% per year Utilities $ 115,000 Increase 6% per year Janitorial $ 15,000 Increase 3% per year Maintenance $ 65,000 Increase 4% per year Total $ 335,000 Note. Before management expenses Your Assignment must include: You must build/use your own template. Project full CFs including the effective gross income, the net operating income, the before-tax cash flows and after-tax cash flows. Find the going-in capitalization rate and the terminal capitalization rate. Calculate the equity dividend rates and the debt coverage ratios for each year (i.e. from year 1 to year 5). Calculate IRRs of the before-tax cash flows (BTIRR) and the after-tax cash flows (ATIRR). Find the NPVs & Equity Multiple (using 11% discount rate) of the before-tax cash flows. Present three scenarios on pessimistic/most-likely/optimistic views based on your assumptions. In addition, you must add one summary tab where you provide a list of major assumptions, your analysis, your final recommendation, and any additional notes.

You must create an excel file with all calculations/equation/models available for review. A value copy of an Excel file or a Google sheet will NOT be accepted. Every Excel sheet (tab) must be readable. In addition, you must add one summary tab where you provide your answers to all listed items above, a list of major assumptions, and any additional notes you find necessary/important.