Question

How to find IRR in excel using trial-error method?

Cash flows for each year are following:

Y0 = -55

Y1 = -30.62

Y2 = 4.14

Y3 = 6.68

Y4 = 8.74

Y5 = 9.54

Y6: tricky part is after Y5, cash flow growth is 3% indefinitly

rate of return = 11%

Answer 1

Terminal value in year 5 = 9.54*1.03/(0.11-0.08)=122.8275

Year	Cash flow	Terminal value	Total
0	-55		-55
1	-30.62		-30.62
2	4.14		4.14
3	6.68		6.68
4	8.74		8.74
5	9.54	122.8275	132.3675
6	9.8262
		0.01	? 59.57
		0.02	? 53.22
		0.03	? 47.23
		0.04	? 41.59
		0.05	? 36.27
		0.06	? 31.24
		0.07	? 26.50
		0.08	? 22.01
		0.09	? 17.77
		0.1	? 13.76
		0.11	? 9.97
		0.12	? 6.38
		0.13	? 2.98
		0.14	? -0.24
		0.15	? -3.30

As we can see that the NPV is 0 between 13% and 14% and it is nearer to 14% than 13% we shall se ta steps of 0.1% or 0.001.

0.13	$    2.98
0.131	$    2.65
0.132	$    2.32
0.133	$    1.99
0.134	$    1.67
0.135	$    1.35
0.136	$    1.02
0.137	$    0.71
0.138	$    0.39
0.139	$    0.07
0.14	$   -0.24
0.141	$   -0.56
0.142	$   -0.87
0.143	$   -1.18
0.144	$   -1.48

Now we can see IRR is somewhere in between 0.139 and 0.14, we shal now see at steps of 0.01% or 0.0001

0.139	$    0.07
0.1391	$    0.04
0.1392	$       -
0.1393	$   -0.02
0.1394	$   -0.05
0.1395	$   -0.09
0.1396	$   -0.12
0.1397	$   -0.15
0.1398	$   -0.18
0.1399	$   -0.21
0.14	$   -0.24
0.1401	$   -0.27
0.1402	$   -0.31
0.1403	$   -0.34
0.1404	$   -0.37

Hence IRR is 13.92%

NPV formula for hit and trial IRR =$L$9+NPV(K17,$L$10:$L$14) where Lp is initla cash flow of -55 and other are cash flows after than K17 is the rate