(Comprehensive) Final Case 2 - Commercial Real Estate Valuation, Return & Risk Analysis Course: FIN 315 - Real Estate Finance Prof. Seung Hee Choi Due Date: Thursday, May 12, 2022 (Canvas only) Submission Format: Excel (Required) Background Information An office building has three floors of rentable space with a single tenant on each floor. The property is purchased for $6,500,000. 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 7.5 percent annual interest for 30- 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 16,000 square feet of rentable space and is currently renting for $17 per square foot. Three years remain on the lease. The lease has an expense stop at $7.5 per square foot excluding management. The second floor has 18,000 square feet of rentable space and is leasing for $19.50 per square foot and has four years remaining on the lease. This lease has an expense stop at $7.50 per square foot excluding management. The third floor has 20,000 square feet of leasable space and a lease just signed for the next five years at the rental rate of $18 per square foot, which is the current market rate. The expense strop is at $7.5 per square foot, which is what expenses per square foot are estimated to be during the next year excluding management. Management expenses are expected to be 6.5 percent of effective gross income and are not included in the expense stop. Each lease also has CPI adjustment that provides for the base rent to increase at half the increase in the CPI. The CPI is projected to increase 6.3 percent per year. Estimated operating expenses for the next year include the following: Property taxes $ 120,000 Insurance $ 18,000 Utilities $ 75,000 Janitorial $ 15,000 Maintenance $ 45,000 Total $ 250,000

FIN 315 with Dr. Seung Hee Choi Comprehensive Final Case 2 Spring 2022 All expenses are projected to increase 4.5% percent per year. The market rental rate at which lease are expected to be renewed is also projected to increase 5.5 percent per year. 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). Your Assignment 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 9% 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, and any additional notes that can help your readers. You must create an excel file with all calculations/equation/models are available for me to review. A value copy of an Excel file or a Google sheet will be accepted. Every Excel sheet must be readable. In addition, you must add one summary sheet where you provide your answers to all listed items above, a list of major assumptions, and any additional notes you find necessary/important. All Excel sheets must be in ONE excel file. Please utilize the AS lecture clips, if necessary.