Sgxstockpicker's Stock Portfolio XIRR - 31.04% or 91.42%

I went to read up more on how to calculate my portfolio returns and here is what I found. To do so, you will need to use the XIRR function, available on most desktop computers which have Microsoft Excel installed. Here is the list of things you need:

1. A relative who has and knows how to use MS Excel 2003 (minimum and you need to toggle XIRR)
2. A record of ALL your transactions – date and amount securities purchased 
3.  The amount of dividends received.

Open a new workbook when you are in Microsoft Excel. For your sales and purchases, dates should be in one separate column, in a DDMMYY format. An important thing to note, for purchases, including the subscription of rights shares, the dollar amount should have a negative sign. For sales as well as the end value of the portfolio, they should be positive. In both sales and purchases, be sure to account for the related transaction costs, such as GST and clearing fees.

Dividends are slightly tricky. Do not key in the dates and amount relating to dividends. Add them to your final portfolio value. The final entry should be the dollar value of your portfolio, inclusive or dividends and any cash you might have left, that formed the portfolio. Excluding investment cash will overstate portfolio performance, same goes if you entered the dividends as money out.

Once you have keyed in all the transactions, click on an empty cell. Go to the “Formulas” menu, and find “XIRR”, which is under “Financial”. A dialogue menu will popup. Under “Values”, highlight all the amounts transacted, while under “Dates”, all the dates transacted. Click okay and a number will appear in the previously empty cell selected.

What you see is what you got. If you have got 0.75, it means that your portfolio gained 75% on an annualized basis between the start and end dates. For me, what I did was to format the cell so that it appears in percentage.

But I have got a tough question. I have 2 screen shots of my XIRR. In the first one, I have omitted my sales, and my XIRR was 31.04%. In the second, I included my sales and my XIRR was 91.42%.  Do note that when I purchase shares, some of them come from the proceeds of earlier sales. I tried to google the answer but no results turn up so far.

In my opinion, we should omit the sales like how we did with the dividends. The reason we omit dividends, is because nothing happened externally (hence the X in XIRR). The only time we should include sales, is if we were to permanently remove the money from the portfolio.

My reasoning is that although we took money from the stock portfolio by selling stocks and converting to cash, the money remains in the portfolio ultimately. If we were to include these sales, we will just overstate the performance and by a lot. we should only add the new capital that is coming in, in excess of the sales. That will be very very tricky. I could be wrong here, what are the experts' thoughts on this?