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.

Recall from slides 12:, which implies the maximum.

(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 with a $1,400,000 down payment and 95% maximum LTV?

NOTE: you may use the formula Maximum Loan = ((LTV)/(1-LTV))*Down payment.

(2.d) Assume for the rest of the question that Ann will make a$1,400,000 down payment. How much will Ann need to borrow?

(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.

A B C D E F Green text represent inputs Square feet 20000.00 Annual rent per square foot 2015 $72.50 Parking income 2015 $65,000.00 Purple text represent outputs 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 10 Potential Gross Income (PGI) 11 Vacancy Expenses 12 Collection losses 13 Effective Gross Income (EGI) 14 Operating Expenses 15 Net Operating Income (NOI) 16 17 18 Asking cap rate 2014 6.50% 19 Asking Price 20 21 22 23 24 25 26 27 28 29 30 31H1 X V fx 444444 A B C D E F G Inputs 2 Annual Interest Rate 5.75%% 10 loan, 5-year balloon Income Test LU Minimum DSCR 1.20 Maximum Annual Mortgage Payment Maximum LTV 95% Maximum Loan Amount 5 Downpayment $1,400,000.00 6 Asking Price $0.00 Collateral Test 7 NOI 2015 $0.00 Maximum Loan Amount w/ downpayment 9 Both tests 10 Maximum Loan Amount 11 Debt Service Payment 12 13 Ann makes a $1,400,000 downpayment 14 Loan amount to buy the property 15 Debt Service Payment 16 17 18 19Inputs Investment Analysis Purchase Price Purchase Costs 300% Year 2014 2015 2016 2017 2018 2019 2020 Loan amount $0.00 Loan Closing Costs 165%% Purchase Price Prepayment Penalty Purchase Costs Debt Service Loan Amount NO 2015 Loan Closing Costs Goling In cap rate 2014 6 50% NO growth rate 2 00%% Net Operating Income (NOT) Exit cap rate 2019 6.25% Debt Service Payment Selling Costs 700% Sale Price Capital Gain $0.00 Selling Costs Loan Balance Repaid Loan Prepayment Penalty Net Cash Flow Sum of Net Cash flow RR Discount Rate INPV 000%E $0.00 5.00%% $0.00 10 00% $0.00 15.00%% 20.00%% 25.00%% $0.00 30.00% $0.00 35.00% $0.00 40.00% 45.00% 50.00%% $0.00 $5.00%% 65.00%E $0 00 70.00%% $0.00 75.00%% 80.00% $0 00 85.00% $0.00 90 00% 95 00% $0.00 100.00%6 $0.00Purchase Costs 3.00% 2014 2015 2016 Loan amount 2017 2018 2019 2020 Loan Closing Costs 165%% Purchase Price Prepayment Penalty Purchase Costs Debt Service $0.00 Loan Amount NO 2015 som Loan Closing Costs Going In cap rate 2014 NO growth rate 2 00%% Net Operating Income (NOT) Exit cap rate 2019 6.25%% Debt Service Payment Selling Costs Sale Price Selling Costs Loan Balance Repaid Loan Prepayment Penalty Net Cash Flow RR Discount Rate UPV SO.00 OLSORE NPV of the Loan 100%% SO.OC $1.00 $0.00 2 00%6 2 50% $0.00 50. 70 4 00%% $0.00 50USO SCORE 30.00 S SORE SOO 6.00% $0.00 6.50%% SO.OC 50.20 $0.00 T SORE so.00 $0.00 900%% $0.00 2090% 250% sox 6 003 6 Sox 2508 10.0 9 50%% $0.00 10 00% Discount Rabe $0.00