Use Excel to create all solutions and submit an excel document for final HW submission. Use the mathematical functions within excel to make all calculations. Partial credit is only possible if functions are used in all calculable cells.

Drexel University (for-profit) makes an investment in a property. The property was closed on 12/31/2019. The property is 200,000 sf and has 3 tenants that came into their lease at the end of 2019 on an 8 year lease. The tenants is a WalMart that occupies 100,000 sf, a BestBuy that occupies 70,000 sf, and a boutique shop that occupies 30,000 sf.

Drexel purchased the property for $60 million. Drexel closes the property with a one-time legal expense of $1 million to be capitalized over 5 years. They financed the purchase with $20 million in equity and a mortgage of $40 million. The structure of the mortgage is amortize over 30 years, 6%, and Drexel pays it monthly.

Drexels CPA (Certified Public Accountant) attributes 30% to the land and the rest to the building which depreciates over 39 years. Drexel gets taxed on the purchase price of the property until it gets reassessed. They will pay 2% of the property value for the next 5 years.

They collect rent revenue of $20 sf/yr for WalMart since it is an anchor store. They charge BestBuy $25 sf/yr plus 10% of sales that exceeds $200 sf/yr. Sales in the first year of BestBuy is expected to be $160 sq/ft and growing at 6% per year. The boutique store is charged $23 sf/yr plus 10% for sales that exceeds $100 sf/yr. Sales for the boutique store in year 1 is $105 sf/yr and is expected to grow at 4% per year.

In order to get these terms, Drexel hired a broker. The broker charged 2% in LC (leasing commission) on the value of the lease (only the base rental income). The lease runs for 8 years. Tenant improvements (TI) of $20 sf was giving to each tenant, which the each tenant uses 50% of the TI in 2020 and the remaining equally throughout the rest of the lease. At the end of the lease, each tenant decides to renew and Drexel doesnt offer any concessions upon renewal. The new rent revenue is the original rent revenue grown by 1% each year since the first lease was made. The addition in rent revenue doesnt change and is based on the details of the 1^st lease.

The costs are allocated as follows for 2020. Insurance on the property cost $0.50 sf/year. Maintenance and Repair is $0.80 sf/year. Utility is $1.00 sf/yr. Accounting expense is $30,000 per year. Legal expense is $40,000 per year. Finance expense is $50,000 per year. Management fee is 1.5% of total revenue. Inflation is expected to be 2% a year after 2020. All of the costs are expected to increase by this amount with the exception of Utility which is expected to grow at 2x of inflation. Tax, Insurance, Maintenance and Repair, and Utility is reimbursable (assume no bad collection).

At the end of March 2020, Drexel decides to repave the driveway for $200,000. At the beginning of June, Drexel re-did the roof for $500,000. At the end of 2020, Drexel re-did the plumbing for $1,000,000. Drexel talked to its CPA and decided these items are capital improvements that would last for 7 years.

At the end of year 5, an appraiser reassess the property at NOI of year 5 projected by your model over the cap rate. The building and land split remains the same. The cap rate for that area (Philadelphia) was determined to be 6%. In addition, the city of Philadelphia is expected to raise the tax rate on Drexels properties to 2.5% of fair market value starting for 2025 and beyond.

Create a Property Level Proforma Analysis statement for the next 10 years (2020-2029) with a tax rate of 21%.