N 1 2020 Rental Assumptions Extra Credit 2 # of Units 10 Unit Type Rent/Month 1 Bedroom $1,600 2 Bedroom $2,200 3 Bedroom $2,800 Small Commercial $3,200 Larse Commercial $4,400 Total $14,200 RSF per Unit 830 995 1,475 1,200 2,100 6,600 Total RSF by Unit Type 3,320 9,950 8,850 3,600 2,100 27,820 2020 Operating Expense Assumptions Expense Expense 2020 Rate Per Taxes $30,453 $1.3767 Residential RSF / year Insurance $106,176,000 $400 Residential Unit / year Repairs & Maintenance $1,327,200 $120 Residential Unit/month Utilities $480 $0.40 Per Common SF / month Legal $3,000 $3,000 Year Accounting $2,400 $2,400 Year Janitorial Cleaning $9,360,000 $150 Day of Cleaning Management Fees $204,480 5% Dollar of Base Rent/year Elevator Maint. $900 $900 Year Security $8,320 $10 Hour Brokerage Fee - Residential Monthly Rent Every 2 Years Brokerage Fee - Commercial $17,100 5% Total lease Base Rent Capital Reserves $13,910 $0.50 RSE Purchase Price Down Payment Down Paymen Loan Amount Annual Interes Loan Term (yea Monthly Debt Annual Debt Pa 24 12 2% Number of months/year Residential Rent Increase Commercial Rent Increase Common Area Sq. Footage Vacancy & Collection Loss Annually Every 5 Years 10% 1200 4.5% Annually Income Unit # Unit Type 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 Unit 1A 1 Bedroom Unit 1B 2 Bedroom Unit 10 2 Bedroom Unit 1D 3 Bedroom Unit 2A 1 Bedroom Unit 2B 2 Bedroom Unit 2C 2 Bedroom Unit 2D 3 Bedroom Unit 3A 1 Bedroom Unit 3B 2 Bedroom Unit 3C 2 Bedroom Unit 3D 3 Bedroom Unit 4A 1 Bedroom Unit 4B 2 Bedroom Unit 40 2 Bedroom Unit 4D 3 Bedroom Unit SA 2 Bedroom Unit 5B 2 Bedroom Unit 5C 3 Bedroom Unit 5D 3 Bedroom Unit 61 Large commercial Unit G2 Small Commercial Unit G3 Small Commercial Unit G4 Small Commercial Total Base Rent 19,200 26,400 26,400 33,600 19,200 26,400 26,100 33,600 19,200 26,400 26,400 33,600 19,200 26,400 26,400 33,600 26,400 26,400 33,600 33,600 52,800 38,400 38,400 38,400 710,400 19,584 26,928 26,928 34,272 19,584 26,928 26,928 34,272 19,584 26,928 26,928 34,272 19,584 26,928 26,928 34,272 26,928 26,928 34,272 34,272 52,800 38,400 38,400 38,400 721,248 19,976 27,467 27,467 34,957 19,976 27,467 27,167 34,957 19,976 27,467 27,467 34,957 19,976 27,467 27,467 34,957 27,467 27,467 34,957 34,957 52,800 38,400 38,400 38,400 732,313 20,375 28,016 28,016 35,657 20,375 28,016 28,016 35,657 20,375 28,016 28,016 35,657 20,375 28,016 28,016 35,657 28,016 28,016 35,657 35,657 52,800 38,400 38,400 38,400 743,599 20,783 28,576 28,576 36,370 20,783 28,576 28,576 36,370 20,783 28,576 28,576 36,370 20,783 28,576 28,576 36,370 28,576 28,576 36,370 36,370 52,800 38,400 38,400 38,400 755,111 21,198 29,148 29,148 37,097 21,198 29,148 29,118 37,097 21,198 29,148 29,148 37,097 21,198 29,148 29,148 37,097 29,148 29,148 37,097 37,097 44 2% Percentage Rent NOI Taxcast + Potential Gross Rent 724,608.00 47 48 Less: Vacancy & Collection Loss Effective Gross Rent 49 Expenses 53 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 55 59 % Increase or Fee Use Hlookup Taxes 3% Insurance 3% Repairs & Maintenance 3% Utilities 3% Legal 3% Accounting 3% Janitorial Cleaning 5% Management Fees 0% Elevator Maint. 3% Security 1 month Brokerage Fee - Residential 5% Brokerage Fee - Commercial 3% Capital Reserves Total Expenses 61 62 63 64 65 66 67 68 69 Net Operating Income 70 71 72 73 Total Debt Service Cash Flow After Debt Service 74 75 76 77 78 79 * Additional Notes Large Commercial Tenant Sales Projections | NOI Taxcast + 520000 530000 550000 545000 548000 580000 595000 610000 625000 640000 In cell E19, enter a VLookup function to calculate the annual base rent for the first unit based on Unit Type. Use the 2020 Rental Assumptions table in your formula and make sure to calculate annual rent. Copy your formula down the column to cell E42. 12 You assume that Residential Rent will increase by 2% each year (see cell E11). For each residential unit, calculate annual rent estimates for Years 2021 to 2029 by applying the Residential Rent Increase to the previous year's rent. Base rent for the commercial leases are fixed for 5 years, after which you will either renew with the current tenant or re-lease to a new tenant for an increased fixed rent for 5 years. You assume a Year 6 rent increase (see cell E12) over the current negotiated rent for each unit. For each commercial unit, enter the annual rent for Years 2021 to 2029, assuming that in the 6th Year, there will be a new increased rent for the next 5 years. 3 15 Calculate Total Base Rent for each year in row 43. Apply Bold font to this row and format the values as Currency format with no decimals. You negotiate a percentage rent component of the lease with the Large Commercia/tenant in Unit G1. The Percentage Rent clause says that the tenant will pay you 2% of their gross sales, not to exceed $12,000 in percentage rent. The Large Commercial tenant has given you their sales projections for the next 10 years (row 79). Enter an IF function in cells E45:N45 that calculates the percentage rent based on the tenant's sales projection for that year. 17 Calculate Potential Gross Rent for each year in row 46 by adding Percentage Rent to Total Base Rent. Apply Bold font to this row and format the values as Currency format with no decimals. You can expect a certain amount of vacancy and uncollected rent each year, which is accounted for in the Vacancy and Collection Loss estimate (cell E14). Calculate Vacancy and Collection Loss estimate by multiplying the loss factor to the Potential Gross Rent for each year in row 48. 19 Subtract Vacancy & Collection Loss from Potential Gross Rent to determine Effective Gross Rent for each year in row 49. Apply Bold font to this row and format the values as Currency format with no decimals. In cell E54, enter an HLookup formula based on the Year to determine the Per RSF tax rate using the table in the Taxcast sheet. (Do not enter the hardcoded tax rate.) You apply the Per RSF rate found by the HLookup to the residential RSF. The commercial units will pay their own share of taxes. Copy your formula to Years 2021 to 2029. Hint: See Tax Expense formula in cells 13 and 13. 20 21 Set cell E55 equal to the 2020 Insurance expense in the 2020 Operating Expense Assumptions table at the top of the sheet. Copy the formula down to cell E60. For each of the expenses in rows 55 to 60, calculate 2021 to 2029 expenses using their respective annual percentage increases displayed in column B. Management Fees are calculated as a percentage of Total Base Rent. In row 61, enter a formula to calculate the Management Fee for each year. You negotiate a maintenance contract with the elevator manufacturer for a fixed cost of $900 per year for 10 years. Set cell E62 equal to the appropriate cell in the 2020 Operating Expense Assumptions table. Enter a formula in cells F62:N62 to display the annual Elevator Maintenance cost for each year. 23 N 1 2020 Rental Assumptions Extra Credit 2 # of Units 10 Unit Type Rent/Month 1 Bedroom $1,600 2 Bedroom $2,200 3 Bedroom $2,800 Small Commercial $3,200 Larse Commercial $4,400 Total $14,200 RSF per Unit 830 995 1,475 1,200 2,100 6,600 Total RSF by Unit Type 3,320 9,950 8,850 3,600 2,100 27,820 2020 Operating Expense Assumptions Expense Expense 2020 Rate Per Taxes $30,453 $1.3767 Residential RSF / year Insurance $106,176,000 $400 Residential Unit / year Repairs & Maintenance $1,327,200 $120 Residential Unit/month Utilities $480 $0.40 Per Common SF / month Legal $3,000 $3,000 Year Accounting $2,400 $2,400 Year Janitorial Cleaning $9,360,000 $150 Day of Cleaning Management Fees $204,480 5% Dollar of Base Rent/year Elevator Maint. $900 $900 Year Security $8,320 $10 Hour Brokerage Fee - Residential Monthly Rent Every 2 Years Brokerage Fee - Commercial $17,100 5% Total lease Base Rent Capital Reserves $13,910 $0.50 RSE Purchase Price Down Payment Down Paymen Loan Amount Annual Interes Loan Term (yea Monthly Debt Annual Debt Pa 24 12 2% Number of months/year Residential Rent Increase Commercial Rent Increase Common Area Sq. Footage Vacancy & Collection Loss Annually Every 5 Years 10% 1200 4.5% Annually Income Unit # Unit Type 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 Unit 1A 1 Bedroom Unit 1B 2 Bedroom Unit 10 2 Bedroom Unit 1D 3 Bedroom Unit 2A 1 Bedroom Unit 2B 2 Bedroom Unit 2C 2 Bedroom Unit 2D 3 Bedroom Unit 3A 1 Bedroom Unit 3B 2 Bedroom Unit 3C 2 Bedroom Unit 3D 3 Bedroom Unit 4A 1 Bedroom Unit 4B 2 Bedroom Unit 40 2 Bedroom Unit 4D 3 Bedroom Unit SA 2 Bedroom Unit 5B 2 Bedroom Unit 5C 3 Bedroom Unit 5D 3 Bedroom Unit 61 Large commercial Unit G2 Small Commercial Unit G3 Small Commercial Unit G4 Small Commercial Total Base Rent 19,200 26,400 26,400 33,600 19,200 26,400 26,100 33,600 19,200 26,400 26,400 33,600 19,200 26,400 26,400 33,600 26,400 26,400 33,600 33,600 52,800 38,400 38,400 38,400 710,400 19,584 26,928 26,928 34,272 19,584 26,928 26,928 34,272 19,584 26,928 26,928 34,272 19,584 26,928 26,928 34,272 26,928 26,928 34,272 34,272 52,800 38,400 38,400 38,400 721,248 19,976 27,467 27,467 34,957 19,976 27,467 27,167 34,957 19,976 27,467 27,467 34,957 19,976 27,467 27,467 34,957 27,467 27,467 34,957 34,957 52,800 38,400 38,400 38,400 732,313 20,375 28,016 28,016 35,657 20,375 28,016 28,016 35,657 20,375 28,016 28,016 35,657 20,375 28,016 28,016 35,657 28,016 28,016 35,657 35,657 52,800 38,400 38,400 38,400 743,599 20,783 28,576 28,576 36,370 20,783 28,576 28,576 36,370 20,783 28,576 28,576 36,370 20,783 28,576 28,576 36,370 28,576 28,576 36,370 36,370 52,800 38,400 38,400 38,400 755,111 21,198 29,148 29,148 37,097 21,198 29,148 29,118 37,097 21,198 29,148 29,148 37,097 21,198 29,148 29,148 37,097 29,148 29,148 37,097 37,097 44 2% Percentage Rent NOI Taxcast + Potential Gross Rent 724,608.00 47 48 Less: Vacancy & Collection Loss Effective Gross Rent 49 Expenses 53 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 55 59 % Increase or Fee Use Hlookup Taxes 3% Insurance 3% Repairs & Maintenance 3% Utilities 3% Legal 3% Accounting 3% Janitorial Cleaning 5% Management Fees 0% Elevator Maint. 3% Security 1 month Brokerage Fee - Residential 5% Brokerage Fee - Commercial 3% Capital Reserves Total Expenses 61 62 63 64 65 66 67 68 69 Net Operating Income 70 71 72 73 Total Debt Service Cash Flow After Debt Service 74 75 76 77 78 79 * Additional Notes Large Commercial Tenant Sales Projections | NOI Taxcast + 520000 530000 550000 545000 548000 580000 595000 610000 625000 640000 In cell E19, enter a VLookup function to calculate the annual base rent for the first unit based on Unit Type. Use the 2020 Rental Assumptions table in your formula and make sure to calculate annual rent. Copy your formula down the column to cell E42. 12 You assume that Residential Rent will increase by 2% each year (see cell E11). For each residential unit, calculate annual rent estimates for Years 2021 to 2029 by applying the Residential Rent Increase to the previous year's rent. Base rent for the commercial leases are fixed for 5 years, after which you will either renew with the current tenant or re-lease to a new tenant for an increased fixed rent for 5 years. You assume a Year 6 rent increase (see cell E12) over the current negotiated rent for each unit. For each commercial unit, enter the annual rent for Years 2021 to 2029, assuming that in the 6th Year, there will be a new increased rent for the next 5 years. 3 15 Calculate Total Base Rent for each year in row 43. Apply Bold font to this row and format the values as Currency format with no decimals. You negotiate a percentage rent component of the lease with the Large Commercia/tenant in Unit G1. The Percentage Rent clause says that the tenant will pay you 2% of their gross sales, not to exceed $12,000 in percentage rent. The Large Commercial tenant has given you their sales projections for the next 10 years (row 79). Enter an IF function in cells E45:N45 that calculates the percentage rent based on the tenant's sales projection for that year. 17 Calculate Potential Gross Rent for each year in row 46 by adding Percentage Rent to Total Base Rent. Apply Bold font to this row and format the values as Currency format with no decimals. You can expect a certain amount of vacancy and uncollected rent each year, which is accounted for in the Vacancy and Collection Loss estimate (cell E14). Calculate Vacancy and Collection Loss estimate by multiplying the loss factor to the Potential Gross Rent for each year in row 48. 19 Subtract Vacancy & Collection Loss from Potential Gross Rent to determine Effective Gross Rent for each year in row 49. Apply Bold font to this row and format the values as Currency format with no decimals. In cell E54, enter an HLookup formula based on the Year to determine the Per RSF tax rate using the table in the Taxcast sheet. (Do not enter the hardcoded tax rate.) You apply the Per RSF rate found by the HLookup to the residential RSF. The commercial units will pay their own share of taxes. Copy your formula to Years 2021 to 2029. Hint: See Tax Expense formula in cells 13 and 13. 20 21 Set cell E55 equal to the 2020 Insurance expense in the 2020 Operating Expense Assumptions table at the top of the sheet. Copy the formula down to cell E60. For each of the expenses in rows 55 to 60, calculate 2021 to 2029 expenses using their respective annual percentage increases displayed in column B. Management Fees are calculated as a percentage of Total Base Rent. In row 61, enter a formula to calculate the Management Fee for each year. You negotiate a maintenance contract with the elevator manufacturer for a fixed cost of $900 per year for 10 years. Set cell E62 equal to the appropriate cell in the 2020 Operating Expense Assumptions table. Enter a formula in cells F62:N62 to display the annual Elevator Maintenance cost for each year. 23