The IRR function returns the Internal Rate of Return for a supplied series of periodic cash flows, i.e. a set of values, which includes an initial investment value and a series of net income values.

Syntax

=IRR(values,[est_irr])

Arguments

Argument Description
values A range of cells that represent the series of cash flows
[est_irr] Optional. It is your guess at the internal rate of return. If this parameter is omitted, it assumes an [est_irr] of 0.1 or 10%

Note: Excel tries to recalculate the IRR until the result is accurate within 0.00001 percent. If after 20 tries Excel has not calculated an accurate value, it will return the #NUM! error.

Examples

  A B C
1 Data    
2 -$7,500 Initial investment  
3 $3,000 Income (yr 1)  
4 $5,000 Income (yr 2)  
5 $1,200 Income (yr 3)  
6 $4,000 Income (yr 4)  
7 $3,800 Income (yr 5)  
8      
9 Formula Result Notes
10 =IRR(A2:A5) 12.16% Investment’s internal rate of return after 3 years
11 =IRR(A2:A7) 35.04% Investment’s internal rate of return after 5 years
12 IRR(A2:A4,-10%) 4.06% To calculate the internal rate of return after two years, you need to include a guess, in this example, -10%

Cash Flow Convention: In line with general cash flow conventions, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers.

Common Function Error(s)

Problem What went wrong
#NUM! Occurs if:

  the supplied values array does not contain at least one negative and at least one positive value
  the calculation failed to converge after 20 iterations

The Internal Rate of Return (IRR) indicates the profitability of an investment and therefore is commonly used in business, when choosing between investments.

This measurement uses a series of cash flows (including an initial investment, along with the net income) over a number of periods, to calculate the compounded return, assuming the Net Present Value of the investment is zero.

The value of the IRR is calculated as the value of r that satisfies the following equation:

    \[ NPV=\sum_{n=0}^N \frac {C_n}{ \left(1+r \right)^n} = 0 \]

where:

  • Cn = a series of cash flows
  • N = the number of periods over which the returns have been made

A full explanation of the IRR can be found on the Wikipedia Internal Rate of Return page.