Transaction Summary

You are the senior acquisitions associate at a New York-based private equity fund with a core investment focus. You have reached an agreement to purchase a well-located, 185,000 square foot office building in a solid suburban market. It was built in 2017 and has no additional leasing since the pre-development leases were signed. The submarket is quite desirable with no new supply coming on-line, yet economic growth is uncertain.

Income Summary

It is currently leased to three tenants on a gross (full-service) basis: Tenant #1 occupies 50,000 square feet at $29 per square foot annually through 12/2029, Tenant #2 occupies 80,000 square feet at $28 through 12/2030, and Tenant #3 occupies 36,500 square feet at $32 per square foot through 12/2031. Other/ancillary income has added another $4 per total sf of revenue annually, which you dont see changing after acquisition. Operating expenses have consistently been $14 per total sf annually but you expect to immediately reduce this by 14.3% at the time of closing. On a run-rate basis (stabilized), capital expenditures, leasing commissions, and tenant improvements are expected to cost you $0.65 per total sf annually. There is no growth expected from this investment (flat revenue, flat expenses, flat cap ex, TIs/LCs).

Capitalization Summary

You are purchasing this building for $227.00 per total square foot. You have lined-up a 10-year mortgage financing of 70% LTV at an interest rate of 6.5% with a 30-year amortization schedule (payments calculated monthly but paid annually, for modeling purposes). You have also lined up a 5-year mezzanine loan (treat as a second mortgage) for an additional 10% of the purchase price at an interest-only rate of 11.00%. Assume zero transaction costs for the sale/disposition of this investment.

Investment Strategy

You are purchasing the building on January 1, 2020 and plan on selling the building at the end of the year 2023. You believe your exit capitalization rate will be 75 basis points lower than your going-in capitalization rate.

_______________________________

Create an Excel and Show all Formulas Please

_______________________________

If you can please answer the following (the Excel and Formulas are more important):

1. What is your first-year NOI? What is your purchase price? What is the going-in capitalization rate (NOI based)?

2. What is the principal amount of your mortgage? What is the annual amount of your mortgage loan payment? What is the principal amount of your mezzanine loan? What is the annual amount of your mezzanine loan payment? What is your total leverage ratio?

3. What is the first-year cash flow available to the equity investor?

4. Assuming that buildings are bought and sold based on NOI, what is the exit price of your investment in December 2023? What is your loan balance at the time of sale? What are your net investment proceeds from this sale?

5. What is the IRR of your equity investment?

6. Assuming that your private equity fund has a hurdle rate of return (Re) of 15%, what is the PV of this equity investment?

7. What is the NPV of this equity investment?

8. What is the mortgage lenders going in DSCR? What is the mortgage lenders going-in Debt Yield?

9. Explain the difference between PV and NPV.

10. What happens to the market value (directionally) of a loan originated 2 years ago if current market interest rates are 200 bps higher?

11. Why would a lender require amortization?

12. Assuming the mortgage lender wanted a YTM of 6.75%, what amount of discount points would the lender need to charge to obtain such yield?

13. Given the amount of points found in the answer above, what will be lenders YTM given that you plan on paying off the loan on 12/31/2023?

14. If inflation is expected to be 3% per year during the life of your hold, how would this impact your decision to make this investment? Would your inflation-adjusted IRR be higher or lower than forecast above? What unique factors of this deal make it well-suited or ill-suited to act as an inflation hedge?

15. In mid 2021, if you learned that Tenant #1 would be willing to vacate on 12/31/2021 if you found a new tenant. By luck, a tenant representation broker just presented a requirement for 50,000 square feet starting on January 1, 2022 for a well-respected international Fortune 500 company with an equal credit rating to Tenant #1. The lease offered is for 10-years and the terms are, zero rent first year, $27 per square foot second year, then growing by 3% each year for years 3 through 10. Rent is paid at the END of each year in one lump sum. A. Strictly looking at the NEW lease versus the EXISTING lease, which gives you the highest NPV as of 1/1/2022 (not factoring the planned sale, or anything else) assuming a discount rate of 8%? B. What would the sale price of the building be on 12/31/2023 if the NEW lease is accepted? C. Assuming the inflation expectation of 3% per annum is expected to remain, what you ADVISE the fund to do regarding this leasing decision (you must support your answer with logic and/or math)?

16. Scenario Analysis You have closed on the deal as planned on 1/1/2020. In March of 2020, a global pandemic hits and mandated lockdowns shutdown the city. On May 1st, 2020, Tenant #2 (the largest at 80,000) announces bankruptcy. This location is their corporate HQ and they intend to remain there if possible, but per bankruptcy law, the terms of the lease could be change and potentially canceled. The tenants legal approaches you with two options they believe the federal judge and creditors would accept.

A. Option A You can accept the following terms for the tenant to keep all 80,000 SF:

- New Rent $22 psf until 12/31/2025, starting 6/1/2020

- Rent increase up of 10% in psf rent starting 1/1/2026 until the end of the original termB.

Option B The original lease terms stay in place until 12/31/2020, at that point the tenant surrenders 40,000 SF (you can now lease this space if a new tenant is found) and will keep 40,000 SF at the following terms:

- New Rent $26 psf until 12/31/2025, starting 1/1/2021

- Annual rent increases of 3% psf starting 1/1/2026 until the end of the original term

Question, what do you recommend your firm does? Support with models, math, etc. Make extra assumptions (like on when and for how much the vacant space leases, if chosen) where needed.