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?