Million Dollar Journey

Questrade Democratic Pricing - 1 cent per share, $4.95 min / $9.95 max

Building Wealth through Saving and Investing

Welcome to Million Dollar Journey! If you're new here, you can learn about me, read our user guide, and even follow my net worth updates. A great place to start reading is with the popular articles located in the right side bar. If you would like to join thousands of others and keep up with the free daily updates, you can subscribe to the RSS feed via reader or E-mail.

On your way out, make sure to check out the exclusive Million Dollar Journey Freebies and Deals.

How to Calculate Annual Investment Returns Using XIRR

After my 2012 year end net worth update, there was some chatter about how to properly calculate annual investment returns.  The fact is that there are a number of ways to calculate annual investment returns, some easier than others.

For example, if you have a starting portfolio value and an ending portfolio value for a particular year and no contributions or withdrawals, it’s a relatively simple calculation.  In this case, a simple brute force way to calculate a return that I use often is:

[Ending portfolio value (Dec 31st of current year)/Starting portfolio value (Dec 31st of previous year)] – 1

If my portfolio is worth $120,000 at year end but started at $100,000, then my annual investment return is 20%.

However, in most situations, investors add and remove capital from particular investment accounts which can weigh on investment returns.  This is where it can get tricky to calculate manually, so I like to use a trusty spreadsheet to do the work for me. Specifically, the XIRR function.

The XIRR Fuction

The XIRR function is similar to the IRR (internal rate of return) function but is more robust.  IRR allows for the calculation of returns with “regular” contributions, but XIRR allows for random contributions or withdrawals throughout the year.  The real beauty is that you do not need to be a spreadsheet guru to use these functions.

The Syntax for the function is:

Excel: XIRR(values, dates, [guess])  Open Office: XIRR(values; dates; [guess])

Values are the range of portfolio values; Dates are the range of dates; and Guess is an approximate percentage return (optional).

Here is an example from Canadian Money Forum using Open Office;



To use this function, you’ll need to gather some information on your investment portfolio:

  • Value of portfolio at the end of the previous year (put positive number in spreadsheet)
  • Value of portfolio at the end of the current year (put negative number in spreadsheet)
  • Date(s) and amount(s) of contribution(s) to the account (put postive number in spreadsheet)
  • Date(s) and amount(s) of withdrawal(s) from the account (put negative number in spreadsheet)

In this example, at the end of 2011, the portfolio value was $10,000. Throughout 2012, there were 3 contributions to the account at various times in the amount of $1,000.  The final value at the end of 2012 was $15,000.  Using the XIRR function, the return for this portfolio is 17.41%.  If you were to simply subtract the $3,000 in contributions from the end value, then the return would be an overstated 20%.

Another example is with my leveraged dividend portfolio.  In 2011, the portfolio ended with $87,500 in value.  There was a $10,000 contribution at the end of August 2012, and the end value for 2012 was $108,000.  Punching it all into the XIRR function shows a 11.5% return for the year.

There you have it, a relatively easy way to calculate your annual investment returns.  Let me know the XIRR function works out for you.

