How to Find the IRR of an Annuity

The IRR of regular payments is calculated using a spreadsheet.
i Dynamic Graphics/Dynamic Graphics Group/Getty Images

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.

the nest