Chapter 9, Problem 13QP

Summary Introduction

To calculate: The rate of crossover for two projects.

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, which makes the predictable investment’s NPV equals zero.

Answer to Problem 13QP

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

Explanation of Solution

Given information:

The details of two projects are provided. The cash flows of project X for year 1, year 2, and year 3, are $8,850, $9,100, and $8,800 respectively. The initial investment is $20,000. The cash flows of project Y for 3 years are $10,100, $7,800, $8,700 respectively, and the initial investment is $20,000.

Note:

• NPV is the difference between the present values of the cash inflows and the present values of cash outflows.
• The IRR is a rate of interest, which makes a 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=-\$20,000+\frac{\$8,850}{\left(1+\text{IRR}\right)}+\frac{\$9,100}{{\left(1+\text{IRR}\right)}^2}+\frac{\$8,800}{{\left(1+\text{IRR}\right)}^3}\\ 0=-\$20,000+\frac{\$8,850}{\left(1+\text{IRR}\right)}+\frac{\$9,100}{{\left(1+\text{IRR}\right)}^2}+\frac{\$8,800}{{\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 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.091166038864%.

Hence, the IRR value is 16.09%.

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 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.2429872464063%.

Hence, the IRR value is 16.24%.

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{-\$1,250}{\left(1+R\right)}+\frac{\$1,300}{{\left(1+R\right)}^2}+\frac{\$100}{{\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 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.1945910036974%.

Hence, the R-value is 11.19%.

Formula to calculate the NPV:

$\text{NPV}=\text{<x-custom-btb-me data-me-id="2507" class="microExplainerHighlight">Present value</x-custom-btb-me> 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{\$8,850}{\left(1+0\right)}+\frac{\$9,100}{{\left(1+0\right)}^2}+\frac{\$8,800}{{\left(1+0\right)}^3}\right)-\$20,000\\ =\$6,750\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{\$10,100}{\left(1+0\right)}+\frac{\$7,800}{{\left(1+0\right)}^2}+\frac{\$8,700}{{\left(1+0\right)}^3}\right)-\$20,000\\ =\$6,600\end{array}$

Hence, the NPVs for projects X and Y @ 0% are $6,750 and $6,600 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{\$8,850}{\left(1+0.05\right)}+\frac{\$9,100}{{\left(1+0.05\right)}^2}+\frac{\$8,800}{{\left(1+0.05\right)}^3}\right)-\$20,000\\ =\$8,428.571429+\$8,253.968254+\$7,601.770867-\$20,000\\ =\$4,284.31\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{\$10,100}{\left(1+0.05\right)}+\frac{\$7,800}{{\left(1+0.05\right)}^2}+\frac{\$8,700}{{\left(1+0.05\right)}^3}\right)-\$20,000\\ =\$9,619.047619+\$7,074.829932+\$7,515.387107-\$20,000\\ =\$4,209.26\end{array}$

Hence, the NPVs for projects X and Y @ 5% are $4.284.31 and $4,209.26 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{\$8,850}{\left(1+0.10\right)}+\frac{\$9,100}{{\left(1+0.10\right)}^2}+\frac{\$8,800}{{\left(1+0.10\right)}^3}\right)-\$20,000\\ =\$8,045.454545+\$7,520.661157+\$6,611.570248-\$20,000\\ =\$2,177.69\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{\$10,100}{\left(1+0.10\right)}+\frac{\$7,800}{{\left(1+0.10\right)}^2}+\frac{\$8,700}{{\left(1+0.10\right)}^3}\right)-\$20,000\\ =\$9,181.818182+\$6,446.280992+\$6,536.438768-\$20,000\\ =\$2,164.54\end{array}$

Hence, the NPVs for projects X and Y @ 10% are $2,177.69 and $2,164.54 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{\$8,850}{\left(1+0.15\right)}+\frac{\$9,100}{{\left(1+0.15\right)}^2}+\frac{\$8,800}{{\left(1+0.15\right)}^3}\right)-\$20,000\\ =\$7,695.652174+\$6,880.907372+\$5,786.142845-\$20,000\\ =\$362.70\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{\$10,100}{\left(1+0.15\right)}+\frac{\$7,800}{{\left(1+0.15\right)}^2}+\frac{\$8,700}{{\left(1+0.15\right)}^3}\right)-\$20,000\\ =\$8,782.608696+\$5,897.920605+\$5,720.391222-\$20,000\\ =\$400.92\end{array}$

Hence, the NPVs for projects X and Y @ 15% are $362.70 and $400.92 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{\$8,850}{\left(1+0.20\right)}+\frac{\$9,100}{{\left(1+0.20\right)}^2}+\frac{\$8,800}{{\left(1+0.20\right)}^3}\right)-\$20,000\\ =\$7,375+\$6,319.444444+\$5,092.592593-\$20,000\\ =-\$1,212.96\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{\$10,100}{\left(1+0.20\right)}+\frac{\$7,800}{{\left(1+0.20\right)}^2}+\frac{\$8,700}{{\left(1+0.20\right)}^3}\right)-\$20,000\\ =\$8,416.666667+\$5,416.666667+\$5,034.722222-\$20,000\\ =-\$1,131.94\end{array}$

Hence, the NPVs for projects X and Y @ 20% are -$1,212.96 and -$1,131.94 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{\$8,850}{\left(1+0.25\right)}+\frac{\$9,100}{{\left(1+0.25\right)}^2}+\frac{\$8,800}{{\left(1+0.25\right)}^3}\right)-\$20,000\\ =\$7,080+\$5,824+\$4,505.6-\$20,000\\ =-\$2,590.4\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{\$10,100}{\left(1+0.25\right)}+\frac{\$7,800}{{\left(1+0.25\right)}^2}+\frac{\$8,700}{{\left(1+0.25\right)}^3}\right)-\$20,000\\ =\$8,080+\$4,992+\$4,454.4-\$20,000\\ =-\$2,473.6\end{array}$

Hence, the NPVs for projects X and Y at 25% are -$2,590.4 and -$2,473.6 respectively.