The internal rate of return, or IRR, of an annuity describes the annualized return rate of the investment, which is useful for comparison to other investment opportunities. The IRR is defined as the rate that produces the total present value of each cash flow equal to the initial investment. However, the present value requires the IRR to discount each payment to the equivalent value in current dollars. This poses a problem that makes hand-calculating the IRR impractical because it requires repetitive trial-and-error calculations until you find the correct result. However, a spreadsheet simplifies this calculation by automatically performing a reiterative calculation of possible values until it finds the correct IRR.
Step 1
Type each cash flow, including the initial investment, down column A of your spreadsheet. Express money contributed, such as the initial investment, as a negative value. For the spreadsheet to calculate IRR, there must be at least one negative value and one positive value in the list, and each cash flow must use the same time period. For example, you might enter "-$20,000," "$5,000," "$12,000," "$10,000" and "$11,000" in cells A1 through A5. These values represent your initial investment and the cash flows of the next four years.
Step 2
Use the IRR function to calculate the IRR for the range of data in column A. In the example, you would enter "=IRR(A1:A5)" without quotes to calculate the IRR of 28 percent.
Step 3
Add a comma, followed by a "best guess IRR," after the formula range, if you encounter an error. The spreadsheet performs a limited number of iterations; if an answer isn't found before the maximum number of iterations is reached, it produces an error. By entering a guess, you start the iteration closer to the actual value to improve your chances of success. One way to calculate a "best guess" is to use the following formula:
Guess = (Total of Subsequent Cash Flows - Initial Investment) / Initial Investment / Number of Periods.
In the example, Add $5,000 plus $12,000 plus $10,000 plus $11,000 and subtract $20,000 to get $18,000. Divide by $20,000 to get 0.9. Divide again by 4 to get a guess of 0.23. Therefore, modify the formula to read "=IRR(A1:A5,0.23)". This will work for Microsoft Excel and Google Docs, but Apache OpenOffice Calc uses a semicolon instead of a comma, so use "=IRR(A1:A5;0.23)" in Calc.
References
Writer Bio
C. Taylor embarked on a professional writing career in 2009 and frequently writes about technology, science, business, finance, martial arts and the great outdoors. He writes for both online and offline publications, including the Journal of Asian Martial Arts, Samsung, Radio Shack, Motley Fool, Chron, Synonym and more. He received a Master of Science degree in wildlife biology from Clemson University and a Bachelor of Arts in biological sciences at College of Charleston. He also holds minors in statistics, physics and visual arts.