Finance Smiths

Personal finance apprentices-in-training

Other and Investment Portfolio

by //

I have decided to take a break from writing about Google Sheets. I should go back to it eventually since there is a lot of material to cover but I might write about other topics in the meantime. Previously, I didn’t think I was going to disclose any more personal finance information as I had written about our ETF and Share Portfolios as well as the passive income received. However, I reckon it would be good to show the Other Portfolio to have a better illustration of our Investment Portfolio.

What does the Other Portfolio consist of?

Bonds

We hold corporate bonds instead of Singapore Savings Bonds (SSBs) to have a higher yield on our investment. After all, these bonds form part of our investment portfolio and the SSBs are a more suitable vehicle for our emergency funds. We try to keep a lookout for retail bond issuances and allocate small sums to different corporate bonds.

Wholesale Life Insurance Policies

These are essentially life insurance policies with cash values (i.e. surrender values) that grow over time. Our parents bought these wholesale life insurance policies for us when we were young and we have taken over the payments now that we are working. Since these policies are what I call legacy assets, we have opted to retain them in our investment portfolio.

Investment Cash Holdings

We hold cash for investment purposes, to be used especially during market dips and bear markets. We try to consistently invest some of these cash holdings every month since we try not to time the market. However, it really only gets drained during bear markets and we just used a chunk of it in Jan and Feb 2016. As discussed in my previous post about asset allocation, we try to allocate a percentage of our savings each month to this component.

Investment Portfolio

The ETF, Share and Other Portfolios form our Investment Portfolio. I won’t be disclosing the figures and percentages of our other cash holdings for emergencies & expenses and retirement funds. That gives too complete a picture of our Total Portfolio i.e. our entire assets. Maybe at a later time when we are more advanced in our journey to Financial Independence but we shall see.

For now, I should only track our Investment Portfolio on this blog. It’s important to understand the relationship between the ETF, Share and Other Portfolios with reference to your asset allocation strategy. The main reason why I decided to disclose our Other Portfolio was because I felt there were gaps when I was explaining our investment approach. You see, although we try to invest every month, it really depends on how the market is like for the month. If it’s trending upwards, we invest less and if it’s trending downwards, we invest more i.e. gradually increase our ETF and Share Portfolios over time.

The point I wanted to highlight is that this does not mean we force ourselves to invest all the time. Any cash not invested just increases our investment cash holdings. Yes, the ETF and Share Portfolios can grow very slowly in some months but that’s okay, because it means your Other Portfolio is growing more quickly. The key is that your Investment Portfolio in its entirety is still increasing. Sometimes, we focus so much on trying to increase our ETF and Share Portfolios that we forget it’s okay not to invest. This just means we have more cash holdings in our Investment Portfolio but it is also considered to be taking a position. By reflecting the Other Portfolio as well, we try to capture this concept when tracking our Investment Portfolio.

Google Sheets – Assets – Cash and Stocks

by //

As discussed in my previous post on why you should use Google Sheets for personal finances, I will outline how to use it in this post. I will be referring to my Google Sheet and how it is set up for illustration purposes. However, I recommend you take the time to prepare your own Google Sheet instead of using templates that are available online. By building your own Google Sheet one cell at a time, it will give you an insight into your personal finances since you have to work out why the information is in the Google Sheet and how best to illustrate it.

How to use Google Sheets?

Basically, it works like an online Excel spreadsheet but its functions are limited. You won’t get the wide range of functions like Microsoft Excel but you will have enough for the purposes of your personal finances.

Why not Microsoft Excel?

Interesting question I have been asked quite a bit. You definitely can use a spreadsheet on Microsoft Excel to track your personal finances. The main reason I prefer to use Google Sheets is that it is better for online work as I pull data (e.g. prices from Yahoo Finance and Google Finance) into the sheet from other websites. The other reason is that Google Sheets is better for collaboration since my wife and I can work on the sheet simultaneously and update it online from anywhere.

Assets

The first tab in my Google Sheet is for my assets. I had mentioned in an earlier post about the importance of asset allocation. To be able to push funds into your various assets to reach your target asset allocation, you must first be able to track the value of the assets. I will describe the basic components of my Assets tab here to help you along. My wife and I have separate Assets tab which we combine into a total Assets tab.

Singapore Cash

  • Emergency Funds (Bank Account Name) – $ Amount – % Proportion of Total Cash
  • Spending Funds (Bank Account Name) – $ Amount – % Proportion of Total Cash
  • Investment Funds (Bank Account Name) – $ Amount – % Proportion of Total Cash

The bullet point represents a different row and the hyphen represents a different column. I group my cash into 3 categories per my asset allocation and each category serves a different purpose. You can have more than the 3 categories but they are sufficient for me. Each category of cash can be held in more than 1 bank account but the key is to separate the different categories of cash. I usually update the figures at the end of each week to see how my cash levels are from income earned and expenses incurred for the week. I also try to add hyperlinks to the internet banking websites of the various bank accounts so I can click into them and login to access my online banking information.

Overseas Cash

  • Overseas Funds (Bank Account Name) – $ Amount – % Proportion of Total Cash

Yes, you can split this into the 3 categories if you want. For me, I prefer to see it in one line because of the nature of these funds. I hold cash in Australia because I studied, worked and lived in Melbourne and Sydney. It’s a country I return to often and makes sense for me to leave some of my savings behind when I left Australia and returned to Singapore. It functions as Spending and Emergency Funds.

Since the funds are in A$, I use this formula to pull the AUD:SGD exchange rate from Google Finance to convert the A$ into S$:

=GoogleFinance(“Currency:AUDSGD”)

Since I am now living and working in Singapore, it makes sense for me to do this foreign currency conversion on my Google Sheet since I am viewing my assets from a S$ perspective.

