how to calculate profitability index in excel

Understanding the Profitability Index (PI)

The Profitability Index (PI), also known as the Value Investment Ratio (VIR) or Profit Investment Ratio (PIR), is a capital budgeting tool used to measure the ratio of the present value of future cash flows to the initial investment. It's a useful metric for ranking projects and making investment decisions, especially when capital is rationed.

A PI greater than 1.0 indicates that the present value of future cash inflows is greater than the initial cost of the project, suggesting the project is financially attractive. A PI less than 1.0 suggests the opposite, while a PI equal to 1.0 means the project's present value of cash inflows exactly covers its initial cost.

The Profitability Index Formula

The formula for the Profitability Index is straightforward:

PI = Present Value of Future Cash Flows / Initial Investment

Where the Present Value (PV) of each cash flow is calculated as:

PV = Cash Flow / (1 + Discount Rate)^Year

You sum up the PVs of all future cash flows to get the "Present Value of Future Cash Flows" for the numerator.

Key Components Explained

• Initial Investment: This is the upfront cost required to undertake the project. It's usually a negative cash flow at time zero.
• Future Cash Flows: These are the net cash inflows expected to be generated by the project over its lifetime.
• Discount Rate: Also known as the required rate of return or cost of capital, this rate is used to discount future cash flows back to their present value. It reflects the time value of money and the risk associated with the project.

Step-by-Step Calculation in Excel

Excel is an excellent tool for calculating the Profitability Index due to its powerful financial functions. Here’s how you can do it:

1. Set Up Your Spreadsheet

Open a new Excel workbook and label your columns/rows clearly. A common setup would be:

• Cell B1: "Initial Investment"
• Cell B2: "Discount Rate (%)"
• Column A: "Year" (0, 1, 2, ...)
• Column B: "Cash Flow"
• Column C: "Present Value of Cash Flow"

2. Input Your Data

Enter your project's financial data:

• In B1, enter the initial investment (e.g., 100,000).
• In B2, enter the discount rate as a percentage (e.g., 10 for 10%). Remember to convert it to a decimal in formulas (e.g., 0.10).
• In Column A, list the years (e.g., 1, 2, 3, 4, 5).
• In Column B, starting from A3, enter the cash flow for each corresponding year.

3. Calculate Present Value of Each Cash Flow

You can do this manually or using Excel's NPV function, though PV is typically for a series of equal payments. For unequal cash flows, it's often easier to calculate each one individually or use NPV for the sum.

Method A: Manual Calculation (Recommended for clarity)

For Year 1 cash flow (e.g., in B3) and discount rate (in B2):

In C3 (PV for Year 1), enter: =B3 / (1 + B$2)^A3

Drag this formula down for all subsequent years. Make sure to use absolute referencing (B$2) for the discount rate so it doesn't change when you drag the formula. Note: If your discount rate is entered as a percentage (e.g., 10 for 10%), you'll need to divide by 100 in the formula: =B3 / (1 + B$2/100)^A3.

Method B: Using Excel's NPV Function (for the sum of PVs)

Excel's NPV function calculates the Net Present Value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values). Crucially, the NPV function assumes that the first cash flow occurs at the end of the first period. Therefore, to get the present value of future cash flows, you'd typically use:

=NPV(discount_rate, cash_flow1, cash_flow2, ...)

For example, if your discount rate is in B2 and cash flows are in B3:B7:

=NPV(B2/100, B3:B7)

This will give you the sum of the present values of cash flows from Year 1 onwards.

4. Sum the Present Values of Future Cash Flows

If you used Method A (manual PV calculation), sum all the values in Column C (e.g., from C3 downwards) to get the total present value of future cash flows. You can use the SUM function:

=SUM(C3:C7) (assuming 5 years of cash flows)

If you used Method B (NPV function), the result of the NPV function is already the sum of the present values of future cash flows.

5. Calculate the Profitability Index

Now, divide the total present value of future cash flows by the initial investment:

Assuming initial investment is in B1, and the sum of PVs is in a cell (e.g., C8 or the result of the NPV function):

=C8 / B1 (if C8 holds the sum of PVs from manual calculation)

=NPV(B2/100, B3:B7) / B1 (if using the NPV function directly)

Example Calculation in Excel

Let's use an example:

• Initial Investment: $100,000
• Discount Rate: 10%
• Year 1 Cash Flow: $30,000
• Year 2 Cash Flow: $40,000
• Year 3 Cash Flow: $50,000
• Year 4 Cash Flow: $35,000
• Year 5 Cash Flow: $25,000

Excel Setup:

|   | A       | B             | C                         |
|---|---------|---------------|---------------------------|
| 1 |         | Initial Inv:  | 100000                    |
| 2 |         | Discount Rate:| 10%                       |
| 3 | Year 1  | 30000         | =B3/(1+$B$2/100)^A3  (27272.73) |
| 4 | Year 2  | 40000         | =B4/(1+$B$2/100)^A4  (33057.85) |
| 5 | Year 3  | 50000         | =B5/(1+$B$2/100)^A5  (37565.74) |
| 6 | Year 4  | 35000         | =B6/(1+$B$2/100)^A6  (23901.89) |
| 7 | Year 5  | 25000         | =B7/(1+$B$2/100)^A7  (15522.90) |
| 8 |         | Sum PVs:      | =SUM(C3:C7) (137321.11)   |
| 9 |         | PI:           | =C8/B1      (1.37)        |
                    

In this example, the Profitability Index is approximately 1.37.

Interpreting the Result

• PI > 1.0: The project is expected to generate a positive net present value, meaning the present value of its future cash inflows exceeds its initial investment. The project is generally acceptable.
• PI < 1.0: The project is expected to generate a negative net present value, meaning the present value of its future cash inflows is less than its initial investment. The project should generally be rejected.
• PI = 1.0: The project's present value of future cash inflows exactly equals its initial investment, resulting in a zero net present value. The project might be marginally acceptable.

When comparing multiple projects, a higher Profitability Index indicates a more desirable project, as it suggests a greater return for each dollar invested.

Conclusion

The Profitability Index is a valuable metric for capital budgeting, providing a clear ratio of benefits to costs. By mastering its calculation in Excel, you can efficiently evaluate investment opportunities and make informed financial decisions. Remember to consider other factors alongside PI, such as risk, strategic fit, and qualitative aspects, for a holistic project evaluation.