Question

Period	0	1	2	3	4	5	6	7	8	9	10	11	PBP	NPV	IRR
Project A	$                       (1,000,000)	$ 200,000	$ 200,000	$ 200,000	$ 200,000	$ 200,000	$ 200,000	$ 200,000	$ 200,000	$ 200,000	$ 200,000	$ 400,000
Project B	$                       (1,000,000)	$ 500,000	$ 500,000	$ 500,000
Project C	$                             (80,000)	$      1,040	$      9,456	$    11,405	$    18,567	$    47,453	$      6,394	$    45,727	$    51,933	$    85,625
Project D	$                           (400,000)	$      4,161	$    37,824	$    45,618	$    74,269	$ 189,812	$    25,577	$ 182,907	$ 207,733	$ 342,499
Project E	$                             (17,000)	$    16,000	$    16,000	$ (16,000)	$    16,000	$ (52,000)
Project F	$                               (5,000)	$      2,000	$      2,000	$      2,000	$      2,000	$      2,000
Project G	$                               (5,000)	$             -	$             -	$             -	$             -	$    15,000
Project H	$                             (20,000)	$      7,000	$      7,000	$      7,000	$      7,000	$      7,000

Answer the question below, i post this question for fourth time please answer it
contains a list of available investment projects and their respective cash flows. Using a cost of capital of 10%,

1. Find the payback period, net present value and internal rate of return for each project.
2. Based on the results, identify conflicts in ranking the above projects based on the IRR and NPV approaches, i.e. a project that has a higher rank using IRR but a lower rank using NPV and vice versa. You should also provide a discussion of what caused the conflict
3. Provide a discussion of the advantages and disadvantages of all the capital budgeting techniques covered in this course, and identify methods to overcome the disadvantages (if available)
4. Provide a brief discussion (a small paragraph) of the practicality (uses in real life) of the different capital budgeting methods.

yes

yes you can use excel

Answer 1

Calculating IRR for the projects - Using Excel function of IRR

Year	A	B	C	D	E	F	G	H
0	-1000000	-1000000	-80000	-400000	-17000	-5000	-5000	-20000
1	200000	500000	1040	4161	16000	2000	0	7000
2	200000	500000	9456	37824	16000	2000	0	7000
3	200000	500000	11405	45618	-16000	2000	0	7000
4	200000		18567	74269	16000	2000	15000	7000
5	200000		47453	189812	-52000	2000		7000
6	200000		6394	25577
7	200000		45727	182709
8	200000		51933	207733
9	200000		85625	342499
IRR	13.70%	23.38%	21.38%	16.96%	No IRR	28.65%	31.61%	22.11%

Calculating NPV of Projects using Discount factors at 8% discount rate

PV = CF X Discount Factor

Discount Factor = 1/ (1+ Discount Rate)^ t  

NPV = Sum of PV of all future values

Year	A	B	C	D	E	F	G	H	Discount Factor at 10%
0	-1000000	-1000000	-80000	-400000	-17000	-5000	-5000	-20000	1.000
1	181818	454545	945	3783	14545	1818	0	6364	0.909
2	165289	413223	7815	31260	13223	1653	0	5785	0.826
3	150263	375657	8569	34273	-12021	1503	0	5259	0.751
4	136603		12682	50727	10928	1366	10245	4781	0.683
5	124184		29465	117858	-32288	1242		4346	0.621
6	112895		3609	14438					0.564
7	102632		23465	93759					0.513
8	93301		24227	96909					0.467
9	84820		36313	145253					0.424
NPV	151805	243426	67090	188259	-22612	2582	5245	6536

Cumulative Cash flows for Pay Back Period

PBP = The immediately preceding year before Cumulative Cash flows is positive + (Cumulative CF in Previous year/ Cash flow in the year cumulative CF turns positive)

Thus PBP for Project D = 6 + (27739 / 182,709) = 6.1518

Similarly for others

Year	A	B	C	D	E	F	G	H
0	(1,000,000)	(1,000,000)	(80,000)	(400,000)	(17,000)	(5,000)	(5,000)	(20,000)
1	(800,000)	(500,000)	(78,960)	(395,839)	(1,000)	(3,000)	(5,000)	(13,000)
2	(600,000)	-	(69,504)	(358,015)	15,000	(1,000)	(5,000)	(6,000)
3	(400,000)	500,000	(58,099)	(312,397)	(1,000)	1,000	(5,000)	1,000
4	(200,000)		(39,532)	(238,128)	15,000	3,000	10,000	8,000
5	-		7,921	(48,316)	(37,000)	5,000		15,000
6	200,000		14,315	(22,739)
7	400,000		60,042	159,970
8	600,000		111,975	367,703
9	800,000		197,600	710,202
PBP	5.00	2.00	4.83	6.12	#DIV/0!	2.50	3.33	2.86

2. Conflicts - On the basis of IRR project G with 31.1% IRR should be the top one, but on the basis of NPV , it is one of the bottom ones. This is because, although G offers the best % returns, the initial investment as well the overall return in G is much less than say project B.

If the Organization has a large money to investment, it would not be wise to invest in Project G, even though it offers a high return.

Projects F,G,H offer high % return but very little in terms of absolute dollar value return.

Projects A,B,C & D offer higher NPV. They also have IRR greater than the discount rate of 10%. These projects would take precedence if NPV is the selection criteria.

Project has no IRR. Also the NPV is negative and it doesn't have a Payback period    

Advantages And Disadvantages of Capital budgeting Method

The aggregate of all present value of the cash flows of an asset, immaterial of positive or negative is known as Net Present Value. Internal Rate of Return is the discount rate at which NPV = 0.

The calculation of NPV is made in absolute terms as compared to IRR which is computed in percentage terms.

The purpose of calculation of NPV is to determine the surplus from the project, whereas IRR represents the state of no profit no loss.

Decision making is easy in NPV but not in the IRR.

Intermediate cash flows are reinvested at cut off rate in NPV whereas in IRR such an investment is made at the rate of IRR.

When the timing of cash flows differs, the IRR will be negative, or it will show multiple IRR which will cause confusion. This is not in the case of NPV.

When the amount of initial investment is high, the NPV will always show large cash inflows while IRR will represent the profitability of the project irrespective of the initial invest. So, the IRR will show better results

Pay back period doesn't take into account time value of money but is most easy to calculate and makes the Decision making easier for managers

A discounted payback period uses the time value of money and is preferred therefore.

Practicality of different methods -

Pay Back period is the easiest to use and calculate too. Managers prefer it for this very purpose only. Although it doesn't take time value in account .

IRR is cumbersome and difficult to calculate.

NPV is preferred as it shows the returns in absolute terms, although the problems associated with discounting rate persists.  

ARR is generally avoided as it gives accounting returns and not cash flow returns.

PI is as good as NPV and used along with NPV