Singapore Stocks

  • Name of Share (Ticker Code) – Number of Shares – $ Sell Price or $ Last Traded Price – $ Value – % Proportion of Total Stocks Value

I prefer to have an overview of the value of the stocks and a more real-time update of the share portfolio in my Google Sheet. When I first set up my Google Sheet, I was updating the price of each share manually at the end of the week. You can imagine the amount of work that went into this. Subsequently, I realised you could use a formula to pull price data into the sheet. Despite working in accounting in an office and being relatively proficient in Microsoft Excel, I still have no idea to this day why it took me such a long time to realise there had to be a formula to replicate what I was doing manually!

At first, using OCBC Bank (Ticker Code on SGX is O39) as an example, I used this formula: =GoogleFinance(“SGX:O39″,”price”)

However, this formula no longer works because Google Finance does not support SGX anymore. Nevertheless, pulling last traded price data from Google Finance on Google Sheets works well and I use it for the other stock exchanges. I must point out that the search for another formula to pull Singapore share price data from another website was long and frustrating. I had to do quite a bit of research and tested multiple formulae I found from different websites before finding one that worked reasonably well.

To save you time, I will write out the formula I use now to pull Singapore share price data from Yahoo Finance:

=value(substitute(Index(ImportHTML(ʺhttps://sg.finance.yahoo.com/q?s=O39.SIʺ,ʺtableʺ,2), 4,2),ʺ*ʺ,ʺʺ))

One of the readers (Steven) commented that when you copy the formula into the Google Sheet, it doesn’t work. I tried this and it really doesn’t work. Yet somehow, the same formula is working on my Google Sheet. I was able to rectify this by retyping or replacing the quotation marks ” ” of the copied and pasted formula in the Google Sheet. This means that when you copy the formula into the Google Sheet, you have to retype or replace the quotation marks ” ” using your keyboard to activate the formula. It should work after that!
Notice the number “4” in the formula above? This pulls the sell price data of the Singapore share from Yahoo Finance. If you change it to “1”, that’s the previous closing price data. If you change it to “2”, that’s the opening price data. If you change it to “3”, that’s the buy price data. This formula updates the price of the Singapore share automatically and gives you a more real-time feed of the value of your stocks. Because I use the number “4”, which pulls sell price data, it sometimes comes up with an error (#N/A or #REF!) because it’s live data that changes and this fluctuation can cause problems when trying to retrieve the price from Yahoo Finance. Nevertheless, this does not happen often and it usually goes away by itself after a while, which means you just need to click into your Google Sheet at a later time for an error (#N/A or #REF!)-free update.

Overseas Stocks

  • Name of Share (Ticker Code) – Number of Shares – $ Sell Price or $ Last Traded Price – $ Value – % Proportion of Total Stocks Value

Again, you can use the Google Finance currency formula to do the foreign currency conversion. It’s likely that you can also use the Google Finance formula to retrieve last traded price data for overseas stocks.

Using ANZ Bank (Ticker Code on ASX is ANZ) as an example:

=GoogleFinance(“ASX:ANZ”,”price”)

This formula is more stable than pulling price data from Yahoo Finance and is almost always error (#N/A or #REF!)-free.

There you have it – how I construct the basic cash and stocks components of my Assets tab. I will cover the other components in another post because there is already quite a bit of detail in this post. In any case,  you can use the above as a starting point and develop your own template with additional information. What’s important is that it will become your own Assets tab and you will be more inclined to monitor and update it regularly!

Why you should use Google Sheets for personal finances

by //

I only started tracking our net worth on Google Sheets in 2015. It’s unbelievable how useful this tool is and I can never quite understand why I didn’t do it earlier. It was only after the size of the investment portfolio becoming significant that triggered the need for me to have a better tracking system. That being said, I track our Singapore portfolio on SGXcafe and it’s a great tool for monitoring the portfolio. I use Google Sheets to monitor our total assets, liabilities, income & expenses; so the market value of the Singapore portfolio is included but I rely on SGXcafe to track the average price, unrealised gains & losses etc of the various Singapore stocks. If you have no idea how and where to start with managing your personal finances, I would recommend getting all the relevant information down into a Google Spreadsheet as your first step.

What is a Google Sheet?

Firstly, you have to sign up for a Gmail account. After you have logged into your Gmail, you can click into the Google Apps on the top right hand corner and access your Google Drive. From there, you can create a new Google Sheet (the one with the green document icon beside it).

Basically, it works like an online Excel Spreadsheet and you can update it from anywhere as long as you have access to internet connection and Gmail.

Why Google Sheets?

In the US, there is a personal finance tool called Personal Capital. Many of the personal finance and investment bloggers recommend this tool as a good way to track your saving, investment, retirement accounts etc. Since it’s a US-based personal finance tool, I tried searching for something similar in Singapore but realised we don’t have an equivalent here yet. Hence, the alternative is to use Google Sheets to track my saving, investment, retirement accounts etc. Although you can update the information on the Google Sheet from anywhere, it’s a manual process prone to error and can be time-consuming. However, this appears to be the best alternative until we have an equivalent of Personal Capital here in Singapore. In my next post, I will write about how to organise the sheets in your Google Sheet as a way to track your personal finances.

Why track your personal finances?

In my previous posts, I wrote about the Balance Sheet (BS) – Assets & Liabilities and Profit & Loss (P&L) – Income & Expenses statements. Understanding their relationships and how they impact each other is essential to your wealth-building journey. To do that, you must be able to track their actual and percentage figures. If not, you will have no idea whether you are making any progress at all. You can read my next few posts about how I organise our Google Sheet as a starting point. Definitely take the time to prepare your own BS and P&L on Google Sheets and you will be amazed at what you can find out!