IP Decision Making and Capital Budgeting

profilejugxyaev
IP.xlsx
Home>Business & Finance homework help>Accounting homework help>IP Decision Making and Capital Budgeting

Sheet1

Solution:
Initial investment $ 2,000,000.00
Savings every year $ 500,000.00
Time $ 10.00
Cost of Capital $ 0.14
$ - 0 $ 1.00 $ 2.00 $ 3.00 $ 4.00 $ 5.00 $ 6.00 $ 7.00 $ 8.00 $ 9.00 $ 10.00
Initial Investment $ (2,000,000.00)
Savings $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00
Net Cash flows $ (2,000,000.00) $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00
Cumulative Cash flows $ (2,000,000.00) $ (1,500,000.00) $ (1,000,000.00) $ (500,000.00) $ - 0 $ 500,000.00 $ 1,000,000.00 $ 1,500,000.00 $ 2,000,000.00 $ 2,500,000.00 $ 3,000,000.00
PVF @ 14% $ 1.00 $ 0.88 $ 0.77 $ 0.67 $ 0.59 $ 0.52 $ 0.46 $ 0.40 $ 0.35 $ 0.31 $ 0.27
Present value $ (2,000,000.00) $ 438,596.49 $ 384,733.76 $ 337,485.76 $ 296,040.14 $ 259,684.33 $ 227,793.27 $ 199,818.66 $ 175,279.53 $ 153,753.97 $ 134,871.90
Calculating NPV
NPV = PV of Cash flows - initial Investment
Present value of cash flows = $ 2,608,057.82
Initial investment = $ (2,000,000.00)
NPV = $ 608,057.82
Calculating NPV using excel formula
NPV = $ 608,057.82
Calculating IRR
IRR = $ 0.21
Calculating Payback period
Payback period = $ 4.00 years
With cost of capital of 25%
Initial investment $ 2,000,000.00
Savings every year $ 500,000.00
Time $ 10.00
Cost of Capital $ 0.25
$ - 0 $ 1.00 $ 2.00 $ 3.00 $ 4.00 $ 5.00 $ 6.00 $ 7.00 $ 8.00 $ 9.00 $ 10.00
Initial Investment $ (2,000,000.00)
Savings $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00
Net Cash flows $ (2,000,000.00) $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00
Cumulative Cash flows $ (2,000,000.00) $ (1,500,000.00) $ (1,000,000.00) $ (500,000.00) $ - 0 $ 500,000.00 $ 1,000,000.00 $ 1,500,000.00 $ 2,000,000.00 $ 2,500,000.00 $ 3,000,000.00
PVF @ 25% $ 1.00 $ 0.80 $ 0.64 $ 0.51 $ 0.41 $ 0.33 $ 0.26 $ 0.21 $ 0.17 $ 0.13 $ 0.11
Present value $ (2,000,000.00) $ 400,000.00 $ 320,000.00 $ 256,000.00 $ 204,800.00 $ 163,840.00 $ 131,072.00 $ 104,857.60 $ 83,886.08 $ 67,108.86 $ 53,687.09
Calculating NPV
NPV = PV of Cash flows - initial Investment
Present value of cash flows = $ 1,785,251.64
Initial investment = $ (2,000,000.00)
NPV = $ (214,748.36)
Calculating NPV using excel formula
NPV = $ (214,748.36)
Calculating IRR
IRR = $ 0.21
Calculating Payback period
Payback period = $ 4.00 years
Least amount of investment
Initial investment $ 2,000,000.00
Time $ 10.00
Cost of Capital $ 0.14
$ - 0 $ 1.00 $ 2.00 $ 3.00 $ 4.00 $ 5.00 $ 6.00 $ 7.00 $ 8.00 $ 9.00 $ 10.00
Initial Investment $ (2,000,000.00)
Savings $ 383,427.08 $ 383,427.08 $ 383,427.08 $ 383,427.08 $ 383,427.08 $ 383,427.08 $ 383,427.08 $ 383,427.08 $ 383,427.08 $ 383,427.08
Net Cash flows $ (2,000,000.00) $ 383,427.08 $ 383,427.08 $ 383,427.08 $ 383,427.08 $ 383,427.08 $ 383,427.08 $ 383,427.08 $ 383,427.08 $ 383,427.08 $ 383,427.08
Cumulative Cash flows $ (2,000,000.00) $ (1,616,572.92) $ (1,233,145.84) $ (849,718.75) $ (466,291.67) $ (82,864.59) $ 300,562.49 $ 683,989.57 $ 1,067,416.65 $ 1,450,843.74 $ 1,834,270.82
PVF @ 14% $ 1.00 $ 0.88 $ 0.77 $ 0.67 $ 0.59 $ 0.52 $ 0.46 $ 0.40 $ 0.35 $ 0.31 $ 0.27
Present value $ (2,000,000.00) $ 336,339.55 $ 295,034.69 $ 258,802.36 $ 227,019.61 $ 199,140.01 $ 174,684.22 $ 153,231.77 $ 134,413.84 $ 117,906.87 $ 103,427.08
Calculating NPV
NPV = PV of Cash flows - initial Investment
Present value of cash flows = $ 2,000,000.00
Initial investment = $ (2,000,000.00)
NPV = $ - 0
Calculating NPV using excel formula
NPV = $ - 0
Calculating IRR
IRR = $ 0.14
Calculating Payback period
Payback period = $ 4.00 years
The least savings required = $ 383,427.08
Most payment
Initial investment $ 2,000,000.00
Savings every year $ 500,000.00
Time $ 10.00
Cost of Capital $ 0.14
$ - 0 $ 1.00 $ 2.00 $ 3.00 $ 4.00 $ 5.00 $ 6.00 $ 7.00 $ 8.00 $ 9.00 $ 10.00
Initial Investment $ (2,608,057.82)
Savings $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00
Net Cash flows $ (2,608,057.82) $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00 $ 500,000.00
Cumulative Cash flows $ (2,608,057.82) $ (2,108,057.82) $ (1,608,057.82) $ (1,108,057.82) $ (608,057.82) $ (108,057.82) $ 391,942.18 $ 891,942.18 $ 1,391,942.18 $ 1,891,942.18 $ 2,391,942.18
PVF @ 14% $ 1.00 $ 0.88 $ 0.77 $ 0.67 $ 0.59 $ 0.52 $ 0.46 $ 0.40 $ 0.35 $ 0.31 $ 0.27
Present value $ (2,608,057.82) $ 438,596.49 $ 384,733.76 $ 337,485.76 $ 296,040.14 $ 259,684.33 $ 227,793.27 $ 199,818.66 $ 175,279.53 $ 153,753.97 $ 134,871.90
Calculating NPV
NPV = PV of Cash flows - initial Investment
Present value of cash flows = $ 2,608,057.82
Initial investment = $ (2,608,057.82)
NPV = $ - 0
Calculating NPV using excel formula
NPV = $ - 0
Maximum payment $ 2,608,057.82