FIN 534
lenci104Ch10P23BuildaModel.xlsx
Build a Model
Solution | 3/18/15 | |||||||
Chapter: | 10 | |||||||
Problem: | 23 | |||||||
Gardial Fisheries is considering two mutually exclusive investments. The projects' expected net cash flows are as follows: | ||||||||
Expected Net Cash Flows | ||||||||
Time | Project A | Project B | ||||||
0 | ($375) | ($575) | ||||||
1 | ($300) | $190 | ||||||
2 | ($200) | $190 | ||||||
3 | ($100) | $190 | ||||||
4 | $600 | $190 | ||||||
5 | $600 | $190 | ||||||
6 | $926 | $190 | ||||||
7 | ($200) | $0 | ||||||
a. If each project's cost of capital is 12%, which project should be selected? If the cost of capital is 18%, what project is the proper choice? | ||||||||
@ 12% cost of capital | @ 18% cost of capital | |||||||
Use Excel's NPV function as explained in this chapter's Tool Kit. Note that the range does not include the costs, which are added separately. | ||||||||
WACC = | 12% | WACC = | 18% | |||||
NPV A = |
DII Labs: Net Present Value of "A" discounted at a WACC of 12% | NPV A = | ||||||
DII Labs: Net Present Value of "A" discounted at a WACC of 18% | NPV B = | NPV B = | ||||||
At a cost of capital of 12%, Project A should be selected. However, if the cost of capital rises to 18%, then the choice is reversed, and Project B should be accepted. | ||||||||
b. Construct NPV profiles for Projects A and B. | ||||||||
Before we can graph the NPV profiles for these projects, we must create a data table of project NPVs relative to differing costs of capital. | ||||||||
Project A | Project B | |||||||
0% | ||||||||
2% | ||||||||
4% | ||||||||
6% | ||||||||
8% | ||||||||
10% | ||||||||
12% | ||||||||
14% | ||||||||
16% | ||||||||
18% | ||||||||
20% | ||||||||
22% | ||||||||
24% | ||||||||
26% | ||||||||
28% | ||||||||
30% | ||||||||
c. What is each project's IRR? | ||||||||
We find the internal rate of return with Excel's IRR function: | ||||||||
IRR A = | Note in the graph above that the X-axis intercepts are equal to the two projects' IRRs. | |||||||
IRR B = | ||||||||
d. What is the crossover rate, and what is its significance? | ||||||||
Cash flow | ||||||||
Time | differential | |||||||
0 | ||||||||
DII Labs: The difference in cash flows between Project "A" and Project "B". | 1 | |||||||
2 | Crossover rate = | |||||||
DII Labs: The IRR for the Cash Flow Differential | 3 | |||||||
4 | The crossover rate represents the cost of capital at which the two projects value, at a cost of capital of 13.14% is: have the same net present value. In this scenario, that common net present | |||||||
5 | ||||||||
6 | ||||||||
7 | ||||||||
e. What is each project's MIRR at a cost of capital of 12%? At r = 18%? Hint: note that B is a 6-year project. | ||||||||
@ 12% cost of capital | @ 18% cost of capital | |||||||
MIRR A = |
DII Labs: Use Excel's MIRR function | MIRR A = | ||||||
MIRR B = | MIRR B = | |||||||
f. What is the regular payback period for these two projects? | ||||||||
Project A | ||||||||
Time period | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
Cash flow | (375) | (300) | (200) | (100) | 600 | $600 | $926 | ($200) |
Cumulative cash flow | ||||||||
Intermediate calculation for payback | ||||||||
Payback using intermediate calculations | ||||||||
Project B | ||||||||
Time period | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
Cash flow | ||||||||
Cumulative cash flow | ||||||||
Intermediate calculation for payback | ||||||||
Payback using intermediate calculations | ||||||||
Payback using PERCENTRANK | Ok because cash flows follow normal pattern. | |||||||
g. At a cost of capital of 12%, what is the discounted payback period for these two projects? | ||||||||
WACC = | 12% | |||||||
Project A | ||||||||
Time period | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
Cash flow | ||||||||
Disc. cash flow | ||||||||
Disc. cum. cash flow | ||||||||
Intermediate calculation for payback | ||||||||
Payback using intermediate calculations | ||||||||
Project B | ||||||||
Time period | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
Cash flow | ||||||||
Disc. cash flow | ||||||||
Disc. cum. cash flow | ||||||||
Intermediate calculation for payback | ||||||||
Payback using intermediate calculations | ||||||||
Discounted Payback using PERCENTRANK | Ok because cash flows follow normal pattern. | |||||||
h. What is the profitability index for each project if the cost of capital is 12%? | ||||||||
PV of future cash flows for A: | ||||||||
PI of A: | ||||||||
PV of future cash flows for B: | ||||||||
PI of B: | ||||||||