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 a 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 property$$s 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 Sprout$$s$,$ 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:

Sprout$$s:

$-$ 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\mathrm{.}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\mathrm{.}15\%$ per month.

$-50\%$ of the costs are reimbursable.

Fancy Nails:

$-$ Unlike Sprout$$s$,$ this tenant is only leasing for a year.

$-$ The tenants were easy to find making the leasing commission only $0\mathrm{.}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\mathrm{.}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\mathrm{.}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\mathrm{.}5$ per square foot of tenant improvements.

$-$ Rent here is also $$6$ a square foot per month and expected to grow by $0\mathrm{.}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\mathrm{.}25$ a square foot in TI for the first $3$ years.

$-$ Rent is $$10$ a square foot per month and grows at $0\mathrm{.}8\%$ per month.

$-$ Operating expenses are $$4$ per square foot initially but are expected to grow $0\mathrm{.}15\%$ per month.

Capital expenditures estimation is $$0\mathrm{.}2$ per leasable square feet on a monthly basis to keep the building competitive but it is expected to grow $0\mathrm{.}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?