Function: IRR

by | Jan 30, 2019 | Excel Formulas

The IRR Excel function is a finance function which can calculatee the internal rate of return (IRR) of a series of periodic cash flows. The function assumes that the cash flows occurs at regular intervals. In this guide, we’re going to show you how to use the IRR Excel function and also go over some tips and error handling methods.

Supported versions

  • All Excel versions

Syntax

IRR(values, [guess])

Arguments

values

A series of cash flows. Cash flows must contain at least one positive value and one negative value. Payments are expressed as negative values and incomes as positive.

[guess]

Optional. Your estimation for an expected internal rate of return. Default value is 0.1 (10%).


Examples

Note that we've used named ranges in this example to make the formulas easier to read. This is not required.

Example 1

=IRR(cash_flow)
 formula demonstrates a simple use case for the IRR Excel function. It calculates the internal rate of return of an investment after four years. Payments are expressed as negative values and incomes as positive.

When the [guess] argument is omitted, Excel assumes it as 0.1 and starts iterations from this value. The IRR calculates iterations until achieving an answer within 0.00001% accuracy.

Example 2

=IRR(cash_flow,guess)
 formula is an example of using [guess] argument in the IRR function. You can see the result is equal to the sample that omits the [guess]. In most cases you, do not need to enter a [guess] argument. Try using different values if you get a #NUM! error.

Download Workbook

Tips

  • The IRR Excel function assumes that cash flows occur at regular intervals. Use the XIRR function instead for non-periodic cash flows and higher precision.
  • The rate of return is calculated by IRR is the interest rate corresponding to a 0 (zero) net present value (NPV). Using the return of the IRR in the NPV function returns 1.79E-09, which is equal to 0 (zero) within the accuracy of the IRR Excel. Sample formula:
    =NPV(IRR(cash_flow,guess),cash_flow)
  • This function ignores text, logical values, or empty cells.
  • Values should be in chronological order.
  • VBA also has an IRR function which uses the same syntax.

Issues

#NUM!

  • If the function cannot find a result within 0.00001% accuracy, or in 20 iterations, you will get a #NUM! error. Try changing the [guess] value.