IRR Function

Returns the Internal Rate of Return for a given cash flow

Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.

What is the IRR Function?

The IRR Function[1] is categorized under Excel Financial functions. IRR will return the Internal Rate of Return for a given cash flow, that is, the initial investment value and a series of net income values.

The function is very helpful in financial modeling, as it helps calculate the rate of return an investment would earn based on a series of cash flows. It is frequently used by businesses to compare and decide between capital projects.

One example is when a company is presented with two opportunities: one is investing in a new factory, and the second is expanding its existing factory. Using the IRR function, we can estimate the IRRs for both scenarios and check which one is higher than the weighted average cost of capital (WACC) of the business (also known as a hurdle rate ). If both are greater than the cost of capital, we will choose the one that shows a higher IRR and/or Net Present Value (NPV) than the other.

Note: For greater precision, CFI recommends you use the XIRR function

Key Highlights

  • The IRR Function calculates the internal rate of return for a sequence of periodic cash flows.
  • As a worksheet function, IRR can be entered as part of a formula in a cell of a worksheet, i.e., =IRR(values,[guess]).
  • Businesses often use the IRR Function to compare and decide between capital projects.

IRR Formula


The IRR function uses the following arguments:

  1. Values (required argument) – This is an array of values that represent the series of cash flows. Cash flows include investment and net income values. Values can be a reference to a range of cells containing values.
  2. [Guess] (optional argument) – This is a number guessed by the user that is close to the expected internal rate of return (as there can be two solutions for the internal rate of return). If omitted, the function will take a default value of 0.1 (=10%).


  1. The argument value should contain at least one positive and one negative value to calculate the internal rate of return.
  2. The IRR function uses the order of the values to interpret cash flows. Hence, it is necessary to enter the payments and income values sequentially.
  3. If the array or reference argument contains logical values, empty cells or text, those values are ignored.

To learn more, launch our free Excel crash course now!

How to use the IRR Function in Excel?

As a worksheet function, IRR can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let us consider a few examples:

Example 1

For an initial investment of 1,000,000, the cash flows are given below:

IRR Function - Sample Table

The initial investment here is a negative value as it is an outgoing payment. The cash inflows are represented by positive values.

The internal rate of return we get is 14%.

IRR - Result

Example 2

Let’s calculate the CAGR using IRR. Suppose we are given the following information:

IRR - Sample Table

The IRR function is not exactly designed for calculating compound growth rate, so we need to reshape the original data in this way:

IRR - Rearranging Data

The beginning value of the investment should be entered as a negative number. The ending value of the investment is a positive number. All intermediate values are kept as zeros.

Now, we would simply apply the IRR function to calculate the CAGR, as shown below:

IRR - Using the IRR function to calculate the CAGR

The CAGR we get is:

IRR - CAGR Result

Let’s do reverse engineering to check if the resulting value is correct or not.

The formula generally used to calculate CAGR is =((End Value/Start Value)^(1/Periods) -1.

The formula to use will be:

IRR - CAGR Formula


The result we get is same as the one we got using IRR function:

IRR - CAGR Result

Things to remember about the IRR Function

  1. #NUM! error – Occurs when:
    • If the given value array does not contain at least one negative and one positive value
    • The calculation fails to converge after 20 iterations. If the internal rate of return is unable to find a result that works after 20 tries, then the #NUM! error value is returned.
  2. IRR is closely related to the NPV (Net Present Value) function. The rate of return calculated by IRR is the discount rate corresponding to a $0 (zero) NPV.

Free Excel Course

If you want to learn more about functions and become an expert on them, check out CFI’s Free Excel Crash Course!  Go through our step-by-step instructions and demonstrations on how to become an Excel power user.

free Excel keyboard shortcuts course

Additional Resources

Thank you for reading CFI’s guide to IRR Function. To learn more, check out these additional resources:

See all Excel resources

Article Sources

  1. IRR Function
0 search results for ‘