Using XIRR to Calculate Returns

Midway through a conversation with a friend a couple of years ago, he asked me how my investments were performing.

“Good” I said.

“So what’s your rate of return?” he asked.

Me: Blinking.

Isn’t that the whole point of index investing? By not focusing on my actual rate of return and instead accepting the market returns, I knew I should expect to do better than a large majority of my peers chasing the stock market up and down.

Yet, I really had no idea how my particular returns were performing.

You can rely on the returns published by the funds each year, but that’s the time-weighted return and not the actual return that you’re getting.

In other words, if you take a look at the Vanguard Total Stock Market Index Fund’s (VTSAX) performance, you’ll see that it returned 12.56% in 2014, 0.39% in 2015 and 12.66% in 2016.

But this isn’t the dollar-weighted return that you’d actually see since you’ve been buying into the fund at various times (and possibly selling as well). Some of those purchases were at dips in the market while others were at high points. How can you figure out how your portfolio is actually doing?

While obsessively tracking your returns is not necessary, you should still know how to check in and see how you’re doing from time to time. This is particularly true if you’ve got an investment in something like a real-estate crowdfunding platform or peer-to-peer lending (e.g. Prosper) where you might not have good data on your actual returns.

It’s surprisingly easy to calculate your returns and keep track of this stuff. All of you have to know is the amount of money that you’ve invested and the dates you made those investments (or any withdrawals you’ve made). After that, a spreadsheet program like Excel or Google Sheets can handle the rest.

Easy tutorial to calculate your returns using XIRR

Step 1) First, record the amounts of your cashflow in column A of the spreadsheet. Any contributions are positive numbers and any withdrawals are negative numbers. The last entry should be a negative number and represents the account balance as of the day you’re doing a calculation. In other words, you need to tell the spreadsheet program that today you’re withdrawing the entire balance of the account.

Here’s an example of how that looks:

Step 2) Next, add the dates of the cashflows to column B of the spreadsheet. Again, the last entry should be the current date. To do this, use the spreadsheet DATE function which looks like this =DATE(2016,10,15) in the format of YEAR, MONTH, DAY (so the date in the example is October 15, 2016).

Here’s an example of how that looks:

Step 3) Now, all you need to do is add the XIRR function to calculate the dollar-weighted return of your investment. The function looks like this =XIRR(A2:A11, B2:B11, 5%), where A2:A11 looks at the cashflow column and B2:B11 looks at the date column. In this example 5% is the estimated return. You can leave it blank if you want.

Here’s an example of the calculation:

Nice, you’ve earned a 21.93% dollar-weighted annualized return over the nearly two years of this investment. Whether you enter a time period of a few months or many years, the 21.93% answer means the return is equivalent to an annual return of 21.93%.

So, to summarize how you do an XIRR calculation:

1. The first entry (equal to the initial investment) must have the earliest date.
2. Investments are entered as positive amounts and withdrawals as negative amounts.
3. The current/final portfolio is entered as a negative amount in the last item.
4. Do not include entries like reinvested dividends or capital gains as those are not new contributions but part of your return. Only include cash that flows in and out of the account (e.g. dividends that are withdrawn would count as outflows).
5. The return generated by XIRR is an annualized return over the period defined by the dates.

Calculating annual returns (2015, 2016, etc.)

If you’d like to see how your returns did each calendar year, the calculation is only a little more complicated.

First, you’ll need the value of your investment on the last calendar day of each year. The first entry is a negative number (i.e. you’re telling the function that you’re withdrawing the money) and the second entry, with the same date, is a positive number (i.e. you’re basically starting all over again with the calculation for that year by telling the spreadsheet that your portfolio started with that amount).

Here’s how that would look (notice how we didn’t change the overall XIRR calculation, since all we did was add endpoints for 2015 and starting points for 2016):

Now, to get the calculations for each year you’d add an XIRR calculation where you start with the first date in the year you’re calculating and end with the last day of the year that we added in the step above.

It would look like this:

If you play around with the function, you’ll get the hang of it pretty quickly and what’s great is all that you have to do is record contributions or withdrawals. You don’t have to take into account fees, commissions, dividends, capital gains or anything else (so long as those dividends and capital gains are reinvested). The XIRR function will factor those into the calculation since your final balance will necessarily either be reduced (in the case of fees/commissions) or increased (dividends/capital gains) by those amounts.

XIRR is a strong and surprisingly easy function that can help you calculate your returns going forward (or historically if you take the time to enter any contributions/withdrawals).

Joshua Holt is a former private equity M&A lawyer and the creator of Biglaw Investor. Josh couldn’t find a place where lawyers were talking about money, so he created it himself. He spends 10 minutes a month on Empower keeping track of his money. He’s also maxing out tax-advantaged accounts like 529 Plans to minimize his taxable income.