How to Calculate Profitability Index in Excel (Easy)

Profitability index is a technique to evaluate the viability of investments/projects.

It helps you forecast the returns and feasibility of a project to see if it is even worthwhile to invest.

It is easy to calculate and is an excellent statistical measure that enables you to look through the returns of a proposed project beforehand.

Here’s your free practice workbook for this guide, download it and read it with me till the end of this article to learn how to calculate profitability index it in Excel step by step 👇

Table of Contents

What is Profitability Index

Profitability index is a profit investment ratio that helps evaluate the potential profitability of an investment.

To calculate the PI, we need the estimated future cash flows to be generated from a project and the WACC for it.

Discount the cashflows on the WACC to calculate the present value of the cash flows. Once you have the present value calculated, divide it by the amount of investment to see if the project is profitable 🤑

Formula

This formula will calculate you a PI which will be somewhere above 0. Normally it ranges between 0 to 2. However, there’s no definitive limit to it.

Once you start looking into how it is calculated and interpreted – it will make more sense.

The only tricky part of calculating PI is the discounting of cashflows to reach the present value of these cashflows. However, since Excel offers multiple inbuilt functions for that job, it will still be super easy.

How to calculate profitability index in Excel

Calculating the profitability index itself in Excel is not a big deal – it is only about a simple division operation.

However, the numbers needed to be able to perform that division operation might be a little bit of science to calculate. Let me show you an example of performing this calculation from start to end.

Suppose you are thinking of beginning a new business with an initial investment of $2000 and here is what the cashflow forecast looks like 📈

We will begin by calculating the present value of these cash flows.

Pro Tip!

There are two functions that you can use to calculate the present value of these cash flows.

PV function: The PV function is used when you have an identical set of cashflows. For example, a cash flow of $500 for 5 years each.

NPV function: The NPV function is used when you have varying cashflows for different years. For example, $500 in year 1, $200 in year 2, $300 in year 3, and so on.

Since we have varying cashflows over the years, we will use the NPV function to calculate the present value of these cashflows.

Step 1) Write the NPV function as follows:

Step 2) As the rate argument, supply the WACC over which the cashflows are to be discounted.

Step 3) Next, refer to the cell range containing the cashflows to be discounted as the next argument.

Step 4) Hit Enter to have the present value of the given cash flows calculated.

Comes out at $3124.3.

To calculate the profitability index now,

Step 5) Divide the present value of cash flows by the initial investment.

Pro Tip!

The formula for the division of the present value of cashflows by the investment is prefixed by a minus sign since the initial investment is a negative number (represents a cash outflow) whereas, the present value of cashflows is a positive number (represents cash inflows).

A negative number divided by a positive number results in a negative output.

The profitability index however can not be a negative number, it can be less than 1 or greater than 1. To neutralize the results in such a situation, you can prefix the PI formula with a minus sign.

And the profitability index comes out as 1.56.

If you have your data sorted, calculating the profitability index for any investment is only this easy 🎯

Interpreting Profitability Index

The main deal about the profitability index does not lie in its calculation but in interpretation.

What does the PI of 1.56 calculated in the above section mean? It means that this investment will bring you a 56% return over the initial investment.

How? Here are the three basic rules to interpret the profitability index.

• PI over 1

A PI over 1 indicates that the project is viable, and it will generate more value than the initial investment. For example, per the above example, you invest $2000 today, and you’d receive $3124 from it (present value, in today’s terms) 💪

Since you’ll be able to earn more than what you invest, the PI for this project is 1.56 (more than 1).

• PI equal to 1

A PI equal to 1 means your project will only touch break even. For the example discussed in the above section, we would have yielded a PI of 1 if the present value of cashflows was $2000.

• PI less than 1

If the PI is less than 1, it means you will not even recover your initial investment. This is an indication that the project is not viable.

The PI for a project will only be less than 1 when the present value of the cashflows is less than the initial investment you’re making. Clearly means you are at a loss by not even getting back what you invested, let alone the return 🤯

Pro Tip!

Why is Profitability Index a better measure than the Net Present Value for evaluating an investment? Let me explain it through an example.

Net present value means the present value of an investment after deducting the cost of investment from it. For the example we discussed above, it would be $1124 ($3124 – $2000).

Now, if you have two investments at your disposal: Investment A offers a positive NPV of $7000 and Investment B offers a positive NPV of $2200. Which one will you choose?

Obviously, Investment A – offers $4800 more than Investment B. But the Profitability Index of Investment A is 1.53 whereas, that of Investment B is 1.78.

Surprised? Here is how:

• Investment A:

Present Value of CFs: $20,000

Investment: $13,000

NPV = ($20,000 – $13,000) = $7000

PI = $20,000 / 13,000 = 1.53

• Investment B:

Present Value of CFs: $5000

Investment: $2800

NPV = ($5000 – $2800) = $2200

PI = $5000 / 2800 = 1.78

Turns out Investment B is actually better. Requires less investment but yields a higher return 🤩

This is why PI is a better measure than NPV when it comes to evaluating investments. It looks through an investment by accounting for the cost of investment and returns on investment. Also, it helps comparison by evaluating all big and small investments in terms of per unit of investment.

Pros and cons of profitability Index

Profitability index is an excellent financial metric. It helps to evaluate the attractiveness and feasibility of an investment. Let the following pros and cons decide if you should or shouldn’t use PI as a financial metric for evaluating your investments 🚀

Pros

• It accounts for the effect of the time value of money.
• Helps compare relative investments.
• Calculates the value generated per unit of investment.
• Can be used for comparison across investments of varying sizes and tenures.
• Simple to understand and easy to calculate.

Cons:

• Relies on cashflows which are based on estimations and may be inaccurate.
• Needs a discount rate for discounting which, might be difficult to estimate.

Conclusion

By now you know how to decide if an investment is worth it or not. Profitability Index is a reliable financial analysis technique to foresee if an investment project will prove financially feasible, or not.

The best I like about Profitability index is that it allows comparison among multiple investments of varying sizes and tenures in relative terms. Helps decision-making in great time.

To learn about other methods that you can use to evaluate investments, read my other Excel tutorials on the Net present value (NPV) model that also involves discounting cashflows on Weighted Average Cost of Capital (WACC) and the Internal Rate of Return model (IRR).