Please look at file. What is highlighted in yellow is what I have difficulty with in EXCEL spreadsheet. Is there a formula in EXCEL that will assist in solving for IRR? The stand formula in EXCEL is =IRR(values) but in this case, I do not have the values required to easily convert to IRR. Thanks

Question 1:

NPV = 0 = $630,000 + 85,000 + $219,800(PVIFA_IRR%,4) + [($219,800 + 49,000 85,000) / (1 + IRR)⁵]

IRR = 28.10%

Question 2:

And the IRR is:

0 = $18,426,000 + $6,071,200(PVIFA_IRR,4) + $266,000 / (1 + IRR)⁴

Using a spreadsheet or financial calculator, we find the IRR is:

IRR = 12.47%

Problem 6-6 Project Evaluation -2-2 Your firm is contemplating the purchase of a new $630,000 computer-based order entry system. The system will be depreciated straight-line to zero over its five-year life. It will be worth $70,000 at the end of that time. You will save $260,000 before taxes per year in order processing costs, and you will be able to reduce working capital by $85,000 (this is a one-time reduction). If the tax rate is 30 percent, what is the IRR for this project? (Do not round intermediate calculations and round your answer to 2 decimal places. (e.g., 32.16)) IRR % 28.10 1% Explanation: First, we will calculate the annual depreciation of the new equipment. It will be: Annual depreciation charge = $630,000 / 5 Annual depreciation charge = $126,000 The aftertax salvage value of the equipment is: Aftertax salvage value = $70,000(1 .30) Aftertax salvage value = $49,000 Using the tax shield approach, the OCF is: OCF = $260,000(1 .30) + .30($126,000) OCF = $219,800 Now we can find the project IRR. There is an unusual feature that is a part of this project. Accepting this project means that we will reduce NWC. This reduction in NWC is a cash inflow at Year 0. This reduction in NWC implies that when the project ends, we will have to increase NWC. So, at the end of the project, we will have a cash outflow to restore the NWC to its level before the project. We also must include the aftertax salvage value at the end of the project. The IRR of the project is: NPV = 0 = $630,000 + 85,000 + $219,800(PVIFAIRR%,4) + [($219,800 + 49,000 85,000) / (1 + IRR)5] IRR = 28.10% Problem 6-21 Calculating NPV and IRR for a Replacement -2-2 A firm is considering an investment in a new machine with a price of $18.16 million to replace its existing machine. The current machine has a book value of $6.16 million and a market value of $4.66 million. The new machine is expected to have a four-year life, and the old machine has four years left in which it can be used. If the firm replaces the old machine with the new machine, it expects to save $6.86 million in operating costs each year over the next four years. Both machines will have no salvage value in four years. If the firm purchases the new machine, it will also need an investment of $266,000 in net working capital. The required return on the investment is 11 percent, and the tax rate is 34 percent. Assume the company uses straight-line depreciation. Requirement 1: a. What is the NPV of the decision to purchase a new machine? (Do not round intermediate calculations and round your answer to 2 decimal places. (e.g., 32.16). Enter your answer in dollars, not millions of dollars, i.e. 1,234,567.) NPV $ 584,790.71 0.1% b. What is the IRR of the decision to purchase a new machine? (Do not round intermediate calculations and round your answer to 2 decimal places. (e.g., 32.16)) IRR % 12.47 Requirement 2: a. What is the NPV of the decision to purchase the old machine? (Do not round intermediate calculations and round your answer to 2 decimal places. (e.g., 32.16). Enter your answer in dollars, not millions of dollars, i.e. 1,234,567. Negative amount should be indicated by a minus sign.) $ NPV -3,545,559.44 0.01% b. What is the IRR of the decision to purchase the old machine? (Do not round intermediate calculations and round your answer to 2 decimal places. (e.g., 32.16). Negative amount should be indicated by a minus sign.) IRR -28.39 1% % rev: 03_04_2015_QC_CS-9633, 09_08_2015_QC_CS-23998 Explanation: Replacement decision analysis is the same as the analysis of two competing projects, in this case, keep the current equipment, or purchase the new equipment. We will consider the purchase of the new machine first. Purchase new machine: The initial cash outlay for the new machine is the cost of the new machine, plus the increased net working capital. So, the initial cash outlay will be: Purchase new machine Net working capital Total -$ 18,160,000 - 266,000 -$ 18,426,000 Next, we can calculate the operating cash flow created if the company purchases the new machine. The saved operating expense is an incremental cash flow. Additionally, the reduced operating expense is a cash inflow, so it should be treated as such in the income statement. The pro forma income statement, and adding depreciation to net income, the annual operating cash flow created by purchasing the new machine will be: Operating $ expense Depreciat ion EBT Taxes 6,860,000 4,540,000 $ 2,320,000 788,800 Net income $ 1,531,200 OCF $ 6,071,200 So, the NPV of purchasing the new machine, including the recovery of the net working capital, is: NPV = $18,426,000 + $6,071,200(PVIFA11%,4) + $266,000 / 1.114 NPV = $584,790.71 And the IRR is: 0 = $18,426,000 + $6,071,200(PVIFAIRR,4) + $266,000 / (1 + IRR)4 Using a spreadsheet or financial calculator, we find the IRR is: IRR = 12.47% Now we can calculate the decision to keep the old machine: Keep old machine: The initial cash outlay for the old machine is the market value of the old machine, including any potential tax consequence. The decision to keep the old machine has an opportunity cost, namely, the company could sell the old machine. Also, if the company sells the old machine at its current value, it will receive a tax benefit. Both of these cash flows need to be included in the analysis. So, the initial cash flow of keeping the old machine will be: Keep machine Taxes -$ Total -$ - 4,660,000 510,000 5,170,000 Next, we can calculate the operating cash flow created if the company keeps the old machine. There are no incremental cash flows from keeping the old machine, but we need to account for the cash flow effects of depreciation. The income statement, adding depreciation to net income to calculate the operating cash flow will be: Depreciation $ 1,540,000 EBT Taxes -$ - 1,540,000 523,600 Net income -$ 1,016,400 $ 523,600 OCF So, the NPV of the decision to keep the old machine will be: NPV = $5,170,000 + $523,600(PVIFA11%,4) NPV = $3,545,559.44 And the IRR is: 0 = $5,170,000 + $523,600(PVIFAIRR,4) Using a spreadsheet or financial calculator, we find the IRR is: IRR = 28.39%