How to use XIRR in Excel to calculate annualized returns

Corporate finance, Excel function tutorials, Personal finance

I’ve previously created a as well as an , but these aren’t always the tools you need when trying to calculate your returns.

In fact, the two CAGR tools I created really only work when you have one initial amount and one final amount, and if that’s your situation, then they work wonderfully. But how can you calculate annualized returns if you put in money to invest in an account more than once, or if you make withdrawals more than once over a period of time? (This was actually exactly the situation I faced when I calculated my stock portfolio performance recently.)

The answer is actually pretty simple if you use Excel. There’s a built-in function called XIRR() that’s easy to use if you format your data correctly. XIRR() uses Newman’s method (an iterative process) to calculate your returns, so it’s not a method that’s easily done by hand.

To find your return, simply list the dates of your deposits and withdrawals in one column. In the next column, list your deposit amounts as positive numbers and your withdrawals (or the current value of the funds you’d be able to withdraw, if you’re interested in that instead) as negative numbers.

For example, let’s suppose you diligently put in a deposit into your 401(k) account on the first weekday of each month. (Maybe you try to deposit in $100 each month and deposit more in January after getting your bonus, but let’s say you also deposit less in one month due to extenuating circumstances.) Then suppose that today, October 18th, you look up how much your 401(k) is worth and see a balance of $1,247 in your account. If you just looked at the total amount you’ve invested so far ($1,100) and your current value of $1,247, you’d get a return of 13.36%. But remember, some of your money has been invested for a longer time than others, so this amount is really an underestimation.

Instead, suppose you want to find the annualized percentage return you’ve gotten so far taking time into account. To use XIRR, this is how you’d enter in your data:

Note: If you’d like to work through the examples yourself, here’s the raw data you can copy into an Excel worksheet. First, open up a blank excel worksheet. Next, highlight the table below. Copy it, and then go back to your excel worksheet. Go to cell A1 (or another empty cell, if you want to put the data elsewhere), and then select “Edit” from the menu bar. Select “Paste Special” and then “Text” from the popup box. Click “OK”. The data should appear in your Excel worksheet just as it does above.

1/2/2006 200
2/1/2006 100
3/1/2006 100
4/3/2006 100
5/1/2006 50
6/1/2006 100
7/3/2006 100
8/1/2006 100
9/1/2006 150
10/2/2006 100
10/18/2006 -1247

Ok, now back to the tutorial.

Next, find XIRR under the “Financial” section of the Function section. XIRR has two required inputs and two optional inputs (a guess, and max number of iterations, both of which we’ll ignore here). The two required inputs are simply the list of values and the list of dates. Let’s suppose that the dates above start in column A, row 1 of a spreadsheet and the values start in column B, row 1.

Then in your selected cell, you’d simply type in:

=XIRR(B1:B11,A1:A11)

to find your annualized return of 32.3%.

That’s it. Pretty simple, right?

Notice how the annual return calculation using XIRR is differs from some other common ways you might use to measure return: you could calculate your overall return of 13.4% (=1247/1100 – 1) by ignoring the time period over which you invested your money into account. If you were to use CAGR, then you might enter in your initial amount of $1,100, your final amount of $1,247, but what period of time would you use? If you chose to use 0.875 of a year (from the beginning of January through mid-October), your CAGR would come out to be 15.4%, which underestimates your investment return because it assumes you invested all your money at once, in January.

There’s no one “right” way to calculate your returns. What you mainly want to focus on are knowing what you’re calculating (data are dumb, garbage-in-garbage-out) and being consistent about the way you compare your returns. Don’t compare returns calculated using one method for some things and another method for others!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

Related posts:


Fatal error: Call to undefined function related_posts() in /home/exp571/public_html/wp-content/themes/a blog beyond theme/single.php on line 96