Problem 8-20 Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. You have 3 projects with the following cash flows: 0 1 2 3 4 Year Project 1 Project 2 Project 3 -$150 -$825 $20 $20 SO $40 $40 $0 $60 $60 $7,000 S80 $80 -$6,500 -$245 a. For which of these projects is the IRR rule reliable? b. Estimate the IRR for each project (to the nearest 1%). c. What is the NPV of each project if the cost of capital is 5%? 20% 50%? a. For which of these projects is the IRR rule reliable? The IRR rule is reliable for b. Estimate the IRR for each project (to the nearest 1%). IRR Project 1 Project 2 Project 3 c. What is the NPV of each project if the cost of capital is 5%? 20%? 50%? 5% 20% 50% Project 1 Project 2 Project 3 Requirements 1. Start Excel - completed. 2. In cell E15, select from the dropdown for which of the projects the IRR rule is reliable (1 pt.). 3. In cell D20, by using cell references and the function IRR, calculate the internal rate of return for Project 1 (1 pt.). Note: Do not enter any value for the Guess argument of the function IRR. 4. To calculate the IRR for projects 2 and 3, copy cell D20 and paste it onto cells D21:D22 (1 pt.). 5. In cell D27, by using cell references and the function NPV, calculate the net present value of Project 1 if the cost of capital is 5% (1 pt.). 6. To calculate the net present values of projects 2 and 3 if the cost of capital is 5%, copy cell D27 and paste it onto cells D28:D29. To calculate the net present values of each project if the cost of capital is 20% and 50% respectively, copy cell D27 and paste it onto cells E27:F29 (1 pt.). 7. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. Problem 8-20 Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. You have 3 projects with the following cash flows: 0 1 2 3 4 Year Project 1 Project 2 Project 3 -$150 -$825 $20 $20 SO $40 $40 $0 $60 $60 $7,000 S80 $80 -$6,500 -$245 a. For which of these projects is the IRR rule reliable? b. Estimate the IRR for each project (to the nearest 1%). c. What is the NPV of each project if the cost of capital is 5%? 20% 50%? a. For which of these projects is the IRR rule reliable? The IRR rule is reliable for b. Estimate the IRR for each project (to the nearest 1%). IRR Project 1 Project 2 Project 3 c. What is the NPV of each project if the cost of capital is 5%? 20%? 50%? 5% 20% 50% Project 1 Project 2 Project 3 Requirements 1. Start Excel - completed. 2. In cell E15, select from the dropdown for which of the projects the IRR rule is reliable (1 pt.). 3. In cell D20, by using cell references and the function IRR, calculate the internal rate of return for Project 1 (1 pt.). Note: Do not enter any value for the Guess argument of the function IRR. 4. To calculate the IRR for projects 2 and 3, copy cell D20 and paste it onto cells D21:D22 (1 pt.). 5. In cell D27, by using cell references and the function NPV, calculate the net present value of Project 1 if the cost of capital is 5% (1 pt.). 6. To calculate the net present values of projects 2 and 3 if the cost of capital is 5%, copy cell D27 and paste it onto cells D28:D29. To calculate the net present values of each project if the cost of capital is 20% and 50% respectively, copy cell D27 and paste it onto cells E27:F29 (1 pt.). 7. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed