Question

You are is considering replacing a five-year-old machine that originally cost $50,000. It was being depreciated using straight-line to an expected salvage value of zero over its original 10-year life and could now be sold for $40,000. The replacement machine would cost $190,000 and have a five-year expected life. It would be depreciated using the MACRS 5-year class life. The actual expected salvage value of this machine after five years is $20,000. The new machine is expected to operate much more efficiently, saving $6,000 per year in energy costs. In addition, it will eliminate one salaried position saving another $54,000 annually. The firm’s marginal tax rate is 35% and the WACC is 7.5%.

a.   Set up an operating cash flow statement, and calculate the payback, discounted payback, NPV, IRR, and MIRR of the replacement project. Should the project be accepted?

Use Excel for this Problem and display cell reference

Answer 1

The final answers are mentioned here. Detailed workings follow after the answers.

Payback period (years)

2.92

Discounted payback period (years)	4.51
NPV	$ 51,696
IRR	19.56%
MIRR	13.86%

Yes, the project should be accepted, as NPV is positive, IRR and MIRR > WACC and payback period is < life of the project

Detailed workings:

I will first show the screenshot of my excel model in "Show formulas" mode so that you can see the formula in all the cells. Just after that I have produced the screenshot of the excel with answers in the cell. This will help you correlate the excel formula with the output.

Cells highlighted in yellow contain the answers.

Formula Mode:

Output Mode

