How to Calculate Dollar-Weighted Return Formula in Excel — The Excel XIRR Function

By Dr. Jim Dahle, WCI Founder

As you invest, knowing how you are doing is important. Luckily, it is easy to see how your individual investments are performing 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, but some of it occurs because most investors are adding money to their accounts throughout the year via ongoing savings.

While I don't think you need to look at your investments every day—or even every month—you ought to check in and see how you are doing from time to time. 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, knowing how to calculate your own return empowers you as an investor.

The best way to calculate your return is to use the Excel XIRR function (also available with other spreadsheets like Google Sheets and financial calculators). 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. The amounts you contributed to retirement and other investment accounts are positive. The 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 day.

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%.)

Here's an example:

Remember that 6.94% is an annualized return, meaning that between 1/1/20 and 3/1/25, this investment returned 6.94% PER YEAR. If your period of time is less than one year, it will also annualize the return. For example, if your period is six months and your return is 5%, then XIRR would return something closer to 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.

If you don't reinvest dividends, those should be considered withdrawals from the account, just like how 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.

You can download the two spreadsheets in this post here and here.

What do you think? How do you calculate your returns? Any questions on how to use XIRR?

[This updated post was originally published in 2011.]

The post How to Calculate Dollar-Weighted Return Formula in Excel — The Excel XIRR Function appeared first on The White Coat Investor - Investing & Personal Finance for Doctors.