You are investing in a 205 unit student housing complex that you bought for $30,000,000 and plan to sell the property in 15 years. You financed 65% of the property by ABC Bank with a 15-year fixed interest rate loan at 4.75% per year and will have to pay 2.5% in loan expenses. You have a 2 year interest only period and will have an amortization term of 30 years. You will have annual taxes of $280,000 for the next 15 years and will have taxes due on sale of 4% 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 40 units with 1 bed/1 bath, 45 units with 2 beds/1 bath, 65 units with 2 beds/2 baths, and 55 units with 4 beds/4 baths. After doing your due dilligence, you project the 1/1 units to rent for $1,200/room/month, the 2/1 units to rent for $850/room/month, the 2/2 units to rent for $1000/room/month, and the 4/4 units to rent for $750/room/month. Each lease will be for 12 months and you project the rent to increase by 3% for the first 2 years and then stabilize to 1.5% for the remaining years. You also expect the vacancy and collection losses to be 12% in the first year and then decrease by 4% for each year until stable in year 3 at 4% for the remaining years. Your operating expenses include basic maintenance on the property totaling $10,000/month, a property management fee of $18,000/month, and a salary of $8,000/month/employee for the 8 employees on site. You also have $85,000 of other expenses and utilities each month and your total operating expenses will grow at a rate of 3.5% yearly. You will also have miscellaneous income of $50/room/month for a pet fee, in which you project 20% of the room total to have pets, and $100/room/month for parking, in which you project 85% of the room total to need a parking spot. Because you noticed the apartment complex's external paint was chipping away when you inspected it, you decide to put a fresh paint on the external buildings to become more visually appealing in year 1 costing you $20,000. You also plan to add some firepits, picnic tables, and some shaded covered areas around the pool in year 2 costing you $65,000. After 15 years when you go to sell the property, you find out that comparable properties are selling at an average cap rate of 5.5% and that you will have 6% selling expenses.

EXCELL FUNCTIONS PLEASE!!!

DISCOUNTED CASH FLOW