Question

1 Approved Answer

Posted on Aug 25, 2024

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

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. image text in transcribed

AutoSave Of H elva M3 Assignment CapitalBudgeting - Saved Search Destiny Allen DA - O X Page Layout Formulas Data Review View Help Share Comments Calibri 11 General Normal Neutral le Home Insert X Cut - [ Copy ste & Format Painter Clipboard A A A - === E # Wrap Text 3 Merge & Center - Bad Check Cell Good Explanatory... AutoSum Fill Sort & Clear Filter BIU $ % 8-98 Input Insert Delete Format Ideas Conditional Format as Calculation Formatting Table Sensitivity Find & Select Font Alignment Number Styles Cells Editing Ideas Sensitivity B C D E F G H I J K L M N O P Q R S T U V W Inputs Cost of Townhome $ Rent $ Rent growth Property tax $ Property tax growth Insurance $ Misc exp $ Income tax rate Depreciation straigh Resell value of towr $ Capital ains tax 220,000.00 24,000.00 3% 3,000.00 3% 2,000.00 1,000.00 24% 27.5 500,000.00 15% $ (220,000.00) Year Initial CF Revenue Propertytax Insurance Misc exp Depreciation Reportable Income Tax Net Income Operating CFS Terminal CFS $ 24,000.00 $ 3,000.00 $ 2,000.00 $ 1,000.00 $ 8,000.00 $ 10,000.00 $ 2,400.00 $ 7,600.00 $ 15,600.00 $ 24,720.00 $ 3,090.00 $ 2,000.00 $ 1,000.00 $ 8,000.00 $ 10,630.00 $ 2,551.20 $ 8,078.80 $ 16,078.80 $ 25,461.60 $ 3,182.70 $ 2,000.00 $ 1,000.00 $ 8,000.00 $ 11,278.90 $ 2,706.94 $ 8,571.96 $ 16,571.96 $26,225.45 $ 3,278.18 $ 2,000.00 $ 1,000.00 $ 8,000.00 $ 11,947.27 $ 2,867.34 $ 9,079.92 $17,079.92 $27,012.21 $ 3,376.53 $ 2,000.00 $ 1,000.00 $ 8,000.00 $12,635.69 $ 3,032.56 $ 9,603.12 $17,603.12 $27,822.58 $28,657.26 $ 3,477.82 $ 3,582.16 $ 2,000.00 $ 2,000.00 $ 1,000.00 $ 1,000.00 $ 8,000.00 $ 8,000.00 $13,344.76 $ 14,075.10 $ 3,202.74 $ 3,378.02 $10,142.01 $10,697.07 $18,142.01 $18,697.07 $29,516.97 $ 3,689.62 $ 2,000.00 $ 1,000.00 $ 8,000.00 $ 14,827.35 $ 3,558.56 $11,268.79 $19,268.79 $30,402.48 $ 3,800.31 $ 2,000.00 $ 1,000.00 $ 8,000.00 $ 15,602.17 $ 3,744.52 $11,857.65 $19,857.65 $ 31,314.56 $ 3,914.32 $ 2,000.00 $ 1,000.00 $ 8,000.00 $ 16,400.24 $ 3,936.06 $ 12,464.18 $ 20,464.18 $446,000.00 Book Value $ 140,000.00 $ 54,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 AutoSave Of H elva M3 Assignment CapitalBudgeting - Saved Search Destiny Allen DA - O X Page Layout Formulas Data Review View Help Share Comments Calibri 11 General Normal Neutral le Home Insert X Cut - [ Copy ste & Format Painter Clipboard A A A - === E # Wrap Text 3 Merge & Center - Bad Check Cell Good Explanatory... AutoSum Fill Sort & Clear Filter BIU $ % 8-98 Input Insert Delete Format Ideas Conditional Format as Calculation Formatting Table Sensitivity Find & Select Font Alignment Number Styles Cells Editing Ideas Sensitivity B C D E F G H I J K L M N O P Q R S T U V W Inputs Cost of Townhome $ Rent $ Rent growth Property tax $ Property tax growth Insurance $ Misc exp $ Income tax rate Depreciation straigh Resell value of towr $ Capital ains tax 220,000.00 24,000.00 3% 3,000.00 3% 2,000.00 1,000.00 24% 27.5 500,000.00 15% $ (220,000.00) Year Initial CF Revenue Propertytax Insurance Misc exp Depreciation Reportable Income Tax Net Income Operating CFS Terminal CFS $ 24,000.00 $ 3,000.00 $ 2,000.00 $ 1,000.00 $ 8,000.00 $ 10,000.00 $ 2,400.00 $ 7,600.00 $ 15,600.00 $ 24,720.00 $ 3,090.00 $ 2,000.00 $ 1,000.00 $ 8,000.00 $ 10,630.00 $ 2,551.20 $ 8,078.80 $ 16,078.80 $ 25,461.60 $ 3,182.70 $ 2,000.00 $ 1,000.00 $ 8,000.00 $ 11,278.90 $ 2,706.94 $ 8,571.96 $ 16,571.96 $26,225.45 $ 3,278.18 $ 2,000.00 $ 1,000.00 $ 8,000.00 $ 11,947.27 $ 2,867.34 $ 9,079.92 $17,079.92 $27,012.21 $ 3,376.53 $ 2,000.00 $ 1,000.00 $ 8,000.00 $12,635.69 $ 3,032.56 $ 9,603.12 $17,603.12 $27,822.58 $28,657.26 $ 3,477.82 $ 3,582.16 $ 2,000.00 $ 2,000.00 $ 1,000.00 $ 1,000.00 $ 8,000.00 $ 8,000.00 $13,344.76 $ 14,075.10 $ 3,202.74 $ 3,378.02 $10,142.01 $10,697.07 $18,142.01 $18,697.07 $29,516.97 $ 3,689.62 $ 2,000.00 $ 1,000.00 $ 8,000.00 $ 14,827.35 $ 3,558.56 $11,268.79 $19,268.79 $30,402.48 $ 3,800.31 $ 2,000.00 $ 1,000.00 $ 8,000.00 $ 15,602.17 $ 3,744.52 $11,857.65 $19,857.65 $ 31,314.56 $ 3,914.32 $ 2,000.00 $ 1,000.00 $ 8,000.00 $ 16,400.24 $ 3,936.06 $ 12,464.18 $ 20,464.18 $446,000.00 Book Value $ 140,000.00 $ 54,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