No need to graph but can you please show the formulas?

You are considering investing in a new gold mine in South Africa. Gold in South Africa is buried very deep, so the mine will require an initial investment of $250 million. Once this investment is made, the mine is expected to produce revenues of $30 million per year for the next 20 years. It will cost $10 million per year to operate the mine. After 20 years, the gold will be depleted. The mine must then be stabilized on an ongoing basis, which will cost $5 million per year in perpetuity. Calculate the IRR of this investment. (Hint: Plot the NPV as a function of the discount rate.) 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. Cost of new mine (million) Annual cash flows (million) Number of periods (years) Operation costs (million) Stabilization costs (million) $250 $30 20 $10 $5 Cost of Capital 1% NPV (million 2% 3% 4% 5% 6% 7% 8% 9% 10% 11% 12% 13% 14% 15% 16% 17% 18% 19% 20% How many IRR are there? Can you trust the IRR rule? Take opportunity (Yes/No) Requirements 1. Start Excel - completed. 2. In cells E14:E33, by using cell references and the function PV, calculate the NPV of the investment opportunity for cost of capital 1% to 20%, respectively (20 pt.). Notes: For the PV function, use cell references to calculate the difference between the annual costs and operation costs. Use this expression as the [pmt] argument. 3. In cells C35:F46, insert a Line Chart to plot the NPV profile of the investment opportunity. Inserting a Chart On the Insert tab, in the Charts group, click the arrow next to Insert Line or Area Chart and choose Line Chart. Selecting Data Series Then in Select Data Source window, delete any series created automatically. Add new series for the NPV profile of the investment opportunity using cells E14:E33 for the series values. Do not name the series. Edit Chart Elements Then in Select Data Source window, edit Horizontal Axis Labels using cells D14:D33. Add a chart title and choose the Above Chart option. Replace Chart Title with NPV Profile. Add axis titles. Replace Axis Title for the horizontal axis with Cost of Capital and Axis Title for the vertical axis with NPV. (3 pt.) 4 In cell E48, select Zero, One, Two, or Three depending on the number of IRRs that you see on the NPV profile (1 pt.). 5. In cell E49, select Yes or No depending on whether you can trust the IRR rule in this case (1 pt.). 6. In cell E50, select Yes or No depending on whether you would take the investment opportunity based on the IRR rule (1 pt.). 7. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. You are considering investing in a new gold mine in South Africa. Gold in South Africa is buried very deep, so the mine will require an initial investment of $250 million. Once this investment is made, the mine is expected to produce revenues of $30 million per year for the next 20 years. It will cost $10 million per year to operate the mine. After 20 years, the gold will be depleted. The mine must then be stabilized on an ongoing basis, which will cost $5 million per year in perpetuity. Calculate the IRR of this investment. (Hint: Plot the NPV as a function of the discount rate.) 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. Cost of new mine (million) Annual cash flows (million) Number of periods (years) Operation costs (million) Stabilization costs (million) $250 $30 20 $10 $5 Cost of Capital 1% NPV (million 2% 3% 4% 5% 6% 7% 8% 9% 10% 11% 12% 13% 14% 15% 16% 17% 18% 19% 20% How many IRR are there? Can you trust the IRR rule? Take opportunity (Yes/No) Requirements 1. Start Excel - completed. 2. In cells E14:E33, by using cell references and the function PV, calculate the NPV of the investment opportunity for cost of capital 1% to 20%, respectively (20 pt.). Notes: For the PV function, use cell references to calculate the difference between the annual costs and operation costs. Use this expression as the [pmt] argument. 3. In cells C35:F46, insert a Line Chart to plot the NPV profile of the investment opportunity. Inserting a Chart On the Insert tab, in the Charts group, click the arrow next to Insert Line or Area Chart and choose Line Chart. Selecting Data Series Then in Select Data Source window, delete any series created automatically. Add new series for the NPV profile of the investment opportunity using cells E14:E33 for the series values. Do not name the series. Edit Chart Elements Then in Select Data Source window, edit Horizontal Axis Labels using cells D14:D33. Add a chart title and choose the Above Chart option. Replace Chart Title with NPV Profile. Add axis titles. Replace Axis Title for the horizontal axis with Cost of Capital and Axis Title for the vertical axis with NPV. (3 pt.) 4 In cell E48, select Zero, One, Two, or Three depending on the number of IRRs that you see on the NPV profile (1 pt.). 5. In cell E49, select Yes or No depending on whether you can trust the IRR rule in this case (1 pt.). 6. In cell E50, select Yes or No depending on whether you would take the investment opportunity based on the IRR rule (1 pt.). 7. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed