In this exercise we solve iteratively for the internal rate of return. Consider an investment which costs 800 and has cash flows of 300, 200, 150, 122, 133 in years 15. Setting up the loan table below shows that 10% is greater than the IRR (since the return of principal at the end of year 5 is less than the principal at the beginning of the year):

47 Basic Financial Calculations

	A	B	C	D	E	F	G	H
1	IRR?	10.00%
2				LOAN TABLE			Division of payment between:
3	Year	Cash flow		Year	Principal at beginning of year	Payment at end of year	Interest	Principal
4	0	-800		1	800.00	300.00	80.00	220.00
5	1	300		2	580.00	200.00	58.00	142.00
6	2	200		3	438.00	150.00	43.80	106.20
7	3	150		4	331.80	122.00	33.18	88.82
8	4	122		5	242.98	133.00	24.30	108.70
9	5	133		6	134.28	<-- Should be zero for IRR

Setting the IRR? cell equal to 3% shows that 3% is less than the IRR, since the return of principal at the end of year 5 is greater than the principal at the beginning of year 5.

By changing the IRR? cell, find the internal rate of return of the investment.

	A	B	C	D	E	F	G	H
1	IRR?	3.00%
2				LOAN TABLE			Division of payment between:
3	Year	Cash flow		Year	Principal at beginning of year	Payment at end of year	Interest	Principal
4	0	-800		1	800.00	300.00	24.00	276.00
5	1	300		2	524.00	200.00	15.72	184.28
6	2	200		3	339.72	150.00	10.19	139.81
7	3	150		4	199.91	122.00	6.00	116.00
8	4	122		5	83.91	133.00	2.52	130.48
9	5	133		6	-46.57	<-- Should be zero for IRR