33 Comments, Comment or Ping

  1. Thanks for the useful information. After I found the IRR function and noticed its limitations, I didn’t look further. I could have saved myself some trouble instead of implementing XIRR myself. At least now I’ve confirmed that my implementation matches the XIRR built-in function.

  2. 2. Al

    Great function and nice time saver – up to now I had been doing it manually (linking value to value from each contribution/withdrawl)

  3. Post of the year on MDJ, so far :)

    Great information for every DIY investor.


  4. 4. Elbyron

    Very helpful!
    I prefer to use Google spreadsheets so my wife and I can access our investment summary from any computer. I just confirmed that Google does support the XIRR function!

  5. 5. AL

    If the dates span longer than 1 year, what does the values that the XIRR function outputs? In other words, it the value the annualized rate of return since the beginning or the annual rate of return for the last 12-month period?

  6. @Elbryon, I figured that Goog spreadsheets would do that as well, but thanks for the confirmation!

    @Al, I believe the function will calculate the return for the time period specified. If you want to calculate multi year cumulative returns, here is good post explaining how to do that:

  7. 7. Matt

    @Al @FT, XIRR always returns an annualized number. For example, if your dates span exactly two years and your XIRR over this entire timeframe is 20%, that means your total return from start to end is 40%, then annualized (at 2 years) is 40% divided by 2, or 20%. The XIRR function differs from compound annual growth rate or CAGR due to this annualization.

  8. @Matt: I just did a little test and what you said doesn’t seem to be true. I tried 3 cash flows on Dec. 31 in the years 2013, 2014, 2015. The flows were -1, 0, 1.44. The XIRR() function gave 20% as an answer. SO, XIRR() seems to correctly compute the yearly CAGR rather than doing some simple interest stuff.

  9. 9. Goldberg

    I have no use for investment returns due to my investment strategy.

    If I buy BMO shares today, my only interest are that BMO will still be around in some form or another when I retire in 20 years, and will be paying a dividend (hopefully at higher than current level).

    Rate of returns only matter if you are an active trader or give your money to a money manager. Otherwise, you have only one option (keep buying good safe companies with good dividends)… no matter what the recent performance, I diversify and sell never.

  10. 10. Matt

    @Michael, more info can be found on this page:

  11. 11. Neil

    @Goldberg: I’m definitely not an active trader, but I do see a need for keeping an eye on the health of my portfolio by tracking rate of return.

    As much as I’d like good companies to continue being good companies, there are so many variables that can make a good company of today a bad company of tomorrow. To decidedly “never sell” seems a bit foolish to me — I’m not going to automatically drop a company if its value starts to fall, but if the underlying value changes for the worse, why hold on to it when there are so many other potential opportunities out there?

  12. @Matt: The link you provided agrees with what I said. The XIRR() function gives the CAGR. What you said earlier about XIRR of 20% over 2 years meaning the total return over the two years being 40% is incorrect. If the XIRR is 20% per year, then the total return for 2 years is 44%.

  13. I will bookmark this post for use in the (hopefully) near future because I made less than $10.00 on my investments in 2012. It was my first year buying stocks.

    At the rate I am investing I can see hitting the triple digits in a few years…..

  14. 14. Sampson

    @ Goldberg,

    This mentality is precisely the reason why portfolio returns must be calculated.

    Sure you are a buy and hold investor. But what if your investments are outperformed by someone holding TD, or RY? or Heaven-forbid, an Couch Potato style portfolio.

    Without discovering your true rate of return, you could be losing out big time.

  15. 15. Matt

    @Michael, you’re absolutely right! Apologies if anyone was left confused from my first comment.

  16. 16. anotherreadersinceday1

    so you can consider dividend/interest as “deposit” ?

  17. @anotehrreadersinceday1: In computing returns, dividends and interest count as withdrawals if they come out of the account. If they are reinvested, then no entry is required (they just lead to a higher account value at the end). But, they are never a deposit.

  18. 18. anotherreadersinceday1

    thanks Michael !

    so if Div/Int stays in the account, the transaction(the date when div/int gets paid) is not required to compute return — the end value should include div/int , is that right ?

  19. @anotehrreadersinceday1: That’s right. However, the end value will include the dividend implicitly in the form of more shares or more cash. You don’t have to add it again.

  20. 20. anotherreadersinceday1

    Thanks again Michael !

    BTW, since XIRR is always annulized function, is there any other excel function for time weighted calcualtion ( say every 3 month ) ?

  21. @anotehrreadersinceday1: I’m not sure what you mean by this question. I can think of a few different interpretations, but each would take some effort to explain. Perhaps I’ll write a post on my own blog next week explaining some of the finer details.

  22. 22. anotherreadersinceday1

    looking forward to reading it Michael.

    My question is what function to use to calcualte non-annulized return less than a year — say I have 9 months data only and I’d like to know compound quater return …

  23. 23. Goldberg

    @ Sampson : The reason you gave is no justification at all but rather an irrational mistake.

    Just because RY outperformed BMO last year, does not justify selling one for the other. Beside numerous transaction fees, the past does not equal the future. What’s to say BMO won’t outperformed RY over the next few years. Then what? Sell RY to buy back BMO? Constantly buying after a good performance and selling after a bad one… that’s not contrarian, that’s mainstream investing.

    If after calculating returns, you find that RY outperformed BMO, I would continue to hold BMO. I don’t chase past results. Only sell, as Neil said, you perceive the company is in serious trouble.

    There are 32,000 analysts in Wall Street alone, nevermind the rest of the world. You think you know something they don’t? An individual can’t beat the market through active trading. You’ll forget your losses and remember your big gains… its not a game, its my retirement!

  24. 24. maven

    Would re-invested dividends also be used in the calculation as a contribution ?

  25. @anotherreadersinceday1, @Maven, and anyone else with questions:

    I put together a post explaining all the detailed steps of how I calculate my returns:

  26. 26. nobleea

    Folks, should the interest paid on a leverage loan for investment purposes be included in XIRR calculation in any way?
    I would think it should be some how since if your return was 3% but interest was 3%, then your rate of return isn’t really 3%, is it.
    But how do you include the payments for interest since they’re not really part of the stock portfolio and are usually made completely outside of it. And if you did do that, I would assume the tax rebate for writing off the interest should be included as well?

  27. Thank you for this tips! I like to play with Excel or similar program but I didn’t know XIRR function yet!

  28. 28. montrealinvestor

    Just getting around to running my own year-end calculations now, and I’m confused by the need to record cash dividends or not when using the XIRR function. I get wildly different results if I only record contributions, start, and end values vs. recording all of the cash dividends, contributions, start, and end values. Note that I’m not talking about DRIP values, capital gains from shares sold, or other fluctuations in holdings month-to-month (since everything is reinvested and it’s an RSP account with no withdrawals at the moment). I’m talking about recording the $ from bond and other dividend paying holdings. Can anyone clarify?

    Also Preet Banjeree @ MoneySense magazine instructs to enter initial values and contributions as negative numbers, while withdrawals and final values should be noted as positive numbers. Your post instructs the opposite. I assume as long as you’re consistent in one approach or the other it should be the same?

    Thanks for any help!

  29. @montrealinvestor, what matters is your beginning and end portfolio value. If your dividends simply sit in your account (not withdrawn), then they do not count as contributions because they get automatically counted in the end portfolio value.

  30. 30. montrealinvestor


    Okay, thanks so much for clarifying!

  31. 31. venky

    Date Days Cash flow Interest
    31-Dec-11 366 10,000 (1,741) =10000*17.41%*366/366
    31-Mar-12 275 1,000 (131) =1000*17.41%*275/366
    30-Jun-12 184 1,000 (88) =1000*17.41%*184/366
    30-Sep-12 92 1,000 (44) =1000*17.41%*92/366
    31-Dec-12 – (15,000)
    Net Return (2,000) (2,003)
    XIRR 17.40769%

    Need help,Interest calculated does not match with XIRR.. when used larger values the difference gets bigger.. is there a way to match net return

  32. 32. financialGuy

    Excel XIRR function screw up sometimes when the sum of cash flow is negative or the annualized return result is negative. I found a handful app in Apple App Store called “Universal Investment Return Calculator”. It can calculate like XIRR function does but it is more robust (i.e. it doesn’t screw up with negative results) and it does not need you to feed a guessed result.

  33. 33. Brian

    Thanks FT for detailing out the xirr function. I don’t think many people know how to calculate their own portfolio’s return, this certainly helps!


Reply to “How to Calculate Annual Investment Returns Using XIRR”

Subscribe without commenting

Get the Latest


Money Tips Newsletter

Premium Sponsors

Recent Comments

  • JT: Thanks, Ed. That is what I suspected. JT
  • Ed Rempel: Hi JT, You have to be careful taking out the growth. In your example, you sell $20,000 and 5/6 of that is...
  • Ed Rempel: Hi K, A couple things: 1. Leveraged investing is a high risk strategy. If your risk tolerance is only at...
  • Loonie Lover: Just on the subject of dotting the “i”s and crossing the “t”s, don’t...
  • Brief summary: Spouse gets everything when I die :). Seriously, my planning goes like this....
  • Jeff Guarino: The explanation is all wrong. My commuted valued continues to go lower as I get older. I can retire at...
  • K: Great Idea, thanks Cannon. Could you run the Rempel Maximum with the inputs below? I would be very interested to...
  • JT: I have a question similar to K’s on drawing off returns and transferring them to a TFSA: Does 100% of a...
  • cannon_fodder: Hi K, Why don’t you post some scenarios here because if we can accomplish the calculations with...
  • cannon_fodder: Hi K, Why don’t you post some scenarios here because if we can accomplish the calculations with...