You have 2 free articles remaining. Subscribe
« Back to Articles
Sep 5, 2014

The Simple Way To Calculate Annualized Rates Of Return The XIRR Function

by Matt Poyner

Matt Poyner

 

 

As a conscientious DIY investor, I feel obligated to measure my own performance. Even with the excellent resources available through my online discount broker, however, this is easier said than done. Annualized rates of return for individual accounts are readily available but with RRSPs, TFSAs, RESPs, unregistered accounts, etc., it was a daunting task to evaluate overall performance . . until now.

The XIRR function found in Excel and Google spreadsheets makes calculating annualized rates of return simple and easy. All you need to know are three things:

  • Deposits and withdrawals from the account(s)
  • Dates of those deposits and withdrawals
  • Matt PoynerFinal balance

Here is how you do it

In one column input deposits as positive numbers and withdrawals asnegative numbers. In the next column input the dates associated with these transactions. Make sure they are formatted as dates in the spreadsheet. At the bottom of the first column input the final balance as a negative number with your end date.

Now, here comes the magic. In another cell, input the following: “=XIRR(” then highlight your first column, add a comma, then highlight your second column, add another comma, then “5%)” or some other reasonable estimate of the rate of return. So, it looks something like this:

Matt Poyner

Now simply press Enter and you will find that in this case your annualized rate of return is 10.17%!

A Few Things To Remember:

These are annualized rates of return, not total rates of return. If your timeperiod spans more or less than one year, XIRR will still calculate the return as annualized. For example, if $10 000 turns to $11 000 over 6 months, XIRR calculates a 21.1% rate of return; over 2 years it’s a 4.9% rate of return.

  • This is a dollar-weighted return, not a timeweighted return. As most of you will know, it is much better to earn 10% per year, rather than 20% the first year and 0% the next. Even though the “average” return for both is 10%, $10 000 invested in the former will yield $12 100, while the latter only $12 000. The XIRR function, using dollar-weighted calculations, is a more accurate representation of performance. In the latter case it will calculate a ROR of 9.5%. (This should fuel some healthy skepticism when appraising advertised past performance of investment funds.)
  • What is the estimated rate of return for? This has to do with mathematical algorithms, numerical analysis and other things that make my neurons spontaneously combust. Suffice it to say, it makes the function work better if you make a decent rough guess, i.e. +10% vs. -10%.
  • What about reinvested dividends? This is the beauty of the system: any money that stays in the account is automatically accounted for. If you are withdrawing your dividends, simply enter them as negative numbers in column A.
  • What about multiple accounts? Simply input all deposits and withdrawals with their dates as if they were in one account. The final balance is the total of balances in all the accounts.
  • What if that function is not supported in my version of Excel? If you did everything right and you get a “?NAME” message in your XIRR cell, try going to “Tools” ➝ “Add-ins”, they select “Analysis Toolpak”. After a quick download and install, the function should work.
  • What about rates of return for individual years? This is only slightly more complicated  as you need the balance at the end of each year in question. Enter this value as a negative number with a date XIIR Function f the last day of the year. Then enter the same value as a positive on the next line with the same date. Now you can use your XIRR formula using the values up to the final balance for the year in question. The positive entry is your starting point for the next year.

If the process of calculating your rate of return seemed daunting before, just spend a little time with the XIRR function as outlined above and I think you will be pleasantly surprised at how simple and powerful a tool this is. One more item in the DIY investor’s armamentarium.

Matt Poyner is a DIY investor and participant in the Oshawa Shareclub. Email: matt.poyner@gmail.com