Question

Complete a spreadsheet (by hand or by Excel) and then determine the after-tax internal rate of return by hand (i.e. no financial calculator or Excel routine). For this problem assume a 10-year analysis period, double declining depreciation is used, 21% corporate tax rate, an initial cost of $200,000, an annual benefit of $60,000 and no salvage value but instead you must pay a disposal fee of $20,000 that your corporate accountant determined is 100% tax deductible.

Answer 1

IRR = 22.56%

WORKINGS

1: Depreciation rate = Straight line * 2 = 10%*2 = 20%

Annual depreciation and tax shield is as below

Year	Opening balance	Depreciation	Closing balance	Tax shield
1	200000	40000	160000	8400
2	160000	32000	128000	6720
3	128000	25600	102400	5376
4	102400	20480	81920	4300.8
5	81920	16384	65536	3440.64
6	65536	13107.2	52428.8	2752.512
7	52428.8	10485.76	41943.04	2202.01
8	41943.04	8388.608	33554.432	1761.608
9	33554.432	6710.8864	26843.5456	1409.286
10	26843.5456	5368.70912	21474.83648	1127.429

2: The Net cash flows are

Year	Initial cost	Annual benefit after tax	Tax shield	Disposal fee after tax	Net Cash flow
0	-200000				-200000
1		47400	8400		55800
2		47400	6720		54120
3		47400	5376		52776
4		47400	4300.8		51700.8
5		47400	3440.64		50840.64
6		47400	2752.512		50152.51
7		47400	2202.0096		49602.01
8		47400	1761.60768		49161.61
9		47400	1409.286144		48809.29
10		47400	1127.428915	-15800	32727.43
				IRR	22.56%

Formulae below