Please use excel and show all formulas

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.

Your expectations for this stabilized property include the following: first-year gross potential income of $540,000; vacancy and collection losses equal to 13% of gross potential income; operating expenses equal to 35% of effective gross income; and capital expenditures equal to 7% of NOI. You have arranged a mortgage loan with 80% LTV and an annual interest rate of 5.5%. The loan will be amortized over 25 years with a monthly payment of $15,812.75.

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 your selling expenses are 5% of the resale price. The interest rate on the mortgage loan is the cost of your debt. The average historical return of the stock market is the cost of your equity.

4. Using the WACC approach, what discount rate should you apply in income valuation of the property?

5. Using this discount rate, what is the value of the property? Based on this value, is the asking price worth it?