How to use IRR Function in Excel

The IRR function 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 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.

Syntax:= IRR (values, [guess])

The IRR function syntax has the following arguments:

• Values    Required. An array or a reference to cells that contain numbers for which you want to calculate the internal rate of return.
  • Values must contain at least one positive value and one negative value to calculate the internal rate of return.
  • IRR uses the order of values to interpret the order of cash flows. Be sure to enter your payment and income values in the sequence you want.
  • If an array or reference argument contains text, logical values, or empty cells, those values are ignored.
• Guess    Optional. A number that you guess is close to the result of IRR.
  • Microsoft Excel uses an iterative technique for calculating IRR. Starting with guess, IRR cycles through the calculation until the result is accurate within 0.00001 percent. If IRR can’t find a result that works after 20 tries, the #NUM! error value is returned.
  • In most cases you do not need to provide guess for the IRR calculation. If guess is omitted, it is assumed to be 0.1 (10 percent).

  • If IRR gives the #NUM! error value, or if the result is not close to what you expected, try again with a different value for guess.

Example: Let’s look at some Excel IRR function examples and explore how to use the IRR function as a worksheet function in Microsoft Excel:

Syntax:  =IRR(B1:B3,-10%)

Result:

Based on the Excel spreadsheet above, the following IRR examples would return:

Note:

• The values array must contain at least one positive value and one negative value.
• Values should be in chronological order.
• If IRR returns the #NUM! or an unexpected result, adjust guess.