Question

1. Find out the NPV, IRR and Payback period for each of the following three projects.

Assume I=6%.                       

                                                                                                            (60 points)

Project	CF0	CF1	CF2	CF3	CF4	CF5
A	-12,000	3000	3000	4000	4000	1000
B	-12,000	4000	4000	3000	3000	1000
C	-12,000	3000	3000	3000	3000	1000
D	-12,000	5000	3000	5000	3000	0
E	-12,000	3000	3000	3000	3000	5000
F	-12,000	0	0	6000	6000	6000

Answer 1

Using excel to calculate NPV

		A	B	C	D	E	F
	Project	CF0	CF1	CF2	CF3	CF4	CF5	NPV	Excel Formula
1	A	-12,000	3000	3000	4000	4000	1000	774.29	NPV(6%,B1:F1)+A1
2	B	-12,000	4000	4000	3000	3000	1000	975.97	NPV(6%,B2:F2)+A2
3	C	-12,000	3000	3000	3000	3000	1000	-857.42	NPV(6%,B3:F3)+A3
4	D	-12,000	5000	3000	5000	3000	0	1961.35	NPV(6%,B4:F4)+A4
5	E	-12,000	3000	3000	3000	3000	5000	2131.61	NPV(6%,B5:F5)+A5
6	F	-12,000	0	0	6000	6000	6000	2273.83	NPV(6%,B6:F6)+A6

		A	B	C	D	E	F
	Project	CF0	CF1	CF2	CF3	CF4	CF5	IRR	Excel Formula
1	A	-12,000	3000	3000	4000	4000	1000	8.49%	IRR(A1:F1)
2	B	-12,000	4000	4000	3000	3000	1000	9.48%	IRR(A2:F2)
3	C	-12,000	3000	3000	3000	3000	1000	3.05%	IRR(A3:F3)
4	D	-12,000	5000	3000	5000	3000	0	13.34%	IRR(A4:F4)
5	E	-12,000	3000	3000	3000	3000	5000	11.84%	IRR(A5:F5)
6	F	-12,000	0	0	6000	6000	6000	10.76%	IRR(A6:F6)

Payback Period Using excel

		A	B	C	D	E	F
	Project	CF0	CF1	CF2	CF3	CF4	CF5
1	A	-12,000	3000	3000	4000	4000	1000
2	Cumulative Cash Flow	-12,000	-9,000	-6,000	-2,000	2,000	3,000
	Payback Period	3.50	(3+2000/4000)
		A	B	C	D	E	F
	Project	CF0	CF1	CF2	CF3	CF4	CF5
1	B	-12,000	4000	4000	3000	3000	1000
2	Cumulative Cash Flow	-12,000	-8,000	-4,000	-1,000	2,000	3,000
	Payback Period	3.33	(3-1000/3000)
		A	B	C	D	E	F
	Project	CF0	CF1	CF2	CF3	CF4	CF5
1	C	-12,000	3000	3000	3000	3000	1000
2	Cumulative Cash Flow	-12,000	-9,000	-6,000	-3,000	0	1,000
	Payback Period	4.00	(4+0)
		A	B	C	D	E	F
	Project	CF0	CF1	CF2	CF3	CF4	CF5
1	D	-12,000	5000	3000	5000	3000	0
2	Cumulative Cash Flow	-12,000	-7,000	-4,000	1,000	4,000	4,000
	Payback Period	2.80	(=2+4000/5000)
		A	B	C	D	E	F
	Project	CF0	CF1	CF2	CF3	CF4	CF5
1	E	-12,000	3000	3000	3000	3000	5000
2	Cumulative Cash Flow	-12,000	-9,000	-6,000	-3,000	0	5,000
	Payback Period	4.00	4
		A	B	C	D	E	F
	Project	CF0	CF1	CF2	CF3	CF4	CF5
1	F	-12,000	0	0	6000	6000	6000
2	Cumulative Cash Flow	-12,000	-12,000	-12,000	-6,000	0	6,000
	Payback Period	4.00	4

Please Discuss in case of Doubt

Best of Luck. God Bless
Please Rate Well