The bad news is that Excel has none of its own financial functions to find the profitability index. The good news however is that tadXL, a library of financial functions offers a large number of financial functions including one to find the financial PI. Here, me Abraham A. as your guide will demonstrat the use of tadPI function to calculate the profitability index using Excel formula.
All tad Finance products are available for download at this link.
The profitability index is one the tools that are used by a financial analyst in evaluating investments to find an investor's ROI - return on investment. It is a ratio of discounted profits over discounted expenditures that show the relative profitability of one investment over the other investments. To find manually will require the sum of discounted profits and the sum of discounted expenditures and their ratio will result in the profitability index. Excel offers no such financial functions to find the profitability index however 3rd party tools like tadXL add-in for Excel 2007, 2010, and 2013 offer tadPI function. This particular function is one of several financial functions in the tadXL package that are used for investment analysis.
Before we proceed to find profitability index in Excel, you would need the tadXL add-in which is available for download here. There are different versions of this add-in i.e. v0.5, v1.0, v2.0 and v2.5 and you would have to select one of them for installation.
The profitability index Excel function requires the following values as input:
=tadPI ( discount_rate, cash_flows, type, compounding, period, distribution )
You can download a copy of profitability index template here.
Finance tutorials on a variety of topics ranging from finding ROI using IRR, NPV, Payback period, etc. TVM calculations i.e. present value & future value of loans and bank deposits. Valuation & yield on stocks and bonds i.e. cost of equity, & debt. Rates of return on investments i.e. ARR, GRR, holding period return & yield.
Excel 4 finance to perform DCF analysis NPV, xNPV, IRR, xIRR, MIRR, xMIRR etc. Time value of money calculations i.e GRADIENT, INTEREST RATE, NPER, PMT, etc, yields and prices of bonds and stocks. Rates of return i.e. HPR, HPY, AHPR, and AHPY, etc.
TI BA II plus guide to analyze investments using capital budgeting methods i.e IRR, NPV, MIRR etc. TVM calculations i.e. RATE, NPER, PMT, PV, FV. Interest rate factors i.e. PVIFA, PVIF, FVIFA, FVIF, etc .
Finance tables to find interest factors for present value and future value of $1 and of annuities with start of period and end of period payments in amount of $1. Interest rates for growing and shrinking annuities are available as well.