Question

You are a financial analyst for the Hittle Company. The director of capital budgeting
has asked you to analyze two proposed capital investments, Projects X and Y. Each
project has a cost of $10,000, and the cost of capital for each is 12%. The projects’
expected net cash flows are as follows:
Expected Net Cash Flows
Year Project X Project Y
0 ?$10,000 ?$10,000
1 6,500 3,500
2 3,000 3,500
3 3,000 3,500
4 1,000 3,500
a. Calculate each project’s payback period, net present value (NPV), internal rate
of return (IRR), modified internal rate of return (MIRR), and profitability
index (PI).
b. Which project or projects should be accepted if they are independent?
c. Which project should be accepted if they are mutually exclusive?
d. How might a change in the cost of capital produce a conflict between the NPV
and IRR rankings of these two projects? Would this conflict exist if r were 5%?
(Hint: Plot the NPV profiles.)
e. Why does the conflict exist

Answer 1

A	B	C	D	E	F	G	H	I	J
2
3		a)
4		Payback Period, NPV, IRR and MIRR Calculation for Project X:
5
6		Cash Flow for Project X is as follows:
7		Year	0	1	2	3	4
8		Cash Flow	($10,000)	$6,500	$3,000	$3,000	$1,000
9
10		Calculation of Payback period:
11		Payback period is the period when investment amount is recovered.
12		Year	0	1	2	3	4
13		Free Cash Flow	($10,000)	$6,500	$3,000	$3,000	$1,000
14		Cumulative cash flow	($10,000)	($3,500)	($500)	$2,500	$3,500
15
16		Payback period is when cumulative free cash flow becomes zero.
17		It can be seen from above that cumulative cash flow becomes zero between year 2 and year 3.
18
19		To estimate the exact payback period cumulative free cash flow can be proprated over the years as follows:
20		Payback period	2.17	=F12+(0-F14)/(G14-F14)
21
22		Hence Payback period is	2.17	Years
23
24
25
26		Given the following cash flow and WACC, NPV for the project can be calculated as follows:
27		Year	0	1	2	3	4
28		Incremental Cash Flow	($10,000)	$6,500	$3,000	$3,000	$1,000
29		MARR (i)	12%
30		(P/F,i,n) for each year		0.89	0.80	0.71	0.64
31		Present Value of cash flows = FCF*(P/F,i,n)		$5,803.57	$2,391.58	$2,135.34	$635.52
32		Present value if future cash flows	$10,966.01	=SUM(E31:H31)
33
34		NPV for Project	=Present value fo future cash flows - Initial investment
35			$966.01	=D32+D28
36
37		Hence NPV for Project	$966.01
38
39		Calculation of IRR:
40		IRR is the rate at which NPV of the project will be zero.
41		Given the following cash flow IRR can be calculated as below:
42
43		Year	0	1	2	3	4
44		Incremental Cash Flow	($10,000)	$6,500	$3,000	$3,000	$1,000
45
46		NPV=-10000+6500/(1+IRR)^1 +3000/(1+IRR)^2+3000/(1+IRR)^3+1000/(1+IRR)^4
47		0=-10000+6500/(1+IRR)^1 +3000/(1+IRR)^2+3000/(1+IRR)^3+1000/(1+IRR)^4
48		IRR can be found using hit and trial method for above equation.
49
50		IRR can also be found using IRR function in excel as follows:
51		Year	0	1	2	3	4
52		Incremental Cash Flow	($10,000)	$6,500	$3,000	$3,000	$1,000
53		IRR	18.03%	=IRR(D52:H52)
54
55		Inremental IRR of the project	18.03%
56
57		Calculation of MIRR:
58		MIRR is the rate at which PV of cash outflows is equal to the PV of FV of cash inflows.
59		Project term	4	years
60		Incremental cash flows:
61		Year	0	1	2	3	4
62		Incremental Cash Flow	($10,000)	$6,500	$3,000	$3,000	$1,000
63
64		PV of cash outflow	$10,000
65
66		Calculation of Future Value of cash inflows
67		Year	0	1	2	3	4
68		Incremental Cash Flow		$6,500	$3,000	$3,000	$1,000
69		WACC	12%
70		Future Value of cash inflows		$9,132	$3,763	$3,360	$1,000
71		Total FV of cash inflows	$17,255
72
73		Let r be the MIRR then,
74		PV of cash outflow*(1+r)4=FV of cash inflow
75		10000*(1+r)4=17255
76
77		Solving the above equation:
78		r =	14.61%
79
80		Hence MIRR is	14.61%
81
82		Calculation of Profitability Index:
83
84		Profitability index is given by following formula:
85		Profitability index = Present value of future cash flows / Initial Investment
86		or
87		Profitability index = (NPV+Initial Investment) / Initial Investment
88
89		Using the following data:
90		NPV	$966.01
91		Initial Investment	$10,000
92
93		Profitability index	= (NPV+Initial Investment) / Initial Investment
94			1.10	=(D90+D91)/D91
95
96		Hence profitability index of project X is	1.10
97
98		Payback Period, NPV, IRR and MIRR Calculation for Project Y
99
100		Cash Flow for Project Y is as follows:
101		Year	0	1	2	3	4
102		Cash Flow	($10,000)	$3,500	$3,500	$3,500	$3,500
103
104		Calculation of Payback period:
105		Payback period is the period when investment amount is recovered.
106		Year	0	1	2	3	4
107		Free Cash Flow	($10,000)	$3,500	$3,500	$3,500	$3,500
108		Cumulative cash flow	($10,000)	($6,500)	($3,000)	$500	$4,000
109
110		Payback period is when cumulative free cash flow becomes zero.
111		It can be seen from above that cumulative cash flow becomes zero between year 2 and year 3.
112
113		To estimate the exact payback period cumulative free cash flow can be proprated over the years as follows:
114		Payback period	2.86	=F106+(0-F108)/(G108-F108)
115
116		Hence Payback period is	2.86	Years
117
118		Given the following cash flow and WACC, NPV for the project can be calculated as follows:
119		Year	0	1	2	3	4
120		Incremental Cash Flow	($10,000)	$3,500	$3,500	$3,500	$3,500
121		MARR (i)	12%
122		(P/F,i,n) for each year		0.89	0.80	0.71	0.64
123		Present Value of cash flows = FCF*(P/F,i,n)		$3,125.00	$2,790.18	$2,491.23	$2,224.31
124		Present value if future cash flows	$10,630.72	=SUM(E123:H123)
125
126		NPV for Project	=Present value fo future cash flows - Initial investment
127			$630.72	=D124+D120
128
129		Hence NPV for Project	$630.72
130
131		Calculation of IRR:
132		IRR is the rate at which NPV of the project will be zero.
133		Given the following cash flow IRR can be calculated as below:
134
135		Year	0	1	2	3	4
136		Incremental Cash Flow	($10,000)	$3,500	$3,500	$3,500	$3,500
137
138		NPV=-10000+3500/(1+IRR)^1 +3500/(1+IRR)^2+3500/(1+IRR)^3+3500/(1+IRR)^4
139		0=-10000+3500/(1+IRR)^1 +3500/(1+IRR)^2+3500/(1+IRR)^3+3500/(1+IRR)^4
140		IRR can be found using hit and trial method for above equation.
141
142		IRR can also be found using IRR function in excel as follows:
143		Year	0	1	2	3	4
144		Incremental Cash Flow	($10,000)	$3,500	$3,500	$3,500	$3,500
145		IRR	14.96%	=IRR(D144:H144)
146
147		Inremental IRR of the project	14.96%
148
149		Calculation of MIRR:
150		MIRR is the rate at which PV of cash outflows is equal to the PV of FV of cash inflows.
151		Project term	4	years
152		Incremental cash flows:
153		Year	0	1	2	3	4
154		Incremental Cash Flow	($10,000)	$3,500	$3,500	$3,500	$3,500
155
156		PV of cash outflow	$10,000
157
158		Calculation of Future Value of cash inflows
159		Year	0	1	2	3	4
160		Incremental Cash Flow		$3,500	$3,500	$3,500	$3,500
161		WACC	12%
162		Future Value of cash inflows		$4,917	$4,390	$3,920	$3,500
163		Total FV of cash inflows	$16,728	=SUM(E162:H162)
164
165		Let r be the MIRR then,
166		PV of cash outflow*(1+r)4=FV of cash inflow
167		10000*(1+r)4=16728
168
169		Solving the above equation:
170		r =	13.73%	=((D163/D156)^(1/D151))-1
171
172		Hence MIRR of Project Y is	13.73%
173
174
175		Calculation of Profitability Index:
176
177		Profitability index is given by following formula:
178		Profitability index = Present value of future cash flows / Initial Investment
179		or
180		Profitability index = (NPV+Initial Investment) / Initial Investment
181
182		Using the following data:
183		NPV	$630.72
184		Initial Investment	$10,000
185
186		Profitability index	= (NPV+Initial Investment) / Initial Investment
187			1.06	=(D183+D184)/D184
188
189		Hence profitability index of project Y is	1.06
190
191		b)
192
193		Since NPV of project X and Y both are positive and independent therefore both projects can be accepted.
194
195		c)
196
197		Since both project are mutually exlusive, therefore only one project can be selected.
198		As profitability index shows the profitability of the project and it is higher for project X,
199		therefore project X should be selected.
200