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. Summary lease information Tenant Square Ft Rent /sq ft Base Rent Remaining CPI adjustment lease term (yrs) (%) 13 $520,000 1 50% Accounting 40,000 Firm Doctor 50,000 14.5 725,000 2 100% 80,000 15 1,200,000 3 100% Insurance Agency Retail Store 110,000 16.25 1,787,500 4 50% Total 280,000 4,232,500 The current market rent is $16.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: Lease Stop Accounting Firm $4.25 Doctor 4.50 Insurance Agency 4.75 Retail Store 5.00 The current expenses and the estimated annual increases for the expenses are listed below: 2.35 First-Year Reimbursable Expenses and Projected Increases Dollars Dollars/sq. ft Projected increases Property tax $658,000 Increase 2%/yr Insurance 98,000 0.35 Increase 2%/yr Utilities 350,000 1.25 Increase 1.5%/yr Janitorial 294,000 1.05 Increase 2.5%/yr. Maintenance 126,000 0.45 Increase 3%/yr. Total 1,526,000 5.45 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 7th 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.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