PLEASE COMPLETE IN EXCEL AND SHOW ALL WORK

12-7 NPV Your division is considering two investment projects, each of which requires an up-front expenditure of $15 million. You estimate that the investments will produce the following net cash flows: Year Project A Project B 1 $5,000,000 $20,000,000 2 10,000,000 10,000,000 20,000,000 6,000,000 a. What are the two projects' net present values, assuming the cost of capital is 9? 10 ? 15? h. What are the two projects IRRs at these same costs of capital? QUESTION 1 Chapter 12 - Problem 7: Change the value of the cost of project A in cell C5 from $15.0 to $18.3 (enter it as a negative value to reflect it as a cost). Record the new calculated value of the internal rate of return visible in cell C16 in the space provided below. [Format the value as a percentage with one decimal place and no percentage sign. For example, 12.3 for 12.396] F G A B D E 1 Problem 12-7 2 3 INPUT ($ in millions) 4 Year Project A Project B 5 0 $ (18.3) 6 1 5,000,000.0 20,000,000.0 7 2 10,000,000.0 10,000,000.0 8 3 20,000,000.0 6,000,000.0 9 10 Cost of Capital 5.0% Set this value initially to five percent 11 12 13 14 Project B OUTPUT ($ in millions) Method Project A NPV $ 31,108,933.2 IRR 27322504.4% 15 16 17 18 19 DATA TABLE 1 - Net Present Value (NPV) Project A Project B 20 21 22 Create one-way data tables to show the impact of changing the assumed cost of capital on the NPV (first table) and IRR (second table) of both Project A and Project B 5% 23 10% 15% 24 25 26 27 28 DATA TABLE 2 - Internal Rate of Return (IRR) Project A Project B 29 30 5% 10% 15% 31 32 12-7 NPV Your division is considering two investment projects, each of which requires an up-front expenditure of $15 million. You estimate that the investments will produce the following net cash flows: Year Project A Project B 1 $5,000,000 $20,000,000 2 10,000,000 10,000,000 20,000,000 6,000,000 a. What are the two projects' net present values, assuming the cost of capital is 9? 10 ? 15? h. What are the two projects IRRs at these same costs of capital? QUESTION 1 Chapter 12 - Problem 7: Change the value of the cost of project A in cell C5 from $15.0 to $18.3 (enter it as a negative value to reflect it as a cost). Record the new calculated value of the internal rate of return visible in cell C16 in the space provided below. [Format the value as a percentage with one decimal place and no percentage sign. For example, 12.3 for 12.396] F G A B D E 1 Problem 12-7 2 3 INPUT ($ in millions) 4 Year Project A Project B 5 0 $ (18.3) 6 1 5,000,000.0 20,000,000.0 7 2 10,000,000.0 10,000,000.0 8 3 20,000,000.0 6,000,000.0 9 10 Cost of Capital 5.0% Set this value initially to five percent 11 12 13 14 Project B OUTPUT ($ in millions) Method Project A NPV $ 31,108,933.2 IRR 27322504.4% 15 16 17 18 19 DATA TABLE 1 - Net Present Value (NPV) Project A Project B 20 21 22 Create one-way data tables to show the impact of changing the assumed cost of capital on the NPV (first table) and IRR (second table) of both Project A and Project B 5% 23 10% 15% 24 25 26 27 28 DATA TABLE 2 - Internal Rate of Return (IRR) Project A Project B 29 30 5% 10% 15% 31 32