7. Analyze the following multi-family housing case (Hint: utilize the excel spreadsheet uploaded on Canvas). (10 points) You are going to purchase a multi-family housing apartment named Atl. Apartment as an investment and plan to operate the property for 7 years and sell it at the end of Year 7. Property information Rent Roll Name: Atl. Apartment Type (Unit): Number of Units: 15 Studio- 5 rooms ($1,000) Purchase Price: $3,000,000 1bd/1ba -5 rooms ($1,100) Cap Rate (Current) : 7% 2bd/1ba 5 rooms ($1,300) Loan Residual Value Loan Amount (LTV 80%): $2,400,000 Terminal Cap Rate: 8% (end of year 7) Interest Rate: 4% Selling Cost: 1% Amortization: 30 years Monthly Payment: $ Operating cost: $7,000 in year 1 Insurance: 53,000 in year 1 Capital Expenditure: $200 in year 1 for each unit Inflation rate: 3% Vacancy rate: 5% *Do not consider the tax in this case 7.1. Calculate the monthly payment 7.2 Calculate the following values (show and upload your work on an Excel spreadsheet) 1) Effective Gross revenue in year 1 2) Net Operating Income in year 7 3) Before Tax Cash Flow in year 7 4) Sale price at the end of year 7 7.3 Given the balance sheet below, provide 1) a balance sheet after purchasing property, 2) a balance sheet after year 1, and 3) an income statement after year 1. Before uurchasin ouroe BALANCE SHEET LIABILITIES 1,957,293 LOANS PAYABLE LOANS PAYABLE EQUITY OWNERS EQUITY $ 1,574,000 RETAINED EARNINS $ 233,293 Total asset $1,957,293 Total liability+equity $ 1,957,293