IRRfunction is used to calculate the Internal Rate of Return for a series of cash flows. The IRR is the interest rate at which the Net Present Value of the cash flows equals zero. It is commonly used to evaluate potential investments or projects.
- How to use
- Examples of using
IRRformula not working?
- Similar formulas to
IRR formula with the syntax shown below, it has 1 required parameter and 1 optional parameter:
- 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.
- 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.
ExamplesHere are a few example use cases that explain how to use the
IRRformula in Google Sheets.
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.
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.
IRRnot working? Here are some common mistakes people make when using the
IRRGoogle Sheets Formula:
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.
The following functions are similar to
IRR or are often used with it in a formula:
NPVfunction 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.
XIRRfunction 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.
MIRRfunction 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.
You can learn more about the
IRR Google Sheets function on Google Support.