Question

Norcross Textile Company is considering automating its screen printing system at a cost of $20,000. The firm expects to phase out the new printing system at the end of five years due to changes in style. At that time the firm could scrap the system of $2,000 in today's dollars. The depreciation schedule is MACRS for a five year property. The expected net savings due to automation also in today's dollars resulting in the following BTCF in constant $. The inflation rate is 5%.

	Constant $
0	($20,000)
1	$15,000
2	$17,000
3	$14,000
4	$14,000
5	$14,000
MV	$2,000

The firm's marginal tax rate is 40%. What is the Real IRR of the ATCF?

I know the answer is 45.46%. I just dont understand why. I need this done in excel

Answer 1

cost of machine	MACRS dep rate	annual Depreciation
20000	20%	4000
20000	32%	6400
20000	19.20%	3840
20000	11.52%	2304
20000	11.52%	2304
accumulated depreciation		18848
Book value of machine	20000-18848	1152
gain on sale of machine	2000-1152	848
after tax sale proceeds	2000-(848*40%)	1660.8
Year	0	1	2	3	4	5
cost of machine	-20000
BTCF		15000	17000	14000	14000	14000
less depreciation		4000	6400	3840	2304	2304
BTCF after depreciation		11000	10600	10160	11696	11696
less tax-40%		4400	4240	4064	4678.4	4678.4
ATCF		6600	6360	6096	7017.6	7017.6
Add depreciation		4000	6400	3840	2304	2304
operating cash flow		10600	12760	9936	9321.6	9321.6
cash flow from scrap						1660.8
net operating cash flow	-20000	10600	12760	9936	9321.6	10982.4
IRR = using IRR function in MS excel =irr(-20000,10600,12760,9936,9312.6,10982.4	IRR(C66:H66)	46.47%