Question

The cash flows for three independent projects are found below.

Year 0 (Initial investment) Project A $(45,000) Project B $(120,000) Project C $(460,000)

Year 1 $12,000    $ 30,000 $ 220,000

Year 2 $14,000 $ 30,000 $ 220,000

Year 3 $ 19,000 $ 30,000 $   220,000

Year 4 $ 27,000 $ 30,000 -

Year 5   

a. Calculate the IRR for each of the projects. b. If the discount rate for all the three projects is 16 percent, which projects or projects would you want to undertake? c. What is the net present value of each projects where the appropriate discount rate is 16 percent? a. The IRR of project A is.

Answer 1

IRR is the internal rate of return such that the Net Present Value of a set of cashflows from an investment/project will equal to its initial investment.

initial investment = CF1/(1+r) +CF2/(1+r)² + CF3/(1+r)³ +CF4/(1+r)⁴ +.....+ CFn/(1+r)ⁿ

In the example, we will calculate IRR using Excel IRR method which takes in the set of cash flows and NPV by discounting each cash flow by 16%. This is copied in the table below:

r	0.16
Year	Project A	Project B	Project C		PV (Project A)	PV (Project B)	PV (Project C)
0	($45,000)	($120,000)	($460,000)		($45,000.00)	($120,000.00)	($460,000.00)
1	$12,000	$30,000	$220,000		$10,344.83	$25,862.07	$189,655.17
2	$14,000	$30,000	$220,000		$10,404.28	$22,294.89	$163,495.84
3	$19,000	$30,000	$220,000		$12,172.50	$19,219.73	$140,944.69
4	$27,000	$30,000			$14,911.86	$16,568.73	$0.00
5
IRR	18.707%	0.000%	20.475%	NPV	$2,833.46	($36,054.58)	$34,095.70

Copying the formula sheet below:

r	0.16
Year	Project A	Project B	Project C		PV (Project A)	PV (Project B)	PV (Project C)
0	-45000	-120000	-460000		=B4/(1+$B$1)^$A4	=C4/(1+$B$1)^$A4	=D4/(1+$B$1)^$A4
1	12000	30000	220000		=B5/(1+$B$1)^$A5	=C5/(1+$B$1)^$A5	=D5/(1+$B$1)^$A5
2	14000	30000	220000		=B6/(1+$B$1)^$A6	=C6/(1+$B$1)^$A6	=D6/(1+$B$1)^$A6
3	19000	30000	220000		=B7/(1+$B$1)^$A7	=C7/(1+$B$1)^$A7	=D7/(1+$B$1)^$A7
4	27000	30000			=B8/(1+$B$1)^$A8	=C8/(1+$B$1)^$A8	=D8/(1+$B$1)^$A8
5
IRR	=IRR(B4:B8)	=IRR(C4:C8)	=IRR(D4:D8)	NPV	=SUM(F4:F8)	=SUM(G4:G8)	=SUM(H4:H8)

Sub part a:

IRR for project A = 18.707%

IRR for project B = 0

IRR for project C = 20.475%

Sub part b:

If discount rate is 16 %, then the NPV of project C ($34,095.70) is the highest

Sub part b:

If discount rate is 16 %, then the NPV of project C ($34,095.70) is the highest

NPV for project A = $2,833.46

NPV for project B = - $36,054.58

NPV for project C = $34,095.70