Question

Project Number       1   2   3   4   5   6   7   8
                                  
Initial Investment       ($2,000)   ($2,000)   ($2,000)   ($2,000)   ($2,000)   ($2,000)   ($2,000)   ($2,000)
Year 1       $330    $1,666        $160    280    $2,200    $1,200    ($350)
2       330    334        200    280        900    (60)
3       330    165        350    280        300    60
4       330            395    280        90    350
5       330            432    280        70    700
6       330            440    280            1,200
7       330            442    280            2,250
8       1,000            444    280           
9                   446    280           
10                   448    280           
11                   450    280           
12                   451    280           
13                   451    280           
14                   452    280           
15               10,000    (2,000)   280           
                                  
Sum of Cash Flow Benefits       $3,310    $2,165    $10,000    $3,561    $4,200    $2,200    $2,560    $4,150
Excess of Cash Flow                                  
Over Initial Investment       $1,310    $165    $8,000    $1,561    $2,200    $200    $560    $2,150
                                  

1. Note the payback period for each project.
2. Calculate the NPV for each project assuming discount rates of 10%, 12% and 14% (round your answers to whole dollars).
3. Compute the IRR for each project (round your answers to one decimal place).
4. Use the above data to rank the 8 projects. Provide a few words to explain your ranking.

Answer 1

The payback period is the no. of years taken for the cumulative cash-flows to become 0

We use the function NPV in excel to calculate the NPV of the 8 projects

NPV = Initial investment + NPV(Discount rate, All cash-flows from year1 to end)

We calculate the IRR using IRR function in excel

IRR = IRR(All cash-flows)

Project	1	2	3	4	5	6	7	8
Payback period (in years)	6.060606061	2	Infinity	6.0520362	7.1429	0.909090909	1.888888889	6.046511628

	Project NPV
Discount rate	1	2	3	4	5	6	7	8
10%	73.0856	-85.4545	393.9205	228.2220	129.7023	0.0000	165.0409	182.9844
12%	-90.0771	-128.7935	-173.0374	30.4112	-92.9579	-35.7143	99.3536	-72.2516
14%	-234.300	-170.224	-599.035	-146.341	-280.193	-70.175	37.287	-296.013

Project	1	2	3	4	5	6	7	8
IRR	10.87%	6.31%	11.33%	12.33%	11.12%	10.00%	15.26%	11.41%

We rank the project according to their NPV, IRR and payback analysis. The top-ranked projects are the ones with the highest average NPV, highest IRR and lowest payback period.

This signifies that the project at the top rank is the ones that would generate the highest value for the firm. Higher the IRR and NPV, better is the project in terms of returns and value. In the case of negative NPV, higher IRR and shorter payback projects would be preferred.

NPV=10% Ranking (1-8)	3	5	4	8	7	6	2	1
NPV=12% Ranking (1-8)	3	5	8	7	6	2	1	4
NPV=14% Ranking (1-8)	7	4	8	3	5	1	6	2

IRR Ranking (1-8)

7

4

8

3

5

1

6

2

Payback period rank (1-8)

6

7

2

8

4

1

5

3