Please answer these real estate finance questions
RES 3200Project 2 Instructions: 1. Round all final dollar amounts to the nearest cent and don’t enter the dollar sign. E.g. if the answer is $1,022.912, enter it as 1,022.91. Commas don’t matter, hence you can also enter 1022.91. 2. Round all final percentages to the nearest basis point and don’t enter the percent sign. E.g. if the answer is 17.238%, enter it as 17.24. If the answer is 0.52%, enter it as 0.52. You are thinking about buying 944 Fulton Street, Brooklyn, NY, a 4,500 square foot mixed-use building, holding it for 5 years and selling it at the end of the 5th year (based on the year 6 proforma NOI). Review the PDF file containing the Setup provided by the broker, Cushman & Wakefield, and marked with handwritten changes. Use the income as provided and the expenses as listed with the modifications. Also include a 4% vacancy and collection loss factor (which you’ll notice the broker did not include). Insert the answers to the questions in Blackboard and attach the spreadsheet for backup. (1) Use the first sheet of the spreadsheet (titled “NOI”) to compute the Net Operating Income (NOI) for this property for Year 1. Using the Income and Expense information as provided in the Setup on Page 2, Fill in the missing information in the spreadsheet. Include the handwritten amounts. Do not assume the broker totals are correct. Do not use the management fee given, you will calculate the Fee based on 3% of EGI. Vacancy and Collection loss are estimated to be 4% combined. (1.a) What is the NOI for Year 1? (1.b) At the $5,400,000 asking price, what is the cap rate? (1.c) What is the purchase price per Gross SF (assuming 4,500 sq feet)? (2) Fill in the second sheet (titled “underwriting”). Where it says “Income Test”, compute the biggest mortgage payment You can make given the NOI in Year 1 computed above. Recall from slides 12: , which implies the maximum. (2.a) What is the biggest mortgage payment the bank will allow You to make given her expected NOI in Year 1? Assume you can obtain a 30-year, fully amortizing loan, with monthly compounding at an interest rate of 4.50% (2.b) What is the annual loan constant () corresponding to these loan terms? Recall for an amortizing loan you must calculate the loan amount based on the annual loan constant which is the payment corresponding to $1 of principal (N=total # payments, I/Y= Int rate/# payments per year, PV=-1, FV=0, CPT PMT) then annualize. (2.c) For the Income Test, what is the maximum loan amount corresponding to the loan terms and a DSCR of 1.2? (2.d) For the Collateral Test, what is the biggest loan You can get assuming 75% maximum LTV? (2.e) Assume for the rest of the question that You will Borrow the maximum available loan amount. What is the loan that You borrow to buy the property? (2.f) What will your debt service payment be? (3) Fill in the sheet titled “NPV-IRR”. You will buy the property now (Year 0), collect NOI for 5 years Year 1-Year 5, and sell it at the end of Year 5. Your loan has a 5/4/3/2/1 prepayment penalty structure, so if you prepay in the first year, you will pay a penalty equal to 5% of the balance, in the second year you will pay a penalty equal to 4% of the balance etc. Payoff the loan balance, the prepayment penalty and any transaction costs. You forecast NOI will grow at 3% per year, compounded annually. You forecast you can sell the property at the end of year 5 at a 5.25% cap rate. Recall: (3.a) How much will You sell the property for in Year 5? (3.b) What is Your IRR for this investment? (3.c) What is your “cash on cash” return for Year 1? Recall that the cash on cash return is equal to your annual Net Cash Flow divided by your total cash investment at closing. The formula = For the following questions, change only the variable indicated. Keep the others as dictated above. (3.d) What is your IRR if NOI growth is 5% compounded annually (all other variables held constant)? (3.e) What is your IRR if your exit cap rate is 4.75% (all other variables held constant)? (3.f) Complete the data table using the What-If Analysis in Excel. (4) Fill in the sheet titled “NPV-IRR Unlevered” Assume you purchase this property without any financing. (4.a) What is your unleveraged IRR (using the base case of 3% NOI growth and 5.25% Exit Cap Rate). Fill in the chart “Breaking down the contribution of NOI and Terminal Value to Investment Return.” Use the numbers from the Net Cash Flow line. (4.b) Using the NCF from years 1 through 5 as presented, what would you be willing to pay if you wanted to earn a 10% IRR on this investment?