Calculating Your Annualized Return – XIRR Function

As the end of the year approaches, many of us will be writing blog posts about our portfolio and its performance over the past year. While it is easy to look at the starting and ending values and determine the percent change, this formula isn’t really what we should be most interested in.

The current market value of a stock is based not only on its performance, but also by cash flow into and out of it.

For example, if your portfolio is worth $100,000 at the beginning of 2014 and is worth $140,000 at the end of 2014, the calculation is easy: Your portfolio is up 40% for the year.

But the calculation gets a little more tricky if you made cash contributions and withdrawals throughout the year so that not all of the $140,000 is truly related to growth of the stocks.

Fortunately, there is an easy way to calculate a dollar-weighted return using any modern spreadsheet software. It is called the XIRR function. It gives a dollar-weighted return that takes into account the timing and amounts of cash flows into and out of your investment accounts.

All that is required to calculate this are the amounts that you put into or took out of your investments and the dates that you did that.

Here’s an example of how this works using the above example in Google Spreadsheets:
=XIRR(cashflow_amounts, cashflow_dates)

xirr

If you had just looked at the beginning and ending values, you would have calculated a growth rate of 40%. However, when taking into account the additional purchases of $3,000 and $10,000 on 2/3/2014 and 5/4/2014, respectively, the actual growth rate is still a very respectable 24.83%.

Since the XIRR function gives the annualized growth rate, the percent that it returns tells what the investment returned PER YEAR.

Below I’ve changed the date that the account was worth $140,000 so that 2 years have passed. You’ll see that the XIRR formula now returns a per year percent of 11.52%.

xirr_2yr

I’ve recently incorporated the XIRR formula into my own personal investment portfolio. You’ll see XIRR calculations for each stock as well as the portfolio as a whole, dating back to its creation in April 2013. (The portfolio existed before then but was made up mostly of ETFs…for ease I’ve arbitrarily defined the start of the portfolio at the moment of the transition over into individual equities.)

The XIRR function was also just put into the dividend portfolio template as of version 2.4.

For further information please see the excellent reference on The White Coat Investor: How to Calculate your Return- The Excel XIRR Function.

You may also like...

5 Responses

  1. Scott,

    Are you sure the XIRR is correct? It’s showing a 50%+ annual return across your entire portfolio? That would be phenomenal, if true. But it seems like some of your numbers are off. I looked at your transactions for Apple just as an example, and you seem to have your cost basis reported incorrectly by a fairly large margin. You started buying Apple in 2013, so there’s no way your cost basis can be anywhere near $10 per share? Is there something I’m missing?

    Cheers!
    Dividend Mantra recently posted…Freedom Fund Update – January 2015My Profile

    • scott says:

      Hey Dividend Mantra,

      Yes, that is actually correct. I was gifted some Apple stock at a per share (split adjusted) basis of $0.87/share. (My father bought some in 1996 and hadn’t sold.) The XIRR formula uses the cash flow from these gifts of about $100 for its calculation. My Schwab account, however, uses their market value of about $11,000 for its calculations, giving an entire portfolio performance of 16.36% for the year. 16.36% is a much more reasonable number, but the XIRR calculations are also correct given the data it has to work with.

      It seems Google is having some server issues with the GoogleFinance function right now so the portfolio might not be showing up correctly, but the cost basis information for Apple is correct, thanks to those gifts of stock.

      XIRR also isn’t the greatest for stocks held for the short term. A stock that goes up or down a few percent over a few days would greatly skew the XIRR positive or negative when extrapolated out to a year.

      Thanks for the commenting! And Happy New Year!
      scott recently posted…2014 Year-End PerformanceMy Profile

  2. Scott,

    Ahh, okay. That clears the Apple question up. Nice gift!!!!

    I still don’t know if your numbers are correct, however. Your total unrealized capital gain % across your entire portfolio is 51.07%. You’ve been at this for well over a year now, yet your annual return is near 60%. I’m pretty familiar with XIRR and returns, yet even though you’ve been at this for about two years with total gains of near 50% (including the gift), your annual return is 60%. I’m not understanding how you came about this? Annual returns of 60% over almost two years would add up to a whole lot more than 50%

    Cheers!
    Dividend Mantra recently posted…Freedom Fund Update – January 2015My Profile

  3. Scott,

    Hmm, looking back through the numbers I guess I can see it. I think the Apple stock is throwing me off, because I’m used to running numbers assuming fairly static contributions. But that one-time gift throws all the numbers off quite a bit. It also grew the portfolio rather substantially all in one shot, skewing everything. I don’t know if I’ve ever seen a portfolio with numbers like what you have here.

    Cheers!
    Dividend Mantra recently posted…Freedom Fund Update – January 2015My Profile

    • scott says:

      I had to take a hard look at it too, but as far as I can tell, I think you’re right. The XIRR calculations for Apple are being thrown off by cash in flows of about $200 that are worth about $20,000 each today.

      It was looking quite bad for Apple for a number of years, but it sure worked out in the long run! I’d like to find another one of these home runs!

      Cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *

CommentLuv badge