Question: How do I calculate the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods?
Answer: By using the internal rate of return (IRR)
Why: Returns the internal rate of return for a series of cash flows represented by the numbers in values. These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly or annually.
Applies To: Excel 2003, 2007 and 2010
1. Reference is made to the example in the following screen shot
2. To calculate the IRR select cell B10 and type; =IRR (B5:B9).The answer will be 18%. That is the interest rate received for the investment
Syntax IRR (Values, [guess])
Values Required. An array or a reference to cells that contain numbers for which you want to calculate the internal rate of return.
Values must contain at least one positive value and one negative value to calculate the internal rate of return.
Guess Optional. A number that you guess is close to the result of IRR.
If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email [email protected] and yours could be the next Tip of the Week.
Alchemex provides examples of MS Excel procedures for illustration only, without warranty expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The MS Excel procedures on this web site are provided “as is” cannot be guaranteed that they can be used in all situations
