CFIN6
Spreadsheet Problem Solution
Chapter 9
1. a. – c(1). r = 13%
INPUT DATA:
KEY OUTPUT:
Expected Cash Flows
Year
Proj. A
Proj. B
Proj. A
Proj. B
0
(45,000)
(50,000)
2,600
2,758
1
(20,000)
15,000
14.28%
15.24%
2
11,000
15,000
3
20,000
15,000
Crossover rate
12.81%
4
30,000
15,000
5
45,000
15,000
Required return
MODEL-GENERATED DATA:
NPV profile:
Proj. A
Proj. B
r
NPV
NPV
0.00%
41,000
25,000
3.00%
29,726
18,696
6.00%
20,104
13,185
9.00%
11,853
CFIN6
If r = 13%, Project B should be purchased, because its NPV is greater than Project A’s NPV.
c(2) r = 9%
INPUT DATA:
KEY OUTPUT:
Expected Cash Flows
Year
Proj. A
Proj. B
Proj. A
Proj. B
0
(45,000)
(50,000)
NPV
11,853
8,345
1
(20,000)
15,000
IRR
14.28%
15.24%
2
11,000
15,000
3
20,000
15,000
Crossover rate
12.81%
5
45,000
15,000
Required return
c(3) r = 15%
INPUT DATA:
KEY OUTPUT:
Expected Cash Flows
Year
Proj. A
Proj. B
Proj. A
Proj. B
0
(45,000)
(50,000)
NPV
–1,398
282
1
(20,000)
15,000
2
11,000
15,000
3
20,000
15,000
Crossover rate
12.81%
4
30,000
15,000
5
45,000
15,000
IRR
14.28%
15.24%
Required return
15.00%
If r = 15%, Project B should be purchased, because its NPV is positive.
d. The crossover rate is determined by first calculating the difference between the cash flows of each
CFIN6
Crossover rate calculation:
Expected Cash Flows
Difference
Year
Proj. A
Proj. B
in CFs
0
(45,000)
(50,000)
5,000
1
(20,000)
15,000
-35,000
2
11,000
15,000
3
20,000
15,000
5,000
4
30,000
15,000
15,000
5
45,000
15,000
30,000
Crossover rate = IRR of diff. CF
12.81%
e. CF5 = $40,000
INPUT DATA:
KEY OUTPUT:
Expected Cash Flows
Year
Proj. A
Proj. B
Proj. A
Proj. B
0
(45,000)
(50,000)
NPV
–114
2,758
1
(20,000)
15,000
IRR
12.94%
15.24%
2
11,000
15,000
3
20,000
15,000
Crossover rate
4
30,000
15,000
5
40,000
15,000
Required return
13.00%
CF5 = $50,000
INPUT DATA:
KEY OUTPUT:
Expected Cash Flows
Year
Proj. A
Proj. B
Proj. A
Proj. B
0
(45,000)
(50,000)
NPV
5,314
2,758
1
(20,000)
15,000
IRR
15.55%
15.24%
2
11,000
15,000
3
20,000
15,000
Crossover rate
16.01%
4
30,000
15,000
5
50,000
15,000
Required return
13.00%