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.

Final Case Study

This case study is a cumulation of all 4 units. You will be taken through all stages of the real estate process. You will acquire a new piece of land, construct it, lease it out until projected stabilization, and then sell it. Hopefully, you get the returns that you want!

Part 1

Drexel just bought a 3 acre lot of land which they are thinking of constructing an office building for different kind of employees so they can hire Drexel students. The construction, finance, and exit assumptions are:

• Land size: 3 acres at $150 a square foot
• Floor Area Ratio: 8
• Leasable to Gross Area Ratio: 85%
• Hard Costs: $200 a square foot
• Soft Costs: $130 a square foot
• Construction Time: 24 months
• LTC: 75%
• Annual interest 6%
• Land cost: 50% on construction and 50% at the end of construction
• Hard and soft costs are split evenly per month
• At the end of construction, Drexel needs $10 per leasable square foot as capital reserves which they add on to the refinance amount.
• Take on the max amount of debt and the rest is equity (80% Drexel and 20% Developer).
• Construction dates: Jan 1, 2021-Dec 31, 2022

Part 2

After construction, Drexel decides to lease it to three TYPES of tenants. Tenant 1 occupies 35% of the leasable square feet, tenant 2 occupies 35% of the leasable square feet, and tenant 3 occupies 30% of the leasable square feet. All these tenants operate on a triple net lease. After 6 years of operations, Drexel will get out of their investment. The general vacancy for the property is 15%, split evenly between the three types of tenants. Drexel hires a management team that charges 4% (annual) on Effective Gross Income

Tenants 1

• To secure these tenants, Drexel hires a broker that charges 1%. The leasing commission is determined by taking the rent paid in the year and multiplying it by the leasing commission.
• In addition, Drexel gives these tenants one free month of rent and $0.50 per square of TI.

• Each tenant pays $3 square feet per month when their lease starts
• Their lease last for one year. Rent grows by .3% each month (meaning each month tenants 1 pays a different price)
• After each year, only 80% of the tenants renew and Drexel must give new tenants one month of free rent in order to fill the remaining 20% and these new tenants receive $0.50 per square feet in TI.
• Leasing commissions of 1% is paid to secure the new tenants (calculated the same way as before)
• The new lease also lasts for one year and when it ends the same thing occurs.
• Once the building is operational, costs for these tenants are determined to be $1 for the first month per square feet. The cost is expected to grow at 0.2% per month and 40% of the costs are reimbursable.

Tenants 2

• Unlike the first tenants, these tenants are locked into a lease for 2 years. Since these tenants are harder to find, Drexel paid 1.5% in leasing commission (leasing commissions costs are determined the same way as tenants 1). Drexel also offer these tenants 1 month for free.
• Rent is $2.5 square feet per month when the lease starts.
• The rent grows by .25% per month after the first month the lease started
• After 2 years, 70% of the tenants renew while Drexel has to replace the other 30%. They pay a leasing commissions of 1.5% and will give them a month of free rent. TI for new tenants are $1 per square feet.
• Costs are determined to be $0.75 for the first month per square feet. The cost is expected to grow at 0.2% per month and 40% of the costs are reimbursable.

Tenants 3

• These tenants sign a three year leases. Drexel paid 2% in leasing commissions for these tenants.
• Drexel offers 2 months of free rent and $1.25 a square foot in TI for new tenants.
• Rent is $2.25 square feet per month when the lease stats
• The rent grows at .20% per month after the first month.
• After 3 years, the renew probability is 85%
• New tenants receive 2 months of free rent and $1.25 square foot in TI.

In order to keep the building competitive, Drexel has to spend capex of $0.3 per gross square feet per month. The capex is expected to grow at 0.1% per month. The beginning cashflows are very negative; however, remember that we took out capital reserves to cover unexpected cash outflows.

Part 3

• Make your proforma income statement for the property.
• Start with Revenue (Rent, Reimbursement, Subtract general vacancy)
• Figure out Costs (Total of Reimbursement and Non-Reimbursement, Management Fee, TI, LC, Capex)
• Determine what NOI is.
• You cannot find out the cash flow to equity holders because you dont know how much the permanent loan you can take out. The permanent loan that is used to replace the construction loan is based on the value of your property.

Part 4

• The first 2 years of operations the cap rate for the area is 4%. It rises to 4.25% for the next 2 years and ends the last 2 years at 4.5%
• Use the value at the end of 2026 to find the property value
• The discount rate is 10%

Part 5

• Now since you know the value of the firm, it is time to re-finance your construction loan at the end of 2021 (when your construction is complete).
• You take your pro-forma to a second bank to refinance your construction loan into a permanent loan,
• The bank offers you a 30 years, 5%, 70% LTV loan. You take the maximum amount possible and finish your pro-forma statement.
• There is a permanent loan issuance fee of 1%

Part 6

• Calculate the Cash Flows to Equity Holders from 2020-2026.

• Calculate the NPV at 10%
• Calculate the IRR
• Calculate the Return on invested capital.
• Should Drexel take this investment?

NOTE: There is no tax rate as Drexel is a non-profit and/or this is a pass-through investment.