Question

Construct an investment opportunity ranking using the following information on four independent projects using IRR:

Project		CF0		Annual CF		Project Life
I		$4 million		$1.1 million		5 years
II		$8 million		$2.8 million		4 years
III		$7 million		$2.1 million		5 years
IV		$6 million		$2.0 million		4 years

I, II, III, IV

II, III, IV, I

III, II, IV, I

IV, III, II, I

Answer 1

Solution :

0 1 2 3 4 5 $ -4,000,000.00 $ 1,100,000.00 $ 1,100,000.00 $ 1,100,000.00 $ 1,100,000.00 $ 1,100,000.00 11.65% $ -8,000,000.00 $ 2,800,000.00 $ 2,800,000.00 $ 2,800,000.00 $ 2,800,000.00 Four Projects Project 1 Cash flows IRR of cash flows Project 11 Cash flows IRR of cash flows Project III Cash flows IRR of cash flows Project IV Cash flows IRR of cash flows 14.96% $ -7,000,000.00 $ 2,100,000.00 $ 2,100,000.00 $ 2,100,000.00 $ 2,100,000.00 $ 2,100,000.00 15.24% $ -6,000,000.00 $ 2,000,000.00 $ 2,000,000.00 $ 2,000,000.00 $ 2,000,000.00 12.59%

Answer is III, II, IV, I

Formulae

с D E F G H o 1 2 3 4 5 -4000000 1100000 1100000 1100000 1100000 1100000 =IRR(C19540:H19540) -8000000 2800000 2800000 2800000 2800000 19538 Four Projects 19539 Project 1 19540 Cash flows 19541 IRR of cash flows 19542 Project II 19543 Cash flows 19544 IRR of cash flows 19545 Project III 19546 Cash flows 19547 IRR of cash flows 19548 Project IV 19549 Cash flows 19550 IRR of cash flows =IRR(C19543: 619543) -7000000 2100000 2100000 2100000 2100000 2100000 =IRR(C19546:H19546) -6000000 2000000 2000000 2000000 2000000 =IRR(C19549:G19549)

Formulae as above...

Answer 2

Project CFO Annual CF Life IRR Project CFO Annual CF Life IRR $4.00 $ 1.10 $5.00 11.65% | son 4 1.1 5 ERATE(D2 C2-B2) $8.00 $ 2.80 $4.00 14.96% 1 8 2.8 4 =RATE(D3,C3,-B3) III $7.00 $ 2.10 $5.00 15.24% I 17 12. 1 5 ERATE(D4.C4-B4) | $6.00 $ 2.00 $4.00 12.59% IV 14 TERATE(D5,C5,-B5) ! IV 16

the answer is III, II, IV, I