EXCEL IRR FUNCTION

The Excel IRR function calculates the Internal Rate of Return for a given set of periodic cash flows (i.e., an initial investment value and a series of net income values). 

Syntax :

=IRR( values[guess] )

Parameters :

The following parameters are:

  • Values – series of cash flows is represented as an array of values (or a pointer to a range of cells holding values) (investment and net income values). his must have at least one negative value (indicating incoming money) and at least one positive value (representing income).

 

  • Guess -A rough estimate of what the IRR may be.  This is an optional parameter; if omitted, the default value of 0.1 (=10 percent) is used.

 

  • (Note: This is merely a starting point for Excel to work with; Excel then utilises an iterative technique to converge to the IRR.)

Step By Step Guide of IRR Function

Example :

Cell B1 in the spreadsheet below shows a $100 initial investment, and cells B2-B6 indicate the net income over the next five years. The IRR function in cell C2 calculates the Internal Rate of Return after three years, and the function in cell C4 calculates the Internal Rate of Return after five years.

  • #NUM! – Occurs if either the given values array lacks at least one negative and one positive value, or the calculation fails to converge after 20 iterations.

Related Functions

HLOOKUP Function

HLOOKUP Function

Explore Now

VLOOKUP Function

VLOOKUP Function

Explore Now

INDEX Function

INDEX Function

Explore Now

MATCH Function

MATCH Function

Explore Now

DATE Function

DATE Function

Explore Now

IF Function

IF Function

Explore Now

OR Function

OR Function

Explore Now

AND Function

AND Function

Explore Now

RANK Function

RANK Function

Explore Now

SUMIF Function

SUMIF Function

Explore Now

XIRR Function

XIRR Function

Explore Now

WORKDAYS Function

WORKDAYS Function

Explore Now