Question

A company is evaluating the purchase of a machine to improve product quality and output levels. The new machine would cost $1.6 million and would be depreciated for tax purposes using the straight-line method over an estimated six-year life to its expected salvage value of $100,000. The new machine would require an addition of $70,000 to working capital at the beginning of the project, which will of course be returned to the firm at the end of the project. In each year of the machine's life, the machine would increase the company's pre-tax cash receipts by $400,000. During each of the six years, additional cash operating costs would increase by $15,000. In addition, at the end of the 4th year, a major repair of the machine costing $40,000 (pre-tax) would be required. The company has a 8% overall cost of capital and is in the 35% marginal tax bracket.

Create an Excel spreadsheet to organize your answers to the following problem.

Part 1: Prepare a Cash Flow Spreadsheet that identifies the incremental cash flows for each year of the machine's life.

Part 2: Calculate the investment's net present value (NPV).

Part 3: Calculate the investment's internal rate of return (IRR).

Part 4: Should the company purchase the machine? Why or why not?

Answer 1

Please find the solution in given screen shots,

Amount in $

Part 1 Incremental Cash Flows Refer 400,000 15,000 400,000 15,000 400,000 15,000 400,000 15,000 6 400,000 15,000 WN-1 Year Cash Receipt Less: Operating Cost Less: Repair Cost for Machine Less: Depreciation EBIT Less: Tax @ 35% EAT Add: Depreciation Net Incremental Cash Flow 250,000 135,000 47,250 87,750 250,000 337,750 250,000 135,000 47,250 87,750 250,000 337,750 250,000 135,000 47,250 87,750 250,000 337,750 400,000 15,000 40,000 250,000 135,000 47,250 87,750 250,000 337,750 250,000 135,000 47,250 87,750 250,000 337,750 250,000 135,000 47,250 87,750 250,000 337,750 WN-1 Machine Cost Less: Salvage Value Depreciable Value Life - Years Depreciation/Year 1,600,000 100,000 1,500,000 250,000

Part-2 Investment's Net Present Value . 2 3 4 5 6 NPV Refer 0 -1,600,000 -70,000 Year Initial Outlay Additional Working Capital Recovery of Working Capital Recovery of Salvage Value Incremental Cash Flow Net Cash flow Discount Factor @8% Discounted Cash Flow Part-1 337,7501 337,750 0.9259 312,731 -1,670,000 1.0000 -1,670,000 337,7501 337,750 0.8573 289,566 337,750 337,750 0.7938 268,117 337,750 337,750 0.7350 248,256 70,000 100,000 337,750 507,750 0.6302 319,9691 337,750 337,750 0.6806 229,867 -1,494 Part-3 Investment's Internal Rate of Return In Part 2 we see that investment is having negative cash flow when discounted at 8% which means it has return less than of 8%. We will use trial and error method to understand what can be Investment's IRR and will prove the same with the help of Excel Formula Since the Return is less than of 8% we will consider 7% rate to arrive at the NPV | 3 | 4 | 5 | 6 | NPV Referi 0 -1,600,000 -70,000 0.07 Year Initial Outlay Additional Working Capital Recovery of Working Capital Recovery of Salvage Value Incremental Cash Flow Net Cash flow Discount Factor @7% Discounted Cash Flow Part-1 337,750 337,750 0.9346 315,654 -1,670,000 1.0000 -1,670,000 337,7501 337,750 0.8734 295,004 337,750 337,750 0.8163 275,705 337,750 337,7501 0.7629 257,668 70,000 100,000 337,750 507,750 0.6663 338,335 337,750 337,750 0.7130 240,811 53,177)

Rate of Return i.e. IRR under trial & error method is derived by extrapolating two NPVs that have been calculated using two random discount rates At 7% we find positive NPV which means the IRR is between 7 to 8%, we have NPVs at 7% & 8%. Both the rates and NPVs can be used for extrapolating to arrive at the IRR R1 NPV 1 R2 NPV 2 8% Discounting Factor as per Part 2 -1,493.56 NPV as per Part 2 7% New Discounting Factor as above - in Part-3 53,176.95 NPV as above - in Part - 3 IRR = R1 + NPV1 X (R2-R1)/(NPV1 - NPV2) R2-R1 NPV1 - NPV2 NPV1 X (R2-R1) NPV1 X (R2-R1) / (NPV1 - NPV2) IRR (putting the numbers in Formula) -1% -54,670.51 14.94 -0.00027319 7.97% Proof with Excel IRR =IRR(values) 7.97% Part - 4 Should Company purchase the Machine? No, because company's cost of capital is higher than investment's internal rate of return No, because investment's net present value is negative If NPV is zero- the machine can be purchased, at zero cost of capital and IRR will be same.