Question

Cost of new machinery	$200,000
Machinery estimated useful life	10 years
Estimated salvage value	$0
Straight-line depreciation
Annual labor hours reduction	10,000
Annual operating costs reduction	$4,000
Average hourly labor rate	$5.50
Income tax rate	40%
Discount rate	10%

Part 1: Calculate the annual incremental income after taxes and the annual net cash flow for each year

Part 2: Calculate the payback period

Part 3: Calculate the rate of return on average investment

Part 4: Calculate the net present value

Part 5: Calculate the internal rate of return

Part 6: Calculate the profitability index

Please show formulas!

Answer 1

A C D $200,000 10 $0 $20,000 10,000 Given in question $4,000 $5.50 40% 10% 8 B 1 Cost of new machinery 2 Machinery estimated useful life (years) 3 Estimated salvage value 4 Depreciation Rate per annum (Straight-line) 5 Annual labor hours reduction 6 Annual operating costs reduction 7 Average hourly labor rate Income tax rate 9 Discount rate 10 11 Part 1 Annual operating costs reduction Annual labor hours reduction Depreciation 14 Earnings Before Tax Tax at 40% 16 Annual Incremental Income after taxes 17 Add Back: Depreciation 18 Annual Net Cash Flow 19 20 Part 2 Payback period (years) 21 22 Part 3 Rate of Return on Average Investment N 3 $4,000 =C6 $55,000 =C5*C7 ($20,000) =-C4 $39,000 =SUM(C11:C13) ($15,600) =C14*-08 $23,400 |=C14+C15 $20,000 --C13 $43,400 =SUM(C16:017) 15 4.6 =C1/C18 11.7% =C16/01

A B с D 23 24 Part 4 Cumulative Discount Factor for 10% for 10 years Present Value of Annual Net Cash Flow Net Present Value 25 6.1446 = (1-(1+09)^-C2))/C9 $266,674 =C18*C24 $66,674 =C25-C1 26 27 29 31 28 Part 5 IRR is the rate at which NPV is zero. At 10% discout rate, NPV is $66,674. Hence for NPV to be zero, lets take higher discount rate of 20%. 30 Cumulative Discount Factor for 20% for 10 years 4.1925 =((1-(1+20%)^-C2))/20% Present Value of Annual Net Cash Flow $181,953 =C18*C30 32 Net Present Value at discount rate of 20% ($18,047) =C31-C1 Movement in Discount rate 10.0% =20%-C9 Movement in Net Present Value $84,721 =C26-C32 35 Internal Rate of Return 17.9% =20%+(C33*(C32)/C34) 36 37 Part 6 Profitability Index 1.33 =C25/01 33 34 28

Note:

Internal Rate of Return is computed using extrapolation method. To find the IRR accurately, excel function IRR can be used as = IRR(-initial investment, cash flow in year 1, ...., cash flow in year 10) which will be 17.30%