How to Calculate Profitability Index in Excel (Formula + Example)

The profitability index (PI) is the present value of a project’s future cash flows divided by the initial investment. A PI above 1 means the project is worth more than it costs, so it’s a quick way to check the viability of investments/projects.

To calculate the profitability index in Excel:

1. Calculate the present value of the future cash flows with the NPV function, for example =NPV(B9,B3:B7) (B9 is the discount rate, B3:B7 the yearly cash flows).
2. Divide that result by the initial investment.

Or do it in one formula: =NPV(B9,B3:B7)/-B2, where B2 holds the initial investment as a negative number. In the example below, that gives a PI of 1.56.

Here’s your free practice workbook for this guide, download it and follow along to calculate the profitability index 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, you need the estimated future cash flows from the project and a discount rate. Companies often use their WACC (weighted average cost of capital) as the discount rate.

Discount the cash flows at that rate to get their present value. Then divide the present value by the initial investment to see if the project is profitable 🤑

Formula

As long as the future cash flows are worth more than zero today, you get a PI which will be somewhere above 0. There’s no upper limit.

You can also write it as PI = 1 + NPV / Investment, where NPV is the net present value (present value of the cash flows minus the investment). Both give the same result.

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’s just a simple division.

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.

It comes out at $3,124.32.

Note that the initial investment in B2 is not part of the NPV range. The NPV function assumes every cash flow happens at the end of a period, so it discounts the first value by one full year. The investment happens today, so it stays outside the formula.

To calculate the profitability index now,

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

Pro Tip!

The formula starts with a minus sign because the initial investment in B2 is a negative number (a cash outflow), while the present value of the cash flows is a positive number (cash inflows).

A positive number divided by a negative number gives a negative result. The minus sign in front flips it back, so the PI comes out positive.

If you prefer, =B10/ABS(B2) gives the same result. And if you type the investment as a positive number, a plain =B10/B2 is all you need.

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 🎯

Want it in a single cell? This formula does both steps at once:

It returns the same 1.56.

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 every $1 you invest brings back $1.56 in today’s money. In other words, the net present value is 56% of 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 get back more than 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 is only less than 1 when the present value of the cash flows is less than the initial investment. In today’s money, you don’t even get back what you invested, let alone a return 🤯

Pro Tip!

When is the profitability index more useful than the net present value (NPV)? 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 minus $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?

Investment A looks like the obvious choice, since it offers $4800 more than Investment B. But the Profitability Index of Investment A is 1.53, while 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 minus $13,000 = $7000

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

• Investment B:

Present Value of CFs: $5000

Investment: $2800

NPV = $5000 minus $2800 = $2200

PI = $5000 / 2800 = 1.78

Investment B creates more value per dollar invested. It needs much less money and still yields a higher return per dollar 🤩

That’s where PI shines: it measures value per unit of investment, so you can compare big and small projects on equal terms. It’s especially useful when your budget is limited and you have to choose which projects to fund.

But if you can afford either project and can only pick one, Investment A still adds more total value ($7000 vs. $2200). That’s why it’s smart to look at both PI and NPV before you decide.

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.

What I like best about the profitability index is that it lets you compare investments of different sizes and lengths in relative terms. That makes decisions a lot faster.

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). Here’s more on how to calculate present value in Excel.

Frequently Asked Questions

What is the formula for the profitability index?

Profitability index = present value of future cash flows / initial investment. In Excel, that’s =NPV(rate,cash_flows)/initial_investment, for example =NPV(B9,B3:B7)/2000. An equivalent formula is PI = 1 + NPV / initial investment.

Can the profitability index be negative?

Only if the present value of the future cash flows is negative, for example when a project is expected to lose money every year. That is rare: a project with a PI below 1 is already rejected. If Excel shows a negative PI for a normal project, it’s usually because the initial investment is entered as a negative number. Put a minus sign in front of the formula, like =-(B10/B2), or use =B10/ABS(B2).

What is a good profitability index?

A PI above 1 means the project’s discounted cash flows are worth more than it costs, so it’s acceptable. A PI of exactly 1 is break-even, and below 1 the project destroys value. When you compare several projects, the higher the PI, the more value you get per dollar invested.

Should I use NPV or PV to discount the cash flows?

Use NPV when the cash flows change from year to year, like in the example above. The PV function works when every cash flow is the same (for example, $500 a year for 5 years): =PV(10%,5,-500) returns their present value. If your cash flows happen on specific dates rather than once a year, use XNPV. XNPV doesn’t discount the first value, so put the (negative) initial investment first in the values and its date first in the dates. Then XNPV returns the NPV, not the present value of the future cash flows, and the profitability index is =1+XNPV(rate,values,dates)/initial_investment, with the initial investment as a positive number.

What is the difference between NPV and the profitability index?

NPV is the present value of the cash flows minus the investment, in dollars. The profitability index is the present value divided by the investment, a ratio. NPV tells you how much value a project adds, and PI tells you how much value it adds per dollar invested.