You are to complete an investment analysis of the following income producing office

property which you are considering purchasing for $$21$ million, where the building is

$75\%$ of the value and the land $25\%.$ The property currently has four tenants. An

accounting firm that signed a five year lease four years ago; a doctor, who signed a five

year lease three years ago; an insurance agency that signed a five year lease two years

ago and a retail store that signed a five year lease last year. The summary information is

below.

The current market rent is $$16\mathrm{.}25$ per square foot, the leasable square feet area is

$280,000,$ market rents are projected to increase by $4\%$ per year, management costs are $3\%$

of the effective gross income and the estimated annual increase in the consumer price

index is $2\%$ per year. The leases all have expense stops which include all operating

expenses, but the owner of the property will incur property management expenses that

will not be chargeable to the tenants. All amounts in excess of the expense stop must be

paid by the tenant in addition to the base rent. No expense reimbursement is projected

for the year that leases are renewed and the stops included in the lease renewals will be

based on the actual expenses in that year. The expense stops in the existing leases are

listed below: The current expenses and the estimated annual increases for the expenses are listed

below:

We assume no vacancy until the first lease is renewed at which time the projects vacancy

will be $3\%$ of the potential rent. The leases will renew at the current market rent for that

year. The holding period will be $6$ years and the estimated sales price will be based on the

$7^{th}$ year NOI and a terminal cap rate of $12.$ Assume the building will be depreciated over

$39$ years straight line and your tax rate is $28\%.$ Assume capital gains and recaptured

depreciation are all taxed at the same $28\%$ and you have enough passive income to take

any passive losses.

Assume you can borrow $80\%$ of the price using a fully amortizing fixed rate mortgage at

a $5\mathrm{.}5\%$ rate of interest for $25$ years.

Using a spread sheet develop the cash flow before and after tax with no debt and with

debt. A template is provided that matches the example in the book. Remember the

spread sheet should have all the inputs at the top and the spread sheet itself should

include all calculations. This way if any of the inputs change such as the interest rate or

price of the property all the calculations would change. The only thing that will NOT

change is the holding period.

What are the BTIRRs with debt and without debt?

What is the first year debt coverage rate?

What's the first year or going in capitalization rate?

What's the NPV on the BTCF without debt using a $14\%$ discount rate?

Should you accept the project based on a required return of $14\%?$

What's the ATIRR with debt and without debt?

The format of the title for anything submitted electronically is: Lastname. firstname. Course. I need you to make a google sheet with all the calculations. I also attached an example with different numbers I$\text{'}$m just not sure how the calculations were achieved.