IRR

• How to use IRR formula?
• Examples of using IRR formula
• IRR formula not working?
• Similar formulas to IRR

Usage

Use the IRR formula with the syntax shown below, it has 1 required parameter and 1 optional parameter:

=IRR(cashflow_amounts, [rate_guess])

1. cashflow_amounts (required):
   An array or range of cash flows representing the initial investment and subsequent income received. The first cash flow must be a negative number representing the initial investment.
2. rate_guess (optional):
   An optional argument representing an estimate for the IRR. If omitted, the function will use 0.1 (10%) as the default value.

Examples

Evaluating investment opportunities

IRR is often used by investors to determine whether a potential investment is worth pursuing. By comparing the IRR to the investor's required rate of return, they can decide whether the investment meets their criteria for profitability.

Comparing investment options

When choosing between multiple investment opportunities, the IRR can help investors compare the potential profitability of each option. The investment with the highest IRR is generally considered the most attractive option.

Project evaluation

IRR can also be used to evaluate the profitability of a business project. By comparing the IRR to the project's cost of capital, business owners can determine whether the project is worth pursuing.

Common Mistakes

Incorrect cashflow amount format

IRR requires cashflow amounts to be in a consecutive range of cells, either all positive or all negative. If the format is incorrect, IRR will return a #NUM error. To fix this mistake, ensure the cashflow amounts are in a consecutive range and have the correct signs.

Missing cashflow amounts

IRR requires at least one positive and one negative cashflow amount to calculate the internal rate of return. If there are missing cashflow amounts, IRR will return a #NUM error. To fix this mistake, ensure there are both positive and negative cashflow amounts in the specified range.

Incorrect rate guess

IRR requires a rate guess to start the calculation. If the rate guess is too far from the actual IRR, IRR may return a #NUM error. To fix this mistake, provide a rate guess that is closer to the expected IRR.

Incorrect input range

IRR requires a range of cashflow amounts as its first argument. If the input range is incorrect, IRR will return a #VALUE error. To fix this mistake, ensure the input range is correct and includes the correct number of cashflow amounts.

Multiple IRR solutions

IRR may have multiple solutions for complex cashflow scenarios. In these cases, IRR may return a #NUM error. To fix this mistake, try using the XIRR function instead or adjust the cashflow scenario to have a single IRR solution.

Related Formulas

The following functions are similar to IRR or are often used with it in a formula:

• NPV

  The NPV function calculates the net present value of a series of cash flows, discounted by a specified rate. It is commonly used to determine the present value of an investment, where the cash flows represent incoming and outgoing payments. The function takes a discount rate and one or more cash flow values as input.

• XIRR

  The XIRR function in Google Sheets calculates the internal rate of return (IRR) for a series of cash flows that occur at irregular intervals. It returns the annualized percentage rate earned by an investment, taking into account the dates on which each payment was made. The function is most commonly used in financial analysis to compare the returns on different investment opportunities.

• MIRR

  The MIRR function calculates the modified internal rate of return for a series of cash flows that occur at irregular intervals. It is commonly used in financial analysis to compare the profitability of different investments. The function takes three arguments: the cash flows, the financing rate, and the reinvestment rate.

Learn More

You can learn more about the IRR Google Sheets function on Google Support.