AutoSave OFF AESU Investment quiz.2020-4.xls - Compatibility Mode Q- poi Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments X Arial v A v 10 Insert v ab Wrap Text Ceneral E... Y. 4 v Paste B 1 I U 7 A = = = - Merge & Center v Delete v Format v Y Ideas Conditional Format Cell Formatting a Table Styles Y Sort & Filter Sensitivity Find & Select C56 fy F G . I L M M N P R S T T U W X 4 5 A TE 4 2 5 POI Potential Gross Income) -VC (Vacancy Collection Lobb) 7 EGI (Efective Gross Income) OE (Ope. Expenses 5 Real Estate TAX 10 Insurance 11 Utilities 12 Repair & Maint 13 Grounds & Recurity 14 -OE (Total Ope. Expenses 15 NOI (Net Operating Income 16 DS (Debt Service 17 Interest Payments 18 Principal Payments 19 DS(Total Debt Service) 20 CAPX (Capital Exp.) 21 Tenant Improvements 22 Lenang Commission 23 Roof Repair 24 CAPX Total Cap Exp. 25 BTCF Before Tax Cash Flow 26 -TAX 27 ATCF (After Tax Cash Flow 22 29 R Analysis 30 31 Capita ization Rate: NOI 32 Acoustion Price 33 34 Futy Chicland Rule BTCF 35 Equity Investment 36 37 Net Income Multplier, Asculation Price 38 NOI 39 40 Gross Income Mulepler Ascuisbon Price 41 EGI 42 43 44 46 Operating Experse Ratio: OE 46 EGI 47 42 Loan Value Ratio: Mortgage Balans 49 Property Value 50 51 Deal Coverage Polo NOI 52 Debt Service 53 Instructions Assumptions Worksheet Year 1 Year 1 Year 2 Year 3 Yeard Years + E- + 100% % AutoSave OFF AESU Investment quiz.2020-4.xls - Compatibility Mode Q- poi Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments X LA Arial Insert v ~ A v 10 ab Wrap Text Ceneral AY 4 v Paste BI U U = = = Merge & Center v Delete Format Y Ideas Conditional Format Cell Formatting a Table Styles Y Sensitivity Sort & Filter v Find & Select E37 B E C . J L M N O o P O R S S T u WY Y Z AA 2 You are considering the purchase of an apartment complex. 3 The following assumptions are made: 4 The purchase price $2,000,000 There are 30 urils and the market rentis 385month 7 Markut rents are expected to increase 4% pur yuar Vacancy and collection loss is 10% 5 Real Estate Taxes are expected to be $20,000 in year 1 and increase 5% per year 10 Irurance le expected to be $10,000 in har 1 and increase 7% per year 11 Uites are expected to be 9% of EGI each year 12 Repairs and Maintenance costs are expected to be of EGI each year 13 Gmunds and Security costs A expected to by 6% of El each year The market value of the investment is espeded to incrsA 6% each har 15 Seling expenses will be 5% 16 The hoding period is 6 years 20% of the purchase price can be borowed on a 30-ycer, monthly payment mortgage 18 The Annual interest rate on the ban wilba % 19 Logn origination fees will be 1% of the loan amount aid in the year the ban is lakeri uut - Year 0) 20 There are no prepayment penalties if you pay the loan carty 21 Tenant Improvements are expected to be $2.000/year 22 LASING Caminare expected to be $1.000 ar 23 Aroor repair lulaing $15,000 will be completed in year 3 The required rate of return for the investor is 12% 25 Assume taxes are 30% of BTCF. 26 27 Assignment: 28 Fil out the income statement 29 Calculate the monthly mortgage payment to find el service What is the IRR and NPV of the property? (CFO - cquity investment loan orgination fees Calculate the ratios for one or five years as indicated on the worksheet 32 To find the mangaga balans, principal and interest payment 33 Enter key strukes lo fine payment on the van 34 Enter 2nd Amort 36 P1 = 1 cnter, dan Arow 1 w 36 P2-12 Antar down amow 37 Balance of lan, pricpal and interest is disayed 38 For year 2 39 P1 - 13 antar, dawn mow 40 P2 - 24 Ar down Amw 41 Balance of loan, principal and interest is displayed 42 42 44 45 46 47 48 49 Instructions Assumptions Worksheet + 90 # E- + 100% % AutoSave OFF AESU Investment quiz.2020-4.xls - Compatibility Mode Q- poi Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments X Arial v A v 10 Insert v ab Wrap Text Ceneral E... Y. 4 v Paste B 1 I U 7 A = = = - Merge & Center v Delete v Format v Y Ideas Conditional Format Cell Formatting a Table Styles Y Sort & Filter Sensitivity Find & Select C56 fy F G . I L M M N P R S T T U W X 4 5 A TE 4 2 5 POI Potential Gross Income) -VC (Vacancy Collection Lobb) 7 EGI (Efective Gross Income) OE (Ope. Expenses 5 Real Estate TAX 10 Insurance 11 Utilities 12 Repair & Maint 13 Grounds & Recurity 14 -OE (Total Ope. Expenses 15 NOI (Net Operating Income 16 DS (Debt Service 17 Interest Payments 18 Principal Payments 19 DS(Total Debt Service) 20 CAPX (Capital Exp.) 21 Tenant Improvements 22 Lenang Commission 23 Roof Repair 24 CAPX Total Cap Exp. 25 BTCF Before Tax Cash Flow 26 -TAX 27 ATCF (After Tax Cash Flow 22 29 R Analysis 30 31 Capita ization Rate: NOI 32 Acoustion Price 33 34 Futy Chicland Rule BTCF 35 Equity Investment 36 37 Net Income Multplier, Asculation Price 38 NOI 39 40 Gross Income Mulepler Ascuisbon Price 41 EGI 42 43 44 46 Operating Experse Ratio: OE 46 EGI 47 42 Loan Value Ratio: Mortgage Balans 49 Property Value 50 51 Deal Coverage Polo NOI 52 Debt Service 53 Instructions Assumptions Worksheet Year 1 Year 1 Year 2 Year 3 Yeard Years + E- + 100% % AutoSave OFF AESU Investment quiz.2020-4.xls - Compatibility Mode Q- poi Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments X LA Arial Insert v ~ A v 10 ab Wrap Text Ceneral AY 4 v Paste BI U U = = = Merge & Center v Delete Format Y Ideas Conditional Format Cell Formatting a Table Styles Y Sensitivity Sort & Filter v Find & Select E37 B E C . J L M N O o P O R S S T u WY Y Z AA 2 You are considering the purchase of an apartment complex. 3 The following assumptions are made: 4 The purchase price $2,000,000 There are 30 urils and the market rentis 385month 7 Markut rents are expected to increase 4% pur yuar Vacancy and collection loss is 10% 5 Real Estate Taxes are expected to be $20,000 in year 1 and increase 5% per year 10 Irurance le expected to be $10,000 in har 1 and increase 7% per year 11 Uites are expected to be 9% of EGI each year 12 Repairs and Maintenance costs are expected to be of EGI each year 13 Gmunds and Security costs A expected to by 6% of El each year The market value of the investment is espeded to incrsA 6% each har 15 Seling expenses will be 5% 16 The hoding period is 6 years 20% of the purchase price can be borowed on a 30-ycer, monthly payment mortgage 18 The Annual interest rate on the ban wilba % 19 Logn origination fees will be 1% of the loan amount aid in the year the ban is lakeri uut - Year 0) 20 There are no prepayment penalties if you pay the loan carty 21 Tenant Improvements are expected to be $2.000/year 22 LASING Caminare expected to be $1.000 ar 23 Aroor repair lulaing $15,000 will be completed in year 3 The required rate of return for the investor is 12% 25 Assume taxes are 30% of BTCF. 26 27 Assignment: 28 Fil out the income statement 29 Calculate the monthly mortgage payment to find el service What is the IRR and NPV of the property? (CFO - cquity investment loan orgination fees Calculate the ratios for one or five years as indicated on the worksheet 32 To find the mangaga balans, principal and interest payment 33 Enter key strukes lo fine payment on the van 34 Enter 2nd Amort 36 P1 = 1 cnter, dan Arow 1 w 36 P2-12 Antar down amow 37 Balance of lan, pricpal and interest is disayed 38 For year 2 39 P1 - 13 antar, dawn mow 40 P2 - 24 Ar down Amw 41 Balance of loan, principal and interest is displayed 42 42 44 45 46 47 48 49 Instructions Assumptions Worksheet + 90 # E- + 100% %