How to measure your investment performance using Excel

What counts as exceptional investment performance?

This tweet appeared on my timeline a few days ago. It shows the outperformance of legendary investors versus the S&P 500 and their longevity as investors

The Calm Investor | Measuring Portfolio Performance
Source: @MebFaber on twitter

Assuming the data here is accurate, this chart says, literally, only a handful of professional money managers, have consistently outperformed the broader market.

I’m not clear whether the x-axis shows the number of years they beat the S&P or their overall tenure and whether the y-axis is average of all years where they beat the S&P or their overall return. But that’s not the subject of this post.

This got me thinking:

What is my portfolio performance over the time that I’ve been an equity investor?

Sounds deceptively simple. For each year (Value of portfolio minus Cost of portfolio) / Cost of portfolio to arrive at annual portfolio returns, right? Turns out, measuring portfolio performance is not as straightforward as it should be. Consider the challenges I faced:

  1. Uneven holding periods: I bought my first stock in May 2006 (10 shares of Hindalco @ Rs 207). Since then over 11 years, I’ve bought and sold several other stocks, with holding periods ranging from a few months to several years
  2. Unevenly distributed transactions: Some years I bought stocks regularly through the year through SIPs, others I sat out and bought infrequently or sold more than I bought so my portfolio cost-base was lower at the end of the year than at the start
  3. Lumpy investments: My transaction sizes increased as the amounts I could invest increased over time. So the total purchase amounts in the later years were significantly greater than the early years

So, while I “see” a portfolio that has generally increased in value over time, might I have been better off socking it all away into Fixed Deposits or making one-time purchases of index funds? This is a question every investor should be asking themselves from time to time.

The mission:

Arrive at a neat little number that represents “your portfolio return“. I’m not even talking about relative performance measures like Sharpe / Treynor / Jensen ratios. (To be fair, once you have an absolute and annualised number adequately capturing your returns over time, those other metrics are easily calculated.)

My online icicidirect trading account reports turned out to be useless for this purpose. It shows annualized returns at a stock level but does a poor job of reflecting overall portfolio performance. It also seems unable to account for the impact of stock splits and bonus issues while calculating returns. So I adopted the mantra of every micromanager who’s ever lived, if you need something done right, do it yourself

It’s actually not too difficult. So here’s how to measure basic portfolio performance:

Step 1: Pull the log of all transactions (Buy / Sell / Bonus / Split) over the time frame

In my case, May 2006 to present day, which is a lot of transactions.

The Calm Investor | Measuring portfolio performance

Step 2: Add a field (if not present) that indicates the direction of flow of money

Buy and Sell transactions should be represented by opposing signs, doesn’t matter which.

The Calm Investor | Measuring portfolio performance

Step 3: Adjusting for open positions

The stocks that are still in your portfolio do not have a ‘Sell’ transaction to close out the ‘Buy’. To adjust for these, you need to add a “fake” ‘SELL’ row for each stock currently in your portfolio with today’s transaction date and value (today’s stock price X number of stocks) i.e. the amount you would get if you were to sell all those stocks today.

The rows in italics are the dummy ‘SELL’ records as on the date I did this exercise

The Calm Investor | Measuring portfolio performance

Step 4: Create a pivot table

Add transaction dates to row labels and ‘Overall’ amount to values. The pivot table will look like this, with one row for each date on which a transaction occurred:

The Calm Investor | Measuring portfolio performance

Note that by adding a stock filter you can choose to see annualised returns of individual or subsets of stocks.

Finally: Use excel’s XIRR function

Passing values (B5: last row) and dates (A5: last row) as parameters to get the one annualised rate of return of your portfolio since the time you started investing.

For example: Your portfolio return  = XIRR(B5:B550,A5:A550,)

The moment of truth

This is it. Your annualized return as an equity investor. If you’re like most people, this number probably surprises you.

  1. Is ‘your portfolio return’ more than the post-tax 6% you’d get from Fixed Deposits?
  2. Is it also more than the 12% annual return from the NIFTY in this same time?

Note that this simple method assumes you invest what you can and therefore doesn’t consider the impact of partially staying in cash. Also, this calculation is “money-weighted”, i.e. higher the portfolio value, more the impact on the return number. See link in ‘further reading’ for the difference between this and an alternate method called the time-weighted return.

If you’ve been investing for a few years and if you answered ‘No’ to both, ask yourself if you’re suited to do your own investing. Take the risk quiz to see what your temperament says. Maybe you need to relook at your investment philosophy.

If it’s Yes to the first and No to the second, ask yourself if the opportunity cost of missing index returns will be too high over time. Maybe you need to revisit your strategy or apply a more disciplined approach to stock selection

Best of luck!

Further Reading

So many stocks…so little time: link

Measuring your portfolio’s performance: link

Money vs. Time-weighted return: link

7 thoughts on “How to measure your investment performance using Excel

  • August 12, 2017 at 8:12 pm

    Hi, what was the number you obtained ?

  • August 12, 2017 at 10:31 pm

    Perhaps you could have tried to use a portfolio tool such as Value Research? They provide an accurate calculation for the MONEY-WEIGHTED AVERAGE RETURN.
    But the correct way to do this is to use the TIME-WEIGHTED AVERAGE RETURN. This is much more complicated than what you’ve described above as it requires rebalancing of the portfolio weights on every cash inflow and outflow. I believe that some paid portfolio analytical tools calculate this as well. Also the performance measure measure Investopedia link you posted is a little misleading.

  • August 13, 2017 at 8:43 am

    Thanks. There are tools available for most things portfolio related but it’s better to peek under the hood to understand what’s driving the number. Disagree with your point about time-weighted returns being “correct”. It is more suited to measure and compare the performance of one professional money manager against another because amounts involved will likely be substantial over the time period in question. For an individual investor who’s building a portfolio from scratch, the money-weighted method is a more “real world” representation of her results. Also, would help folks to know what’s misleading about the investopedia link.

  • August 13, 2017 at 8:44 am

    Was thankfully above index returns by a decent margin, with a fair bit of luck involved 🙂

  • August 14, 2017 at 4:55 pm

    Hi, beating the index by a decent margin’s a nice achievement! Keep it up.

  • Pingback: How to evaluate an investor | The Calm Investor

Leave a Reply

Your email address will not be published. Required fields are marked *