Complete this problem using excel.

Your company is looking at an investment in a mixed-use commercial property. The property is on the market for $1,600,000. IF financing is chosen, the property will be financed with a commercial mortgage and the bank has offered a 20 year note, 80% financing and interest rate of 7.2%. The prospective property has four commercial units on the first floor and 16 residential units on the upper floors. The residential units are 12 one-bedroom apartments (900 square feet each) and 4 two-bedroom apartments (1,300 square feet each). Each of the commercial units are 2,000 square feet. The market rents for residential units are based on gross lease terms with one-bedroom apartments renting for $950 per month and two-bedroom apartments $1,200 per month. The apartments have typical finishes and parking for all tenants. The vacancy rate in the market for residential units is 12%, but declining. The market rents for the commercial units are based on triple-net (NNN) leases and are $12.50 per square foot. The tenants CAM reimbursement is $1.25 per square foot. The commercial rental market is tight and vacancy rates are at 3%. Expenses at the property are based on comparable properties and actual expenses when available. Taxes are $19.00 per $1,000 of the property purchase price, repairs and maintenance are $22,500 per year, utilities are estimated at $7,000 per year, insurance $12,000 per year, management fees are 3% of EGI and miscellaneous costs are $2,500 per year. Capital reserves are based on $0.75 per square foot of building area. A survey of the market was completed to determine the appropriate cap rate. Based on sales of similar properties a going in cap rate of 8.5% is appropriate. With your management team you determined that you would likely hold this property for six years. The economy is looking good and over that period you expect property prices to increase 2.6% per year. Typically you maintain your properties to a high quality and invest in growing markets so your load factor for terminal cap rate is 125 basis points, and includes all selling related costs. Rents are currently at market and the market is in balance. Growth in rents will be slow and a conservative 2.8% annual growth rate is used. Expenses have been a challenge for property owners as utility and insurance costs rise, therefore a 2.6% growth estimate is used for expenses. The company is currently using an 11% discount rate on all projects.

Questions: What is the PGI?

What is EGI?

What are the Total Expenses?

What are the Capital Reserves?

What is the NOI?

What is the property value?

What is the IRR/NPV with and without debt?