Using the Excel IRR Function
SummaryThe internal rate of return (IRR) function is one of Excel's financial functions and returns the internal rate of return for a series of cash flows represented by numbers in values. Unlike, PV and FV calculations, IRR cash flows do not have to be even. However, the cash flows must occur at regular intervals, such as monthly.
The IRR is the interest rate received for an investment consisting of payments, represented as negative numbers, and income, shown as positive numbers, that occur at regular periods.
Syntax=IRR (values, [guess])
Required. An array or cell reference that contain numbers for which you want to find the IRR. The values argument must contain at least one positive and one negative number.
Be sure to enter the payment and income values in the correct order as the IRR function uses the order of values to interpret the order of cash flows.
The function ignores text and logical values and empty cells.
Optional. The user's guess as to what the IRR will be. If omitted, it is assumed to be 10%.
If IRR returns the #NUM! error or is drastically different from what you expected, try again with a different guess value.
Usage NotesIRR returns the internal rate of return for a series of cash flows that occur at regular intervals, such as monthly or annually. Payments should be expressed as negative numbers while income is positive.
The first value needs to be negative, as this represents cash outflows.
Similar to the RATE function, IRR uses iteration to find a result. IRR begins with the guess value, if provided, or 10%. If the IRR cannot be found after 20 iterations, the function returns the #NUM! error. Adjusting the guess value can help prevent this error.