Spread Sheet Problem 2

Spread Sheet Problem 2

Real Estate Investment and Finance

You are to complete an investment analysis of the following income producing office property which you are considering purchasing for $17.5 million, where the building is 73% of the value and the land 27%. 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 lease term (yrs)	CPI adjustment (%)
Accounting Firm	50,000	13.50	$675,000	1	100%
Doctor	40,000	14	560,000	2	50%
Insurance Agency	70,000	15	1,050,000	3	100%
Retail Store	120,000	16	1,920,000	4	50%
Total	280,000		4,205,000

The current market rent is $16.00 per square foot, the leasable square feet area is 280,000, market rents are projected to increase by 3% per year, management costs are 4% of the effective gross income and the estimated annual increase in the consumer price index is 3% 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:

First-Year Reimbursable Expenses and Projected Increases
	Dollars	Dollars/sq. ft	Projected increases
Property tax	$672,000	2.31	Increase 2%/yr
Insurance	84,400	0.30	Increase 3%/yr
Utilities	350,000	1.25	Increase 2.5%/yr
Janitorial	280,000	1.00	Increase 2.5%/yr.
Maintenance	112,000	0.4	Increase 3.5%/yr.
Total	1,498,400	5.35

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 75% of the price using a fully amortizing fixed rate mortgage at a 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 13% discount rate?

Should you accept the project based on a required return of 13%?

What’s the ATIRR with debt and without debt?

The format of the title for anything submitted electronically is: Lastname. firstname. Course number.sec.nameassignment. For example, if I were emailing me the first assignment the title of the file would be: Donovan.Shannon.486.001.assignment3.docx