nputs Cost of townhome $ 220,000.00 Rent Rent growth $ 24,000.00 3% Property tax $ 3,000.00...

Transcribed Image Text:

nputs Cost of townhome $ 220,000.00 Rent Rent growth $ 24,000.00 3% Property tax $ 3,000.00 Property tax growth 3% nsurance $ 2,000.00 Misc Exp $ 1,000.00 ncome tax rate 24% Depreciation straight-line 27.5 Resell value of townhome $ 500,000.00 Capital Gains Tax 15% Year Initial CF Revenue Property Tax Insurance Misc Exp Depreciation 0 1 $ (220,000.00) $ 24,000.00 2 3 4 5 6 7 8 9 10 Reportable Income Tax Net income Operating CFs $2,000.00 $ 2,000.00 $1,000.00 $ 1,000.00 $24,720.00 $25,461.60 $26,225.45 $27,012.21 $27,822.58 $28,657.26 $29,516.97 $30,402.48 $31,314.56 $ 3,000.00 $ 3,090.00 $3,182.70 $3,278.18 $3,376.53 $3,477.82 $3,582.16 $3,689.62 $3,800.31 $ 3,914.32 $ 2,000.00 $ 2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $ 2,000.00 $1,000.00 $ 1,000.00 $1,000.00 $1,000.00 $1,000.00 $1,000.00 $1,000.00 $1,000.00 $ 8,000.00 $ 8,000.00 $8,000.00 $8,000.00 $8,000.00 $8,000.00 $8,000.00 $8,000.00 $8,000.00 $ 8,000.00 $10,000.00 $10,630.00 $11,278.90 $11,947.27 $12,635.69 $13,344.76 $14,075.10 $14,827.35 $15,602.17 $2,551.20 $2,706.94 $2,867.34 $3,032.56 $3,202.74 $3,378.02 $3,558.56 $3,744.52 $8,078.80 $8,571.96 $9,079.92 $9,603.12 $10,142.01 $10,697.07 $11,268.79 $11,857.65 $16,078.80 $16,571.96 $17,079.92 $17,603.12 $18,142.01 $18,697.07 $19,268.79 $19,857.65 $2,400.00 $7,600.00 $15,600.00 $16,400.24 $3,936.06 $12,464.18 $20,464.18 $446,000.00 Terminal CF Book Value $140,000.00 Total CF -$220,000.00 $15,600.00 $16,078.80 $16,571.96 $17,079.92 $17,603.12 $18,142.01 $18,697.07 $19,268.79 $19,857.65 $466,464.18 Tax $ 54,000.00 Required return 12% NPV $22,166.28 Accept! IRR 13.38% 13.38>12% ACCEPT! ASSIGNMENT Sensitivity Analysis in Capital Budgeting Analysis Use Sheet "4" for this assignment. In the townhome investment model, do a sensitivity analysis by using a two-input data table. The two input variables are Rent and Resell Value: Rent ($16,000, $18,000,..., $30,000) Resell value ($250,000, $280,000, ..., $550,000) Show IRR as a function of these two variables. Use conditional formatting to highlight the unprofitable combinations. M3 Assignment - Sensitivity Analysis in Capital Budgeting Analysis Rubric Construct input variables Use the correct result cell Complete the data table 0.5 pt 0.5 pt 3 pts Highlight correct cells using conditional formatting 1 pt Total 5 pts nputs Cost of townhome $ 220,000.00 Rent Rent growth $ 24,000.00 3% Property tax $ 3,000.00 Property tax growth 3% nsurance $ 2,000.00 Misc Exp $ 1,000.00 ncome tax rate 24% Depreciation straight-line 27.5 Resell value of townhome $ 500,000.00 Capital Gains Tax 15% Year Initial CF Revenue Property Tax Insurance Misc Exp Depreciation 0 1 $ (220,000.00) $ 24,000.00 2 3 4 5 6 7 8 9 10 Reportable Income Tax Net income Operating CFs $2,000.00 $ 2,000.00 $1,000.00 $ 1,000.00 $24,720.00 $25,461.60 $26,225.45 $27,012.21 $27,822.58 $28,657.26 $29,516.97 $30,402.48 $31,314.56 $ 3,000.00 $ 3,090.00 $3,182.70 $3,278.18 $3,376.53 $3,477.82 $3,582.16 $3,689.62 $3,800.31 $ 3,914.32 $ 2,000.00 $ 2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $ 2,000.00 $1,000.00 $ 1,000.00 $1,000.00 $1,000.00 $1,000.00 $1,000.00 $1,000.00 $1,000.00 $ 8,000.00 $ 8,000.00 $8,000.00 $8,000.00 $8,000.00 $8,000.00 $8,000.00 $8,000.00 $8,000.00 $ 8,000.00 $10,000.00 $10,630.00 $11,278.90 $11,947.27 $12,635.69 $13,344.76 $14,075.10 $14,827.35 $15,602.17 $2,551.20 $2,706.94 $2,867.34 $3,032.56 $3,202.74 $3,378.02 $3,558.56 $3,744.52 $8,078.80 $8,571.96 $9,079.92 $9,603.12 $10,142.01 $10,697.07 $11,268.79 $11,857.65 $16,078.80 $16,571.96 $17,079.92 $17,603.12 $18,142.01 $18,697.07 $19,268.79 $19,857.65 $2,400.00 $7,600.00 $15,600.00 $16,400.24 $3,936.06 $12,464.18 $20,464.18 $446,000.00 Terminal CF Book Value $140,000.00 Total CF -$220,000.00 $15,600.00 $16,078.80 $16,571.96 $17,079.92 $17,603.12 $18,142.01 $18,697.07 $19,268.79 $19,857.65 $466,464.18 Tax $ 54,000.00 Required return 12% NPV $22,166.28 Accept! IRR 13.38% 13.38>12% ACCEPT! ASSIGNMENT Sensitivity Analysis in Capital Budgeting Analysis Use Sheet "4" for this assignment. In the townhome investment model, do a sensitivity analysis by using a two-input data table. The two input variables are Rent and Resell Value: Rent ($16,000, $18,000,..., $30,000) Resell value ($250,000, $280,000, ..., $550,000) Show IRR as a function of these two variables. Use conditional formatting to highlight the unprofitable combinations. M3 Assignment - Sensitivity Analysis in Capital Budgeting Analysis Rubric Construct input variables Use the correct result cell Complete the data table 0.5 pt 0.5 pt 3 pts Highlight correct cells using conditional formatting 1 pt Total 5 pts