needed as an excel sheet

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 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 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% 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 80 units with 4 beds:/4 baths. After doing your due dilligence, you take a conservative approach and project the 1/1 units to 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 tease 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 stabilize to 1.5% for the remaining years. You also expect the vacancy and collection losses to be 10% in the first year and then decrease by 2for each year until stable in year 4 for the remaining years. Your operating expenses include basic maintenance on the property totaling 545,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 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 $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 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 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 popl and outdoor 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 properties are selling at an average cap rate of 6.5% and that you will have 5.5% selling expenses. Amount/Notes Cost Totals (Monthly) Unit Mix Of Apartment Complex 1bed/ibath 2bed/1bath 2bed/2bath 4bed/4bath Miscellaneous Income Pet Fee Parking Spot Operating Expenses Maintenance Fee Property Management Fee On-Site Employees Totals (Yearly) Capital Expenses Repaving Roadways (Plus Labor) Pool & Outdoor Area (Plus Labor) Assumptions Initial Stable Stable in Yr Inc/Dec/Yr Until Stable Monthly Rent Rent Growth (YEARLY) Vacancy/Collection Losses OpX Growth (YEARLY) Purchase Price (YEAR O) Year Of Sale Selling Expense Required Return (RR) Unlevered Before Tax Required Return (RR) Levered Before Tax Required Return (RR) Levered After Tax 5 Annual Taxes Taxes Due On Sale Average Cap Rate At Sale YR 1 YR 2 YR 3 YR 4 0 1 2 -3 Potential Gross income 4 Vacancy and Collection 25 Miscellaneous Income 26 Effective Gross Income 27 OpX 28 29 Net Operating Income 30 Debt Service 31 Before Tax Cash Flow 32 Tax 33 After Tax Cash Flow 34 35 36 37 Acquisition Price 38 Loan Amount 39 Loan Expenses 40 Equity Investment 41 Initial Equity Investment Sale Price Selling Cowan Sater Art Net Mortgage Mature Power Before Taste TOO Ayo YND YR Chrom TR1 YES YH RE wered Bette Lewend botas Lewed Antal Vas rederea Lerefore ved at Outumn Gap Rate Most Rust in Years GEOR Templates Assumptions Loan Amount (0%) Loan Expenses (.0%) Loan Term Amortization Term Interest Only Period Rate Payments PMT 1/O PMT Month Beginning Balance Amortization Table Monthly Payment Principal Interest Ending Balance 7 8 9 0 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 -> 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 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 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% 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 80 units with 4 beds:/4 baths. After doing your due dilligence, you take a conservative approach and project the 1/1 units to 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 tease 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 stabilize to 1.5% for the remaining years. You also expect the vacancy and collection losses to be 10% in the first year and then decrease by 2for each year until stable in year 4 for the remaining years. Your operating expenses include basic maintenance on the property totaling 545,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 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 $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 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 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 popl and outdoor 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 properties are selling at an average cap rate of 6.5% and that you will have 5.5% selling expenses. Amount/Notes Cost Totals (Monthly) Unit Mix Of Apartment Complex 1bed/ibath 2bed/1bath 2bed/2bath 4bed/4bath Miscellaneous Income Pet Fee Parking Spot Operating Expenses Maintenance Fee Property Management Fee On-Site Employees Totals (Yearly) Capital Expenses Repaving Roadways (Plus Labor) Pool & Outdoor Area (Plus Labor) Assumptions Initial Stable Stable in Yr Inc/Dec/Yr Until Stable Monthly Rent Rent Growth (YEARLY) Vacancy/Collection Losses OpX Growth (YEARLY) Purchase Price (YEAR O) Year Of Sale Selling Expense Required Return (RR) Unlevered Before Tax Required Return (RR) Levered Before Tax Required Return (RR) Levered After Tax 5 Annual Taxes Taxes Due On Sale Average Cap Rate At Sale YR 1 YR 2 YR 3 YR 4 0 1 2 -3 Potential Gross income 4 Vacancy and Collection 25 Miscellaneous Income 26 Effective Gross Income 27 OpX 28 29 Net Operating Income 30 Debt Service 31 Before Tax Cash Flow 32 Tax 33 After Tax Cash Flow 34 35 36 37 Acquisition Price 38 Loan Amount 39 Loan Expenses 40 Equity Investment 41 Initial Equity Investment Sale Price Selling Cowan Sater Art Net Mortgage Mature Power Before Taste TOO Ayo YND YR Chrom TR1 YES YH RE wered Bette Lewend botas Lewed Antal Vas rederea Lerefore ved at Outumn Gap Rate Most Rust in Years GEOR Templates Assumptions Loan Amount (0%) Loan Expenses (.0%) Loan Term Amortization Term Interest Only Period Rate Payments PMT 1/O PMT Month Beginning Balance Amortization Table Monthly Payment Principal Interest Ending Balance 7 8 9 0 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 ->