H J K 2020 Rental Assumptions # of Units 3 Unit Type 1 Bedroom 2 Bedroom 3 Bedroom Small Commercial Large Commercial Total Rent /Month $1,600 $2,200 $2,800 $3,200 $4,400 $14,200 RSF per Unit 830 995 1,475 1,200 2,100 24 Total RSF by Unit Type 3,320 9,950 8,850 3,600 2,100 27,820 6 6 3 1 2020 Operating Expense Assumptions Expense Expense 2020 Rate Per Taxes $30,453 $1.3767 Residential RSF / year Insurance $400 Residential Unit / year Repairs & Maintenance $120 Residential Unit / month Utilities $0.40 Per Common SF / month Legal $3,000 $3,000 Year Accounting $2,400 $2,400 Year Janitorial Cleaning $150 Day of Cleaning Management Fees 5% Dollar of Base Rent / year Elevator Maint. $900 $900 Year Security $10 Hour Brokerage Fee - Residential Monthly Rent Every 2 Years Brokerage Fee - Commercial 5% Total Lease Base Rent Capital Reserves $0.50 RSF 8 9 10 - 11 Number of months / year Residential Rent Increase Commercial Rent Increase Common Area Sq. Footage Vacancy & Collection Loss 12 2% 10% Annually Every 5 Years 12 13 1200 SF 14 4.5% Annually 15 Step Instructions Points Possible 1 Start Excel. Download and open the file named Excel Project. In the NOI sheet, calculate the Total Rentable Square Feet (RSF) by Unit Type in range F3:F7 by multiplying the number of units for each type by the square footage of each unit type. Then calculate the Total rentable square footage of the building in cell F8. In cell 14, enter a formula that calculates the annual insurance expense for Year 1. As the building owner, you obtain insurance at a rate of $400 per residential unit. Commercial units pay their own insurance. So you only pay insurance on the residential units. 2 In cell 15, enter a formula to calculate the Repairs & Maintenance expense estimate for Year 1. You assume that each month, there will be a repair expense of $120 for half of the residential units. Commercial units pay for their own repairs and maintenance. 3 In cell 16, enter a formula to calculate the annual Utilities expense estimate for Year 1. All tenants will pay their own utilities. But you, the owner, will pay $0.40 per square foot per month for the 1200 square feet of common area space (E13) of the building (lobby, hallways, elevator, gym, etc.). 2. In cell 19, enter a formula to calculate the annual Janitorial Cleaning expense estimate for Year 1. A cleaning company can clean the 1200 SF common area twice per week at a rate of $150 per day. Enter the annual cleaning expense, assuming 52 weeks in a year. In cell 110, enter a formula to calculate the annual property Management fee. Management companies charge 5% of base rent. (Use monthly rent estimates in range C3:C7 and the number of units per type in range D3:07 to determine annual base rent.) In cell 112, enter a formula to calculate the annual doorman security expense estimate for Year 1. You employ 2 doormen each day, one for an 8am-4pm shift and the other between 4pm-12midnight. Each one spends an 8 hour shift each day, 6 days per week. Assume 52 weeks per year and $10/hour wage for each doorman. In cell 113, enter a formula to calculate the Brokerage Fee to find tenants for the residential units. Realtors charge the equivalent of one month of rent for each unit leased. In cell 114, enter a formula to calculate the Brokerage Fee to find tenants for the commercial units. Each commercial lease is negotiated for a 5-year term of fixed base rent. So the brokerage fee for each unit is an upfront charge of 5% of the total base rent for the entire 5-year lease period. In cell 115, enter a formula to calculate the Capital Reserves estimate for Year 1. Capital Reserves are savings put aside in case there is a large unforeseen repair or expense incurred. You estimate a capital reserve of $0.50 cents per RSF of the building