Tracking Investment Margin Operations with Google Sheets
Updated: Apr 22, 2021
After publishing my previous post, Tracking your ETF Underlying Holdings with Google Sheets, I've been continuously adding the features and tools to make my life easier. One of the main things I wanted to figure out, was how to relate leverage with the current portfolio that was already tracked with those pages. And so (kind of) I did.
Leverage (Margin) on Brokerage Accounts
This section MUST start with a disclaimer: Do not take any of the following as investment advice as I'm not a CFA and have not any association with any brokerage firm or institution.
Leverage is a delicate topic and not everyone can deal with it the same way. I'll leave a separate article to talk about that in the future. But still, I don't want to encourage anyone to use it to invest. So, in case you are doing so, the following spreadsheet will help you monitor your progress and hopefully will help you better understand your process and improve your decision-making guts.
The new spreadsheet page sample was set up on top (and connected) to the previous portfolio one. The purpose is to allow me to calculate how margin directly relates to all my assets while having a specific section to track and understand how it behaves within your account.
The Spreadsheet Page
For tracking it, I created a new separate page in the Google Sheets doc that connects to the main Portfolio Dashboard one. Similarly to the other pages, the current portfolio is set up as follows:
A $100,000 sample net worth distributed as:
~50% U.S. Total Market with iShares ITOT units.
~35% Nasdaq100 with Invesco QQQ units.
~17% ARK Innovation Active ETF with ARKK units.
~3% S&P500 Tracking index with iShares IVV units.
The following fields can be customized to set up your margin transactions to be recorded together with the evolution of your portfolio:
Margin Yearly Interest Rate: It is set up account-wide and assuming that all operations receive the same margin interest rate.
Ticker, Transaction Date, Commission and Book Price: These are transaction-specific fields. You will be able to calculate automatically how your investment has evolved since the transaction date and how much, including the commissions involved, does it cost to maintain the loan.
And with this, it should be more than enough for you to start. As I said, this page was simple enough to add some "basic margin tracking" and I hope you find it useful!
Do you Need Help Setting this Up?
If you need help setting up the Google Sheet, you can book a call with me. I've helped lots of investors track their expenses by setting up their sheets, which result in a great time-saving opportunity and portfolio tracking dashboard across multiple accounts.
New To Investing?
If you are in Canada and are looking for ways to grow your wealth, you should start by opening an account at Questrade Inc. and begin investing your money. It is the #1, most prestigious and most affordable brokerage in Canada. By following this link, you will get a cash bonus, up to $250, depending on the amount you deposit when you open/transfer your TFSA, RRSP, RESP or brokerage account.