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 equal to zero.

Answer to Problem 13QP

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

Explanation of Solution

Given information:

The details of two projects are provided. The project X cash that flows for year 1, year 2, and year 3, are $10,620, $10,900, and $10,500 respectively. The initial investment is $24,000. The project Y cash that flows for 4 years are $12,100, $9,360, $10,400 respectively and the initial investment is $24,000.

Note:

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

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

$\begin{array}{l}\text{NPV}=0=-\$24,000+\frac{\$10,600}{\left(1+\text{IRR}\right)}+\frac{\$10,900}{{\left(1+\text{IRR}\right)}^2}+\frac{\$10,500}{{\left(1+\text{IRR}\right)}^3}\\ 0=-\$24,000+\frac{\$10,600}{\left(1+\text{IRR}\right)}+\frac{\$10,900}{{\left(1+\text{IRR}\right)}^2}+\frac{\$10,500}{{\left(1+\text{IRR}\right)}^3}\end{array}$

Compute IRR for the project X using a spreadsheet:

Step 1:

• Type the equation of NPV in H6 of 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 a set cell, select H6 (the Formula).
• The “To value” is considered as 0 (the assumption value for NPV).
• The H7 cell is selected for the 'by changing the cell'.

Step 4:

• Following the previous step click OK in the goal seek. The goal seek status appears with the IRR value

Step 5:

• The value appears to be 15.9555848576331%

Hence, the IRR value is 15.96%.

Compute the IRR for project Y using a spreadsheet:

Step 1:

• Type the equation of NPV in H6 of 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 a set cell, select H6 (the Formula).
• The “To value” is considered as 0 (the assumption value for NPV).
• The H7 cell is selected for the 'by changing the cell'.

Step 4:

• Following the previous step click OK in the goal seek. The goal seek status appears with the IRR value.

Step 5:

• The value appears to be 16.1307523542427%.

Hence, the IRR value is 16.13%.

Formula to compute the crossover rate is as follows:

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

Equation of the crossover rate to compute R is as follows:

$0=\frac{-\$1,480}{\left(1+R\right)}+\frac{\$1,540}{{\left(1+R\right)}^2}+\frac{\$100}{{\left(1+R\right)}^3}$

Where,

“R” denotes crossover rate.

Compute R by using a spreadsheet:

Step 1:

• Type the equation of NPV in H6 of 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 whatif 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 the by changing cell

Step 4:

• Following the previous step click OK in the goal seek. The goal seek status appears with the IRR value.

Step 5:

• The value appears to be 10.1861807452249%

Hence, the R-value is 10.19%.

Formula to calculate the NPV is as follows:

$\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 the project X:

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$10,620}{\left(1+0\right)}+\frac{\$10,900}{{\left(1+0\right)}^2}+\frac{\$10,500}{{\left(1+0\right)}^3}\right)-\$24,000\\ =\$8,020\end{array}$

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

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$12,100}{\left(1+0\right)}+\frac{\$9,360}{{\left(1+0\right)}^2}+\frac{\$10,400}{{\left(1+0\right)}^3}\right)-\$24,000\\ =\$7,860\end{array}$

Hence, the NPV for the projects X and Y at the rate of 0% is $8,020 and $7,860 respectively.

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

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$10,620}{\left(1+0.05\right)}+\frac{\$10,900}{{\left(1+0.05\right)}^2}+\frac{\$10,500}{{\left(1+0.05\right)}^3}\right)-\$24,000\\ =\$10,114.2857142857+\$9,886.62131519274+\$9,070.29478458049-\$24,000\\ =\$5071.20\end{array}$

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

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$12,100}{\left(1+0.05\right)}+\frac{\$9,360}{{\left(1+0.05\right)}^2}+\frac{\$10,400}{{\left(1+0.05\right)}^3}\right)-\$24,000\\ =\$11,523.8095238095+\$8,489.79591836734+\$8,983.91102472735-\$24,000\\ =\$4,997.52\end{array}$

Hence, the NPV for the projects X and Y at 5% is $5071.20 and $4,997.52 respectively.

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

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$10,620}{\left(1+0.10\right)}+\frac{\$10,900}{{\left(1+0.10\right)}^2}+\frac{\$10,500}{{\left(1+0.10\right)}^3}\right)-\$24,000\\ =\$9654.54545454545+\$9008.26446280991+\$7888.80540946656-\$24,000\\ =\$2,551.62\end{array}$

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

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$12,100}{\left(1+0.10\right)}+\frac{\$9,360}{{\left(1+0.10\right)}^2}+\frac{\$10,400}{{\left(1+0.10\right)}^3}\right)-\$24,000\\ =\$11,000+\$7735.53719008264+\$7813.67392937640-\$24,000\\ =\$2,549.21\end{array}$

Hence, the NPV for the projects X and Y at the rate of 10% is $2,551.62 and $2,549.21 respectively.

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

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$10,620}{\left(1+0.15\right)}+\frac{\$10,900}{{\left(1+0.15\right)}^2}+\frac{\$10,500}{{\left(1+0.15\right)}^3}\right)-\$24,000\\ =\$9,234.78260869565+\$8,241.96597353497+\$6,903.92044053587-\$24,000\\ =\$380.67\end{array}$

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

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$12,100}{\left(1+0.15\right)}+\frac{\$9,360}{{\left(1+0.15\right)}^2}+\frac{\$10,400}{{\left(1+0.15\right)}^3}\right)-\$24,000\\ =\$10,521.7391304347+\$7,077.50472589792+\$6,838.16881729267-\$24,000\\ =\$437.41\end{array}$

Hence, the NPV for the projects X and Y at the rate of 15% is $3,80.67 and $437.41 respectively.

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

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$10,620}{\left(1+0.20\right)}+\frac{\$10,900}{{\left(1+0.20\right)}^2}+\frac{\$10,500}{{\left(1+0.20\right)}^3}\right)-\$24,000\\ =\$8,850+\$7,569.44444444444+\$6,076.38888888888-\$24,000\\ =-\$1,504.17\end{array}$

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

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$12,100}{\left(1+0.20\right)}+\frac{\$9,360}{{\left(1+0.20\right)}^2}+\frac{\$10,400}{{\left(1+0.20\right)}^3}\right)-\$24,000\\ =\$10,083.33333333333+\$6,500+\$6,018.51851851851-\$24,000\\ =-\$1,398.15\end{array}$

Hence, the NPV for the projects X and Y at the rate of 20% is -$1,504.17 and -$1,398.15 respectively.

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

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$10,620}{\left(1+0.25\right)}+\frac{\$10,900}{{\left(1+0.25\right)}^2}+\frac{\$10,500}{{\left(1+0.25\right)}^3}\right)-\$24,000\\ =\$8,496+\$6,976+\$5,376-\$24,000\\ =-\$3,152\end{array}$

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

$\begin{array}{c}\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}\\ =\left(\frac{\$12,100}{\left(1+0.25\right)}+\frac{\$9,360}{{\left(1+0.25\right)}^2}+\frac{\$10,400}{{\left(1+0.25\right)}^3}\right)-\$24,000\\ =\$9,680+\$5,990.4+\$5,324.8-\$24,000\\ =-\$3,004.80\end{array}$

Hence, the NPV for the projects X and Y at the rate of 25% is -$3,152 and -$3,004.80 respectively.