Profitability index Excel function
Written by:Abraham A.Here, I will illustrate use of a procedural method to find profitability index with MS Excel formula.
What is Profitability Index
Profitability Index is a discounted cash flow technique used in financial analysis to evaluate capital investment proposals. Profitability Index is computed as the ratio of sum of discounted net cash flows over the initial expense. Profitability Index greater than 1 is acceptable, a Profitability Index of 1 suggests there are no benefits to be had from the project. A Profitability Index of less than 1 means it is a losing proposition
Profitability Index Formula in Excel
MS Excel provides NO built in formula to find Profitability Index, we put together the Profitability Index formula by using the Excel NPV function and dividing it by the initial cash outflow. To use this formula you type in
=NPV(interest_rate,values)/value.
Here the values are a range of cells such as A1:B10 that are annual or periodic annuity payments. The first argument in the formula is the the interest rate at which to discount the cash stream.
Profitability Index MS Excel Example
Miami Herald newspaper is planning an installation of soda machine for its fire fighters. The machine will cost $12,000 and is expected to generate sales in amounts of $3000, $4000, $5000, and $6000 each of the next four years. Herald's has a WACC of 9%. Compute the Profitability Index
In Cell A1, Type the text WACC. In Cell B1 enter the value 9%.
In Cell A2, Type the text Time, In Cell B2 type 0, in cell C2 type 1, in cell D2 type 2, in cell E2 type 3 and in cell F2 type 4.
In Cell A3, Type the text Cash Flows, In Cell B3 type -12000, in cell C3 type 3000, in cell D3 type 4000, in cell E3 type 5000 and in cell F3 type 6000.
In Cell A4, Type the text PI. In Cell B4 enter the following function
=NPV(B1,C3:F3)/ABS(B3)
MS Excel Profitability Index Template
Download this MS Excel Template to find Profitability Index| A | B | C | D | E | F | |
| 1 | WACC | 9% | ||||
| 2 | Time | 0 | 1 | 2 | 3 | 4 |
| 3 | Cash Flow | -12000 | 3000 | 4000 | 5000 | 6000 |
| 4 | PI | 1.19 |
Related Investment Analysis Excel Templates
Following is a list of related Excel templates that cover other 5 commonly used investment analysis methodsProfitability index Calculator
|
type in the authorization code in the box located below:
|
|
ResultsProfitability Index: 1.54
|
|
Input Datatype in the reinvestment rate (WACC) aka discount rate: %type in net cash flows in the space below: |
|
Instructions
- Enter the series of cash flows in the text box where each of the cash flows is separated by a space.
- Enter the discount rate (aka WACC) at which to discount the cash flows
