ExcelFunctions.net

Excel IRR Function

Related Function:
Home » Excel-Built-In-Functions » Excel-Financial-Functions » Excel-Irr-Function

Internal Rate of Return

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

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:

Internal Rate of Return Equation

where the series of cash flows provide the values for Cn and N is 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

Basic Description

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

The syntax of the function is :

IRR( values, [guess] )

Where the arguments are as follows :


values -

A reference to a range of cells containing the series of cash flows (investment and net income values)

(must contain at least one negative and at least one positive value)
[guess] -

An initial guess at what you think the IRR might be. This is an optional argument, which, if omitted, takes on the default value of 10% (=0.1)

(This is only a value for Excel to start off working with - Excel then uses an iterative procedure to converge to the IRR)

Irr Function Example

In the spreadsheet below, the cashflow for an investment is shown in cells B1 - B6. The initial investment of $100 is shown in cell B1 and the net income over the next 5 years is shown in cells B2 - B6.

The IRR function in cell D2 shows the calculation of the Internal Rate of Return after 3 years and the function in cell D4 shows the Internal Rate of Return after 5 years. The formulas for the equations are shown in the spreadsheet on the left and the results are shown in the spreadsheet on the right.

 Formulas:
Example of use of the Excel IRR Function 		 Results
Excel IRR Function Example Results

Further information and examples of the Excel Irr function can be found on the Microsoft Office website.


Irr Function Error

If you get an error from the Excel Irr function, this is likely to be the #NUM! error:

Common Error
#NUM! - Occurs if either:
          -   the supplied values array doesn't contain at least one negative and at least one positive value
or
          -   the calculation failed to converge after 20 iterations