IRR function

The IRR function in Excel is used to calculate the Internal Rate of Return (IRR) for a series of cash flows. The IRR is the rate at which the net present value (NPV) of the cash flows equals zero. It is commonly used in capital budgeting and investment analysis to evaluate the profitability of an investment or project.

Syntax

IRR(values, [guess])

Parameters

• values: A range or array of cash flows that represents the investment or project’s cash inflows and outflows. These cash flows should include both negative and positive values:
  • Negative values represent cash outflows (such as an initial investment).
  • Positive values represent cash inflows (such as returns from the investment). The values must be in chronological order (starting with the initial investment, followed by the subsequent periods’ cash flows).
• [guess] (optional): An estimate of the IRR. If omitted, Excel assumes a guess of 10%. Providing a guess may help the function find the IRR more quickly or accurately, especially for more complex cash flow series with multiple IRRs.

How It Works

The IRR function calculates the rate of return at which the present value of the cash flows equals zero. It uses an iterative process to approximate this rate, meaning it tries different values of the IRR and evaluates the NPV until the NPV gets as close to zero as possible.

The formula for IRR is:

$$\text{NPV} = \sum \frac{\text{Cash Flow}_t}{(1 + \text{IRR})^t}$$

Where:

• Cash Flow: Cash inflow or outflow at each period.
• IRR: The internal rate of return.
• t: The time period.

Example

Let’s say you have the following cash flows for an investment:

• Year 0: Initial investment of $-10,000 (outflow)
• Year 1: Return of $3,000 (inflow)
• Year 2: Return of $4,000 (inflow)
• Year 3: Return of $5,000 (inflow)

To calculate the IRR for these cash flows:

=IRR(A1:A4)

Where the values in cells A1 to A4 are:

• A1: -10000 (initial investment)
• A2: 3000 (return in Year 1)
• A3: 4000 (return in Year 2)
• A4: 5000 (return in Year 3)

Explanation of the Example:

• The cash flows (values) for the investment include an initial outflow of $10,000 and inflows of $3,000, $4,000, and $5,000 over the next three years.
• The IRR function will return the internal rate of return that makes the NPV of these cash flows equal to zero.

Important Notes

• The IRR function assumes that cash flows are made at regular intervals (e.g., yearly, monthly). If the cash flows occur at irregular intervals, you may need to use the XIRR function instead.
• IRR can have multiple solutions or no solution at all if the cash flow series involves multiple changes in the sign of cash flows (e.g., alternating between inflows and outflows).
• If the IRR function cannot find a solution after several iterations, it will return an error (usually #NUM!). In such cases, you may want to provide a different guess or adjust the cash flow series.

Example with Guess Parameter

If you want to improve the calculation’s accuracy or speed, you can provide a guess for the IRR:

=IRR(A1:A4, 0.1)

In this case, the guess is 10% (0.1).

Summary

The IRR function in Excel calculates the Internal Rate of Return (IRR) for a series of cash flows. It is widely used in finance and investment analysis to assess the profitability of projects and investments. By finding the rate that makes the NPV of cash flows equal to zero, the IRR gives an estimate of the expected annual return.