My Financial Independence Journey » Investing, Investment portfolio » Calculating total annual returns
Calculating total annual returns
As an investor, it’s critical that I have a good idea of how well my investments are doing. This is relatively easy if you make one investment, sit on it, then just check the price at some later point in time. But what if you have multiple investments all made at different points in time, like every few weeks when a new paycheck hits your account. Or multiple different strategies like dividend investing, capital growth investing, options strategies and who knows what else. It turns out that it is incredibly easy to calculate the total annual return of your portfolio using the XIRR function available in most major spreadsheet packages. This post is all about how to do just that.
The XIRR Function
XIRR is probably not pronounced “Zir”, like the name of some kind of evil alien overlord. Or maybe it is. I don’t work with finance types so I’ve never heard it used in conversation. Regardless of how it’s pronounced, the IRR part stands for internal rate of return. And the X? I don’t know, maybe it’s just put there to make it sound cool and more eXtreme.
For the purposes of calculating your portfolio’s performance you can think of IRR as being similar to your interest rate or annual percent yield.
What makes the XIRR function so great is that it is completely agnostic about what you’re actually doing inside of the account. XIRR’s sole concern is cash flows into and out of the account and the dates when those occurred. If you know the four pieces of information detailed below, you’re golden.
What information do you need to know?
As I mentioned above, using the XIRR function is incredibly easy. Here’s what you need to make it work.
1. Starting balance (date and amount) - Remember that this isn’t just the value of your stock positions, it also includes any additional cash that you happen to have laying around in the account. I calculate the starting and ending balance by adding the total value of all my stock positions, plus additional cash in the account, minus the value of any outstanding put options. If you bought options rather than sold them, you would add the option value.
Why do I subtract the value of the put options instead of ignoring them? Because if I had to buy back the put options to close them out it would cost me money. So the options have a negative value.
2. Deposits into account (date and amount) - Every day that you put money into the account, you enter a positive number.
What about dividends, interest, options premiums? Ignore them unless you are taking the money out of the account as a withdrawal. Any money that is added to (or subtracted from) your account because of something happening inside of the account (ex: dividends) can be ignored as those gains or losses will be reflected in the final balance.
3. Withdrawals from account (date and amount) - Every day that you take money out of the account, you enter a negative number. Same rules apply as with deposits.
4. Ending balance (date and amount) - Just like the starting balance, the ending balance is calculated as stock value + cash – option value.
Example XIRR Calculation
.
Notice that the ending balance is negative. That’s because the XIRR function treats this as if it was one big cash flow out of your account, and uses that information to calculate the annualized returns.
Also note that I’ve provided the code at the bottom of the spreadsheet. The code is the same for Excel and Google Sheets.
My Portfolio’s Performance vs the S&P500
Want to compare your portfolio to the S&P500? Use this calculator over at Don’t Quit Your Day Job. Just be sure that you’re comparing your returns against the S&P500 with dividends reinvested.
2010 (May-Dec) - My portfolio pulled in 20.29% vs 20.75% (annualized). For my first few months of dividend investing I was tracking the S&P500. Had I not bought BP back then I would have probably been beating it. Lesson learned, don’t buy a stock on a dip caused by a material failing in the company.
2011 - A phenomenal year for my portfolio. I pulled in 15.96% vs -1.31% for the S&P. So what happened? Well I started selling put options in 2011 so that may have contributed to things. But also, I made some investments at the bottom of the market when the fiscal cliff drama was at it’s worst. And dividends represent real gains. Every time a dividend is paid out, the market can’t take it away on a whim.
2012 - Another year of basically tracking the S&P500. 11.52% for me compared to 12.57% for the S&P. There were several months where I didn’t do any investing this year because I was in between jobs and also in between paychecks.
2013 (Jan-June) - It’s off to a good start so far. 31.35% for me vs 29.01% for the S&P500 (annualized). A few things have happened so far this year. First I’ve become much more focused on finding good value in my investments. Second, I converted my account to a margin account which means that I no longer have cash sitting idle in order to keep my puts outstanding. The market also took a big dip at the end of last month and drug a number of my stocks with it. We’ll see how the rest of the year goes.
May 2010 to June 2013 - Over the entire 38 month period, my portfolio pulled in 18.2% compared to 15.1% for the S&P500 (annualized). Beating the index by 3% over a 3 year span is pretty good for an amateur who’s learning as he goes along. if this keeps up, I’ll hit financial independence faster than I originally anticipated.
Readers: How are you doing compared to the S&P? Hopefully better than me. Do you use XIRR to calculate your returns? Do you have a method that you prefer more?
Written by MFIJ
Filed under: Investing, Investment portfolio · Tags: annual returns, xirr
12 Responses to "Calculating total annual returns"
Leave a Reply to Pauline Cancel reply
I use the XIRR function in excel to calculate my returns. Comparing to that calculator, I’m basically in line with the S&P 500 over the past year and half.
One thing I’ve thought of though is I’m adding money each month, making new buys, as the market has been continuing to increase. Whereas the S&P return calculator is as if you just bought the S&P at the beginning of the period right(with dividends reinvested over time). So my thinking is that my returns will be weighed down slightly in an increasing market (compared to the S&P) but will be getting a boost in a decreasing market (compared to the S&P) as I continue to add new money.
I guess the only true way to calculate the S&P returns compared to mine would be to compare my portfolio’s returns to as if instead I had been just buying the S&P index with each purchase. So I would have to get the S&P prices for each time I added money to the portfolio and calculate out the returns. Too much work for me though. Maybe some day.
That is a good point, the S&P500 returns are just based on whatever the starting and ending points were. I did try a different version where I would “buy” an equivalent amount of S&P500 shares each time I made an investment and got similar results.
The problem with the previous approach was that the S&P500 got an undeserved boost by adding dividends and options premiums that wouldn’t have been there had I just dropped money into an index fund.
I’m still trying to identify the best way to compare my results to the S&P500. It is reassuring that both methods have suggested that I’m beating the S&P500.
Thank you for that, just what I needed. I don’t use more than a spreadsheet with NW updates. NW has more than doubled since May 2010 so I guess that’s good! From January just 7.5% so under the SP500.
Glad that I was able to help you out.
I also use the XIRR function although not on an annual basis just overall. When I wrote about calculating returns I was beating the S&P on some accounts and trailing on others. One of the issues I came across and am assuming accounted for some of the differences was the fact that for the index returns I was calculating it as if the cash was invested immediately into the market whereas in my real life portfolio the cash still would sit there waiting to be deployed usually for at least a few days but in cases like earlier this year it was weeks to months of lag. Time in the market is definitely a big factor.
I need to see how difficult it’s going to be to go back and calculate my returns on an annual basis just to see how I did. I’ve got over a 30% IRR since I started my portfolio in late 2011, not sure how that compares to the S&P right now, but I’m happy with it.
For the purposes of the comparison I would consider the cash as being immediately invested – since that is probably what you would do if you were just dumping money into an index fund. The decision to wait to buy a stock could be thought of as part of your investment strategy.
I think that the XIRR function beats my original system for comparing myself to the S&P500, but I see that there are still some flaws to work out in order to have a proper comparison.
Also, 30% IRR is pretty solid.
XIRR means IRR of Irregular payments. In contrast: the function IRR is used to calculate IRR of regular payments (be it daily, montly, yearly, whatever).
Great article and it provides a nice way of tracking annual returns. However, it’s not the main goal of dividend investing. You could argue that you would like to see negative returns because that provides great buying oppurtunities (assuming you buy solid companies).
If you are a net buyer of stocks (accumulating phase), you want declining prices. If you are a net seller of stock, you want rising prices.
I don’t personally want declining or increasing prices. I want to buy stocks at fair or under value, while avoiding buying stocks that are over valued.
That’s a really useful function to know
They should be teaching stuff like this students in math or computer classes. Maybe they are now but not when I was still in highschool. I haven’t put any new money into my US account this year but I think so far I’m underperforming the S&P500 by just a little bit. I like how the US dollar is stronger now compared to CAD. It means my US stock portfolio is worth more now 
I didn’t learn anything practical when I was in high school. I wish I would have learned some basic personal finance or investing information. Even if it was “here’s 10 things to look at if you ever want to buy a stock.” I think math and computer science would be way better if they focused on teaching kids practical applications for what they’re learning, and then using those as the framework around which to teach theory and other less engaging concepts.
Somewhere I read that X stands for non uniform cash flows, so it would be an Internal rate of return of irregular payments.
I use XIRR function when calculating my P2P returns, but it is a pain for me to manually plug in all data to provide calculations.
It was a little bit of a pain to set up XIRR when I first tried it out. But now that I’ve got all the data in a spreadsheet, it’s pretty easy to keep it updated. I’m still not sure that my comparison is exactly accurate, since the S&P500 measurement doesn’t take into account irregular payments.