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.
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:
Or,
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%)^2Here 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%)^3Here 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%)^4Here 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%)^5Here 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%)^6Here 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,000So, 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%)^1PV of cash flow in Year 2 = C7/(1+13%)^2 PV of cash flow in Year 3 = C8/(1+13%)^3PV of cash flow in Year 4 = C9/(1+13%)^4PV of cash flow in Year 5 = C10/(1+13%)^5 PV of cash flow in Year 6 = C11/(1+13%)^6Here 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,000Profitability 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.
