step by step excel formuale for these case :

Case 1: You are considering purchasing an office building for $2,500,000. You expect the potential gross income (PGI) in the first year to be $450,000; vacancy and collection losses to be 9 percent of PGI; and operating expenses and capital expenditures to be 42 percent of effective gross income (EGI). You will finance the acquisition with 25 percent equity and 75 percent debt. The annual interest rate on the debt financing will be 5.5 percent. Payment will be made monthly based on a 25-year amortization schedule.

1. What is the implied first year overall capitalization rate?
2. What is the expected debt coverage ratio in year 1 of operations?
3. If the lender requires the DCR to be 1.25 or greater, what is the maximum loan amount?
4. What is the debt yield ratio?

Case 2: You are considering the purchase of a quadruplex apartment building. Effective gross income (EGI) during the first year of operations is expected to be $33,600 ($700 per month per unit). First-year operating expenses are expected to be $13,440 (at 40 percent of EGI). Ignore capital expenditures. The purchase price of the quadruplex is $200,000. The acquisition will be financed with $60,000 in equity and a $140,000 standard fixed-rate mortgage. The interest rate on the debt financing is eight percent and the loan term is 30 years. Assume, for simplicity, that payments will be made annually and that there are no up-front financing costs.

1. What is the overall capitalization rate?

2. What is the effective gross income multiplier?

3. What is the equity dividend rate (the before-tax return on equity)?

4. What is the debt coverage ratio?

5. Assume the lender requires a minimum debt coverage ratio of 1.2. What is the largest loan that you could obtain if you decide to borrow more than $140,000?