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

Answer to Problem 11QP

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

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 $13,100, $9,480, and $7,890 respectively. The initial investment is $23,400. The project Y cash flows for 4 years are $9,200, $10,620, $11,180 respectively and the initial investment is $23,400.

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=-\$23,400+\frac{\$13,100}{\left(1+\text{IRR}\right)}+\frac{\$9,480}{{\left(1+\text{IRR}\right)}^2}+\frac{\$7,890}{{\left(1+\text{IRR}\right)}^3}\\ 0=-\$23,400+\frac{\$13,100}{\left(1+\text{IRR}\right)}+\frac{\$9,480}{{\left(1+\text{IRR}\right)}^2}+\frac{\$7,890}{{\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 15.980199204821%.

Hence, the IRRvalue is 15.98%.

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

$\begin{array}{l}\text{NPV}=0=-\$23,400+\frac{\$9,200}{\left(1+\text{IRR}\right)}+\frac{\$10,620}{{\left(1+\text{IRR}\right)}^2}+\frac{\$11,180}{{\left(1+\text{IRR}\right)}^3}\\ 0=-\$23,400+\frac{\$9,200}{\left(1+\text{IRR}\right)}+\frac{\$10,620}{{\left(1+\text{IRR}\right)}^2}+\frac{\$11,180}{{\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 IRRvalue.

Step 5:

• The value appears to be 14.953339658603%.

Hence, the IRRvalue is 14.95%.

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,900}{\left(1+R\right)}-\frac{\$1,140}{{\left(1+R\right)}^2}-\frac{\$3,290}{{\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 IRRvalue.

Step 5:

• The value appears to be 7.61811282095653%.

Hence, the R-value is 7.62%.

Formula to calculate the NPV:

$\text{NPV}=\text{Present value of cash inflow}-\text{Present value of cash outflow}$

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{\$13,100}{\left(1+0\right)}+\frac{\$9,480}{{\left(1+0\right)}^2}+\frac{\$7,890}{{\left(1+0\right)}^3}\right)-\$23,400\\ =\$7,070\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{\$9,200}{\left(1+0\right)}+\frac{\$10,620}{{\left(1+0\right)}^2}+\frac{\$11,180}{{\left(1+0\right)}^3}\right)-\$23,400\\ =\$7,600\end{array}$

Hence, the NPV for project X and Y at 0% are $7,070 and $7,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{\$13,100}{\left(1+0.05\right)}+\frac{\$9,480}{{\left(1+0.05\right)}^2}+\frac{\$7,890}{{\left(1+0.05\right)}^3}\right)-\$23,400\\ =\$12,476.19048+\$8,598.639456+\$6,815.678652-\$23,400\\ =\$4,490.51\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{\$9,200}{\left(1+0.05\right)}+\frac{\$10,620}{{\left(1+0.05\right)}^2}+\frac{\$11,180}{{\left(1+0.05\right)}^3}\right)-\$23,400\\ =\$8,761.904762+\$9,632.653061+\$9,657.704352-\$23,400\\ =\$4,652.26\end{array}$

Hence, the NPV for project X and Y at 5% are $4,490.51 and $4,652.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{\$13,100}{\left(1+0.10\right)}+\frac{\$9,480}{{\left(1+0.10\right)}^2}+\frac{\$7,890}{{\left(1+0.10\right)}^3}\right)-\$23,400\\ =\$11,909.09091+\$7,834.710744+\$5,927.873779-\$23,400\\ =\$2,271.68\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{\$9,200}{\left(1+0.10\right)}+\frac{\$10,620}{{\left(1+0.10\right)}^2}+\frac{\$11,180}{{\left(1+0.10\right)}^3}\right)-\$23,400\\ =\$8,363.636364+\$8,776.859504+\$8,399.699474-\$23,400\\ =\$2,140.20\end{array}$

Hence, the NPV for project X and Y at 10% are $2,271.68 and $2,140.20 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{\$13,100}{\left(1+0.15\right)}+\frac{\$9,480}{{\left(1+0.15\right)}^2}+\frac{\$7,890}{{\left(1+0.15\right)}^3}\right)-\$23,400\\ =\$11,391.30435+\$7,168.241966+\$5,187.803074-\$23,400\\ =\$347.35\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{\$9,200}{\left(1+0.15\right)}+\frac{\$10,620}{{\left(1+0.15\right)}^2}+\frac{\$11,180}{{\left(1+0.15\right)}^3}\right)-\$23,400\\ =\$8,000+\$8,030.245747+\$7,351.031479-\$23,400\\ =-\$18.72\end{array}$

Hence, the NPV for project X and Y at 15% are $347.35 and -$18.72 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{\$13,100}{\left(1+0.20\right)}+\frac{\$9,480}{{\left(1+0.20\right)}^2}+\frac{\$7,890}{{\left(1+0.20\right)}^3}\right)-\$23,400\\ =\$10,916.66667+\$6,583.333333+\$4,565.972222-\$23,400\\ =-\$1,334.03\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{\$9,200}{\left(1+0.20\right)}+\frac{\$10,620}{{\left(1+0.20\right)}^2}+\frac{\$11,180}{{\left(1+0.20\right)}^3}\right)-\$23,400\\ =\$7,666.666667+\$7,375+\$6,469.907407-\$23,400\\ =-\$1,888.43\end{array}$

Hence, the NPV for project X and Y at 20% are -$1,334.03 and -$1,888.43 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{\$13,100}{\left(1+0.25\right)}+\frac{\$9,480}{{\left(1+0.25\right)}^2}+\frac{\$7,890}{{\left(1+0.25\right)}^3}\right)-\$23,400\\ =\$10,480+\$6,067.2+\$4,039.68-\$23,400\\ =-\$2,813.12\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{\$9,200}{\left(1+0.25\right)}+\frac{\$10,620}{{\left(1+0.25\right)}^2}+\frac{\$11,180}{{\left(1+0.25\right)}^3}\right)-\$23,400\\ =\$7,360+\$6,796.8+\$5,724.16-\$23,400\\ =-\$3,519.04\end{array}$

Hence, the NPV for project X and Y at 25% are -$2,813.12 and -$3,519.04 respectively.

NPV profile: