Economics and finance both require cost and benefit analysis. Professionals in these fields always consider the benefits and profits of all involved parties before starting any project, no matter how big or small. It is important to measure profitability in project finance before investing in a project. The Profitability Index (PI) is a ratio of the present value of expected cash flows to the initial investment required for a project. In this article, I will demonstrate the steps to calculate the Profitability Index in Excel. Hopefully, you will find this article helpful before you start any professional project.

**Table of Contents**hide

## What is Profitability Index?

Let’s first take a look at what the Profitability Index is. **The Profitability Index (PI)** is a ratio of the present value of expected cash flows to the initial investment required for a project. It is measured to find out whether a project is profitable or not.

How does this Profitability Index work? Let’s find out.

## Profitability Index Formula

You can easily calculate the **Profitability Index(PI)** by using the Profitability Index formula. The **Profitability Index (PI)** formula is as follows:

**Profitability Index=(PV of Future Cash Flows)/(Initial Investment)**

Or,

**Profitability Index=(Net Present value + Initial Investment) / Initial Investment**

## How to Interpret Profitability Index (PI)

No matter what kind of business you run or what industry you work in, making a profit is essential to growing and expanding. It is important to understand the benefits you can receive when it comes to projects or possible investments. Using the **Profitability Index (PI)**, you can determine a project’s cost and benefits.

Therefore:

- If the
**Profitability Index (PI) > 1**, it indicates that the project generates value for the company, so they should proceed with it. - If the
**Profitability Index (PI) < 1**, it indicates that the project destroys value for the company, so they should not proceed with it. - If the
**Profitability Index (PI) = 1**, it indicates that the project creates no value for the company or destroys value for the company, so they should be indifferent about whether to proceed or not.

## How to Calculate Profitability Index in Excel (with Easy Steps)

In this part of the article, I will show you the steps of calculating the **Profitability Index (PI)**. Suppose we have a company and we are considering **two projects- Project A** and **Project B**. Let’s calculate the **Profitability Index (PI)** of **Project A **and** Project B** and see which project is better to choose. We are going to use the *Excel 365 version* in this article. You can use any other version according to your convenience.

### Calculating Profitability Index (PI) for Project A

Suppose **Project A** needs an initial investment of **$2,100,000** and a discount rate of **10%** and with estimated annual cash flows of:

**$300,000**in**Year 1****$900,000**in**Year 2****$600,000**in**Year 3****$700,000**in**Year 4****$600,000**in**Year 5****$400,000**in**Year 6**

**Calculating Present Value (PV) of Cash Flow: **

Let’s calculate the present value of future cash flows for different years for **Project A**.

#### PV of cash flow in Year 1

To calculate the present value of cash flow in year 1, enter the following formula in cell **D6**.

`= C6/(1+10%)^1 `

Here is the final output image of the present value of cash flow in Year 1.

**Read More: **How to Convert Percentage to Basis Points in Excel

#### PV of cash flow in Year 2

To calculate the present value of cash flow in year 2, enter the following formula in cell **D7**.

`= C7/(1+10%)^2`

Here is the final output image of the present value of cash flow in Year 2.

**Read More: **How to Calculate Mileage Reimbursement in Excel

#### PV of cash flow in Year 3

To calculate the present value of cash flow in year 3, enter the following formula in cell **D8**.

`= C8/(1+10%)^3`

Here is the final output image of the present value of cash flow in Year 3.

#### PV of cash flow in Year 4

To calculate the present value of cash flow in year 4, enter the following formula in cell **D9**.

`= C9/(1+10%)^4`

Here is the final output image of the present value of cash flow in Year 4.

#### PV of cash flow in Year 5

To calculate the present value of cash flow in year 5, enter the following formula in cell **D10**.

`= C10/(1+10%)^5`

Here is the final output image of the present value of cash flow in Year 5.

**Read More:** How to Calculate Time Weighted Return in Excel

#### PV of cash flow **in** Year 6

To calculate the present value of cash flow in year 6, enter the following formula in cell **D11**.

`= C11/(1+10%)^6`

Here is the final output image of the present value of cash flow in Year 6.

So, the **Sum of PV of future cash flows** will be:

`=SUM(D6:D11) `

Now,

`Initial investment = $2,100,000`

So, the **Profitability Index **of Project A will be the ratio of the sum of the present value of future cash flow to the initial investment.

`=D12/D13 `

### Calculating the Profitability Index (PI) for Project B

Let’s say **Project B** needs an initial investment of **$2,500,000** and a discount rate of **13%** and with estimated annual cash flows of:

**$200,000**in**Year 1****$700,000**in**Year 2****$500,000**in**Year 3****$400,000**in**Year 4****$900,000**in**Year 5****$600,000**in**Year 6**

**Calculating Present Value (PV) of Cash Flow:**

Now, let’s calculate the present value of future cash flows for different years for **Project B**. In this case, we followed the same procedure as in **Project A**.

`PV of cash flow in Year 1 = C6/(1+13%)^1`

`PV of cash flow in Year 2 = C7/(1+13%)^2 `

`PV of cash flow in Year 3 = C8/(1+13%)^3`

`PV of cash flow in Year 4 = C9/(1+13%)^4`

`PV of cash flow in Year 5 = C10/(1+13%)^5 `

`PV of cash flow in Year 6 = C11/(1+13%)^6`

Here is the final output image after estimating the **PV of cash flow **for different years for **Project B**.

So, the **Sum of PV of future cash flows** will be:

`=SUM(D6:D11)`

Now,

`Initial investment = $2,500,000`

**Profitability Index of Project B **

`=D12/D13 `

Now, the **Profitability Index** of **Project A **is **1.211318853,** and the **Profitability Index** of **Project B **is **0.837488584**. **Project A** creates an additional value of **$0.21** for every **$1** invested in the project compared to **Project B**, which does not create any additional value. As a result, the company loses value. Therefore, **Project A** should be chosen over **Project B** for our company.

## Advantages of the Profitability Index

Here are some major advantages of estimating the** Profitability Index(PI)**:

**Profitability**indicates whether an investment is likely to increase or decrease the value of a company.**The Profitability Index(PI)**considers the time value of money by discounting future cash flows back to the present. This means that it provides a more accurate assessment of the value of future cash flows than methods that don’t account for the time value of money.**The Profitability Index(PI)**can be used to rank projects in order of profitability. This allows investors to select the most profitable projects to invest in.**The Profitability Index(PI)**takes into account the size of the investment required for the project. This means that it provides a more accurate picture of the profitability of a project than methods that don’t consider the investment required.

## Disadvantages of the Profitability Index

While **the profitability index (PI)** has several advantages, it also has some disadvantages. Here are some of them:

**The Profitability Index(PI)**only considers the financial aspects of an investment project and doesn’t take into account non-financial factors, such as social or environmental impacts. This can lead to investment decisions that don’t align with the organization’s broader objectives.**The Profitability Index(PI)**is dependent on the cost of capital, which is generally difficult to estimate.**The Profitability Index(PI)**assumes that cash flows will remain constant throughout the life of the project. In reality, cash flows can be unpredictable and may vary significantly over time, which can affect the accuracy of the analysis.

However, it is important to recognize its limitations and to use the **PI** in conjunction with other analysis methods when evaluating investment projects.

## Can the Profitability Index be negative?

**The Profitability Index (PI)** can be negative. The **PI** is calculated by dividing the present value of the expected cash inflows by the initial investment. If the present value of expected cash inflows is less than the initial investment, the **PI** will be less than **1** and **negative**. This indicates that the project is not expected to generate positive returns and may not be worth pursuing.

In summary, a** negative PI** is possible and indicates that the investment is expected to generate negative returns. However, it’s important to evaluate the reasons behind the** negative PI** and consider other factors before making investment decisions.

## Frequently Asked Questions

**How to calculate the profitability index?**

I have already discussed some easy steps for calculating the **Profitability Index(PI)** in this article. Just go through this article and you will be able to estimate the **Profitability Index(PI)** in** Excel**.

**How do you calculate PV in Excel?**

You can check the “**Steps to Calculate Profitability Index in Excel**” part of this article. I have explained step by step how to calculate **PV **in** Excel**. I hope you will get your answer.

**Which is better: NPV or the profitability index?**

Well, **NPV** is generally considered the more comprehensive and reliable metric because it takes into account the time value of money and considers all cash flows over the investment’s lifespan. It also provides an absolute dollar amount of the investment’s value, which is easier to compare against other investments.

**The Profitability Index**, while useful, has some limitations. It does not provide an absolute dollar amount, and it assumes that all cash flows occur at the same time, which may not be the case in some investments.

Overall, **NPV** is considered the more comprehensive and reliable method of evaluating investments compared with **PI**.

## Key Takeaways from the Article

- In this article, I have explained what is
**Profitability Index (PI)**is. - A chosen real-life dataset for better understanding.
- Focusing on how to estimate the
**Profitability Index (PI)**for two different projects and making decisions based on their estimated value. - Explained how to interpret the result of the
**Profitability Index (PI)**. - Provide solutions to frequently asked questions by readers.
- Overall, focused on how to calculate the
**Profitability Index (PI)**in Excel.

**Download Practice Workbook**

You can download the practice workbook from here:

## Conclusion

In this article, I have tried my best to explain how to calculate the Profitability Index (PI) in Excel with some easy steps. I hope this article was informative and useful for you Feel free to leave any questions, comments, or recommendations in the comments section.