Hello, I need help with this excel homework. please show me how to do it and also provide me the formula. Thank you!

You are considering the purchase of an apartment complex. The following assumptions are made: The purchase price is $2,000,000 There are 30 units and the market rent is $850/ month Market rents are expected to increase 4% per year Vacancy and collection loss is 10% Real Estate Taxes are expected to be $20,000 in year 1 and increase 5% per year Insurance is expected to be $10,000 in year 1 and increase 7% per year Utities are expected to be 9% of EGl each year Repairs and Maintenance costs are expected to be 7% of EGI each year 3 Grounds and Security costs are expected to by 6% of EGI each year The market value of the investment is expected to increase 6% each year Selling expenses will be 5% The holding period is 5 years 80% of the purchase price can be borrowed on a 30-year, monthly payment mortgage The annual interest rate on the loan will be 8%. Loan origination fees will be 1% of the loan amount (paid in the year the loan is taken out - Year 0 ) There are no prepayment penalties if you pay the loan early. Tenant improvements are expected to be $3,000/ year Leasing Commissions are expected to be \$1,000lyear A roof repair totaling $15,000 will be completed in year 3 The required rate of retum for the investor is 12%. Assume taxes are 30% of BTCF. Assignment: Fill out the income statement. Calculate the monthly mortgage payment to find debt service. 30 What is the IRR and NPV of the property? (CFO = equity investment + loan origination fees) Calculate the ratios for one or five years as indicated on the worksheet To find the mortgage balance, principal and interest payments: Enter key strokes to find payment on the loan Enter 2nd Amort P1=1 enter, down anrow P2=12 enter, down amow Balance of ban, princlpal and interest is displayed For year 2, P1 - 13 enter, down arrow P2=24 enter, down arrow Balance of loan, principal and interest is displayed Pro Forma Income Statement 29 Ratio Analysis NPV=IRR=