Carry out all your calculations in Excel using Excel functions and show your work! Do not cooperate with other students! BACKGROUND An investor is interested in purchasing a multi-tenant office building in Portland, OR which has an asking price of $183 per SF. Assume that, apart from 2% purchasing cost, there are no additional acquisition-related costs. The building size is 60,390 SF. It is currently leased to three tenants: The first tenant occupies 24,156 SF and currently pays $25.5/SF/year. The lease will expire in 2 years (for simplicity, assume it will expire in December 2022). Based on conversations with the tenant, you can assume that the lease renewal probability is 10%. . The second tenant occupies 10,266 SF and currently pays $24/SF/year. Font Jase will expire in 4 years (for simplicity, assume December 2024) and has annual rent increases of 3%. Based on conversations with the tenant, you can assume that the lease renewal probability is 30%. The third tenant occupies the remaining space and their current rent is $22.5/SF/year. The lease will expire in 6 years (for simplicity, assume December 2026). Rental increases of $2 per SF will occur in January 2023 and January 2025. Note: Assume the current contract rents as basis for your year 1 potential gross income. V&C for the first two years are assumed to be 0%. After the first lease expires, assume a V&C of 5% of the PGI each year, which will increase to 10% once the second lease expires. The average market rent for office is currently $19.5/SF/year, and it is forecasted to decrease by 4% each year for two years and then increase again at 3.5% each year for the subsequent 4 years. Operating expenses for all leases are currently $8/SF/year and will increase annually by 2% (expense inflation). The landlord covers 25% of operating expenses. Assume that the landlord pays $2/SF of tenant improvements for new tenants and $1/SF for renewing old tenants. TIs are not tenant-specific and will also be useful for future tenants (i.e. treat them as a capital improvement as opposed to a tenant-specific improvement when determining tax-liability). For simplicity, no other capital expense reserve is assumed, and no leasing commission is assumed for new or renewing tenants. Assume that the building is bought in January 2021 and sold in December 2025. The building is depreciated over 39 years (mid-month convention for first and last year) and the value of improvements (building) is considered to be 80% of the purchasing price. The TI are depreciated over 15 years. The going out cap rate is 8.5% and the investor requires a return of 10%. Selling costs are 2% of the sales price. The investor expects to hold the building for 5 years. Assume an income tax of 35% and a capital gains tax of 17%. Makes sure you tax the depreciation recapture and capital gain with the appropriate tax rates. Assuming that the investor wants to hold the property for 5 years, conduct a discounted cash flow analysis (DCF) to calculate the IRR and NPV based on NOI and net selling price for this investment. Is this investment worth undertaking? Why/why not? Carry out all your calculations in Excel using Excel functions and show your work! Do not cooperate with other students! BACKGROUND An investor is interested in purchasing a multi-tenant office building in Portland, OR which has an asking price of $183 per SF. Assume that, apart from 2% purchasing cost, there are no additional acquisition-related costs. The building size is 60,390 SF. It is currently leased to three tenants: The first tenant occupies 24,156 SF and currently pays $25.5/SF/year. The lease will expire in 2 years (for simplicity, assume it will expire in December 2022). Based on conversations with the tenant, you can assume that the lease renewal probability is 10%. . The second tenant occupies 10,266 SF and currently pays $24/SF/year. Font Jase will expire in 4 years (for simplicity, assume December 2024) and has annual rent increases of 3%. Based on conversations with the tenant, you can assume that the lease renewal probability is 30%. The third tenant occupies the remaining space and their current rent is $22.5/SF/year. The lease will expire in 6 years (for simplicity, assume December 2026). Rental increases of $2 per SF will occur in January 2023 and January 2025. Note: Assume the current contract rents as basis for your year 1 potential gross income. V&C for the first two years are assumed to be 0%. After the first lease expires, assume a V&C of 5% of the PGI each year, which will increase to 10% once the second lease expires. The average market rent for office is currently $19.5/SF/year, and it is forecasted to decrease by 4% each year for two years and then increase again at 3.5% each year for the subsequent 4 years. Operating expenses for all leases are currently $8/SF/year and will increase annually by 2% (expense inflation). The landlord covers 25% of operating expenses. Assume that the landlord pays $2/SF of tenant improvements for new tenants and $1/SF for renewing old tenants. TIs are not tenant-specific and will also be useful for future tenants (i.e. treat them as a capital improvement as opposed to a tenant-specific improvement when determining tax-liability). For simplicity, no other capital expense reserve is assumed, and no leasing commission is assumed for new or renewing tenants. Assume that the building is bought in January 2021 and sold in December 2025. The building is depreciated over 39 years (mid-month convention for first and last year) and the value of improvements (building) is considered to be 80% of the purchasing price. The TI are depreciated over 15 years. The going out cap rate is 8.5% and the investor requires a return of 10%. Selling costs are 2% of the sales price. The investor expects to hold the building for 5 years. Assume an income tax of 35% and a capital gains tax of 17%. Makes sure you tax the depreciation recapture and capital gain with the appropriate tax rates. Assuming that the investor wants to hold the property for 5 years, conduct a discounted cash flow analysis (DCF) to calculate the IRR and NPV based on NOI and net selling price for this investment. Is this investment worth undertaking? Why/why not