IRR

Formulas / IRR
Determines the internal rate of return for a given investment.
=IRR(values, [guess])
  • values - an array of Double values representing cash flows with at least one negative value in it
  • guess - [OPTIONAL] a variant used to estimate what IRR should return. IRR sets guess to 10% (0.1) by default.

Examples

  • =IRR(A2:A6)

    This calculates the internal rate of return for an investment after four years.

  • =IRR(A2:A7)

    This calculates the internal rate of return after five years.

  • =IRR(A2:A4,-10%)

    This calculates the internal rate of return after two years. The guess for the IRR is -10%.

Summary

The IRR function calculates the internal rate of return for a set of cash flows that are not necessarily even.

  • The IRR function returns the internal rate of return for an investment that consists of regular intervals (e.g. monthly, annual) of payments and income.
  • The arguments for the IRR function are values (required) and guess (optional).
  • The guess argument is an estimate for the IRR function.
  • Note that the array in the values argument requires a minimum of one positive and one negative number.
  • Note that if the IRR function throws a #NUM! error or returns an unexpected result, you should change the guess argument.

Frequently Asked Questions

What is the internal rate of return (IRR)?
The internal rate of return is the interest rate earned on an investment that has regular period payments and income.
How is the internal rate of return calculated?
The IRR function calculates the internal rate of return by using a series of cash flows, which appear as numerical values in the function argument.
What are the requirements for the cash flows in the IRR function?
Unlike an annuity calculation, cash flows in the IRR function do not have to be even. However, they must occur at regular time intervals.
How does Sourcetable calculate IRR?
Sourcetable calculates IRR with an iterative technique. The IRR throws a #NUM! error if IRR does not work in 20 tries.

Make Better Decisions
With Data

Analyze data, automate reports and create live dashboards
for all your business applications, without code. Get unlimited access free for 14 days.