IRR

IRR(values [,guess])

Returns the interest rate for a series of unequal cash flows at regular intervals (implicit reinvestment rate).

values	A series of cash flows.
guess	(Optional) An estimate as to what the rate will be (10%).

REMARKS

* The IRR stands for Internal Rate of Return.
* This function uses an iterative approach to find a solution.
* This function assumes all payments are at the end of each period.
* A negative number represents any cash you pay out.
* A positive number represents any cash you are receive (start with or end with).
* If the cash flows are monthly, you need to multiply the value returned by 12 to get an annual rate of return.
* The "values" series must contain at least one positive value and one negative value. Example ??
* This function has an implicit assumption that all cash flows you receive are reinvested immediately at the same rate of return.
* If the function does not converge, you should try a different value for the "guess".
* Using a different "guess" might return a different result if there is more than one possible rate.
* If there is more than one acceptable answer, then the first one it finds will be returned.
* If the successive results of this function do not converge to within 0.0000001 after 20 iterations, then #NUM! is returned.
* Any text, logical values or blank cells are ignored.
* If an array or reference argument contains text, logical values, or empty cells, those values are ignored.
* If "guess" is left blank, then 0.1 (or 10%) is used. In most cases you will not need to provide a "guess".
* You can use the RATE function to return the interest rate for a series of equal cash flows at regular intervals.
* You can use the MIRR function to return the interest rate for a series of unequal cash flows at regular intervals (explicit reinvestment rate).
* You can use the XIRR function to return the interest rate for a series of unequal cash flows at irregular intervals (implicit reinvestment rate).
* You can use the NPV function to return the net present value of a series of unequal cash flows at regular intervals.
* The equivalent VBA function is VBA.IRR
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

	A	B
1	=IRR({-10, 10}) = 0%	-100
2	=IRR({-10, 0, 10}) = 0%	50
3	=IRR({-10, 10, 0}) = 0%	40
4	=IRR({-10, 0, 0, 10}) = 0%	-30
5	=IRR({-20, 10, 10}) = 0%	25
6	=IRR({-30, 10, 20}) = 0%
7	=IRR({-60, 20, 20, 30}) = 8%
8	=IRR({-60, 20, 30, 20}) = 8%
9	=IRR({-60, 30, 20, 20}) = 9%
10	=IRR({-20, 10, 10, 10}) = 23%
11	=IRR({-20, 10, 10, 10, 10}) = 35%
12	=IRR({-50, 10, 20}) = -26%
13	=IRR({-10, 10, 20}) = 100%
14	=IRR({-20, 10, 20}) = 28%
15	=IRR(B1:B5) = -9%
16	=NPV(IRR(B1:B5), B1:B5) = 0.00
17	=IRR({40, 10}) = #NUM!
18	=IRR(B7:B8) = #NUM!
19	=IRR("-10;10") = #NUM!

1 - What annual interest rate have you received if you pay out £10 initially and then receive £10 after one year.
2 - What annual interest rate have you received if you pay out £10 initially and then receive £10 after one month.
3 - What annual interest rate have you received if you pay out £10 initially and then receive £10 after one day.
15 - Important - The result is 0 which proves that the IRR function has returned the correct rate.