Question

1. Your division is considering two investment projects, each of which requires an up-front expenditure of $20 million. You estimate that the investments will produce the following net cash flows:

Year                       Project A              Project B

1                              $4,500,000           $20,000,000

2                              10,000,000           10,000,000

3                              20.000.000           6,500,000

1. What are the two project’s NPVs assuming the cost of capital is 3%, 12%, 17%?
2. What are the two projects’ IRRs at those same costs of capital?

Answer 1

a]

NPV is calculated using NPV function in Excel

B32 A Year foc =NPV (12%,$B$28:$B$30)+$B$27 Τ Ε 26 A 0 28 1 2 30 3 (20,000,000) (20,000,000) 4 ,500,000 20,000,000 10,000,000 10,000,000 20,000,000 6,500,000 12,097,724 14,791,856 6,225,401 10,455,653 3,638,700 8,457,561 31 NPV @ 3% 32 NPV @ 12% 33 NPV @ 17%

26 Year 27 0 28 1 29 2 30 3 31 NPV @ 3% 32 NPV @ 12% 33 NPV @ 17% -20000000 -20000000 4500000 20000000 10000000 10000000 20000000 6500000 !=NPV(3%,$B$28:$B$30)+$B$27 1=NPV(3%,$C$28:$C$30)+$C$27 =NPV(12%,$B$28:$B$30)+$B$27 =NPV(12%, $C$28:$C$30)+$C$27 =NPV(17%,$B$28:$B$30)+$B$27 =NPV(17%,$C$28:$C$30)+$C$27 |

b]  

IRR is calculated using MIRR function in Excel

B34 - fax =MIRR($B$27:$B$30,3%,3%) DEF Year A 0 31 NPV @ 3% 32 NPV @ 12% 33 NPV @ 17% 34 IRR 35 IRR 36 IRR 27 (20,000,000) 4,500,000 10,000,000 20,000,000 12,097,724 6,225,401 3,638,700 21%) 23% 24% (20,000,000) 20,000,000 10,000,000 6,500,000 14,791,856 10,455,653 8,457,561 24% 29% 32%

A В 26 Year 27 0 28 1 29 2 30 3 31 NPV @ 3% 32 NPV @ 12% 33 NPV @ 17% 34 IRR 35 IRR 36 IRR 37 -20000000 4500000 10000000 20000000 =NPV(3%,$B$28:$B$30)+$B$27 =NPV(12%,$B$28:$B$30)+$B$27 =NPV(17%,$B$28:$B$30)+$B$27 =MIRR($B$27:$B$30,3%,3%) =MIRR($B$27:$B$30,12%,12%) =MIRR($B$27:$B$30,17%,17%) -20000000 20000000 10000000 6500000 =NPV(3%,$C$28:$C$30)+$C$27 =NPV(12%,$C$28:$C$30)+$C$27 =NPV(17%,$C$28:$C$30)+$C$27 1=MIRR($C$27:$C$30,3%,3%) =MIRR($C$27:$C$30,12%,12%) =MIRR($C$27:$C$30,17%,17%)