Question

Go Huskies is considering replacing a point-of-sale system that is currently being used.  The old system is fully depreciated but can be used for another 4 years, at which time it would have no salvage value.  Go Huskies can sell the old system for $45,000 on the date that the new system is purchased.  Go Huskies has an effective tax rate of 35%, so the gain on the sale of the old system will be fully taxable.

If the purchase occurs, the new system will be acquired for a cash payment of $139,000.  Because of the increased efficiency of the new system, estimated annual cash savings of $23,000 would be generated during its useful life of 8 years.  Repairs costing $3,500 each are expected in years 3 and 5.  The new system is expected to have a salvage value of $9,000, which would be fully taxable because the MACRS book value would be zero.  

Go Huskies has a minimum required rate of return of 18% and uses MACRS depreciation.  The system falls into the five year asset category with the half-year convention which has the following depreciation rates:

                                    Year                          Rate

                                    1                                 14.29%

                                    2                                 24.49%

                                    3                                 17.49%

                                    4                                 12.49%

                                    5                                 8.93%

                                    6                                 8.92%

                                    7                                 8.93%

                                    8                                 4.46%

Create and submit a printout of an Excel spreadsheet (along with the spreadsheet in formula view using the CTRL ~ function) that will answer the following questions:

1)  What is the net present value of replacing the old system with the new system?  (Use the =NPV function and then subtract the initial cash outflow to obtain the net present value).

2)  What is the internal rate of return for the new system? (Use the =IRR function).

3)  What is the payback period for the new system?

4)  Should Go Huskies purchase the new system?  Why or why not?  You can type your answer in your Excel spreadsheet.

Answer 1

1. Net Present Value @ 18%

Year 6 23,000 Year 7 23,000 Year 8 NPV 23,000 Particulars Cash Savings Repair cost Depreciation Profit on Sale of Old Equipment Increase in Profit before Tax Tax @ 35% Increase in Profit after Tax Add Depreciation Less Purchase of new Equipment Net Cash Flow PVAF @ 18% Present value of Cash Flows Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 23,000 23,000 23,000 23,000 23,000 -3,500 -3,500 | -19,863 -34,041 -24,311 -17,361 -12,413 45,000 45,000 3,137 -11,041 -4,811 5,639 7,087 15,750 1,098 -3,864 -1,684 1,974 2,481 29,250 2,039 -7,177 -3,127 3,665 4,607 19,863 34,041 24,311 17,361 12,413 -1,39,000 -1,09,750 21,902 26,864 21,184 21,026 17,019 1.00 0.85 0.72 0.61 0.52 0.44 -1,09,750 18,561 19,294 12,893 10,845 7,439 -12,399 -12,413 -6,199 9,000 10,601 10,587 25,801 3,710 3,706 9,030 6,891 6,882 16,770 12,399 12,413 6,199 19,290 0.37 7,145 19,294 22,970 0.31 0.27 6,057 6,111 -21,404

As per the above calculation, the NPV of replacing the old system with new system is - $ 21,404, hence the system should not be replaced.

2. Internal Rate of Return

As per the calculation below the the rate at which the NPV would be '0' is 11.21 %

Year 0 Particulars Cash Savings Repair cost Depreciation Profit on Sale of Old Equipment Increase in Profit before Tax Tax @ 35% Increase in Profit after Tax Add Depreciation Less Purchase of new Equipment Net Cash Flow PVAF @ 11.21% of Cash Flows 45,000 45,000 15,750 29,250 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 NPV 23,000 23,000 23,000 23,000 23,000 23,000 23,000 23,000 -3,500 -3,500 -19,863 -34,041 -24,311 -17,361 -12,413 -12,399 -12,413 -6,199 9,000 3,137 -11,041 -4,811 5,639 7,087 10,601 10,587 25,801 1,098 -3,864 -1,684 1,974 2,481 3,710 3,706 9,030 2,039 -7,177 -3,127 3,665 4,607 6,891 6,882 16,770 19,863 34,041 24,311 17,361 12,413 12,399 12,413 6,199 -1,39,000 -1,09,750 1.00 - 1,09,750 21,902 0.90 19,694 26,864 0.81 21,721 21,184 0.73 15,402 21,026 0.65 13,746 17,019 0.59 10,005 19,290 0.53 10,197 19,294 22,970 0.48 0.43 9,171 9,818

3. Payback Period

Year 0 Year 1 23,000 Year 2 23,000 Year 4 23,000 Year 5 23,000 Year 6 23,000 Year 7 23,000 Year 8 NPV 23,000 Year 3 23,000 -3,500 -24,311 -19,863 -34,041 -17,361 -12,413 -12,399 -12,413 Particulars Cash Savings Repair cost Depreciation Profit on Sale of Old Equipment Increase in Profit before Tax Tax @ 35% Increase in Profit after Tax Add Depreciation Less Purchase of new Equipment Net Cash Flow Cumulative Cash Flows 45,000 45,000 15,750 29,250 3,137 1,098 2,039 19,863 -11,041 -3,864 -7,177 34,041 4,811 -1,684 -3,127 24,311 5,639 1,974 3,665 17,361 7,087 2,481 4,607 12,413 10,601 3,710 6,891 12,399 10,587 3,706 6,882 12,413 -6,199 9,000 25,801 9,030 16,770 6,199 -1,39,000 -1,09,750 1,09,750 21,902 26,864 -87,848 -60,984 21,184 21,026 -39,800 -18,773 17,019 -1,754 19,290 17,536 19,294 36,830 22,970 59,800

As can be seen from the above calculation, the cumulative cash flow is zero between year 5 & year 6, hence the actual payback period can be calculated as follows:-

Year 5 + $ 1,754/ $ 19,290

5.09 Years

4. The huskies should not purchase the new system as the IRR for new system is 11.21%, whereas the minimum required rate of return of Huskies is 18%.