please show excel formulas in the models!

H 1 Assignment 2 3 4 You are investing in a 250 unit student housing complex that you bought for $21,000,000 and plan to sell the property in 12 years. You financed 55% of the property by ABC Bank 5 with a 15-year fixed interest rate loan at 4.75% per year and will have to pay 3.5% in loan expenses. You have a 2 and a half year interest only period and will have an amortization 6 term of 30 years You will have annual taxes of 590,525 for the next 12 years and will have taxes due on sale of 6% on the property. You hope to receive a 10% unlevered return, 7 14% levered return on the property before taxes, and a 9.8% levered return after taxes. The apartment has 45 units with 1 bed/1 bath, 55 units with 2 beds/1 bath, 70 units with 2 8 beds/2 baths, and 80 units with 4 beds/4 baths. After doing your due diligence, you take a conservative approach and project the 1/1 units to rent for $1,000/room/month, the 9 2/1 units to rent for 5500/room/month, the 2/2 units to rent for $600/room/month, and the 4/4 units to rent for $550/room/month. Each lease will be for 12 months and you 10 project the rent to increase by 2 for the first 4 years of owning the property and then stabilize to 1.5% for the remaining years. You also expect the vacancy and collection losses 11 to be 10% in the first year and then decrease by 25 for each year until stable in year 4 for the remaining years. Your operating expenses include basic maintenance on the property 12 totaling $45,000/month, a property management fee of $65,000/month, and a salary of $14,500/month/employee for the 8 employees on site. Your operating expenses will grow 13 at a rate of 1.8% yearly. You will also have miscellaneous income of $50/room/month for a pet fee, in which you project 40% of the room total to have pets, and 14 $100/room/month for parking, in which you project 72% of the room total to need a parking spot. When you had the property inspected prior to ownership, you realized you 15 needed to repave all the concrete roads and fix pot holes as well as build a pool and outdoor social area for residents within the next 6 years. You have asked multiple companies 16 how much it will cost to fix the roadways, build a pool, and an outdoor social area and find out it will cost $8,700 to fix the roadways and $35,000 to install a pool and outdoor 17 social area. You plan to fix the roadways in year 1 and build the pool and outdoor area in year 2. After 12 years when you go to sell the property, you find out that comparable 18 properties are selling at an average cap rate of 65% and that you will have 5.5% selling expenses. 19 WN Cost Totals (Monthly) 1 Computational Assumptions 2 3 4 Unit Mix Of Apartment Complex Amount/Notes 5 lbed/1bath 6 2bed/1bath 7 2bed/2bath 8 4bed/4bath 9 10 Miscellaneous Income 11 Pet Fee 12 Parking Spot 13 14 Operating Expenses 15 Maintenance Fee 16 Property Management Fee 17 On-Site Employees 18 19 20 Capital Expenses 21 Repaving Roadways (Plus Labor) 22 Pool & Outdoor Area (Plus Labor) 23 Totals (Yearly) Ini Animations State Stable in Yr Ind/Dec/Yr Until Stable Monthly Rent Rent Growth [YEARLY . Vacancy/Collection Lones Op Growth YEARLY) 10 Phase Price (YEAR OJ 11 Year Of Sale 12 Seiting tapente 1 Required Return (Univered Before Tas 14 Required Return I Lovered Before Tax 15 required Return (A) Lavered After TM 16 Annual Tanes 17 Tones De On Sale 18 Arena Cape Alfa 19 20 21 22 23 Gross come 24 Vacancy and Collection 25 Macoustic 26tive Gross income 27 Opx 28 Cap 29 Net Operating income 30 Debt Service 31 before Tax Cash Flow 32 Afer Tax Cash Flow Prodama YRE YR2 YR YR YRS VRO YR 7 YRE Louiestment D LIH 36 17 A 38 Ant 19 40 werd 41 43 Cather 44 45 cm 46 Net de Before the TO My fave Cachowroom 48 49 SO $1 Untevedra Tas 2 red Before 53 Lared Aner Tu YO YRI YRZ YRA YES VA Vilure NPV 35 56 53 58 univered from 59 Lever Before Tax 60 verder as 61 63 Going Out Terminal Cape 64 66 67 Monthly Rent in Yew 1 71 Going Out Terminal Casaten C D E F Payments PMT V/O PMT B 1 Amortization Table 2 3 4 Assumptions 5 Loan Amount (0%) 6 Loan Expenses (0%) 7 Loan Term 8 Amortization Term 9 Interest Only Period 10 Rate 11 12 13 14 Month Beginning Balance 15 16 17 18 19 20 21 22 23 24 25 26 27 Amortization Table Monthly Pa Principal rest Ending Balance H 1 Assignment 2 3 4 You are investing in a 250 unit student housing complex that you bought for $21,000,000 and plan to sell the property in 12 years. You financed 55% of the property by ABC Bank 5 with a 15-year fixed interest rate loan at 4.75% per year and will have to pay 3.5% in loan expenses. You have a 2 and a half year interest only period and will have an amortization 6 term of 30 years You will have annual taxes of 590,525 for the next 12 years and will have taxes due on sale of 6% on the property. You hope to receive a 10% unlevered return, 7 14% levered return on the property before taxes, and a 9.8% levered return after taxes. The apartment has 45 units with 1 bed/1 bath, 55 units with 2 beds/1 bath, 70 units with 2 8 beds/2 baths, and 80 units with 4 beds/4 baths. After doing your due diligence, you take a conservative approach and project the 1/1 units to rent for $1,000/room/month, the 9 2/1 units to rent for 5500/room/month, the 2/2 units to rent for $600/room/month, and the 4/4 units to rent for $550/room/month. Each lease will be for 12 months and you 10 project the rent to increase by 2 for the first 4 years of owning the property and then stabilize to 1.5% for the remaining years. You also expect the vacancy and collection losses 11 to be 10% in the first year and then decrease by 25 for each year until stable in year 4 for the remaining years. Your operating expenses include basic maintenance on the property 12 totaling $45,000/month, a property management fee of $65,000/month, and a salary of $14,500/month/employee for the 8 employees on site. Your operating expenses will grow 13 at a rate of 1.8% yearly. You will also have miscellaneous income of $50/room/month for a pet fee, in which you project 40% of the room total to have pets, and 14 $100/room/month for parking, in which you project 72% of the room total to need a parking spot. When you had the property inspected prior to ownership, you realized you 15 needed to repave all the concrete roads and fix pot holes as well as build a pool and outdoor social area for residents within the next 6 years. You have asked multiple companies 16 how much it will cost to fix the roadways, build a pool, and an outdoor social area and find out it will cost $8,700 to fix the roadways and $35,000 to install a pool and outdoor 17 social area. You plan to fix the roadways in year 1 and build the pool and outdoor area in year 2. After 12 years when you go to sell the property, you find out that comparable 18 properties are selling at an average cap rate of 65% and that you will have 5.5% selling expenses. 19 WN Cost Totals (Monthly) 1 Computational Assumptions 2 3 4 Unit Mix Of Apartment Complex Amount/Notes 5 lbed/1bath 6 2bed/1bath 7 2bed/2bath 8 4bed/4bath 9 10 Miscellaneous Income 11 Pet Fee 12 Parking Spot 13 14 Operating Expenses 15 Maintenance Fee 16 Property Management Fee 17 On-Site Employees 18 19 20 Capital Expenses 21 Repaving Roadways (Plus Labor) 22 Pool & Outdoor Area (Plus Labor) 23 Totals (Yearly) Ini Animations State Stable in Yr Ind/Dec/Yr Until Stable Monthly Rent Rent Growth [YEARLY . Vacancy/Collection Lones Op Growth YEARLY) 10 Phase Price (YEAR OJ 11 Year Of Sale 12 Seiting tapente 1 Required Return (Univered Before Tas 14 Required Return I Lovered Before Tax 15 required Return (A) Lavered After TM 16 Annual Tanes 17 Tones De On Sale 18 Arena Cape Alfa 19 20 21 22 23 Gross come 24 Vacancy and Collection 25 Macoustic 26tive Gross income 27 Opx 28 Cap 29 Net Operating income 30 Debt Service 31 before Tax Cash Flow 32 Afer Tax Cash Flow Prodama YRE YR2 YR YR YRS VRO YR 7 YRE Louiestment D LIH 36 17 A 38 Ant 19 40 werd 41 43 Cather 44 45 cm 46 Net de Before the TO My fave Cachowroom 48 49 SO $1 Untevedra Tas 2 red Before 53 Lared Aner Tu YO YRI YRZ YRA YES VA Vilure NPV 35 56 53 58 univered from 59 Lever Before Tax 60 verder as 61 63 Going Out Terminal Cape 64 66 67 Monthly Rent in Yew 1 71 Going Out Terminal Casaten C D E F Payments PMT V/O PMT B 1 Amortization Table 2 3 4 Assumptions 5 Loan Amount (0%) 6 Loan Expenses (0%) 7 Loan Term 8 Amortization Term 9 Interest Only Period 10 Rate 11 12 13 14 Month Beginning Balance 15 16 17 18 19 20 21 22 23 24 25 26 27 Amortization Table Monthly Pa Principal rest Ending Balance