Calculating Net Present Value (NPV) and Internal Rate of Return (IRR) in Excel

Net Present Value (NPV) Function

The NPV function calculates the present value of a series of cash flows at equal time intervals. The function is represented as follows:

= NPV(rate,value1,value2,...)
Here, rate is the discount rate for one period, and values are the cash flows. Any payments are entered with a negative sign, and income is entered as positive.

Note that even though the function is named Net Present Value (NPV), it doesn’t really calculate the net present value. This is because it does not take into consideration the initial investment at time 0.

To calculate the net present value, you will need to subtract the initial investment from the result you get from the NPV function.

Lets take an example to demonstrate this function. Assume that you started a business with an initial investment of $10,000 and received the following income for the next five years.

To calculate the net present value, we will apply the NPV function as follows:

This is the present value of all the future cash flows.

The net present value will be:

Net Present Value = 11,338.77 – 10,000
= $1,338.77

Internal Rate of Return (IRR) Function

IRR is based on NPV. It as a special case of NPV, where the rate of return calculated is the interest rate corresponding to a 0 (zero) net present value.

IRR function is represented as follows:

= IRR(values,guess)

This function accounts for the inflows and the outflows, including the initial investment at time 0.

Using the same example above, the IRR calculation is shown below:

The IRR of 14.974% means that at this rate the net present value will be zero.

Other Related Functions

MIRR: MIRR calculates the modified internal rate of return for a series of periodic cash flows, considering both cost of investment and interest on reinvestment of cash.

Syntax: MIRR(values,finance_rate,investment_rate)

XNPV: This function calculates the net present value for a series of cash flows that are not equally spaced in time.

Syntax: XNPV(rate,values,dates)

XIRR: This function calculates the internal rate of return for a series of cash flows that are not equally spaced in time.

Syntax: XIRR(values,dates,guess)

Download the NPV IRR Excel to view these example calculations.

Download: NPV IRR Calculations Excel

Related Downloads

Related Quizzes

Capital Budgeting

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 $29 (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.