Calculating stock return with LibreOffice Calc

Earlier this year BinckBank, the bank I used to invest in stocks for years, introduced new subscriptions. These now require payment of a service fee which is dependent on the size of the portfolio. For my portfolio of slightly more than € 5,000 I pay a bit more than € 10 per quarter. This comes on top of the transaction costs which were already relatively high with BinckBank.

BinckBank has good customer service, accessible information on investing, a good website and app, but that doesn’t justify the price difference with DeGiro, another bank. There I don’t pay a service fee and the transaction costs are much lower as well. As a consequence I’ve decided to cancel my BinckBank subscription and pursue all my stock investments through DeGiro. Apparently BinckBank wants to lose its smaller and less active customers, that’s the only explanation I can think of.

Initially I wanted to transfer my entire portfolio from BinckBank to DeGiro. I had a vague memory about a phone call with a DeGiro employee earlier this year. I reckon being told then that costs would be high for this. For that reason I decided to simply sell my stocks with BinckBank and then rebuy them with DeGiro.

I’ve come to regret this because the stock price of the sole stock I possessed, the Dutch construction company BAM, had risen notably since I sold my stock. When I investigated what transferring a portfolio would cost me, I saw that BinckBank charges € 25 per fund for a portfolio transfer and that DeGiro charges nothing because the portfolio is bigger than € 3,000. Had I known this I would have chosen for the portfolio transfer, certainly because it just contained one fund. Obviously this is another lesson in how assumptions are the mother of all fuckups. And yes, I know a portofolio consisting of € 5,000 in just one fund is not good risk-spreading.

But now the core of this post. I have the issue that I purchased and sold stock of BAM through both banks. Because DeGiro won’t pick up the historical purchase price of the stock I bought through BinckBank, I wanted to use LibreOffice Calc to calculate the costs and potential return of my transactions. You could do this by simply entering the numbers, but if you are like me you want to do this in a pretty way with a formula. Because it took my half an hour to figure out the solution I thought others would be grateful to me if I’d share it here.

My document can be downloaded here. The kind of transaction (purchase or sale), amount of stock, stock price and commission are entered manually. It’s all about the following formula which calculates the total result of a transaction:

=IF(B3="Purchase";((D3*E3)+F3)*-1;IF(B3="Sale";(D3*E3)-F3))

The IF function is nested in another IF function here. First IF evaluates if cell B3 contains the text ‘Purchase’. If yes, it multiplies amount and price and adds the commission. The result is then multiplied by -1 to make it negative (because a purchases order gives negative return). If cell B3 doesn’t contained the text ‘Purchase’, it will execute the next IF function. There IF evaluated if cell B3 contains the text ‘Sale’. If yes, then amount and price are multiplied here as well, but the commission is substracted instead.

More explanation is given on this page of Microsoft Office Support. The syntax of Excel is slightly different because it uses comma’s instead of semicolons, but the IFS function mentioned at the bottom of the document is also useable in Calc. The more simple notation of the IFS function doesn’t require a nested formula and is equivalent to the formula with the IF function above if written as follows:

=IFS(B3="Purchase";((D3*E3)+F3)*-1;B3="Sale";(D3*E3)-F3)

These formulas aren’t a necessity for such simple things as calculating return on stocks, but I thought it was useful to learn more about Calc and Excel this way.

Leave a Reply

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