How do you solve for Q.1-5 using excel formulas?

PGI 340,000 51,000 Q1. Calculate Going-in Cap Rate (Ro): -VC + MI = EGI -OE -CAPEX = NOI -DS = BTCF 289,000 115,600 14,450 158,950 125,630 33,320 You are considering purchasing a small office building for $1,975,000 Your expectations include: - First-year gross potential income of $340,000; - Vacancy & collection losses equal to 15% of PGI; - Operating expenses - 40% of EGI; - Capital expenditures = 5% of EGI - $1,481,250 mortgage (75% LTV) @ 7% - Mortgage will be amortized over 25 years with a monthly payment of $10,469.17 - Total up-front financing costs = 2% of the loan amount - Required equity investment is $523,375 [$1,975,000 - ($1,481,250 - $29,625)] Q2. Calculate the Equity Dividend Ratio (EDR): Q3. Calculate the Effective Gross Income Multiplier (EGIM): Q4. Calculate the Operating Expense Ratio (OER): Q5. Calculate the Debt Coverage Ratio (DCR)