1. Money

How to Calculate Internal Rate of Return

Figure Out What Return Your Investments Make Each Year

By

Internal Rate of Return XIRR

This helps you discover how your investments -- like your retirement portfolio -- are performing over time.

© Paula Pant

How big of a return do your investments REALLY make?

The answer is tougher to calculate than you might expect.

Let’s say Jim invests $1,000 on New Year’s Day. The money sits for a year. The following New Years, Jim checks his balance and sees that he now has $1,100.

Clearly, he’s earned an annual percentage yield of 10 percent. That’s obvious.

But what if Jim invested only $400 in January, $200 in April, $350 in August and $50 in December? His contribution throughout the year stills total $1,000. But if he has $1,100 in his account by the following January 1, he would have earned more than 10 percent annualized.

“But he only invested $1,000 and now he has $1,100. Why is that more than 10 percent?”

Because of the time value of money. He’s only kept $400 in his investment account for the span of one calendar year. The rest of the money entered his account in bits and chunks throughout the year. How can he calculate his annual return?

Enter: a calculation called the Internal Rate of Return, or IRR. This formula helps you adjust your returns based on the amount of time the money is invested, so you can decipher your annual percentage yield.

Calculating it is simple: Just open Microsoft Excel. In one column, list an increment of time (like months of the year). In the next column, list your contributions. Then calculate your Internal Rate of Return using a function called XIRR.

Example:

Let’s say you contribute $416 per month ($5,000 per year) into a retirement savings account.

On January 1 of the following year, you have a total balance of $5,300. You want to know what kind of return you had.

Step 1: List 12 months (Jan – Dec) in a column in Excel.

Step 2: List $416 after each month, in a separate column.

Step 3: In the final row, list January of the following year (in the months column), and list your $5300 total (in the amounts column).

Step 4: Use the XIRR function to calculate your return.

©2014 About.com. All rights reserved.