Use Excel to create all solutions and submit an excel document for final project submission. Use the mathematical functions within excel to make all calculations. Partial credit is only possible if functions/equations are used in all calculable cells. You finally graduated from Drexel University with a REMD degree. Out of college, you received employment from a Real Estate development company that purchased a one acre plot of land with the purpose of constructing a strip mall in University City with 4 tenants: a grocery store, a nail salon, a barber shop, and an electronics retail shop. You are given the task to go through all stages of this propertys development and leasing. The purchase was for $125 per gross square foot. The FAR for this plot is 1 and the leasable to gross area ratio is 60%. You expect the construction to be quick, finalizing in 12 months from Jan 1, 2023 to Dec 31, 2023. Additional assumptions are as follows: - Hard costs: $250 per gross square foot - Soft costs: $180 per gross square foot - LTC: 70% with an annual interest of 8% - Land cost will be paid in two parts: 70% at the start of construction and 30% at the end of construction. - Hard and soft costs are split evenly per month. Build a construction phase proforma for the property. Find the ending construction loan balance. Now that the construction is done, it is time to refinance the construction loan at the end of 2023. You take your proforma to Wells Fargo to refinance your construction loan into a permanent loan. Wells Fargo offers a 30 year, 5% interest rate, 75% LTV loan. There is a permanent loan issuance fee of 1% After the construction of the UC strip mall, your boss found the 4 tenants: Tenant 1, a Sprouts, occupies 40% of the leasable square feet. Tenant 2, Fancy Nails, occupies 10%. Tenant 3, Best Cuts, occupies 20% and Tenant 4, a RadioShack, occupies the remaining leasable area. General vacancy is 10%of total rent revenue split evenly between the tenants. The management team behind this strip mall charges 6% annually on EGI. Your boss wants to sell the property after 5 years. Additional tenant assumptions are as follows: Sprouts: - The broker behind this deal charged a leasing commission of 3% based on monthly rent paid by the tenant. - The deal came with a month of free rent and a dollar per square feet of tenant improvements. - Monthly rent is $12 a square foot. Rent grows 0.25% each month. - Lease lasts for the 5 years. - Operating expenses for this tenant are $5 per square foot initially but are expected to grow 0.15% per month. - 50% of the costs are reimbursable. Fancy Nails: - Unlike Sprouts, this tenant is only leasing for a year. - The tenants were easy to find making the leasing commission only 0.5% based on monthly rent paid by the tenant. - No benefits were offered in this contract. - Rent is $6 a square foot per month when the lease starts and grows by 0.15% per month - Operating expenses are determined to be $2 per square foot for the first 6 months, then are expected to grow by 0.25% per month. - 15% of the operating expenses are reimbursable. - After the year, new tenants with the same business are expected to lease the space but you will have to give them 2 months of free rent in order to secure the deal and give them a dollar of TI per square feet as well. Best Cuts: - Tenant signs a 2 year lease. 2% in leasing commissions are paid based on monthly rent paid by the tenant. - No tenant is expected after the contract ends even though 3 months of free rent are offered plus $1.5 per square foot of tenant improvements. - Rent here is also $6 a square foot per month and expected to grow by 0.15% per month - Operating expenses are not given so it is assumed that the tenant will pay for all of these costs. RadioShack: - Once again, tenant signs a 5 year lease and 3% leasing commissions are paid based on monthly rent paid by the tenant. - Your boss offers 2 months of free rent and $1.25 a square foot in TI for the first 3 years. - Rent is $10 a square foot per month and grows at 0.8% per month. - Operating expenses are $4 per square foot initially but are expected to grow 0.15% per month. Capital expenditures estimation is $0.2 per leasable square feet on a monthly basis to keep the building competitive but it is expected to grow 0.2% per month. The building is depreciated over 39 years. Going out cap rate 2028 is 6% (assume an 8% growth rate of NOI beyond year 2028). The depreciation recapture tax rate is 25% and the capital gains tax rate is 20%. The ordinary tax rate in this case would be 20%. Calculate the after tax cash flows to equity holders from 2024 to 2028 including ATER. Calculate the NPV at 8% and the IRR. Should you and your boss take this investment