Calculating portfolio returns from multiple stocks using XIRR

Question

I am confused and would like some clarification.

For around four years I have been building a portfolio of 30 stocks and I would like to calculate the portfolio returns as accurately as possible over that period.

On a Google Sheet, I have tracked all the purchases, sales and dividends for these stocks during the time I owned them. There are situations in which I have purchased, received dividends from and then sold companies fully - therefore I don’t own them now. In other words they are not in my current portfolio.

In order to calculate the returns for this portfolio, I have used the XIRR formula where I consider any purchases of stocks as negative cash flow amounts and any sales as positive cash flows (the same for existing companies in the portfolio). Along with this I have dates of these transactions and put today’s date for any stocks I currently still hold as if they were to be sold today.

After all this, I get an annualised return of around 3% returns. However I am not convinced this is actually the case - with a due sense of modesty. Yes, there have been losers, but there have been many more and much larger winners, especially in recent times.

Before the questions get asked, it is worthy to note that I have also invested in funds during this period in addition to individual stocks. Therefore, dividends received from stocks may have gone towards fund purchases to some extent. However, I want to calculate the return on my portfolio of individual stocks.

Please can someone confirm whether I am:

1. correct in using the XIRR formula across multiple stocks as opposed to purchases and sales of a single fund.

I am aware that another alternative to calculating portfolio returns is calculating weighted returns based on the percentage of individual holdings relative to the overall value, and then summing these up. Is this a better alternative for me?

2. if I am right with XIRR, am I correct with putting the purchases and sales of the transactions excluding the dividend transactions given they are reinvested and would naturally reflect in the buys and sells.

Thank you for your help in advance.

Answer 1

Your scenario is correct and the understanding and usage of XIRR is also correct. Just double check the following:

Formula:

=XIRR(F2:F,A2:A)

Here F2:F column would basically mean that my investment and cash withdrawal both are in column F (starting row 2). As you have mentioned any investment (buy) will be -ve outgo and any profit (or loss) booking will be +ve income in column F.

Next, please make sure column A2:A has proper date format and not just a string data. Google sheets need to interpret this column as date format to work.

Lastly, the common mistake we do is we do not add a last row with the portfolio holdings. The current holdings should be a +ve value at the end of all your transaction records and the date for this row should be the day of calculation. I hope you have not missed this last current portfolio value row?