Question

4. Construct an Excel spreadsheet to answer the questions in Problem 8-43 as given below. 8-43 Calculating NPV and IRR Using Excel. Rouse Production Company would like to further automate its production process by purchasing production equipment for $660,000. The equipment is expected to have a useful life of 8 years, and will be sold at the end of 8 years for $40,000. The equipment requires significant maintenance work at an annual cost of $75,000. Labor and material cost savings, shown in the table, are also expected to be significant. Year 1 $160,000 Year 2 $190,000 Year 3 $200,000 Year 4 $240,000 Year 5 $280,000 Year 6 $220,000 Year 7 $180,000 Year 8 $155,000 The company's required rate of return is 11 percent. Required: a. Use Excel to calculate the net present value and internal rate of return in a format similar to the Computer Application spreadsheet shown in the chapter Should the company purchase the production equipment? Explain

Answer 1

Net Present Value Year Equipment Cost Annual Cost Saving Net CashFlow Present Value Factor@11% Present Value Of CashFlow d=(a+b+c) -$6,60,000.00 f-d*e a C e -$6,60,000.00 $0.00 $0.00 $1.00 -$6,60,000.00 $1,08,120.00 $0.00 -$40,000.00 $1,60,000.00 $1,20,000.00 $0.90 $0.81 1 $0.00 -$40,000.00 $1,90,000.00 $1,50,000.00 $1,21,800.00 2 $0.00 -$40,000.00 $2,00,000.00 $1,60,000.00 $0.73 $1,16,960.00 $0.00 -$40,000.00 $2,40,000.00 $2,00,000.00 $0.66 $1,31,800.00 $1,42,320.00 $0.00 -$40,000.00 $2,80,000.00 $2,40,000.00 $0.59 5 $0.00 -$40,000.00 $2,20,000.00 $1,80,000.00 $0.55 $98,100.00 6 $0.00 -$40,000.00 $1,80,000.00 $1,40,000.00 $0.48 $67,480.00 $49,910.00 $1,76,490.00 $0.00 -$40,000.00 $1,55,000.00 $1,15,000.00 $0.43 8 Net Present Value LO 00

f IRR(F23:F31) F32 A. C Е F 19 Internal Rate of Return 20 Year Equipment Cost Annual Cost Saving Net CashFlow 21 22 d a+b+c) a C -$6,60,000.00 -$6,60,000.00 -$40,000.00 $1,60,000.00 $1,20,000.00 $0.00 23 $0.00 24 $0.00 -$40,000.00 $1,90,000.00 $1,50,000.00 25 2 $0.00 -$40,000.00 $2,00,000.00 $1,60,000.00 26 3 $0.00 -$40,000.00 $2,40,000.00 $2,00,000.00 27 4 $0.00 -$40,000.00 $2,80,000.00 $2,40,000.00 28 $0.00 -$40,000.00 $2,20,000.00 $1,80,000.00 29 6 $0.00 -$40,000.00 $1,80,000.00 $1,40,000.00 30 7 -$40,000.00 $1,55,000.00 $1,15,000.00 $0.00 31. 8 17.82% 32 IRR 33 34 As The NPV is positive and IRR is greater than Required rate of Return the Company should buy the equipment 35 36