Calculating Profitability Index in Excel

Profitability index is an important measure in project finance to decide whether to invest in a project or not. It is calculated as the ratio of present value of a project cash flows and the initial investment.

Profitability Index=PV of Future Cash FlowsInitial InvestmentProfitability\ Index = \frac{PV\ of\ Future\ Cash\ Flows}{Initial\ Investment}

If the profitability index is greater than 1, the project is accepted, and if it is less than 1, the project is rejected.

Let’s see how profitability index can be calculated in excel. Let us say that we are examining a project, which requires an initial investment of $10,000, and after the will give us cash flow of $3,000, $4,000, $2,000, 41,500, and $1,800 in the next five years.

To calculate the profitability index:

Step 1: Assume a required rate of return, or cost of capital for the project. Let’s say the cost of capital is 10%.

Step 2: Calculate the present value of all future cash flows. You can use the PV() function in excel for this calculation.

Step 3: Take the total of PV of all future cash flows. In our example, the total is 9677.87.

Step 4: Calculate profitability Index as follows:

Profitability Index = 9677.87/10,000 = 0.97

Since the profitability index is less than one, this project should not be accepted. The cash flows and calculations are shown below:

Post Resources

Member-only

Data Science in Finance: 9-Book Bundle

Data Science in Finance Book Bundle

Master R and Python for financial data science with our comprehensive bundle of 9 ebooks.

What's Included:

  • Getting Started with R
  • R Programming for Data Science
  • Data Visualization with R
  • Financial Time Series Analysis with R
  • Quantitative Trading Strategies with R
  • Derivatives with R
  • Credit Risk Modelling With R
  • Python for Data Science
  • Machine Learning in Finance using Python

Each book includes PDFs, explanations, instructions, data files, and R code for all examples.

Get the Bundle for $39 (Regular $57)
JOIN 30,000 DATA PROFESSIONALS

Free Guides - Getting Started with R and Python

Enter your name and email address below and we will email you the guides for R programming and Python.

Data Science in Finance: 9-Book Bundle

Data Science in Finance Book Bundle

Master R and Python for financial data science with our comprehensive bundle of 9 ebooks.

What's Included:

  • Getting Started with R
  • R Programming for Data Science
  • Data Visualization with R
  • Financial Time Series Analysis with R
  • Quantitative Trading Strategies with R
  • Derivatives with R
  • Credit Risk Modelling With R
  • Python for Data Science
  • Machine Learning in Finance using Python

Each book comes with PDFs, detailed explanations, step-by-step instructions, data files, and complete downloadable R code for all examples.