Function: IRR in Excel

by | Jan 30, 2019

The IRR Excel function is a crucial finance tool for calculating the internal rate of return (IRR) of periodic cash flows. It assumes that cash flows occur at regular intervals. In this guide, we'll demonstrate how to use the IRR Excel function effectively, including essential tips and error handling methods. Whether you're a finance professional or an Excel user handling financial data, mastering the IRR function is essential for accurate financial analysis. Join us as we explore the ins and outs of the IRR Excel function, empowering you to make informed financial decisions.

IRR Excel: Supported versions

  • All Excel versions

IRR Excel: Syntax

IRR(values, [guess])

IRR Excel: 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

Welcome to our comprehensive guide on examples for IRR! 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.