Table of contents

Excel Functions to Calculate NPV and IRR

The Net Present Value (NPV) and the Internal Rate of Return (IRR) are crucial criteria for making investment decisions. While Excel makes it easy to model the future cash flows of an investment, the formulas for calculating NPV and IRR require a rigorous approach, otherwise the results of the calculation may be distorted.

There are indeed two types of functions in Excel for calculating the NPV and IRR of an investment. The following details outline how to use each function and their specificities: 

Summary Table of NPV and IRR Functions: 

NPV Excel Function

This function allows calculating the net present value of an investment using cash flows that occur at regular intervals. 

  • 1st parameter: discount rate of the period 
  • 2nd parameter: cash flows 

It should be noted that at least one value must be provided to obtain a result, and the formula allows up to 254 cash flow values in total. 

It is also important to understand that the calculation of the NPV function is based on future cash flows. This function discounts all flows starting from the first period of the selected range. Therefore, if you wish to include a flow occurring before the first period (zero flow), this value must be added to the function result and not included in the second parameter’s arguments. 

XNPV Excel Function

This function differs from the classic NPV formula in that it calculates the net present value of an investment using cash flows that occur at irregular intervals.

  • 1st parameter: annual discount rate 
  • 2nd parameter: cash flows 
  • 3rd parameter: cash flow dates 

The concept of dates becomes crucial in this function as it takes into account the specific payment schedule corresponding to the cash flows. It discounts all flows for the period, including those occurring in the first period. 

IRR Excel Function

This function calculates the internal rate of return of an investment using cash flows that occur at regular intervals. 

  • 1st parameter: cash flows 
  • 2nd parameter (optional): guess value 

This second argument, optional in the IRR formula, represents the rate that is estimated to be the closest to the actual result. Excel uses 10% as the default value. 

This function returns a periodic IRR, not an annual one. To convert the obtained periodic IRR to an annual IRR, you simply need to use the following formula: 

XIRR Excel Function

Similar to the XNPV function, this function allows calculating the internal rate of return of an investment using cash flows that occur at irregular intervals. 

  • 1st parameter: cash flows 
  • 2nd parameter: cash flow dates 
  • 3rd parameter (optional): guess value 

The XIRR function in Excel returns an annual IRR for the considered investment. 

Here is the formula defining the link between IRR and NPV: 

The IRR is defined as the discount rate for which the NPV value is zero. 

However, the above equation may have multiple solutions or even none, meaning an NPV may correspond to several or no IRR at all. This raises the question of how Excel calculates NPV and IRR. 

When calculating the IRR, Excel works through iteration and will return the first value found. Moreover, if no value is possible, Excel will return the error value “#NUM!”. 

This is where the guess argument in the IRR and XIRR functions becomes important: by specifying your IRR guess value, Excel will return the IRR closest to your estimate if multiple values are possible.