## Find profitability index in Excel 2007|2010|2013

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

## More Excel Functions

Other than this Excel tutorial, we have a bunch of other tutorials that you may read to find return on investment using Excel functions. For more details, please visit the Excel financial functions page.

Feedback

You can let us know what you think of our products and services by filling out the following form. Your comments will help other visitors to our site in judging the quality of our products and services.

Form fields marked with an * are required.