Monitoring stocks and net worth the cheapo way
Way back in 2003, I started monitoring our monthly net worth and stock purchases using Excel. It was spurred by a question I asked myself as I attended a course called "Finance for Non-Financial managers". "Why was I looking at the finances of other people at work and not looking at my own?" And so began the monthly tally which my husband and I eagerly look forward to even to this day. In Dec 2017, we migrated to Google Sheets as it allows my husband and I to edit and share with each other freely. We still discussed and shared views over it, just that it has gotten a lot more complex than that fateful first spreadsheet.
It's definitely not the best spreadsheet. In fact, I would say the process had been rather ad hoc and haphazard at times over the 18 years. But the plus side was we didn't spend a single cent on it and it does meet our needs, at least for now.
These were what we did:
1) We divided our cash components according to where the money is placed, e.g. DBS Joint account, bond and other fixed income vehicles. These had to be updated manually.
2) Next was our stock investments, which formed the bulk. This went through quite a few iterations. First, we had them by owner (i.e. me and my husband) and the data was updated manually every time. Just two months back, I realised our stocks are placed under a number of custodians (because we wanted to make use of certain cheaper options) and the listings by "owner" was getting messy. So, I listed the stocks alphabetically and within each stock, would segment according to the custodian which it is held. This is also to prevent selling stocks from the wrong custodian!
Next was the issue of tracking stock prices. We currently have them updated using the "importhtml" function to pull live price data. This met with some problems after Google Finance stopped pulling data from SGX stocks. We got around it but there were still a handful of stocks that needed manual updating every month when I updated the monthly NW charts. Right now, I am exploring the use of Webdata Hub, a Google Sheet add-on, to see if I can synchronise all the stocks' live prices.
3) Where foreign stocks were involved, I opened a sheet for foreign exchange rates and updated that every 3 months so that that can be referenced for foreign stocks. I believe this can be improved.
4) A few years back, I adapted a spreadsheet template by InvestmentMoats.com to add on "Stocks Summary" and "Transactions" tabs. The "Transactions" spreadsheet would feed data automatically into the "Stocks Summary" spreadsheet. I did a lot of backward engineering calculations to estimate past dividends received as well as stock prices bought, as I regretfully missed out some stock transactions and dividend receipts before that. I suppose this happens as I wasn't entirely organized and I wasn't sure if I needed to be so detailed. I didn't imagine our net worth would grow to include so many transactions!
5) Next was "Property". In order to arrive at a "net worth" value for the property, we had to update our monthly property outstanding loan amounts as well as "estimated market price", the latter of which we rarely change unless there is a major revision to market rates.
6) For "Car", I seriously have no idea haha! My husband used an estimated PARF linking it to a formula which is dependent on the month (manual entry). He then linked the PARF, ARF and OMV values in a formula to arrive at the net value of our car.
7) "CPF" section was easy enough, albeit tedious. I would update the figures by logging into my husband's CPF account every month. I didn't have any CPF contributions so that saved me the trouble.
8) Next up was the SRS investments. This could be easily updated using the online bank account.
9) A few years after monitoring, I decided we needed something graphical. So there began our "Chart" tab, where I have a bar chart just simply tracking the total net worth. My husband improved it by dividing the bar into "Liquid (Cash)", "Liquid (Stocks)" and "Non-liquid". For comparison, he also plotted it against the Straits Times Index. He added two elements to the table linked to this chart which we monitor closely: "Passive Income" and "Goose Account". This is to differentiate the amount of money we generate based purely on investments versus those through his job.