please provide the excel functions

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 Utilities are expected to be 9% of EGI 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 5 Selling expenses will be 5% 6 The holding period is 5 years 7 80% of the purchase price can be borrowed on a 30-year, monthly payment mortgage 8 The annual interest rate on the loan will be 8%. 9 Loan origination fees will be 1% of the loan amount (paid in the year the loan is taken out - Year) 20 There are no prepayment penalties if you pay the loan early. 21 Tenant Improvements are expected to be $3,000/year 22 Leasing Commissions are expected to be $1,000/year 23 A roof repair totaling $15,000 will be completed in year 3 24 The required rate of retum for the investor is 12%. 25 Assume taxes are 30% of BTCF. 26 27 Assignment: 28 Fill out the income statement. 29 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) 31 Calculate the ratios for one or five years as indicated on the worksheet 32 To find the mortgage balance, principal and interest payments: 33 Enter key strokes to find payment on the loan 34 Enter 2nd Amort 35 P1 = 1 enter, down arrow 36 P2 = 12 enter, down arrow 37 Balance of loan, principal and interest is displayed 38 For year 2. Year 3 27581 2758 24823 2550 4 28884 2868 25816 5 29831 2983 26848 23153 12250 10 15 SU 22050 11440 2234 1738 1480 38900 -14137 471 1 2 Pro Forma Income Statement 3 4 1 2 5 PGI Potential Gross Income) 25500 26520 6 VC Vacancy Collection Loss) 2652 7EGI (Effective Gross Income) 22950 23868 OE (Ope. Expenses) 9 Real Estate Tax 20000 21000 Insurance 10000 10700 ULOS 2016 2148 12 Repair & Maint 1607 1871 Grounds & Security 1377 1432 T 14 DE Total Ope Expenses) 35049 15 NOI (Net Operating Income) 120.99 -13083 DS (Debt Service) 403 438 che 17 Interest Payments 18 Principal Payments te o 10-DS(Total Debt Service) ere 20 CAPX (Capital Exp) Tenant improvements 22 Leasing Commons Roof Repair Why 24 CAPX Total Cap Exp.) Shou 28 BTCF (Before Tax Cash Flow) 20 TAX the to 27 ATCF Ante Tax Cash Flow) 2 pts) 928 20 Ratio Anaya before 30 quity reas Captation to Avon Pu of 15 24310 13108 2418 1870 1611 43325 -18475 540 2323 1807 1549 41082 -15267 509 36951 06 now 23 sca 23 Year Ratio Analysis Year 1 Capitalization Rate: NOI Acquisition Price Equity Dividend Rate: BTCF Equity Investment Net Income Multiplier: Acquisition Price NOI Gross Income Multiplier Acquisition Price EGI Year 2 Year 1 Year 3 Year 4 Year 5 Operating Expense Ratio: OE EGI + Loan-to-Value Ratio: Mortgage Balance Peperty Value Debt Coverage Ratio: NOI Debt Service NPV = IRR - Pro Forma Income Statement 1 306000 30600 275400 318240 31824 286416 Year 3 330970 33097 297873 4 344208 34421 309788 5 357977 35790 322179 H& 20000 10000 24786 19278 16524 90588 184812 6160 127517 21000 10700 25777 20049 17185 94712 191704 22050 11449 26809 20851 17872 99031 198842 1 23153 12250 27881 21685 18587 103556 206231 1 PGI Potential Gross Income VC Vacancy/Collection Losa) EGI (Effective Gross Income) OE (Ope. Expenses) Real Estate Tax Insurance Utilities Repair & Maint Grounds & Security OE Total Ope. Expenses) NOI (Net Operating Income) DS (Debt Service) Interest Payments Principal Payments -DS(Total Debt Service) CAPX (Capital Exp.) Tenant Improvements Leasing Commissions Roof Repair CAPX Total Cap Exp. BTCF (Before Tax Cash Flow) - ATCF (After Tax Cash Flow) 24310 13100 28990 22553 19331 108297 213882 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 Utilities are expected to be 9% of EGI 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 5 Selling expenses will be 5% 6 The holding period is 5 years 7 80% of the purchase price can be borrowed on a 30-year, monthly payment mortgage 8 The annual interest rate on the loan will be 8%. 9 Loan origination fees will be 1% of the loan amount (paid in the year the loan is taken out - Year) 20 There are no prepayment penalties if you pay the loan early. 21 Tenant Improvements are expected to be $3,000/year 22 Leasing Commissions are expected to be $1,000/year 23 A roof repair totaling $15,000 will be completed in year 3 24 The required rate of retum for the investor is 12%. 25 Assume taxes are 30% of BTCF. 26 27 Assignment: 28 Fill out the income statement. 29 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) 31 Calculate the ratios for one or five years as indicated on the worksheet 32 To find the mortgage balance, principal and interest payments: 33 Enter key strokes to find payment on the loan 34 Enter 2nd Amort 35 P1 = 1 enter, down arrow 36 P2 = 12 enter, down arrow 37 Balance of loan, principal and interest is displayed 38 For year 2. Year 3 27581 2758 24823 2550 4 28884 2868 25816 5 29831 2983 26848 23153 12250 10 15 SU 22050 11440 2234 1738 1480 38900 -14137 471 1 2 Pro Forma Income Statement 3 4 1 2 5 PGI Potential Gross Income) 25500 26520 6 VC Vacancy Collection Loss) 2652 7EGI (Effective Gross Income) 22950 23868 OE (Ope. Expenses) 9 Real Estate Tax 20000 21000 Insurance 10000 10700 ULOS 2016 2148 12 Repair & Maint 1607 1871 Grounds & Security 1377 1432 T 14 DE Total Ope Expenses) 35049 15 NOI (Net Operating Income) 120.99 -13083 DS (Debt Service) 403 438 che 17 Interest Payments 18 Principal Payments te o 10-DS(Total Debt Service) ere 20 CAPX (Capital Exp) Tenant improvements 22 Leasing Commons Roof Repair Why 24 CAPX Total Cap Exp.) Shou 28 BTCF (Before Tax Cash Flow) 20 TAX the to 27 ATCF Ante Tax Cash Flow) 2 pts) 928 20 Ratio Anaya before 30 quity reas Captation to Avon Pu of 15 24310 13108 2418 1870 1611 43325 -18475 540 2323 1807 1549 41082 -15267 509 36951 06 now 23 sca 23 Year Ratio Analysis Year 1 Capitalization Rate: NOI Acquisition Price Equity Dividend Rate: BTCF Equity Investment Net Income Multiplier: Acquisition Price NOI Gross Income Multiplier Acquisition Price EGI Year 2 Year 1 Year 3 Year 4 Year 5 Operating Expense Ratio: OE EGI + Loan-to-Value Ratio: Mortgage Balance Peperty Value Debt Coverage Ratio: NOI Debt Service NPV = IRR - Pro Forma Income Statement 1 306000 30600 275400 318240 31824 286416 Year 3 330970 33097 297873 4 344208 34421 309788 5 357977 35790 322179 H& 20000 10000 24786 19278 16524 90588 184812 6160 127517 21000 10700 25777 20049 17185 94712 191704 22050 11449 26809 20851 17872 99031 198842 1 23153 12250 27881 21685 18587 103556 206231 1 PGI Potential Gross Income VC Vacancy/Collection Losa) EGI (Effective Gross Income) OE (Ope. Expenses) Real Estate Tax Insurance Utilities Repair & Maint Grounds & Security OE Total Ope. Expenses) NOI (Net Operating Income) DS (Debt Service) Interest Payments Principal Payments -DS(Total Debt Service) CAPX (Capital Exp.) Tenant Improvements Leasing Commissions Roof Repair CAPX Total Cap Exp. BTCF (Before Tax Cash Flow) - ATCF (After Tax Cash Flow) 24310 13100 28990 22553 19331 108297 213882