The spreadsheet must be active with formulas with no hard inputs for calculated cells. Overview This hypothetical property is used to review the discounted cash flow model in Excel to include before and after-tax: cash flows, sales proceeds, and performance measures. In addition, the case lays the groundwork for more complex case studies. Topics covered in the case include: Purchase price and acquisition costs Before-tax cash flows from operations and future sale Before-tax investment measures - internal rate of return, net present value, and capital accumulation Mortgage concepts - amortization, periodic interest expense, and loan balance Income tax concepts including cost recovery allowance, recapture, and capital gains After-tax investment measures - internal rate of return, net present value, and capital accumulation . Case Setup As a real estate professional, you are evaluating an apartment complex, and your client is an individual investor who is not familiar with real estate. Property Information The property consists of the following: . . . 50 one-bedroom/one-bath units the average market rent is $1,200 per month 60 two-bedroom/one-bath units - the average market rent is $1,500 per month 40 three-bedroom/two-bath units the average market rent is $2,150 per month Miscellaneous income is $2,500 per month (clubhouse rental, vending, income, application & late fees, etc.) Rents are increasing by 3.5 percent per year The market vacancy is 8 percent per year Operating expenses are 32 percent of effective gross income Purchase price $18,500,000 Acquisition costs $200,000 . . . . . Assessed land value $2,630,000 Assessed improvement value $10,500,000 Disposition cap rate 9.75 percent Disposition cost of sale 4 percent . Investor Information Ordinary tax rate: 37 percent Capital gains tax rate: 20 percent Cost recovery recapture tax rate: 25 percent Assume that the client qualifies to use any operating loss in the year incurred Before-tax target yield: 12 percent (discount rate) Holding period: 5 years Assume acquisition occurs on the first day of the tax year Assume disposition occurs on the last day of the tax year Apply the midmonth convention to the month of acquisition and disposition. Thus, 3.485 percent in years 1 and 5 and 3.636 percent in years 2 to 4. . TABLE 7 General Depreciation System-MACRS Residential Rental Real Property Placed in Service After 12/31/86 Applicable Recovery Period: 27.5 Years Applicable Convention: Mid-Month Applicable Depreciation Method: Straight Line If the Recovery And the Month in the First Recovery Year the Property is placed in Service is: Year is: 1 2 3 4 5 6 7 9 8 10 11 12 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 The Depreciation Rate is: 3.485 3.182 2.879 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.637 3.637 3.636 3.636 3.636 3.637 3.637 3.637 3.636 3.636 3.636 3.637 3.637 3.637 3.636 3.636 3.636 3.637 3.637 3.637 3.636 3.636 3.636 3.637 3.637 3.637 3.636 3.636 3.636 3.637 3.637 3.637 3.636 3.636 3.636 3.637 3.637 3.637 3.636 3.636 3.636 3.637 3.637 3.637 3.636 3.636 3.636 3.637 3.637 3.637 3.636 3.636 3.636 1.970 2.273 2.576 0.000 0.000 0.000 2.576 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 2.879 0.000 2.273 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.182 0.000 1.970 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.485 0.000 1.667 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 0.152 1.364 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 0.455 1.061 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 0.758 0.758 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 1.061 0.455 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 1.364 0.152 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 1.667 Financing Maximum loan-to-value (LTV) ratio: 75 percent Minimum debt service coverage ratio: 1.20 Loan amount: calculate the loan amount using both the LTV and DSCR and use the lower amount rounded down to the nearest $1,000 Interest rate 6 percent Amortization period: 20 years Payments per year: 12 Loan cost: 2 percent of the loan amount . Questions 1. What is the acquisition cap rate? 2. What is the gross rent multiplier? 3. What is the equity dividend rate? 4. What is the interest deduction in year three? 5. What are the cost recovery deductions in years two and five? 6. What are the before-tax cash flows in years two and four? 7. What are the after-tax cash flows in years one and three? 8. What is the mortgage balance at the time of sale? 9. What is the tax on the cost recovery? 10. What is the tax on the capital gain from appreciation? 11. What are the before-tax sales proceeds? 12. What are the after-tax sales proceeds? 13. What is the before-tax NPV and IRR? 14. What is the after-tax NPV and IRR? 15. Do you recommend purchasing the property? 16. What is the before and after-tax capital accumulation? The spreadsheet must be active with formulas with no hard inputs for calculated cells. Overview This hypothetical property is used to review the discounted cash flow model in Excel to include before and after-tax: cash flows, sales proceeds, and performance measures. In addition, the case lays the groundwork for more complex case studies. Topics covered in the case include: Purchase price and acquisition costs Before-tax cash flows from operations and future sale Before-tax investment measures - internal rate of return, net present value, and capital accumulation Mortgage concepts - amortization, periodic interest expense, and loan balance Income tax concepts including cost recovery allowance, recapture, and capital gains After-tax investment measures - internal rate of return, net present value, and capital accumulation . Case Setup As a real estate professional, you are evaluating an apartment complex, and your client is an individual investor who is not familiar with real estate. Property Information The property consists of the following: . . . 50 one-bedroom/one-bath units the average market rent is $1,200 per month 60 two-bedroom/one-bath units - the average market rent is $1,500 per month 40 three-bedroom/two-bath units the average market rent is $2,150 per month Miscellaneous income is $2,500 per month (clubhouse rental, vending, income, application & late fees, etc.) Rents are increasing by 3.5 percent per year The market vacancy is 8 percent per year Operating expenses are 32 percent of effective gross income Purchase price $18,500,000 Acquisition costs $200,000 . . . . . Assessed land value $2,630,000 Assessed improvement value $10,500,000 Disposition cap rate 9.75 percent Disposition cost of sale 4 percent . Investor Information Ordinary tax rate: 37 percent Capital gains tax rate: 20 percent Cost recovery recapture tax rate: 25 percent Assume that the client qualifies to use any operating loss in the year incurred Before-tax target yield: 12 percent (discount rate) Holding period: 5 years Assume acquisition occurs on the first day of the tax year Assume disposition occurs on the last day of the tax year Apply the midmonth convention to the month of acquisition and disposition. Thus, 3.485 percent in years 1 and 5 and 3.636 percent in years 2 to 4. . TABLE 7 General Depreciation System-MACRS Residential Rental Real Property Placed in Service After 12/31/86 Applicable Recovery Period: 27.5 Years Applicable Convention: Mid-Month Applicable Depreciation Method: Straight Line If the Recovery And the Month in the First Recovery Year the Property is placed in Service is: Year is: 1 2 3 4 5 6 7 9 8 10 11 12 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 The Depreciation Rate is: 3.485 3.182 2.879 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.637 3.637 3.636 3.636 3.636 3.637 3.637 3.637 3.636 3.636 3.636 3.637 3.637 3.637 3.636 3.636 3.636 3.637 3.637 3.637 3.636 3.636 3.636 3.637 3.637 3.637 3.636 3.636 3.636 3.637 3.637 3.637 3.636 3.636 3.636 3.637 3.637 3.637 3.636 3.636 3.636 3.637 3.637 3.637 3.636 3.636 3.636 3.637 3.637 3.637 3.636 3.636 3.636 1.970 2.273 2.576 0.000 0.000 0.000 2.576 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 2.879 0.000 2.273 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.182 0.000 1.970 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.485 0.000 1.667 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 0.152 1.364 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 0.455 1.061 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 0.758 0.758 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 1.061 0.455 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 1.364 0.152 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 3.637 3.636 1.667 Financing Maximum loan-to-value (LTV) ratio: 75 percent Minimum debt service coverage ratio: 1.20 Loan amount: calculate the loan amount using both the LTV and DSCR and use the lower amount rounded down to the nearest $1,000 Interest rate 6 percent Amortization period: 20 years Payments per year: 12 Loan cost: 2 percent of the loan amount . Questions 1. What is the acquisition cap rate? 2. What is the gross rent multiplier? 3. What is the equity dividend rate? 4. What is the interest deduction in year three? 5. What are the cost recovery deductions in years two and five? 6. What are the before-tax cash flows in years two and four? 7. What are the after-tax cash flows in years one and three? 8. What is the mortgage balance at the time of sale? 9. What is the tax on the cost recovery? 10. What is the tax on the capital gain from appreciation? 11. What are the before-tax sales proceeds? 12. What are the after-tax sales proceeds? 13. What is the before-tax NPV and IRR? 14. What is the after-tax NPV and IRR? 15. Do you recommend purchasing the property? 16. What is the before and after-tax capital accumulation