How to Calculate Profitability Index in Excel?

One could annualize these net returns using the equivalent annual annuity formula, for the sake of like comparison. And depending on the risk-free rate (typically the yield on U.S. Government Bonds), Garch Ltd should either deposit the remaining $50,000 and earn the risk-free rate. And between NPV and the Profitability Index, you’re probably better off applying the rule or investment appraisal criteria using profitability index rather than NPV. Similarly, for every dollar we invest in Archer, we expect to earn 5 cents. And lastly, similar story for Catcher; we expect to earn $0.02 for every $1 invested. The projects are divisible, meaning Garch Ltd can invest in parts of a project instead of having to invest fully in a given project.

The projects require investments of $300,000; $200,000; and $600,000 for Archer, Brochure, and Catcher respectively. We’d say that for every £1 pound that you invest in A, you earn £1.50 in cash flow, in present value terms. The NPV method reveals exactly how profitable a project will be in comparison to alternatives. When a project has a positive net present value, it should be accepted.

It also identifies the exact rate of return for a project or investment, which makes understanding the cost-benefit ratio of projects easier. The profitability index (PI) is a measure of the attractiveness of a project or investment. It is calculated by dividing the present value of future expected cash flows by the initial investment amount in the project.

In all, there are many factors to consider when calculating a profitability index. For example, taxes must be considered when determining a profitability index. This is the tax charged by the government on both individuals and corporations. Let’s take an example to understand the calculation of the Profitability Index formula in a better manner. Furthermore, you learned that there are 2 ways to calculate the PI, including one where we take the ratio of NPV to I, and another, where we take it as the ratio of PV to I.

What Is the Profitability Index? Definition & Calculation

For example, a company might determine if their new line of clothing should be introduced. If the expenses outweigh the potential revenue, then it’s not worth it. The company would then decide to either lower the initial cost of production or scrap the idea altogether. A profitability index also takes into consideration many other factors. These include things like transportation, sales commissions, production facilities, and more. These include net income from operations, operating margin, and corporate taxes.

  • To make this work easier, Excel offers to calculate the profitability index accurately.
  • The best uses of the profitability index involve investments in new products or services.
  • What if the 3 year project has an NPV of $1,000 and the 5 year project has an NPV of $1,100.
  • Therefore, the metric quantifies the economic feasibility of a project (or investment), which can then be ranked to comparable opportunities to allocate capital toward the most profitable option.

The company might decide to pursue this project instead of the new factory project because it is expected to generate more value per unit of investment. When using the profitability index exclusively, calculations greater than 1.0 are ranked based on the highest calculation. The 3 measures of profitability are gross profit margin (GPM), net profit margin (NPM), and return on investment (ROI).

Generally the PI ratio of 1 is least acceptable as it represents the break even point of a project, which defines the point where total sales (revenue) equal to the total cost. A PI ratio of less than 1 is completely undesirable as it represents that a project will cost more than it is expected to earn. Norwich University’s online Master of Business Administration program helps create strong leaders well-versed in business management practices. Students can customize their MBA by choosing a concentration in construction management, finance, organizational leadership, project management, supply chain management & logistics, or energy management. It doesn’t matter the type of business that you operate or the industry that you are in.

What Is the Profitability Index (PI) Rule?

At least from a financial perspective, a score greater than 1.0 indicates that an investment should be made. As the score increases above 1.0, so too does the attractiveness of the investment. The ratio could be used to develop a ranking of projects, to determine the order in which available funds will be allocated to them. The profitability index (PI) offers a way to measure the balance between an initial investment and its present value of future cash flows. It’s a simple ratio that indicates whether or not an investment project is likely to be profitable by showing the value created per investment unit. A general rule of thumb is that a PI greater than one indicates the project should go forward, while a PI below one indicates it will not be worth the investment.

It is calculated by dividing the present value of future cash flows by the initial amount invested. If the profitability index is greater than or equal to 1, it is termed a good and acceptable investment. The profitability index is an appraisal technique applied to potential capital outlays.

How to Calculate Profitability Index in Excel [Free Template]

The Profitability Index (PI) is the ratio between the present value of cash inflows and the present value of cash outflows. The higher the profitability index, the more attractive the investment. Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.

Example of the Profitability Index

It is used to evaluate the potential of a company to generate profit from an investment. A profitability index calculation can be helpful after an investment has been made. If a company doesn’t have good credit ratings or assets that could be sold, it might consider investing in other assets. These assets can then be sold for a profit if a company is in need of quick cash inflows. They will be able to quickly turn around and sell their investment when they need the extra capital. As mentioned above, having a profitability index higher than 1 is ideal.

NPV is calculated by discounting the future cash flows by a discounting factor. The first thing you notice is that Project I has a larger scale compared to Project II — it requires larger initial investment and returns higher cash flows. The first project will return cash flows for a period of 10 years, while the second one is expected to deliver for 8 years only. The PI ratio uses discounting, the cash flows are discounted by an appropriate rate of return. Running a profitable business demands a lot of investments and assessing them for profitability is essential. The profitability index (PI), also known as profit investment ratio (PIR) is a method to describe the relationship between cost and benefits of a project.

Therefore, using the payback period in combination with other capital budgeting methods is far more reliable. A profitability index is a useful tool for companies to help make the best investment decisions. This Profitability Index (PI) template will help visualize the present value of future cash flows, which will then be used to calculate the PI of the project. The PI measures the ratio between the present value of future cash flows to the initial investment.

The higher a profitability index means a project has benefits and would be considered more attractive. It can be very helpful in ranking potential projects in order to let investors quantify their value. A final issue is that the index cannot be used to rank projects that are mutually exclusive; that is, only one investment or the other would be chosen, which is a binary solution. In this situation, a project with a large total net present value might be rejected if its profitability index were lower than that of a competing but much smaller project. Where PV is the present value, CF is the cash flow in a given year, r is the discount rate, and n is the number of years.

Date and Time Calculators

The profitability index (PI), also known as the profit investment ratio (PIR) or value investment ratio (VIR), is a capital budgeting tool that gauges the potential profitability of an investment or project. It can be used as an appraisal technique or applied to potential capital outlays, and functions as a useful formula for ranking a project’s financial outlook alongside other investments. The profitability index allows investors to quantify the amount of value created per unit of investment. For example, a project with an initial investment of $1 million and a present value of future cash flows of $1.2 million would have a profitability index of 1.2. Based on the profitability index rule, the project would proceed, even though the initial capital expenditure required are not identified. Profitability is used to find out whether a project is profitable or not.

Calculations that equal 1.0 bring about situations of indifference where any gains or losses from a project are minimal. When using the profitability index to compare the desirability of projects, it’s essential to consider how the technique disregards project size. Therefore, projects with larger cash inflows may result in lower profitability index calculations because their profit margins are not as high.