please fill out charts showing excel formulas!

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 vears. You financed 55% of the property by ABC 5 Bank with a 15 year fixed interest rate loan at 475 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 6 amortization term of 30 years You will have annual taxes of $90,525 for the next 12 years and will have taxes due on sale of 6% on the property. You hope to receive a 10% 7 unlevered return, 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 beds/2 baths, and so units with 4 beds/4 baths. After doing your due diligence, you take a conservative approach and project the 1/1 units to 9 rent for $1.000/room/month, the 2/1 units to rent for $500/room/month, the 2/2 units to rent for $600/room/month, and the 4/4 units to rent for $550/room/month. Each 10 lease will be for 12 months and you project the rent to increase by 2 for the first 4 years of owning the property and then stabilise to 1.5 for the remaining years. You also 11 expect the vacancy and collection losses to be 10% in the first year and then decrease by 2 for each year until stable in year 4 for the remaining years. Your operating 12 expenses include basic maintenance on the property totaling $45.000/month, a property management fee of $65,000/month, and a salary of $14,500/month/employee for 13 the employees on site. Your operating expenses will grow at a rate of 18 yearly. You will also have miscellaneous income of $50/room/month for a pet fee, in which you 14 project 40% of the room total to have pets, and $100/room/month for parking, in which you project 72% of the room total to need a parking spot. When you had the property 15 inspected prior to ownership, you realized you needed to repave all the concrete roads and fix pot holes as well as build a pool and outdoor social area for residents within 16 the next 6 years. You have asked multiple companies 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 17 fix the roadways and $35,000 to install a pool and outdoor social area. You plan to fix the roadways in year 1 and build the pool and outdoor area in year 2. Alter 12 years 18 when you go to sell the property you find out that comparable properties are selling at an average cap rate of 65% and that you will have 55% selling capenses Amount/Notes Cost Totals (Monthly) 2 3 4 Unit Mix Of Apartment Complex 5 ibed/1bath 62bed/1bath 7 2bed/2bath 8 Abed/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) 28 Totals (Yearly Atas wable May Monthly Rent Bent with EARLYI Vacan/Collection Los Opx Growth (YEARLY 10 Prie VARO 11 Year of Sale 12 pm 15 gurur Undevered for los 14 Required Returnered Before loc 15 Required Return (RK) overed Aner Tax 16 Anes 17 Tue On Sale 1 Average Capa All 19 20 23 VE YS VH V TRE YR 2 Ponta Grosinca 2 Vacanty and Collection 25 Milano 2 ffective income 27 OK 2 Can 29 Net Operating income 90 De Service Theo BER DCH Amortization D 30 O Service 31 Betawah 33 Aero Cachow nim 35 36 3. Pre 38 Loan 10 Tapente 10 pulty Inwestment 41 Sale PC 45 Dp 16 Net Sales Proceeds Cash Flow Sale Of Agent Sede Benin adity [reves fue on sale Atau shwe put YRE TR VO YHT YR WH VES 50 1 dvered Before To 5 erettore Tas levind Alter Tax Valuut YRO VR 51 Unlevered Before 5 levered for Tax 5 Lered Atom YR VS TO 55 50 Vah NPV IRE se dvered Before 50 Lever for Arte But Terminal Sensitivity Meet Going Out (min Cape DO D E Payments PMT VO PMT B 1 Amortization Table 2 3 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 The Assignment Rent Roll Ready Amortization Table Monthly Payment Principal Interest Ending Balance DCF Amortization 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 vears. You financed 55% of the property by ABC 5 Bank with a 15 year fixed interest rate loan at 475 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 6 amortization term of 30 years You will have annual taxes of $90,525 for the next 12 years and will have taxes due on sale of 6% on the property. You hope to receive a 10% 7 unlevered return, 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 beds/2 baths, and so units with 4 beds/4 baths. After doing your due diligence, you take a conservative approach and project the 1/1 units to 9 rent for $1.000/room/month, the 2/1 units to rent for $500/room/month, the 2/2 units to rent for $600/room/month, and the 4/4 units to rent for $550/room/month. Each 10 lease will be for 12 months and you project the rent to increase by 2 for the first 4 years of owning the property and then stabilise to 1.5 for the remaining years. You also 11 expect the vacancy and collection losses to be 10% in the first year and then decrease by 2 for each year until stable in year 4 for the remaining years. Your operating 12 expenses include basic maintenance on the property totaling $45.000/month, a property management fee of $65,000/month, and a salary of $14,500/month/employee for 13 the employees on site. Your operating expenses will grow at a rate of 18 yearly. You will also have miscellaneous income of $50/room/month for a pet fee, in which you 14 project 40% of the room total to have pets, and $100/room/month for parking, in which you project 72% of the room total to need a parking spot. When you had the property 15 inspected prior to ownership, you realized you needed to repave all the concrete roads and fix pot holes as well as build a pool and outdoor social area for residents within 16 the next 6 years. You have asked multiple companies 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 17 fix the roadways and $35,000 to install a pool and outdoor social area. You plan to fix the roadways in year 1 and build the pool and outdoor area in year 2. Alter 12 years 18 when you go to sell the property you find out that comparable properties are selling at an average cap rate of 65% and that you will have 55% selling capenses Amount/Notes Cost Totals (Monthly) 2 3 4 Unit Mix Of Apartment Complex 5 ibed/1bath 62bed/1bath 7 2bed/2bath 8 Abed/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) 28 Totals (Yearly Atas wable May Monthly Rent Bent with EARLYI Vacan/Collection Los Opx Growth (YEARLY 10 Prie VARO 11 Year of Sale 12 pm 15 gurur Undevered for los 14 Required Returnered Before loc 15 Required Return (RK) overed Aner Tax 16 Anes 17 Tue On Sale 1 Average Capa All 19 20 23 VE YS VH V TRE YR 2 Ponta Grosinca 2 Vacanty and Collection 25 Milano 2 ffective income 27 OK 2 Can 29 Net Operating income 90 De Service Theo BER DCH Amortization D 30 O Service 31 Betawah 33 Aero Cachow nim 35 36 3. Pre 38 Loan 10 Tapente 10 pulty Inwestment 41 Sale PC 45 Dp 16 Net Sales Proceeds Cash Flow Sale Of Agent Sede Benin adity [reves fue on sale Atau shwe put YRE TR VO YHT YR WH VES 50 1 dvered Before To 5 erettore Tas levind Alter Tax Valuut YRO VR 51 Unlevered Before 5 levered for Tax 5 Lered Atom YR VS TO 55 50 Vah NPV IRE se dvered Before 50 Lever for Arte But Terminal Sensitivity Meet Going Out (min Cape DO D E Payments PMT VO PMT B 1 Amortization Table 2 3 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 The Assignment Rent Roll Ready Amortization Table Monthly Payment Principal Interest Ending Balance DCF Amortization