This simple, yet powerful free investment tracker Google Sheet is intended for those who want to track their investment performance, regardless of the type. It is suited for stocks, real estate, loans, cryptos and P2P alike.
How to track investment performance
The investment tracker template works by tracking three kinds of transactions:
- Deposits/Withdrawals
- Value change
- payouts/costs
There is no required frequency, so you can do the tracking as seldom as you want. How to use the transactions are described below.
Deposits and withdrawals
Deposit is usually the first transaction you make in an investment, e.g. you transfer money to your p2p or brokerage account, buy crypto or you make the down payment on a rental property. Deposits equally increase the book value of the asset. Respectively, withdrawals are when you transfer money back to your bank account or sell crypto for example. If you exchange crypto to another crypto, just make a withdrawal from one and a deposit to the other, equal to the fiat value.
In the case of mortgage, you should only track the net value. Therefore, when you make mortgage payments, those count as deposits. To be 100% accurate, you'd count only the principal part as deposit and interest part as payment, but you can do fine considering the whole payment as a deposit, since the rental property usually increases in value with interest anyway. Besides, you use the value change every now and then to re-valuate the property.
Value change
All assets can change in value. P2P platforms like Crowdestor or Bondora increase in value as interest is paid from loans. You don't need to know how much interest was paid on a particular time period, it is enough to check what the current value of the account is and record that. The script I've made will calculate the value change. This makes things much easier for you. And you can do this at any point, as often or as infrequently as you wish. No need to do this on the 1st of every month for example.
When you get defaults, it will be reflected in your total account value and they will be accounted for that way.
Rental properties change in value as well, not by much, but they do. You could e.g. every year check what you believe the selling value of the property is and the script will calculate the profit/loss. Be sure to substract your mortgage from this and only track net value.
Brokerage accounts and cryptos change value also. The only thing you need to do is check how much they are valued now.
Payouts and costs
A payout does not increase the value of the asset, but gets counted towards the profit/loss. This way, a payout or cost is simply a withdrawal or deposit coupled with an equal value change, resulting in the same value as it was before.
Do not use this when a peer lending platform pays interest that stays inside the platform as the account value is increased. The exceptions are only platforms such as Robocash, where you can choose to pay the interest out to your bank account automatically. When money leaves your peer lending account like this, you can record it as a payout.
When a tenant pays rent, it's a payout. When you pay housing maintenance fees, it's a cost.
Categories and assets
I've set the sheet up so that you can group assets into categories. My categories would be Real Estate, Stocks, P2P and Cryptos. You can choose these or something else. Categories will be tracked on the first tab.
The second level is the asset. This should be chosen in a way that it is relatively easy to track. As I have two brokerage accounts, I've chosen to track these separately. If you own only a handful of ETFs, you could track each one on their own. If you have many stocks (like me) I would not track each of them separately, although you could, because of the effort required to update everything.
If you did track each stock separately, then you would consider buying the ticker a deposit and selling it a withdrawal. You could create multiple portfolios (this is easy with e.g. Lynx Broker).
The recommended way is to track each P2P platform as a whole. Not individual projects inside them.
With real estate, the recommended way is to track each property individually. You could also choose to put real estate platforms such as Estateguru into this category, but I've chosen to put it in P2P.
How to use the free investment tracker Google Sheet
The first thing you need to do is head over to the investment tracker on Google Sheets and make a copy of it for yourself.
You can have a look at the History tab that contains historical dummy data. If you're planning to import historical data, have a look at how it is structured and once you're set, you can clear out the rows 2 and forward.
On the first tab you will not find any input fields. The first tab is for tracking your total investment portfolio. It calculates easily details such as Internal Rate of Return (IRR), also known as Compound Annual Growth Rate (CAGR). If you don't have any data, you'll see errors.
You'll find the category specific metrics below the chart.
The second tab contains the input form, individual asset tracker and historical transactions. When you do your periodical tracking, this is the easiest way to do that. If you have lots of historical data to input, it's easiest to modify it to the correct format and copy-paste directly into the History tab. When you've done that once, it's better to use the Input tab going forward.
Let me know what you think
If you enjoyed this tool, please also have a look at my free budget and expenses tracker Google Sheet.
No comments:
Post a Comment