1 2 Spreadsheet Exercise: Chapter 10 The Drillago Company is involved in searching for locations in which to drill for oil. The firm's current project requires an initial investment of $15 million and has an estimated life of 10 years. The expected future cash inflows for the project are as shown in the following table. 4 5 7 8 9 10 Year 1 2 3 4 5 6 7 8 9 10 Cash Inflows $ 600,000 1,000,000 1,000,000 2,000,000 3,000,000 3,500,000 4,000,000 6,000,000 8,000,000 12,000,000 11 12 13 14 15 16 The firm's current cost of capital is 13%. 17 18 19 20 21 22 To Do Create a spreadsheet to answer the following questions: a. Calculate the project's net present valve (NPV). Is the project acceptable under the NPV technique? Explain. b. Calculate the project's internal rate of return (IRR). Is the project acceptable under the IRR technique? Explain. c. In this case, did the two methods produce the same results? Generally, is there a preference between the NPV and IRR techniques? Explain. d. Calculate the payback period for the project. If the firm usually accepts projects that have payback periods between 1 and 7 years, is this project acceptable? 23 24 25 26 Solution 27 28 29 Estimated life (years) Cost-of-capital (1) Initial investment 10 13% $ 15,000,000 30 31 32 35 36 Year 0 1 2 3 4 5 6 7 37 Cash Flow $-15,000,000 600,000 1,000,000 1,000,000 2,000,000 3,000,000 500,000 4,000,000 6,000,000 8,000,000 12,000,000 38 40 O 00 41 42 9 10 43 44 45 a. Calculate the project's net present valve (NPV). Is the project acceptable under the NPV technique? Explain. 46 47 NVP a. Calculate the project's net present valve (NPV). Is the project acceptable under the NPV technique? Explain. NVP the project since the NPV is than zero. b. Calculate the project's internal rate of return (IRR). Is the project acceptable under the IRR technique? Explain. IRR the project since the IRR is the cost of capital (13%). c. In this case, did the two methods produce the same results? Generally, is there a preference between the NPV and IRR techniques? Explain. When the decision is simply accept or reject then the NPV and IRR method will always produce the same results. However, when ranking several projects the NPV method is preferred over the IRR method because the IRR method assumes the cashflows are reinvested at the IRR not the required cost of capital. Is the statement above true or false? The statement is d. Calculate the payback period for the project. If the firm usually accepts projects that have payback periods between 1 and 7 years, is this project acceptable? Payback $ -15,000,000 Year 0 1 2 3 4 5 6 7 8 9 10 Payback period (years) Since the payback period is 7 years the project is Points 1 1 1 1 1 Requirements 1 In cell D47, by using cell references to the given data and the function NPV, calculate the net present valve of the project. 2 In cell C49, type either Accept or Reject depending on whether you would take on the project or not, based on the NPV rule. 3 In cell F49, type either > or or or