• Tweet

• Tweet

As you invest, it is important to know about how you are doing.  It is easy to see how your individual investments are doing each year.  They report to you their time-weighted return.  Of course, that isn’t the return you generate.  You get a dollar-weighted return.  It turns out that the dollar-weighted return for most investors is far lower than the time-weighted return of their investments.  This occurs mostly due to performance chasing and the buying high/selling low phenomenon that results from it. While I don’t think you need to look at your investments every day, or even every month, from time to time you ought to check in and see how you are doing.  If you don’t know what returns you have been getting, it is hard to gauge how well you are progressing toward your goals.  It is also easier to get sucked into investments that promise a high return, but don’t actually deliver.  In short, being able to calculate your own return empowers you as an investor.

The best way to calculate your return is to use the Excel XIRR function.  This gives you a dollar-weighted return because it takes into account the timing and amount of your cash flows into and out of your retirement funds.  It is surprisingly easy to calculate.  All you need to know is the amounts you have put in or taken out of the account and the dates on which you did that.  Here’s a quick tutorial:

Put the amounts of your cash flows into column A. Amounts you contributed to retirement and other investment accounts are positive.  Amounts you took out are negative.  The last entry should be the current amount you have, as a negative number.  Put the dates of the cash flows into column B.  You need to use the excel DATE function to do this.  It looks like this: =DATE(2004,8,16) where 2004 is the year, 8 is the month, and 16 is the date.

Now, in another cell, put in the xirr function.  It looks like this: =XIRR(A1:A10, B1:B10, 5%) where your cash flows are in cells A1 to A10, your date functions are in cells B1 to B10, and 5% is your estimated return.  (If left blank, it defaults to 10%.)

Now for an example:

 
If you want, click on the button in the lower right and follow the directions if you want to download this file to play with it.

Remember that 9.75% is an annualized return, so it means that between 7/11/07 and 5/19/11 this investment returned 9.75% PER YEAR. If your period of time is less than one year, it will also annualize the return. For example, if your period is 6 months, and your return is 5%, then XIRR would return 10%.

If you would like to calculate a year to date return and/or calculate out your return for each calendar year you have had the investment, it only gets a little more complicated. First, you’ll need to add in the value of the investment at the end of each year. I use two entries, the first negative and the second positive, both with the date of 12/31 of the given year. Then, run the XIRR function from the positive entry on 12/31 of one year, to the negative entry on 12/31 of the next. See the next spreadsheet for details of how to calculate returns for partial years, full years, and the year to date.

Play around with it for a few minutes and you’ll figure it out. If you’re a spreadsheet junkie, you can also break it down for each retirement account, or even each individual investment. All you need are your inflows and outflows, and the corresponding dates. If you want to calculate the yearly returns or the year to date return, you’ll also need the year end values of the investments. But you don’t have to take into account fees, commissions, or any dividends or capital gains that are reinvested. Of course, if you don’t reinvest dividends, those should be considered withdrawals from the account, just like fees paid from a separate account, should be considered contributions to the account. XIRR is a powerful function that will allow you to calculate your portfolio’s overall returns, no matter how many different retirement accounts you have.