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.

If you would like to read more articles like this, you can sign up for my*free*newsletter service below (we will not spam you).

{ 44 comments… add one }

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.

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

Post of the year on MDJ, so far :)

Great information for every DIY investor.

Mark

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!

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?

@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:

http://canadianmoneyforum.com/showthread.php/14538-Question-to-OpenOffice-users-how-to-calculate-XIRR?highlight=xirr

@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.

@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.

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.

@Michael, more info can be found on this page: http://www.wikihow.com/Calculate-Annualized-Portfolio-Return

@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?

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

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…..

@ 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.

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

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

@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.

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 ?

@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.

Thanks again Michael !

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

@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.

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 …

@ 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!

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

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

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

http://www.michaeljamesonmoney.com/2013/01/how-to-calculate-investment-returns.html

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?

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

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!

@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.

@FrugalTrader

Okay, thanks so much for clarifying!

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

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.

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!

I have a seemingly silly problem which I cannot explain …

The Microsoft help page says that XIRR does its calculations based on a 365 day year.

Now, if I assume an initial investment of 100 units on Jan 1, 2014, which grows to 120 units on Dec 31, 2014 (exactly 365 days), I get the correct answer of 20%

However, when I change the end date to Dec 31, 2015 (exactly 730 days), XIRR returns an annualised interest of 9.56%, when it should be 10%

Have I got something wrong? Can someone help explain what I am seeing?

Thanks….. Ed

@Edward: XIRR is giving you the right answer. If you compound 10% for 2 years, the result is 21%, not 20%. Good luck.

How stupid of me not to see the fact that XIRR compounds!

Thank you Michael for your response.

Edward

I have a peculiar problem wherein the CAGR return and the XIRR return both vary for the same data points.

Illustration: Investment of 50mn as at Sep 15, 2010 equals to 97.290 as at Mar 31, 2014. The XIRR function provides 21.2% performance for the period and the CAGR formula provides 20.6%.

Which is the correct number to consider and can someone explain the reason for this variation between CAGR and XIRR

@Bill: I get 20.67% when I calculate the IRR with the excel XIRR() function.

Thanks Michael for reverting.

While this is strange I continue to get the difference. Is there a way you could email me your excel sheet. I could in turn revert with my working on both CAGR and XIRR. You could email it to me at support@newhorizonfunds.com.

Not sure if the version makes a difference as we used Excel 2003 or whether some amendments need to be carried out in the settings/ options

Thanks for assistance

Bill

Does anyone know how to combine the output of multiple XIRR() outputs? This way, we can not only find the rate of return of individual investments, but also of the entire portfolio.

@K if you have the data to perform xirr on several investments then you could just do an xirr calculation that uses all the data as input to get the annual return for your entire portfolio, no?

@mr_l, the problem is that the individual xirr outputs will be in different spreadsheets, and it is a hassle to bring them all into a single spreadsheet …

Good stuff! I agree not a lot of people know how to calculate their investment returns. Though, I can’t think of a good reason why would one need to include in xirr calculations current holdings since they are not finalized yet (i.e sold). It probably makes sense to calculate ROI on the current individual holdings. I calculate xirr on my past investments to understand my investment performsnce. I use the following two column format:

Transaction date – proceeds (positive # for proceeds from sale of securities and cash dividends and negative # for proceeds from buying the securities). This way I don’t care about portfolio starting values as xirr spits annualized returns anyway. If anyone has any suggestions, feel free to critique.

is there a particular date format to be used?

i keep getting “#num!” error?