V X Y AA AF 0 2 Year - achine 94 Original cost 50000 95 Original life 10 96 Annual depreciation -w94/W95 97 Accumulated depreciation so far -W96 .5 98 Book value -W94-W97 e value TO Gain -w99-W98 01 Tax on gain 0.35 12 Post tax salvage value -W99- W100 W101 -190000 103 Cost of new machine 04 Initial cash flows W102+ W103 16 MACRS 5 vear class schedule 107 Annual depreciation 0.192 -SW$103 210e 0.1152 -SW$103 AA106 0.1152 =SW$103 AB106 0.2 32 -sw$103 X106 ESW$103 Y106 n new machine = SW$96 - SW$96 - SW$96 SW$96 -AB107+ AB108 108 Annual depreciation foregone on old machine =Sw$96 -X107+ X108 Y107+Y108 - Z107+ 2108 Incremental depreciation -AA107 AA108 11 Operating cash flow statement 112 Energy cost savings 113 Manpower cost saving 6000 6000 6000 6000 6000 54000 =X109 54000 - Z109 54000 54000 54000 114 Incremental depreciation = AB109 =Y109 =AA109 l12 AA1141 -AA115 ( 1- SWS101) -AA116-AA114 12:21141 112:AB114) 115 NOPAT 117 OCF -X115 ( 1- Sw$1013 - X116 - X114 -Y115 (1-SW$101) -Y116-Y114 -Z115 ( 1- SW$101 |- 7116 -2114 -AB115 ( 1- SW$101) -AB116- AB114 118 20000 119 Salvage value of new machine Book value after 5 years AI07 AB107) -35 % " AB121 -AB121+ AB122 122 Tax on gain on sale 123 Post tax salvage value 124 125 Net cash flows 126 Cumulative cash flows -w104 +W117 +W123 -SUM (SW$125:W125) Y104+Y117+Y123 -SUM(SW$125:Y125) -2104+2117+ Z123 -SUM(SW$125:2125) -AB104+ AB117 AB123 -SUM(SWS125 AB125) -X104 +X117+ X123 -AA104 +AA117+AA123 -SUM(SW$125 AA125) -SUM(SW$125:X125) Payback period (years) -Y92-Y126/2125 no 129 WACC 0.075 - 1 / ( 1 - SWS129 ) (W92 ) - 1 / ( 1+ SW$129) (X92) 30 Discount factor -1/(1+ 5W5129)(Y92) -Y130 . Y125 -SUM(SW$131:Y131) |1/(1+SWS129) (292) -Z130 Z125 SUM(SW$131:Z131) -1/(1+SW5129)^(AA92) - AA130 AA125 -SUM(SW$131 AA131) |-1/(1+SW$129)^( AB92 ) - AB130 ° AB 125 = SUM($W$131 AB131) 131 Discounted cash flow X130 X125 SUM($W$131 X131) -W130 ° W125 2 Discaunted nayheck period (years) 1131A9121 134 135 NPV 136 IRR - NPV (W129,X125:AB125)+W125 IRR(W125:AB125) 137 MIRR -MIRR (W125: AB125,W129,W129)

X V W Y 7 AA AB 92 Year 1 2 93 Old Machine 94 Original cost 50,000 95 Original life 10 96 Annual depreciation 97 Accumulated depreciation so far 5,000 25,000 98 Book value 25,000 99 Salvage value 100 Gain 40,000 15,000 101 Tax on gain 35% 102 Post tax salvage value 34,750 103 Cost of new machine (190,000) 104 Initial cash flows (155,250) 105 106 MACRS 5 year class schedule 20.00% 19.20% 11.52% 32.00% 11.52% 107 Annual depreciation (36,480) (21,888) (21,888) (38,000) (60,800) on new 108 on old machine 5,000 5,000 5,000 5,000 5,000 109 Incremental depreciation (31,480) (16,888) (16,888) (33,000) (55,800) 110 111 Operating cash flow statement 112 Energy cost savings 113 Manpower cost saving 114 Incremental depreciation 6,000 54,000 6,000 6,000 6,000 6,000 54,000 54,000 54,000 54,000 (31,480)(16,888) (16,888) (33,000) 27,000 (55,800) 115 EBIT 4,200 28,520 43,112 43,112 116 NOPAT 17,550 2,730 18,538 28,023 28,023 117 OCF 44,911 50,550 58,530 50,018 44,911 118 119 Salvage value of new machine 20,000 120 Book value after 5 years 121 Gain on sale (10,944) 9,056 122 Tax on gain on sale (3,170) 123 Post tax salvage value 5,886 124 125 Net cash flows (155,250) (155,250) 50,550 58,530 50,018 44,911 50,797 126 Cumulative cash flows (104,700) (46,170) 3,848 48,759 99,556 127 Payback period (years) 2.92 128 129 WACC 7.50% 130 Discount factor 1.0000 0.9302 0.8653 0.8050 0.7488 0.6966 131 Discounted cash flow (155,250) 47,023 50,648 40,263 33,629 35,383 132 Cumulative (108,227) (17,316) (155,250) (57,579) 16,313 51,696 133 Discounted payback period (years) 4.51 134 135 NPV 51,696 136 IRR 19.56% 137 MIRR 13.86% 138

V X Y AA AF 0 2 Year - achine 94 Original cost 50000 95 Original life 10 96 Annual depreciation -w94/W95 97 Accumulated depreciation so far -W96 .5 98 Book value -W94-W97 e value TO Gain -w99-W98 01 Tax on gain 0.35 12 Post tax salvage value -W99- W100 W101 -190000 103 Cost of new machine 04 Initial cash flows W102+ W103 16 MACRS 5 vear class schedule 107 Annual depreciation 0.192 -SW$103 210e 0.1152 -SW$103 AA106 0.1152 =SW$103 AB106 0.2 32 -sw$103 X106 ESW$103 Y106 n new machine = SW$96 - SW$96 - SW$96 SW$96 -AB107+ AB108 108 Annual depreciation foregone on old machine =Sw$96 -X107+ X108 Y107+Y108 - Z107+ 2108 Incremental depreciation -AA107 AA108 11 Operating cash flow statement 112 Energy cost savings 113 Manpower cost saving 6000 6000 6000 6000 6000 54000 =X109 54000 - Z109 54000 54000 54000 114 Incremental depreciation = AB109 =Y109 =AA109 l12 AA1141 -AA115 ( 1- SWS101) -AA116-AA114 12:21141 112:AB114) 115 NOPAT 117 OCF -X115 ( 1- Sw$1013 - X116 - X114 -Y115 (1-SW$101) -Y116-Y114 -Z115 ( 1- SW$101 |- 7116 -2114 -AB115 ( 1- SW$101) -AB116- AB114 118 20000 119 Salvage value of new machine Book value after 5 years AI07 AB107) -35 % " AB121 -AB121+ AB122 122 Tax on gain on sale 123 Post tax salvage value 124 125 Net cash flows 126 Cumulative cash flows -w104 +W117 +W123 -SUM (SW$125:W125) Y104+Y117+Y123 -SUM(SW$125:Y125) -2104+2117+ Z123 -SUM(SW$125:2125) -AB104+ AB117 AB123 -SUM(SWS125 AB125) -X104 +X117+ X123 -AA104 +AA117+AA123 -SUM(SW$125 AA125) -SUM(SW$125:X125) Payback period (years) -Y92-Y126/2125 no 129 WACC 0.075 - 1 / ( 1 - SWS129 ) (W92 ) - 1 / ( 1+ SW$129) (X92) 30 Discount factor -1/(1+ 5W5129)(Y92) -Y130 . Y125 -SUM(SW$131:Y131) |1/(1+SWS129) (292) -Z130 Z125 SUM(SW$131:Z131) -1/(1+SW5129)^(AA92) - AA130 AA125 -SUM(SW$131 AA131) |-1/(1+SW$129)^( AB92 ) - AB130 ° AB 125 = SUM($W$131 AB131) 131 Discounted cash flow X130 X125 SUM($W$131 X131) -W130 ° W125 2 Discaunted nayheck period (years) 1131A9121 134 135 NPV 136 IRR - NPV (W129,X125:AB125)+W125 IRR(W125:AB125) 137 MIRR -MIRR (W125: AB125,W129,W129)

X V W Y 7 AA AB 92 Year 1 2 93 Old Machine 94 Original cost 50,000 95 Original life 10 96 Annual depreciation 97 Accumulated depreciation so far 5,000 25,000 98 Book value 25,000 99 Salvage value 100 Gain 40,000 15,000 101 Tax on gain 35% 102 Post tax salvage value 34,750 103 Cost of new machine (190,000) 104 Initial cash flows (155,250) 105 106 MACRS 5 year class schedule 20.00% 19.20% 11.52% 32.00% 11.52% 107 Annual depreciation (36,480) (21,888) (21,888) (38,000) (60,800) on new 108 on old machine 5,000 5,000 5,000 5,000 5,000 109 Incremental depreciation (31,480) (16,888) (16,888) (33,000) (55,800) 110 111 Operating cash flow statement 112 Energy cost savings 113 Manpower cost saving 114 Incremental depreciation 6,000 54,000 6,000 6,000 6,000 6,000 54,000 54,000 54,000 54,000 (31,480)(16,888) (16,888) (33,000) 27,000 (55,800) 115 EBIT 4,200 28,520 43,112 43,112 116 NOPAT 17,550 2,730 18,538 28,023 28,023 117 OCF 44,911 50,550 58,530 50,018 44,911 118 119 Salvage value of new machine 20,000 120 Book value after 5 years 121 Gain on sale (10,944) 9,056 122 Tax on gain on sale (3,170) 123 Post tax salvage value 5,886 124 125 Net cash flows (155,250) (155,250) 50,550 58,530 50,018 44,911 50,797 126 Cumulative cash flows (104,700) (46,170) 3,848 48,759 99,556 127 Payback period (years) 2.92 128 129 WACC 7.50% 130 Discount factor 1.0000 0.9302 0.8653 0.8050 0.7488 0.6966 131 Discounted cash flow (155,250) 47,023 50,648 40,263 33,629 35,383 132 Cumulative (108,227) (17,316) (155,250) (57,579) 16,313 51,696 133 Discounted payback period (years) 4.51 134 135 NPV 51,696 136 IRR 19.56% 137 MIRR 13.86% 138