B D E F G 5 Use a 5-year holding period to decide whether or not this is a financially feasible 6 investment. Compute the ratios, before-tax and after-tax NPV and IRR. 7 Comment on whether your investment is worthwhile. Comment on the effective tax rate 8 9 Do all your calculations in Excel. You cannot do your calculations using a 10 financial calculator and input your answers in Excel. 11 12 Use the template on the other tab for your answers. 13 14 Assumptions 15 16 Number of units 300 d 17 Rent $ 1,400 per unit per month 18 Rental growth 4.0% 19 Other income $ 415,000 20 Other income growth 3.0% 21 Vacancy rate 6.0% 22 Expenses 23 Real Estate Taxes $ 325,000 24 Insurance S 75,000 25 Utilities $ 250,000 26 Payroll and Benefits $ 425,000 27 Maintenance, Repairs & D S 200,000 28 Services $ 60,000 29 Miscellaneous & Admin $ 85,000 30 Advertising & Promotions S 75,000 31 Property Management Fer 3.0% of EGI 32 Reserves and Replacements 300 per unit 33 34 Expense growth rate (other than taxes) 2.5% 35 Taxes growth rate 3.0% ue E F 30 years A D 32 Reserves and Replacemen $ 300 per unit 33 G 34 Expense growth rate (other than taxes) 2.5% 35 Taxes growth rate 3.0% 36 Acquistion price $ 67,884,500 37 Mortgage amount $ 48,750,000 38 Mortgage rate 3.25% 39 Mortgage amortization 40 Mortgage payments Monthly 41 Mortgage loan fees 3.00% 42 Selling cost 5.00% 43 Terminal cap rate 100 bps above purchase cap rate 44 Land value 15.00% of purchase price 45 Discount rate for Unlevered NPV 8.00% 46 Investors Before-tax required return 12.25% 47 Investors After-tax required return 8.00% 48 Investor's tax rate 35.00% 49 Recapture rate 25.00% 50 Capital gains tax rate 20.00% 51 Holding period 5 years 52 A D E F G H Year 2 Year) Year 4 Year 5 Year 6 Year 1 3 REVE Gross Potential Rent 5 Other Income 6 Patential Gross income 7. Vacancy/Bad Debt/Concessions 8 Efective Grosse 9 10 Expense 11 Real Estate Taxes 12 Insurance 13 Utilities 14 Payroll and Benefits 15 Maintenance, Repairs & Decorating 16 Services 17 Miscellaneous & Admin 18 Advertising & Promotions 19 Property Management Fee 20 Reserves and Replacements 21 Total Expen 22 23 Net Operating income 24 25 UNLEVERED ANALYSIS 26 27 Et Price 28 Exit Cap Rate 29 Sales Price (based on YR 6 NOI) Sales expenses 31 Net Sales Price 32 33 Discount rate 34 35 DCF Year 36 0 NOI PV IRR cale C3 x & fic D E B F G 1 Real Estate Taxes 2 Insurance 3 Utilities 4 Payroll and Benefits 5 Maintenance, Repairs & Decorating 6 Services 7 Miscellaneous & Admin 8 Advertising & Promotions Property Management Fee 0 Reserves and Replacements 1 Total Expenses 2 3 Net Operating Income 4 5 UNLEVERED ANALYSIS 6 7 Ext Price 8 Exit Cap Rate Sales Price (based on YR 6 NOI) 0 Sales expenses 1 Net Sales Price 2 3 Discount rate 14 35 DCF 36 17 38 39 30 31 2 93 14 NOI PV IRR calc Year 0 1 2 3 4 5 NPV IRR 15 B D E F G 5 Use a 5-year holding period to decide whether or not this is a financially feasible 6 investment. Compute the ratios, before-tax and after-tax NPV and IRR. 7 Comment on whether your investment is worthwhile. Comment on the effective tax rate 8 9 Do all your calculations in Excel. You cannot do your calculations using a 10 financial calculator and input your answers in Excel. 11 12 Use the template on the other tab for your answers. 13 14 Assumptions 15 16 Number of units 300 d 17 Rent $ 1,400 per unit per month 18 Rental growth 4.0% 19 Other income $ 415,000 20 Other income growth 3.0% 21 Vacancy rate 6.0% 22 Expenses 23 Real Estate Taxes $ 325,000 24 Insurance S 75,000 25 Utilities $ 250,000 26 Payroll and Benefits $ 425,000 27 Maintenance, Repairs & D S 200,000 28 Services $ 60,000 29 Miscellaneous & Admin $ 85,000 30 Advertising & Promotions S 75,000 31 Property Management Fer 3.0% of EGI 32 Reserves and Replacements 300 per unit 33 34 Expense growth rate (other than taxes) 2.5% 35 Taxes growth rate 3.0% ue E F 30 years A D 32 Reserves and Replacemen $ 300 per unit 33 G 34 Expense growth rate (other than taxes) 2.5% 35 Taxes growth rate 3.0% 36 Acquistion price $ 67,884,500 37 Mortgage amount $ 48,750,000 38 Mortgage rate 3.25% 39 Mortgage amortization 40 Mortgage payments Monthly 41 Mortgage loan fees 3.00% 42 Selling cost 5.00% 43 Terminal cap rate 100 bps above purchase cap rate 44 Land value 15.00% of purchase price 45 Discount rate for Unlevered NPV 8.00% 46 Investors Before-tax required return 12.25% 47 Investors After-tax required return 8.00% 48 Investor's tax rate 35.00% 49 Recapture rate 25.00% 50 Capital gains tax rate 20.00% 51 Holding period 5 years 52 A D E F G H Year 2 Year) Year 4 Year 5 Year 6 Year 1 3 REVE Gross Potential Rent 5 Other Income 6 Patential Gross income 7. Vacancy/Bad Debt/Concessions 8 Efective Grosse 9 10 Expense 11 Real Estate Taxes 12 Insurance 13 Utilities 14 Payroll and Benefits 15 Maintenance, Repairs & Decorating 16 Services 17 Miscellaneous & Admin 18 Advertising & Promotions 19 Property Management Fee 20 Reserves and Replacements 21 Total Expen 22 23 Net Operating income 24 25 UNLEVERED ANALYSIS 26 27 Et Price 28 Exit Cap Rate 29 Sales Price (based on YR 6 NOI) Sales expenses 31 Net Sales Price 32 33 Discount rate 34 35 DCF Year 36 0 NOI PV IRR cale C3 x & fic D E B F G 1 Real Estate Taxes 2 Insurance 3 Utilities 4 Payroll and Benefits 5 Maintenance, Repairs & Decorating 6 Services 7 Miscellaneous & Admin 8 Advertising & Promotions Property Management Fee 0 Reserves and Replacements 1 Total Expenses 2 3 Net Operating Income 4 5 UNLEVERED ANALYSIS 6 7 Ext Price 8 Exit Cap Rate Sales Price (based on YR 6 NOI) 0 Sales expenses 1 Net Sales Price 2 3 Discount rate 14 35 DCF 36 17 38 39 30 31 2 93 14 NOI PV IRR calc Year 0 1 2 3 4 5 NPV IRR 15
