You can do this assignment on a spreadsheet. In fact, I recommend it. One mistake early can mess up everything. This is easily fixed on a spreadsheet. However, follow the directions below for showing your work. If you do a spreadsheet, turn it in as well.

You are considering purchasing an upscale, suburban shopping center for the price of $11,500,000. Seventy percent (70%) will be financed with a 20-year loan at 10 1/4 % interest with fixed monthly payments. The remaining 30% will be financed with cash (equity). Ninety percent (90%) of the purchase price is attributed to the improvements and is depreciable. A total of 75,000 square feet can be leased at $20 per square foot per year. Vacancy and credit losses are expected to be 12%. Operating expenses are expected to run 20% of the effective gross income. Rent is expected to grow 2.5% per year.

Assume you will sell the property in 5 years. At the end of year 5, the property is projected to be valued at the year 6 NOI capitalized at 7.75% (direct capitalization). Selling expenses are estimated to be 7% of the selling price. You are in the 30% income tax bracket (congratulations!!) and expect to pay a 15% tax on the capital gain and a 25% tax on the recovery of depreciation.

Partial Amortization Schedule: (rounded to nearest dollar)

Year Balance Due Interest Principal

1 $7,920,904 $819,172 $129,096

2 $7,777,936 $805,300 142,968

3 $7,619,606 789,937 158,330

4 $7,444,262 $772,923 175,344

5 $7,250,078 $754,082 194,185

For the following, SHOW ALL YOUR WORK EXPLICITLY BY HAND [check answer: ATCF₃ = $144,951]:

A. Calculate the first years estimated ATCF.

B. What is the sixth years estimated NOI?

C. Calculate the estimated taxes on sale, as if you were selling after 5 years [Value₅=$15,416,349]?

D. Calculate the estimated ATER, as if you were selling after 5 years?

E. What is the break-even vacancy rate (year 1)?

F. What is the debt coverage ratio?

For the following, you may use cash flow tables or calculator strokes. If you use a spreadsheet, include it as an attachment. If not, include your work as an attachment.

G. If the discount rate is 14%, what is the NPV?

H. What is the IRR? [The answer to this is 16.2%]