Calculating Your Annualized Return – XIRR Function
As the end of the year approaches, many of us will be writing blog posts about our portfolio and its performance over the past year. While it is easy to look at the starting and ending values and determine the percent change, this formula isn’t really what we should be most interested in.
The current market value of a stock is based not only on its performance, but also by cash flow into and out of it.
For example, if your portfolio is worth $100,000 at the beginning of 2014 and is worth $140,000 at the end of 2014, the calculation is easy: Your portfolio is up 40% for the year.
But the calculation gets a little more tricky if you made cash contributions and withdrawals throughout the year so that not all of the $140,000 is truly related to growth of the stocks.
Fortunately, there is an easy way to calculate a dollar-weighted return using any modern spreadsheet software. It is called the XIRR function. It gives a dollar-weighted return that takes into account the timing and amounts of cash flows into and out of your investment accounts.
All that is required to calculate this are the amounts that you put into or took out of your investments and the dates that you did that.
Here’s an example of how this works using the above example in Google Spreadsheets:
If you had just looked at the beginning and ending values, you would have calculated a growth rate of 40%. However, when taking into account the additional purchases of $3,000 and $10,000 on 2/3/2014 and 5/4/2014, respectively, the actual growth rate is still a very respectable 24.83%.
Since the XIRR function gives the annualized growth rate, the percent that it returns tells what the investment returned PER YEAR.
Below I’ve changed the date that the account was worth $140,000 so that 2 years have passed. You’ll see that the XIRR formula now returns a per year percent of 11.52%.
I’ve recently incorporated the XIRR formula into my own personal investment portfolio. You’ll see XIRR calculations for each stock as well as the portfolio as a whole, dating back to its creation in April 2013. (The portfolio existed before then but was made up mostly of ETFs…for ease I’ve arbitrarily defined the start of the portfolio at the moment of the transition over into individual equities.)
The XIRR function was also just put into the dividend portfolio template as of version 2.4.
For further information please see the excellent reference on The White Coat Investor: How to Calculate your Return- The Excel XIRR Function.