Question: Refer to Problem. Develop a spreadsheet to investigate the sensitivity of annual profit (loss) to changes in the occupancy rate and the annual rental fee.

Problem: A group of private investors borrowed $30 million to build 300 new luxury apartments near a large university. The money was borrowed at 6% annual interest, and the loan is to be repaid in equal annual amounts (principal and interest) over a 40-year period. Annual operating, maintenance, and insurance expenses are estimated to be $4,000 per apartment, and these expenses are incurred independently of the occupancy rate for the apartments. The rental fee for each apartment will be $12,000 per year, and the worst-case occupancy rate is projected to be 80%.

a. How much profit (or loss) will the investors make each year with 80% occupancy?

b. Repeat Part (a) when the occupancy rate is 95%.