1. Use the following information and the provided Excel template to complete the calculations in steps 2 and 3. a. Rent income is $2,275 per month. b. Property taxes average $105 per month. c. Utilities (water for units 1 and 3) average $110 per month. d. Property management fees will be 10% of the monthly rental income. e. Homeowner's insurance will be $1,586 for the year (paid monthly). f. Other expenses are estimated to be $448 per month. g. The property cost $270,000 and the land is valued at $70,000. The building is depreciated over 27.5 years. h. The mortgage payment is based on a loan balance starting at $216,000 after you make a $54,000 down payment. The loan is at a 4.75% interest rate and has a 15-year term (see Project 2). i. Your combined federal and state tax rate is 28% (24% federal + 4% state). 2. Create a cash flow worksheet on the Cash Flow Worksheet" tab of the provided Excel spreadsheet (see Table 9.2 in the text for an example). 3. On the "Sale of Property tab, calculate the following items. You can refer to the examples in the "Popular Methods for Setting Value section of Chapter 8. a. Estimated value of the property using GRM. Three comparable properties are provided in the spreadsheet. b. Estimated value of the property using cap rate. The standard cap rate at the time of sale in the area is 5.1%. 4. Based on your calculations, write a paragraph in which you analyze your investment. Explain what the numbers mean as far as the profitability of your investment. Did you pay the right amount to purchase the property? If you were to sell it today, what price would you list it for and why? 2912 North Main Street Cash Flow Worksheet Cash In and Out Tax Calculation Notes 10 Description Revenue Rent Income 11 Total Revenue 14 Expenses Insurance Property Management Fees Property Taxes Other Expenses Utilities Depreciation * Use only the value of the building and a 27.5 year life Mortgage Payment: Principal Interest * Base on your first mortgage payment (see project 2) * Base on your first mortgage payment (see project 2) 24 25 * This is your loss from a tax calculation perspective * Use the tax calculation column and your effective tax rate 26 Total Payments 27 Rents less Payments 28 Tax Savings 29 Net cash flow 30 31 Net Income Before Financing 32 * Add back the full mortgage payment (principal + interest) Setting Value on Property to Sell 7 GRM Method Comparabe Properties Sale Price 9 Property #1 300,000 10 Property #2 327,900 11 Property #3 256,850 Rent 1,920 2,475 1,825 Average GRM 156.25 132.48 140.74 * Calculate the average GRM for all three comparables 14 Estimated Value of Property: = Average GRM * Monthly Rent 15 16 Cap Rate Method 17 Calculated Cap Rate at Purchase 18 Comparable Cap Rate 19 Estimated Value Based on comparable Cap Rate 20 21 Average Valuation 22 = (Net Income Before Financing * 12) / Purchase Price 5.10% Standard cap rate at time of sale = (Net Income Before Financing *12)/ Comparable Cap Rate at Time of Sale Average the two valuation methods 1. Use the following information and the provided Excel template to complete the calculations in steps 2 and 3. a. Rent income is $2,275 per month. b. Property taxes average $105 per month. c. Utilities (water for units 1 and 3) average $110 per month. d. Property management fees will be 10% of the monthly rental income. e. Homeowner's insurance will be $1,586 for the year (paid monthly). f. Other expenses are estimated to be $448 per month. g. The property cost $270,000 and the land is valued at $70,000. The building is depreciated over 27.5 years. h. The mortgage payment is based on a loan balance starting at $216,000 after you make a $54,000 down payment. The loan is at a 4.75% interest rate and has a 15-year term (see Project 2). i. Your combined federal and state tax rate is 28% (24% federal + 4% state). 2. Create a cash flow worksheet on the Cash Flow Worksheet" tab of the provided Excel spreadsheet (see Table 9.2 in the text for an example). 3. On the "Sale of Property tab, calculate the following items. You can refer to the examples in the "Popular Methods for Setting Value section of Chapter 8. a. Estimated value of the property using GRM. Three comparable properties are provided in the spreadsheet. b. Estimated value of the property using cap rate. The standard cap rate at the time of sale in the area is 5.1%. 4. Based on your calculations, write a paragraph in which you analyze your investment. Explain what the numbers mean as far as the profitability of your investment. Did you pay the right amount to purchase the property? If you were to sell it today, what price would you list it for and why? 2912 North Main Street Cash Flow Worksheet Cash In and Out Tax Calculation Notes 10 Description Revenue Rent Income 11 Total Revenue 14 Expenses Insurance Property Management Fees Property Taxes Other Expenses Utilities Depreciation * Use only the value of the building and a 27.5 year life Mortgage Payment: Principal Interest * Base on your first mortgage payment (see project 2) * Base on your first mortgage payment (see project 2) 24 25 * This is your loss from a tax calculation perspective * Use the tax calculation column and your effective tax rate 26 Total Payments 27 Rents less Payments 28 Tax Savings 29 Net cash flow 30 31 Net Income Before Financing 32 * Add back the full mortgage payment (principal + interest) Setting Value on Property to Sell 7 GRM Method Comparabe Properties Sale Price 9 Property #1 300,000 10 Property #2 327,900 11 Property #3 256,850 Rent 1,920 2,475 1,825 Average GRM 156.25 132.48 140.74 * Calculate the average GRM for all three comparables 14 Estimated Value of Property: = Average GRM * Monthly Rent 15 16 Cap Rate Method 17 Calculated Cap Rate at Purchase 18 Comparable Cap Rate 19 Estimated Value Based on comparable Cap Rate 20 21 Average Valuation 22 = (Net Income Before Financing * 12) / Purchase Price 5.10% Standard cap rate at time of sale = (Net Income Before Financing *12)/ Comparable Cap Rate at Time of Sale Average the two valuation methods