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 $55,000 on the date that the new system is purchased. Go Huskies has an effective tax rate of 30%, 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 $145,000. Because of the increased efficiency of the new system, estimated annual cash savings of $28,000 would be generated during its useful life of 8 years. Repairs costing $8,500 each are expected in years 4 and 7. The new system is expected to have a salvage value of $19,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 an Excel spreadsheet that will answer the following questions (note that a spreadsheet created individually will always be unique):

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 (you can round this to the nearest quarter year such as 3.75 years)?

4) Should Go Huskies purchase the new system? Why or why not? Type your answer on the bottom of your Excel spreadsheet.

Answer 1

1)
Cash flow year 0 =  $145000 + $55000 x (1 - 30%)	$     (183,500.00)
Year	Rate	Depreciation	Tax shield dep. Exp
1	14.29%	$          20,720.50	$          6,216.15
2	24.49%	$          35,510.50	$        10,653.15
3	17.49%	$          25,360.50	$          7,608.15
4	12.49%	$          18,110.50	$          5,433.15
5	8.93%	$          12,948.50	$          3,884.55
6	8.92%	$          12,934.00	$          3,880.20
7	8.93%	$          12,948.50	$          3,884.55
8	4.46%	$            6,467.00	$          1,940.10
Year	0	1	2	3	4	5	6	7	8
Initial Cash flow	$     (183,500.00)
Annual Savings		$          28,000.00	$        28,000.00	$          28,000.00	$           28,000.00	$   28,000.00	$   28,000.00	$      28,000.00	$   28,000.00
Less: Repairs					$             8,500.00			$        8,500.00
Less: Depreciation		$          20,720.50	$        35,510.50	$          25,360.50	$           18,110.50	$   12,948.50	$   12,934.00	$        6,467.00	$     1,940.10
Net operating income		$            7,279.50	$         (7,510.50)	$            2,639.50	$             1,389.50	$   15,051.50	$   15,066.00	$      13,033.00	$   26,059.90
Less: Tax		$            2,183.85	$         (2,253.15)	$               791.85	$                416.85	$     4,515.45	$     4,519.80	$        3,909.90	$     7,817.97
Net  income		$            5,095.65	$         (5,257.35)	$            1,847.65	$                972.65	$   10,536.05	$   10,546.20	$        9,123.10	$   18,241.93
Add: Depreciation		$          20,720.50	$        35,510.50	$          25,360.50	$           18,110.50	$   12,948.50	$   12,934.00	$        6,467.00	$     1,940.10
Salvage value after tax									$   13,300.00
Net Cash Flow	$     (183,500.00)	$          25,816.15	$        30,253.15	$          27,208.15	$           19,083.15	$   23,484.55	$   23,480.20	$      15,590.10	$   33,482.03
PV @ 18%	1.0000	0.8475	0.7182	0.6086	0.5158	0.4371	0.3704	0.3139	0.2660
Present Value	$     (183,500.00)	$          21,878.09	$        21,727.34	$          16,559.72	$             9,842.88	$   10,265.31	$     8,697.81	$        4,894.12	$     8,907.50
NPV	$       (80,727.23)
2) IRR	1.797%
3) Pay Back period
Year	0	1	2	3	4	5	6	7	8
Net Cash Flow	$     (183,500.00)	$          25,816.15	$        30,253.15	$          27,208.15	$           19,083.15	$   23,484.55	$   23,480.20	$      15,590.10	$   33,482.03
Cummulative NCF	$     (183,500.00)	$       (157,683.85)	$     (127,430.70)	$      (100,222.55)	$          (81,139.40)	$ (57,654.85)	$ (34,174.65)	$     (18,584.55)	$   14,897.48
Pay Back period  = 7 years + (18584.55/33482.03)	7.56	years
4) Go Huskies should not purchase the new system because NPV is negative and IRR is less than the cost of capital.