Chapter 8, Problem 11QP

Summary Introduction

To calculate: The rate of crossover for two projects and sketch the NPV profile.

Introduction:

The net present value is one of the capital budgeting techniques, which is used to identify the profitability in the proposed investment. The internal rate of return is a rate of discount that makes the predictable investment’s NPV equals zero.

Answer to Problem 11QP

The crossover rate for the two projects is 11.95%. The NPV profiles shows that both projects have a higher NPV for the rate of discount below 11.95% and have a lower NPV for the rate above 11.95%.

Explanation of Solution

Given information:

The details of 2 projects are provided. The cash flows of Project X for year 1, year 2, and year 3, are $10,800, $8,630, and $5,210 respectively. The initial investment is -$19,000. The Project Y cash flows for 4 years are $6,840, $9,410, $9,300 respectively and the initial investment is -$19,000.

Note:

• NPV is the difference between the present values of the cash inflows and the present value of the cash outflows.
• The IRR is the rate of interest, which makes the project’s NPV equals zero. Hence, using the available information, assume that NPV is equal to zero and form an equation to compute the IRR.

Equation of NPV to compute IRR assuming that NPV is equal to zero:

$\begin{array}{l}\text{NPV}=0=-\$19,000+\frac{\$10,800}{\left(1+\text{IRR}\right)}+\frac{\$8,630}{{\left(1+\text{IRR}\right)}^2}+\frac{\$5,210}{{\left(1+\text{IRR}\right)}^3}\\ 0=-\$19,000+\frac{\$10,800}{\left(1+\text{IRR}\right)}+\frac{\$8,630}{{\left(1+\text{IRR}\right)}^2}+\frac{\$5,210}{{\left(1+\text{IRR}\right)}^3}\end{array}$

Compute IRR for Project X using a spreadsheet:

Step 1:

• Type the equation of NPV in H6 in the spreadsheet and consider the IRR value as H7.

Step 2:

• Assume the IRR value as 10%.

Step 3:

• In the spreadsheet, go to data and select the What-if analysis
• In What-if analysis, select Goal Seek.
• In “Set cell”, select H6 (the formula).
• The “To value” is considered as 0 (the assumption value for NPV).
• The H7 cell is selected for “By changing cell”.

Step 4:

• Following the previous step, click OK in the Goal Seek Status. The Goal Seek Status appears with the IRR value.

Step 5:

• The value appears to be 16.2231415880947%.

Hence, the IRR value is 16.22%.

Equation of NPV to compute IRR assuming that NPV is equal to zero:

$\begin{array}{l}\text{NPV}=0=-\$19,000+\frac{\$6,840}{\left(1+\text{IRR}\right)}+\frac{\$9,410}{{\left(1+\text{IRR}\right)}^2}+\frac{\$9,300}{{\left(1+\text{IRR}\right)}^3}\\ 0=-\$19,000+\frac{\$6,840}{\left(1+\text{IRR}\right)}+\frac{\$9,410}{{\left(1+\text{IRR}\right)}^2}+\frac{\$9,300}{{\left(1+\text{IRR}\right)}^3}\end{array}$

Compute IRR for Project Y using a spreadsheet:

Step 1:

• Type the equation of NPV in H6 in the spreadsheet and consider the IRR value as H7.

Step 2:

• Assume the IRR value as 10%.

Step 3:

• In the spreadsheet, go to data and select the What-if analysis.
• In What-if analysis, select Goal Seek.
• In “Set cell”, select H6 (the formula).
• The “To value” is considered as 0 (the assumption value for NPV).
• The H7 cell is selected for “By changing cell”.

Step 4:

• Following the previous step, click OK in the Goal Seek Status. The Goal Seek Status appears with the IRR value.

Step 5:

• The value appears to be 15.5355995360623%.

Hence, the IRR value is 15.53%.

Formula to compute the crossover rate:

$\text{Crossover rate for each year}=\text{Cash flows from project X}-\text{Cash flows from project Y}$

Equation of crossover rate to compute R:

$0=\frac{\$3,960}{\left(1+R\right)}-\frac{-\$780}{{\left(1+R\right)}^2}-\frac{-\$4,090}{{\left(1+R\right)}^3}$

Where,

“R” denotes the crossover rate.

Compute R using a spreadsheet:

Step 1:

• Type the equation of NPV in H6 in the spreadsheet and consider the IRR value as H7.

Step 2:

• Assume the IRR value as 10%.

Step 3:

• In the spreadsheet, go to data and select the What-if analysis.
• In What-if analysis, select Goal Seek.
• In “Set cell”, select H6 (the formula).
• The “To value” is considered as 0 (the assumption value for NPV).
• The H7 cell is selected for “By changing cell”.

Step 4:

• Following the previous step, click OK in the Goal Seek Status. The Goal Seek Status appears with the IRR value.

Step 5:

• The value appears to be 11.9527226931838%.

Hence, the R-value is 11.95%.

Formula to calculate the NPV:

$\text{NPV}=\text{Present value of <x-custom-btb-me data-me-id="2578" class="microExplainerHighlight">cash inflow</x-custom-btb-me>}-\text{Present value of <x-custom-btb-me data-me-id="2612" class="microExplainerHighlight">cash outflow</x-custom-btb-me>}$

