Question

Case Study 3--Capital Budgeting
(Comprehensive Spreadsheet Problem 11-23, page 408)
Your division is considering two projects. Its WACC is 10%, and the projects' after-tax cash flows (in millions
of dollars) would be as follows:
		Expected Cash Flows
	Time	Project A	Project B
	0	($30)	($30)
	1	$5	$20
	2	$10	$10
	3	$15	$8
	4	$20	$6
a.   Calculate the projects' NPVs, IRRs, MIRRs, regular paybacks, and discounted paybacks.
WACC =	10%		Use Excel's NPV function as explained in
NPVA =			11model.xlsx. Note that the range does not include
NPVB =			the initial costs, which are added separately.
We find the internal rate of return with Excel's IRR function:
IRRA =
IRRB =
We find the modified internal rate of return with Excel's MIRR function using the 10% WACC:
MIRRA =
MIRRB =
Project A Payback Period:
	Time period:	0	1	2	3
	Cash flow:
	Cumulative cash flow:
PaybackA:
Project B Payback Period:
	Time period:	0	1	2	3
	Cash flow:
	Cumulative cash flow:
PaybackB:
Project A Discounted Payback Period:
	Time period:	0	1	2	3
	Cash flow:
	Disc. cash flow:
	Disc. cum. cash flow:
	Discounted PaybackA:
Project B Discounted Payback Period:
	Time period:	0	1	2	3
	Cash flow:
	Disc. cash flow:
	Disc. cum. cash flow:
	Discounted PaybackB:
b. If the two projects are independent, which project(s) should be chosen?
c. If the two projects are mutually exclusive and the WACC is 10%, which project(s) should be chosen?
d.   Plot NPV profiles for the two projects. Identify the projects' IRRs on the graph.
Hint: Before you can graph the NPV profiles for these projects, you must create a data table of project NPV relative to
differing costs of capital--use Excel's NPV formula and the space below to do so. The graph will automatically create,
as values are added.
	Project A	Project B
	$0.00	$0.00
0.00%
2.00%
4.00%
6.00%
8.00%
10.00%
12.00%
14.00%
16.00%
18.00%
19.19%
20.00%
22.00%
22.52%
24.00%
e. If the WACC was 5%, would this change your recommendation if the projects were mutually exclusive?
If the WACC was 15%, would this change your recommendation? Explain your answers.
f. The "crossover rate" is 13.5252%. Explain what this rate is and how it affects the choice between
mutually exclusive projects.
g. Is it possible for conflicts to exist between the NPV and the IRR when independent projects are being evaluated?
Explain your answer.
h. Now, look at the regular and discounted paybacks. Which project looks better when judged by the paybacks?
i. If the payback was the only method a firm used to accept or reject projects, what payback should it choose
as the cutoff point, that is, reject projects if their paybacks are not below the chosen cutoff? Is your selected
cutoff based on some economic criteria, or is it more or less arbitrary? Are the cutoff criteria equally
arbitrary when firms use the NPV and/or the IRR as the criteria? Explain.
j. Define the MIRR. What's the difference between the IRR and the MIRR, and which generally gives a better
idea of the rate of return on the investment in a project? Explain.
k. Why do most academics and financial executives regard the NPV as being the single best criterion and
better than the IRR? Why do companies still calculate IRRs?

Answer 1

a)

Formula sheet

A	B	C	D	E	F	G	H	I	J
2
3
4			Cash Flows
5		Time	Project A	Project B
6		0	-30	-30
7		1	5	20
8		2	10	10
9		3	15	8
10		4	20	6
11
12		a)
13
14		WACC	0.1
15
16		NPVA	=NPV(D14,D7:D10)+D6
17		NPVB	=NPV(D14,E7:E10)+E6
18
19		IRRA	=IRR(D6:D10)
20		IRRB	=IRR(E6:E10)
21
22		MIRRA	=MIRR(D6:D10,D14,D14)
23		MIRRB	=MIRR(E6:E10,D14,D14)
24
25
26		Project A Payback Period:
27			Time period:	0	1	2	3	4
28			Cash flow:	=D6	=D7	=D8	=D9	=D10
29			Cumulative cash flow:	=E28	=E29+F28	=F29+G28	=G29+H28	=H29+I28
30
31		PaybackA:	3	years
32
33		Project B Payback Period:
34			Time period:	0	1	2	3	4
35			Cash flow:	=E6	=E7	=E8	=E9	=E10
36			Cumulative cash flow:	=E35	=E36+F35	=F36+G35	=G36+H35	=H36+I35
37
38		PaybackB:	2	Years
39
40		Project A Discounted Payback Period:
41			Time period:	0	1	2	3	4
42			Cash flow:	=E28	=F28	=G28	=H28	=I28
43			Disc. cash flow:	=E42/((1+$D$14)^E41)	=F42/((1+$D$14)^F41)	=G42/((1+$D$14)^G41)	=H42/((1+$D$14)^H41)	=I42/((1+$D$14)^I41)
44			Disc. cum. cash flow:	=E43	=E44+F43	=F44+G43	=G44+H43	=H44+I43
45
46			Discounted PaybackA:	=3+(0-H44)/(I44-H44)	Years
47
48		Project B Discounted Payback Period:
49			Time period:	0	1	2	3	4
50			Cash flow:	=E35	=F35	=G35	=H35	=I35
51			Disc. cash flow:	=E50/((1+$D$14)^E49)	=F50/((1+$D$14)^F49)	=G50/((1+$D$14)^G49)	=H50/((1+$D$14)^H49)	=I50/((1+$D$14)^I49)
52			Disc. cum. cash flow:	=E51	=E52+F51	=F52+G51	=G52+H51	=H52+I51
53
54			Discounted PaybackB:	=2+(0-G52)/(H52-G52)	Years
55