IRR(values [,guess])

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

valuesA series of cash flows.
guess(Optional) An estimate as to what the rate will be.

Remarks

* 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.
* You can use the NPV function to return the present value of a series of unequal cash flows at regular intervals.
* In Excel 2010 the accuracy of this function was improved.
* The equivalent VBA function is IRR
* For the Microsoft documentation refer to support.office.com

 AB
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.

© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited Top