Question

1 Approved Answer

Posted on Jul 11, 2024

After completing the townhome investment model (Shown Below), do a sensitivity analysis by using a two-input data table. The two input variables are Rent and

After completing the townhome investment model (Shown Below), 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.

image text in transcribed Zooming makes the image readable.

1 2 3 5 6 7 8 10 Inputs Cost of Townhome $ 220,000.00 Rent $ 24,000.00 Rent Growth 3% Property Taxes $ 3,000.00 Property Tax Growth 3% Insurance $ 2,000.00 Misc Exp $ 1,000.00 Income Tax Rate 24% Depreciation Straight-Line 27.5 Resell Value of Townhome $500,000.00 Capital Gains Tax 15% Year 0 Initial CF $(220,000.00) Revenue Property Tax Insurance Misc Exp Depreciation Reportable Income Tax Net Income Operationg CFS Termical CF $ 24,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 $ 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 $ 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 $ 16,400.24 $ 2,400.00 $ 2,551.20 $ 2,706.94 $ 2,867.34 $ 3,032.56 $ 3,202.74 $ 3,378.02 $ 3,558.56 $ 3,744.52 $ 3,936.06 $ 7,600.00 $ 8,078.80 $ 8,571.96 $ 9,079.92 $ 9,603.12 $10,142.01 $10,697.07 $11,268.79 $ 11,857.65 $ 12,464.18 $ 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 $ 20,464.18 $ 446,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 Required Return NPV IRR 12% $22,166.28 Accept 13.38% Accept Book Value Tax $ 140,000.00 $ 54,000.00 1 2 3 5 6 7 8 10 Inputs Cost of Townhome $ 220,000.00 Rent $ 24,000.00 Rent Growth 3% Property Taxes $ 3,000.00 Property Tax Growth 3% Insurance $ 2,000.00 Misc Exp $ 1,000.00 Income Tax Rate 24% Depreciation Straight-Line 27.5 Resell Value of Townhome $500,000.00 Capital Gains Tax 15% Year 0 Initial CF $(220,000.00) Revenue Property Tax Insurance Misc Exp Depreciation Reportable Income Tax Net Income Operationg CFS Termical CF $ 24,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 $ 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 $ 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 $ 16,400.24 $ 2,400.00 $ 2,551.20 $ 2,706.94 $ 2,867.34 $ 3,032.56 $ 3,202.74 $ 3,378.02 $ 3,558.56 $ 3,744.52 $ 3,936.06 $ 7,600.00 $ 8,078.80 $ 8,571.96 $ 9,079.92 $ 9,603.12 $10,142.01 $10,697.07 $11,268.79 $ 11,857.65 $ 12,464.18 $ 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 $ 20,464.18 $ 446,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 Required Return NPV IRR 12% $22,166.28 Accept 13.38% Accept Book Value Tax $ 140,000.00 $ 54,000.00