# 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 👇

## 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.

**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.

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 🤯

## 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).