You are considering the purchase of a small existing office building for $2,575,000 today. Below, you are given the information you need to analyze the investment and decide how to proceed. Remember: Your submission for this assignment should be calculated in Microsoft Excel. Please show all your formulas in the spreadsheet. I can only give you partial credit if I see how you did each calculation. Your expectations for this stabilized property include the following: first-year gross potential income of $450,000; vacancy and collection losses equal to 15% of gross potential income; operating expenses equal to 40% of effective gross income; and capital expenditures equal to 5% of EGI. You have arranged a mortgage loan with 70% LTV and an annual interest rate of 7%. The loan will be amortized over 20 years with a monthly payment of $13,974.76.

1. What is the estimated net operating income (NOI) and property-before-tax cash flow (PBTCF) for the first year of operations? Please show these calculations in the form of a pro forma.

2. Calculate the following for comparison to other similar properties: (a) Capitalization rate? (b) Equity yield ratio? (c) Effective gross income multiplier? (d) Operating expense ratio? (e) Debt coverage ratio? (f) Debt yield ratio?

3. What can you conclude about this property based on these ratios? What opportunities do you see? What risks are you concerned about?

The value of the building and the NOI both grow at 10% per year for the next five years. You sell the property after year 5, and you pay off the remaining balance of the loan, $1,554,776. (In other words, the cost of your investment is the present value of both the purchase price and the balloon payment.)

4. If you assume a 9% discount rate, what is the NPV of your investment?

5. What is the IRR of your investment?