You will learn to find profitability index in Excel 2007, 2010 and 2013 by using a simple formula that accepts a discount rate, and a series of cash flows that make either start of period or end of period payments. You may select from the type of interest compounding as either discrete or as continuous. There are no Excel financial functions to calculate the profitability index. This allows third party Excel add-on products such as TADXL to provide for the missing and demanded financial functions. This discussion will begin with a cursory look at definition of profitability index followed by example calculation of the financial PI in Excel.
Profitability index
NPV and IRR may be the most commonly used financial tools to evaluate an investment yet there are other tools notably the profitability index that provides information about the relative profitability of one project as compared to the others. Profitability index is the ratio of discounted incomes over discounted expenses. The series of incomes and expenses are discounted to reflect the present worth of such future cash flows. The cash flows in various projects may differ in size thus making it useless to evaluate such projects on the basis of net present value and internal rate of return. In such cases, a profitability index is the right tool to find how one project provides more or less of profits when compared to the others.
Profitability index Excel function
As stated earlier in this discussion, Excel does not have a financial function to calculate the profitability index. But worry not, you can use the tadPI Excel function to find the profitability index in Excel. This is a 3rd party user defined Excel function that is part of a large library of financial functions called TADXL.
How to use tadPI in Excel
tadPI Excel function takes four arguments, of which the last two are optional: the first of these arguments is the discount rate, the second one is the range of cells that contain the cash flows. You may also provide the function with an array of cash flows where each cash flow is separated by a comma and all values are enclosed within curly brackets. The third argument is called type that takes a value of 1 or omitted for start of period cash flows and a value of 0 for end of period cash flows. The last argument is used to specify the type of interest compounding where a value of 0 or omitted is used for periodic compounding and a value of 1 is used for continuous compounding.
| A | B | C | D | E | F | |
| 1 | Cash Flow | -10000 | 3000 | 3000 | 3000 | 3000 |
| 2 | PI | =tadPI(2%, B1:F1, 0, 0) | 1.14231861 | =tadPI(2%, B1:F1, 0, 1) | 1.141760698 | |
| 3 | PI | =tadPI(2%, B1:F1, 1, 0) | 1.14231861 | =tadPI(2%, B1:F1, 1, 1) | 1.141760698 |
Excel profitability index calculator
You can download this Excel template to find profitability index by using tadPI Excel function.
