As my holdings of equity increases, I realised that I needed to track these (un)realised gains/losses as the strike prices for each transaction varies. Calculating the percentage gain/loss is not just simply taking the change over the purchase price! To appropriately calculate returns, the IRR function (or its derivatives) is required due to compounding effects and different base prices.

Fortunately, the great guys over at Investment Moats have already created a Stock Portfolio Tracker google spreadsheet that is easy to use and is a good place to start.

To begin, follow their introduction / instructions.

By tracking your portfolio over time, you’ll be able to see poorly performing positions and do something about them before it’s too late due to opportunity costs.

Unfortunately, in the most recent version of the tracker, the authors removed the XIRR function as it is too computationally intensive for the browser, if you have many different stock counters.

To enable for selected counters, follow the instructions in the version 1.8 row of the Version History table within the Read This First tab, but use the following formula instead:

=IFERROR(XIRR(
	SPLIT(JOIN("~", QUERY(Transactions!A$2:R, "SELECT R WHERE (B = 'Buy' OR B = 'Sell' OR B = 'Div') AND C = '" & B2 & "'"), U2, "~" ), "~"),
	SPLIT(JOIN(",", QUERY(Transactions!A$2:R, "SELECT A WHERE( B = 'Buy' OR B = 'Sell' OR B = 'Div') AND C = '" & B2 & "'"), TODAY(), ","), ",")
), 0)

This updated formula takes into account the structural changes of the Transactions sheet up until version 1.13 (dated: 2015/05/24). For use with the Transactions USD sheet and others, update the formula above, replacing Transactions!A$2:R throughout with 'Transactions USD'!A$2:R.

With that, happy investing!