Note: As the discount rate is over a range of 0% to 25%, calculate NPV for 0%, 5%, 10%, 15%, 20%, and 25%.

Compute the NPV with the discount rate of 0% for Project X:

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$10,800}{\left(1+0\right)}+\frac{\$8,630}{{\left(1+0\right)}^2}+\frac{\$5,210}{{\left(1+0\right)}^3}\right)-\$19,000\\ =\$5,640\end{array}$

Compute the NPV with the discount rate of 0% for Project Y:

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$6,840}{\left(1+0\right)}+\frac{\$9,410}{{\left(1+0\right)}^2}+\frac{\$9,300}{{\left(1+0\right)}^3}\right)-\$19,000\\ =\$6,550\end{array}$

Hence, the NPV for Project X and Y at 0% are $5,640 and $6,550 respectively.

Compute the NPV with the discount rate of 5% for Project X:

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$10,800}{\left(1+0.05\right)}+\frac{\$8,630}{{\left(1+0.05\right)}^2}+\frac{\$5,210}{{\left(1+0.05\right)}^3}\right)-\$19,000\\ =\$10,285.71429+\$7,827.664399+\$4,500.593888-\$19,000\\ =\$3,613.972\end{array}$

Compute the NPV with the discount rate of 5% for Project Y:

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$6,840}{\left(1+0.05\right)}+\frac{\$9,410}{{\left(1+0.05\right)}^2}+\frac{\$9,300}{{\left(1+0.05\right)}^3}\right)-\$19,000\\ =6,514.285714+\$8,535.147392+\$8,033.689666-\$19,000\\ =\$4,083.12\end{array}$

Hence, the NPV for Project X and Y at 5% are $3,613.972 and $4,083.12 respectively.

Compute the NPV with the discount rate of 10% for Project X:

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$10,800}{\left(1+0.10\right)}+\frac{\$8,630}{{\left(1+0.10\right)}^2}+\frac{\$5,210}{{\left(1+0.10\right)}^3}\right)-\$19,000\\ =\$9,818.181818+\$7,132.231405+\$3,914.350113-\$19,000\\ =\$1,864.76\end{array}$

Compute the NPV with the discount rate of 10% for Project Y:

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$6,840}{\left(1+0.10\right)}+\frac{\$9,410}{{\left(1+0.10\right)}^2}+\frac{\$9,300}{{\left(1+0.10\right)}^3}\right)-\$19,000\\ =\$6,218.181818+\$7,776.859504+\$6,987.227648-\$19,000\\ =\$1,982.26\end{array}$

Hence, the NPV for Project X and Y at 10% are $1,864.76 and $1,982.26 respectively.

Compute the NPV with the discount rate of 15% for Project X:

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$10,800}{\left(1+0.15\right)}+\frac{\$8,630}{{\left(1+0.15\right)}^2}+\frac{\$5,210}{{\left(1+0.15\right)}^3}\right)-\$19,000\\ =\$9,391.304348+\$6,525.519849+\$3,425.659571-\$19,000\\ =\$342.48\end{array}$

Compute the NPV with the discount rate of 15% for Project Y:

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$6,840}{\left(1+0.15\right)}+\frac{\$9,410}{{\left(1+0.15\right)}^2}+\frac{\$9,300}{{\left(1+0.15\right)}^3}\right)-\$19,000\\ =\$5,947.826087+\$7,115.311909+\$6,114.900962-\$19,000\\ =178.03\end{array}$

Hence, the NPV for Project X and Y at 15% are $342.48 and $178.03 respectively.

Compute the NPV with the discount rate of 20% for Project X:

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$10,800}{\left(1+0.20\right)}+\frac{\$8,630}{{\left(1+0.20\right)}^2}+\frac{\$5,210}{{\left(1+0.20\right)}^3}\right)-\$19,000\\ =\$9,000+\$5,993.055556+\$3,015.046296-\$19,000\\ =-\$991.89\end{array}$

Compute the NPV with the discount rate of 20% for Project Y:

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$6,840}{\left(1+0.20\right)}+\frac{\$9,410}{{\left(1+0.20\right)}^2}+\frac{\$9,300}{{\left(1+0.20\right)}^3}\right)-\$19,000\\ \$5,700+\$6,534.722222+\$5,381.944444-\$19,000\\ =-\$1,383.33\end{array}$

Hence, the NPV for Project X and Y at 20% are -$991.89 and -$1,383.33 respectively.

Compute the NPV with the discount rate of 25% for Project X:

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$10,800}{\left(1+0.25\right)}+\frac{\$8,630}{{\left(1+0.25\right)}^2}+\frac{\$5,210}{{\left(1+0.25\right)}^3}\right)-\$19,000\\ =\$8,640+\$5,523.2+\$2,667.52-\$19,000\\ =-\$2,169.28\end{array}$

Compute the NPV with the discount rate of 25% for Project Y:

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$6,840}{\left(1+0.25\right)}+\frac{\$9,410}{{\left(1+0.25\right)}^2}+\frac{\$9,300}{{\left(1+0.25\right)}^3}\right)-\$19,000\\ =\$5,472+\$6,022.4+\$4,761.6-\$19,000\\ =-\$2,744\end{array}$

Hence, the NPV for Project X and Y at 25% are -$2,169.28 and -$2,744 respectively.

NPV profile: