1. Net present value and profitability index
Welcome back! In this demo, we will practice capital budgeting concepts with net present value and the profitability index.
To start, we’ll analyze the cash flows for these two projects, Project A and Project B. They both have the same cumulative cash flow, but Project B has a two year initial investment period, which could impact our capital budgeting metrics.
Let’s start by finding the net present value of each. We’ll create a new measure called “Project NPV” using the XNPV function.
The syntax for XNPV is straightforward. First, we need to reference the table with the measures we want to use. Then, we will use the cash flows as the values and reference the needed dates.
Finally, the rate is just the discount rate we want to use for this analysis. Let’s say that the company’s weighted average cost of capital is eight point 75 percent, so we’ll use that as our discount rate. Remember to format the rate in decimals, since percentages cannot be accepted within the syntax.
Great, now we can put this into a multi-row card with Project and see the net present values for each project. Let’s also format this as a currency with zero decimal places. We’ll add a title to the multi-row card, and we’ll call it Project metrics.
Project A has the highest NPV. Was that surprising?
Finally, we’ll calculate the profitability index, which is also very straightforward. We just need to divide the net present value by the absolute value of the initial invested amount and then add one.
Here, the Initial Investment is already calculated, so we’ll use DIVIDE to divide Project NPV by the Initial Investment, and we’ll set our alternative answer to zero. It’s always a good practice to use DIVIDE because the alternative answer will turn any errors into zeros.
Let’s add this to our multi-row card, and now we can see that, not surprisingly, project A has the higher profitability index.
Since Project A has a higher NPV and profitability index, it would be the best project to invest in. Generally, these metrics will point to the same project in the real world. However, when there is a conflict, it’s up to your judgment as an analyst to pick the project that makes the most sense.
In the real world, companies will have their metric of choice. Ratios, like the profitability index, are helpful in understanding the efficiency of capital, but NPV is by far the most popular as it gives an actual dollar amount. Of course, that dollar amount is subject to a discount rate, so there is a lot of discretion needed in financial analysis.
Now that you’ve seen it, it’s time to do it. You’ve got this!
2. Let's practice!