Ann is looking to buy an office building in 2014. She plans to rent it out for 5 years (2015-2019) and sell it at the end of 2019.

(1) Use the first sheet of the spreadsheet (titled "NOI") to compute the Net Operating Income (NOI) for this property for 2015. Fill in the missing information in the spreadsheet.

This property has two types of operating income, rental income and parking.

Rental Income in 2015 = (# square feet)*(Annual rent per square foot in 2015)

Parking is a fixed dollar amount given

Vacancy expenses in 2015 = (Rental vacancy rate in 2015)*(rental income in 2015)

Collection losses in 2015 = (collection loss rate in 2015)*(PGI in 2015)

Operating Expenses in 2015 = (Operating expense rate in 2015)*(EGI in 2015)

(1.a) What is the NOI for 2015?

(1.b) If this building is being sold in 2014 at an asking cap rate of 6.50%, what is the asking price?

(2) Fill in the second sheet (titled "underwriting").

Where it says "Income Test", compute the biggest mortgage payment Ann can make given her NOI in 2015 computed above.

Note: , which implies the maximum. This is the debt service coverage test.

(2.a) What is the biggest mortgage payment the bank will allow Ann to make given her expected NOI in 2015?

Using the maximum payment, compute the maximum loan Ann can get.

Recall for an IO loan:

The interest rate is given in the spreadsheet. Use the PMT computed in (2.a)

(2.b) What is the maximum loan amount corresponding to a DSCR of 1.2?

(2.c) What is the biggest loan Ann can get assuming 90% maximum LTV?

(2.d) Assume for the rest of the question that Ann will Borrow the maximum available loan amount. What is the loan that Ann borrows to buy the property?

(2.e) What will Ann's annual debt service payment be?

(3) Fill in the sheet titled "NPV-IRR".

Ann will buy the property in 2014, she will collect NOI for 5 years 2015-2019, and she will sell it in 2019.

Ann's loan has a 5/4/3/2/1 prepayment penalty structure, so if she prepays in the first year, she will pay a penalty equal to 5% of the balance, in the second year she will pay a penalty equal to 4% of the balance etc.

Ann forecasts NOI will grow at 2% per year, compounded annually.

Ann forecasts she can sell the property in 2019 at a 6.25% cap rate.

Recall:

(3.a) How much will Ann sell the property for in 2019?

(3.b) How much of a capital gain will Ann earn? (Hint: capital gain = sale price - purchase price)

(3.c) What is Ann's IRR for this investment?

(3.d) If Ann's discount rate is 25%, what is her NPV? Should she make this investment?

(3.e) Plot Ann's NPV for discount rates 0%-100%. Copy and paste the chart below.

(4) Fill in the sheet titled "Loan Analysis" but ONLY include cash-flow related to Ann's loan in the sheet "NPV-IRR". Leave cells unrelated to the loan cash flow blank.

(4.a) What is the annualized IRR for Ann's loan?

(4.b) Is the IRR for Ann's loan higher or lower than the advertised loan contract rate?

(4.c) Why?

(4.d) Plot Ann's NPV for Ann's loan, for discount rates 0%-10%. Copy and paste the chart below.

Inputs Green text represent inputs Square feet 20000.00 Annual rent per square foot 2015 $72.50 Parking income 2015 $55,000.00 Rental vacancy rate 2015 2.40%% of square feet 2015 Collection loss rate 2015 2.00%% of PGI Operating expense rate 2015 47.00%% of EGI Rental Income $1,450,000.00 Parking $65,000.00 Potential Gross Income (PGI) Vacancy Expenses Collection losses Effective Gross Income (EGI) Operating Expenses Net Operating Income {MOI} Asking cap rate 2014 6.50% Asking Price