What is the crossover rate in capital budgeting, and how does an NPV profile help you understand it?
The crossover rate is the discount rate beyond which the net present value ranking flips
when comparing two projects in capital budgeting.
In the example that we explore in this video, we will see that for discount rates from 0%
to 19%, the net present value of project A exceeds the net present value of project B,
so you would prefer A over B when making an investment decision.
However, for discount rates above 19%, the net present value of project A is lower than
the net present value of project B!
You would now prefer B over A. The ranking flips.
How can this happen?
Calculating and visualizing the crossover rate and building an NPV profile will help
us understand this phenomenon.
Our base case is project A. It has an upfront investment of $1000 (shown as a negative,
an outflow), and four years of expected nominal benefits of $400 each (shown as positives, inflows).
We are going to compare the cash flow profile of project A to that of project B. More specifically,
project B1 has an upfront investment of $900 (shown as a negative, an outflow), and four
years of nominal benefits of $400 each (shown as positives, inflows).
It’s easy to find the difference in cash flow between the projects.
If the benefits over the years are expected to be exactly the same for the two projects,
and the only difference between them is a lower upfront investment ($100 less in cash
outflow) for project B1, then project B1 is better than project A at any discount rate
and no crossover rate is available simply because the NPV ranking does not flip.
Something similar happens when comparing the cash flow profile of project A to that of
project B2.
The upfront investment is the same for the two projects ($1000 each), but in project
B2 the expected annual benefits are bigger: $425 per year, instead of $400.
Project B2 is better than project A at any discount rate, and therefore no crossover
rate is available!
That changes when we compare project A to project B3.
Here project B3 has the advantage of having a lower upfront investment, which is good
news for project B3 in terms of its chances of becoming the preferred project, but the
disadvantage of having no expected benefits in year 4, which is bad news for project B3.
If you look at the cash flow differential between project A and project B3, then what
you are really looking at is a difference of $200 now, versus a difference of $400 four
years from now.
Money now versus money later.
A classic time value of money situation!
Only if we take the $400 that we expect to receive in the future (four years from now),
and translate that $400 to its present value equivalent (today),
can we really compare the two amounts.
That’s where we can apply capital budgeting techniques such as IRR, the internal rate of return.
IRR asks the question: what is the discount rate that makes the NPV of the two amounts equal to zero?
The easiest way to figure that out is to plug the numbers into an Excel spreadsheet, and
insert a formula in cell B14: equals IRR(B13:F13).
Rounded to a whole percentage, the answer is that at a discount rate of 19%, the present
value of $400 four years from now, minus $200 today, is zero.
$400 divided by 1.19 is $336.
Divide that by 1.19, you get $283.
Divide that by 1.19, you get $238.
Divide that by 1.19, you get $200.
$200 versus $200, the discounted cash flows are each other’s exact opposite amounts,
and the crossover discount rate of 19% makes it so.
So how do you calculate the crossover rate?
You simply need to find the Internal Rate of Return (IRR) of the difference in cash
flows between two projects.
A common way to see the crossover rate in a bigger perspective is to build an NPV profile.
An NPV profile shows the sensitivity of a project's NPV for different discount rates.
Let’s build an NPV profile in Excel from the ground up.
We start with a discount rate of 0%, in other words we don’t discount!
We will still insert the full Excel formula, as we are going to copy this formula across
for discount rates higher than 0%.
The formula references six cells: cell B2 for the cash outflow of the investment for
project A, cell B5 for the discount rate that is applied to the projects, and cells C2 through
F2 for the expected benefits of project A. You can copy over the formula from cell B6
to B7 to do the same for project B: reference the same discount rate in cell B5, but the
project B cash flows in row 3.
At a discount rate of 0% (in other words undiscounted), the NPV of project A is equal to the nominal
cash flow amounts: $1600 of expected benefits over the four years, minus $1000 of investment
today, which is a net amount of $600.
At a discount rate of 0%, the NPV of project B is equal to $1200 of expected benefits over
the three years, minus $800 of investment today, which is a net amount of $400.
The NPV of project B at a discount rate of 0% is $200 lower than the NPV of project A.
So far, so good.
If we add fields to calculate the NPVs of the projects at a 2% discount rate, we find
that the NPVs for both project A as well as project B drop, while the difference between
the NPVs decreases to $170.
At 4% discount rate, the difference between the NPVs decreases to $142.
At 6%, to $117.
That’s an interesting pattern.
Why not add fields in 2% increments to see where this leads us?
Here’s how that table looks for discount rates from 0% through 24%.
The table shows that for discount rates from 0% through 18%, the NPV of project A exceeds
that of project B.
For discount rates from 20% and above, the NPV of project A is lower than that of project
B. The crossover rate, the point where the NPVs of the two projects are equal, is halfway
between 18% and 20%: around 19%.
It’s easier to grasp that, when we visualize the NPV profile and the crossover rate in
a graph.
Discount rate on the horizontal axis, net present value on the vertical axis.
The NPV of project A as a function of the discount rate is shown as an orange line,
the NPV of project B as a blue line.
For both projects, the higher the discount rate (the further we go to the right), the
lower the net present value.
The NPV of project A is much higher than that of project B at low discount rates, which
means that if you had to select one of these two projects for investment at any of those
lower discount rates, then project A would be preferred.
However, that NPV gap shrinks the further you go to the right, until the NPVs are equal
at the crossover rate of 19%.
Beyond the crossover rate of 19%, even though the NPVs are getting fairly low or even negative
in absolute terms, the NPV of project B is higher than the NPV of project A, which means
that if you had to select one of these two projects for investment, project B would be
preferred at those higher discount rates.
The graph of the NPV profile gives us some additional information.
What are the respective discount rates that make the NPV of the projects equal to zero?
In other words, what are their IRRs?
For project A, the IRR is 21.9%,
that’s where the orange line intersects the horizontal axis.
For project B, the IRR is 23.4%,
that’s where the blue line intersects the horizontal axis.
So while the IRR for project B is higher than that of project A, the NPV of project A is
higher than that of project B for discount rates up to 19%.
Only for cases where the discount rate is higher than 19%, do NPV and IRR both suggest
to favor project B. When in doubt, NPV is more important than IRR in capital budgeting.
If NPV and IRR don’t point you in the same direction of which project you should select,
then apply crossover rate analysis to understand the NPV profiles!