finances

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 contain 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.

No mortgage from Triodos Bank

In February this year Stephanie and I bought a home in The Hague. Since June we live in this corner house with a small garden in Loosduinen. We have much more space now than in our small apartment in Rotterdam. The location is convenient because Stephanie can now go to work by bicycle in ten minutes. I will write more about the home and The Hague later, for now I want to write about the mortgage we have taken to buy our home.

For me, the first bank to consider as a mortgage provider was Triodos Bank. This bank is one of the few ethical and honest banks in the mass of big and greedy ‘too big to fail’ banks. I already had a bank account there and if there had to be anyone to charge me an arm and a leg for an expensive home, it had better be Triodos. Total costs for the (compulsory) advice and closing of the mortgage turned out to be quite high: € 2,050 for those with an existing mortgage, including a a discount.

High costs, if you compare with competitors such as Hypotheek24. There it’s possible to close a mortgage for € 650 without advice. Advice often isn’t necessary, especially since only linear and annuity repayment schemes are tax deductible for new mortgages in the Netherlands now. These are relatively uncomplicated. So why can’t Triodos make it’s advice optional? Why can’t they go with the flow?

In the end we decided to use the services of Stephanie’s last financial advisor again, the VvAA. We actually needed the more complex advice because it turned out to be financially advantageous to maintain our ‘bankspaarhypotheek’, an older mortgage type with a savings-based repayment scheme. Combined with advice for insurances, we paid them € 3,000. The mortgage was closed with, unfortunately, ABN Amro: the greedy bank which had to be bailed out by the government, with the tax payer’s money. Triodos apparently doesn’t do business with external advisors, so the VvAA couldn’t close a mortgage with Triodos.

I might want to transfer the mortgage from ABN Amro to Triodos in the future, but for transfers the costs are € 2,050 as well. I could spend that on two weeks of holiday in summer. It seems as if Triodos doesn’t want mortgage customers.

Scroll to Top