Question

Use the after-tax IRR method to evaluate the following three alternatives for MACRS 3-year property, and offer a recommendation. The after-tax MARR is 25%, the project life is 5 years, and the firm has a combined incremental tax rate of 45%.

Answer 1

Formula sheet

A	B	C	D	E	F	G	H	I	J
2
3
4		Project Life	5	years
5		Tax Rate	0.45
6		MARR	0.25
7
8		Cash Flow Calculation for Altenative A:
9
10		Depreciation follows MACRS 3 year convention.
11		Tax basis of the equipment (B)	14000
12
13				Year 1	Year 2	Year 3	Year 4	Year 5
14		MACRS 3 Year depreciation rate (rt)		0.3333	0.4445	0.1481	0.0741	0
15		Depreciation (B*rt)		=$D$11*E14	=$D$11*F14	=$D$11*G14	=$D$11*H14	=$D$11*I14
16		Book Value	=D11	=D16-E15	=E16-F15	=F16-G15	=G16-H15	=H16-I15
17
18		Calculation of after tax net cash flow from the sale of the asset at the end of year 5:
19
20		Proceed from sale of machine	5000
21		Book Value of Machine at the end of Year 5	=I16
22		Gain or Loss on sale	=Proceed From Sale - Book value at the end of sale
23			=D20-D21
24
25		Tax rate	=D5
26		Gain or Loss on sale	=D23
27		Tax on Gain & Loss	=D26*D25
28		Net Proceed from Sale of assets at the end	=Proceed from Sale - Tax Expense on gain or loss
29			=D20-D27
30
31		Hence after-tax net cash flow from the sale of the asset	=D29
32
33		Cash Flow can be calculated as follows:
34		Year	0	1	2	3	4	5
35		Investment	=-D11
36		Annual cost		-2500	-2500	-2500	-2500	-2500
37		Depreciation		=-E15	=-F15	=-G15	=-H15	=-I15
38		Operating Income Before Tax (EBIT)		=SUM(E36:E37)	=SUM(F36:F37)	=SUM(G36:G37)	=SUM(H36:H37)	=SUM(I36:I37)
39		Tax expense		=-E38*$D$5	=-F38*$D$5	=-G38*$D$5	=-H38*$D$5	=-I38*$D$5
40		After Tax operating income (EBIT*(1-T))		=E38+E39	=F38+F39	=G38+G39	=H38+H39	=I38+I39
41		Add Depreciation		=-E37	=-F37	=-G37	=-H37	=-I37
42		Net Operating Cash Flow		=E40+E41	=F40+F41	=G40+G41	=H40+H41	=I40+I41
43		Net cash flow from the sale of the asset						=D31
44		Net Cash Flow for Alt A	=D35	=E42+E43	=F42+F43	=G42+G43	=H42+H43	=I42+I43
45
46		Cash Flow Calculation for Altenative B:
47
48		Depreciation follows MACRS 3 year convention.
49		Tax basis of the equipment (B)	18000
50
51				Year 1	Year 2	Year 3	Year 4	Year 5
52		MACRS 3 Year depreciation rate (rt)		0.3333	0.4445	0.1481	0.0741	0
53		Depreciation (B*rt)		=$D$54*E52	=$D$54*F52	=$D$54*G52	=$D$54*H52	=$D$54*I52
54		Book Value	=D49	=D54-E53	=E54-F53	=F54-G53	=G54-H53	=H54-I53
55
56		Calculation of after tax net cash flow from the sale of the asset at the end of year 5:
57
58		Proceed from sale of machine	10000
59		Book Value of Machine at the end of Year 5	=I54
60		Gain or Loss on sale	=Proceed From Sale - Book value at the end of sale
61			=D58-D59
62
63		Tax rate	=D5
64		Gain or Loss on sale	=D61
65		Tax on Gain & Loss	=D64*D63
66		Net Proceed from Sale of assets at the end	=Proceed from Sale - Tax Expense on gain or loss
67			=D58-D65
68
69		Hence after-tax net cash flow from the sale of the asset	=D67
70
71		Cash Flow can be calculated as follows:
72		Year	0	1	2	3	4	5
73		Investment	=-D49
74		Annual cost		-1000	-1000	-1000	-1000	-1000
75		Depreciation		=-E53	=-F53	=-G53	=-H53	=-I53
76		Operating Income Before Tax (EBIT)		=SUM(E74:E75)	=SUM(F74:F75)	=SUM(G74:G75)	=SUM(H74:H75)	=SUM(I74:I75)
77		Tax expense		=-E76*$D$5	=-F76*$D$5	=-G76*$D$5	=-H76*$D$5	=-I76*$D$5
78		After Tax operating income (EBIT*(1-T))		=E76+E77	=F76+F77	=G76+G77	=H76+H77	=I76+I77
79		Add Depreciation		=-E75	=-F75	=-G75	=-H75	=-I75
80		Net Operating Cash Flow		=E78+E79	=F78+F79	=G78+G79	=H78+H79	=I78+I79
81		Net cash flow from the sale of the asset						=D69
82		Net Cash Flow for Alt B	=D73	=E80+E81	=F80+F81	=G80+G81	=H80+H81	=I80+I81
83
84
85		Cash Flow Calculation for Altenative C:
86
87		Depreciation follows MACRS 3 year convention.
88		Tax basis of the equipment (B)	10000
89
90				Year 1	Year 2	Year 3	Year 4	Year 5
91		MACRS 3 Year depreciation rate (rt)		0.3333	0.4445	0.1481	0.0741	0
92		Depreciation (B*rt)		=$D$93*E91	=$D$93*F91	=$D$93*G91	=$D$93*H91	=$D$93*I91
93		Book Value	=D88	=D93-E92	=E93-F92	=F93-G92	=G93-H92	=H93-I92
94
95
96		Cash Flow can be calculated as follows:
97		Year	0	1	2	3	4	5
98		Investment	=-D88
99		Annual cost		-5000	-5000	-5000	-5000	-5000
100		Depreciation		=-E92	=-F92	=-G92	=-H92	=-I92
101		Operating Income Before Tax (EBIT)		=SUM(E99:E100)	=SUM(F99:F100)	=SUM(G99:G100)	=SUM(H99:H100)	=SUM(I99:I100)
102		Tax expense		=-E101*$D$5	=-F101*$D$5	=-G101*$D$5	=-H101*$D$5	=-I101*$D$5
103		After Tax operating income (EBIT*(1-T))		=E101+E102	=F101+F102	=G101+G102	=H101+H102	=I101+I102
104		Add Depreciation		=-E100	=-F100	=-G100	=-H100	=-I100
105		Net Operating Cash Flow		=E103+E104	=F103+F104	=G103+G104	=H103+H104	=I103+I104
106		Net cash flow from the sale of the asset						0
107		Net Cash Flow for Alt C	=D98	=E105+E106	=F105+F106	=G105+G106	=H105+H106	=I105+I106
108
109		Calculation of Incremental IRR A-C:
110
111		Year	0	1	2	3	4	5
112		Net Cash Flow for Alt A	=D44	=E44	=F44	=G44	=H44	=I44
113		Net Cash Flow for Alt C	=D107	=E107	=F107	=G107	=H107	=I107
114		Incremental Cash Flow A-C	=D112-D113	=E112-E113	=F112-F113	=G112-G113	=H112-H113	=I112-I113
115		Incremental IRR A-C	=IRR(D114:I114)
116
117		Hence Incremental IRR A-C	=D115
118
119
120		Calculation of Incremental IRR B-A:
121
122		Year	0	1	2	3	4	5
123		Net Cash Flow for Alt B	=D82	=E82	=F82	=G82	=H82	=I82
124		Net Cash Flow for Alt A	=D44	=E44	=F44	=G44	=H44	=I44
125		Incremental Cash Flow B-A	=D123-D124	=E123-E124	=F123-F124	=G123-G124	=H123-H124	=I123-I124
126		Incremental IRR B-A	=IRR(D125:I125)
127
128		Hence Incremental IRR B-A	=D126
129
130
131		If the incremental IRR is higher than the required rate of return then the higher cost alternative should be selected.
132
133		Given the following data:
134		Incremental IRR A-C	=D117
135		Incremental IRR B-A	=D128
136
137		Since C is the lowest cost alternative and alternative A is the next lowest cost alternative,
138		therefore incremental IRR A-C is to be considered first.
139
140		Since incremental IRR A-C is 43.81% which is higher than MARR,
141		therefore alternative A should be selected and alternative C should be removed from the analysis.
142
143		Next incremental IRR of the B with respect to A needs to be considered.
144		Since incremental IRR B-A is 28.26% which is higher than MARR,
145		therefore alternative B should be selected and alternative A should be removed from the analysis.
146
147		Thus alternative B should be selected.
148