After my previous post reviewing my progress in 2019, one reader wanted to know how I track my investments.
Just one question, how do you create a sheet that automatically track your portfolio such that you can plot graphs like this?
I think your XIRR chart and what not is really cool too. Would you mind sharing the spreadsheet so I can take a look at how you did it up?HY
So I figured that some of you guys might feel the same!
Now, the spreadsheet I use on a daily basis was not really created to be shared and is therefore a bit of a mess (ok… a lot of a mess) but since I figured that you guys might find it useful, I cleaned it up and created a template that should be easier to use and reference.
You can check it out here:
Since I didn’t want anybody but me to mess up the template, I’ve set it to read-only for those with the link. In order for you to make use of the spreadsheet yourself you must create a copy of the sheet by going to “File > Make a copy.” Now you have free rein to edit the sheet in any way you need for your own personal use!
How to use the sheet
Even though I tried my best to ensure that the spreadsheet is easy – or easier – to use, I think some explanation will be helpful. So if you want to understand how to use each sheet in detail, read on!
I’ll be explaining the template by breaking down each sheet in the template one by one. If you take a look at the bottom of the screen, you’ll see that there are a total of 6 sheets:
Let’s start first with the Summary sheet.
The “Summary” sheet contains the current snapshot of your portfolio as of today.
You generally do not need to make any changes to this sheet unless you are adding new tickers or cash to the portfolio.
The information in this sheet is at both Portfolio and Ticker level. Let’s take a look at the portfolio level information first.
Portfolio Level Information
At the portfolio level, you get to see:
- Total Portfolio Value: How much the portfolio is worth today by adding the current value of all the tickers and cash together.
- Total Cost of the Portfolio: How much money was used to purchase the current portfolio.
- Total Gain/Loss Amount & Percentage: Portfolio Value minus the Cost of the Portfolio.
- Current XIRR of the Portfolio: Annual rate of return of the portfolio based on the trades in the “Trades” sheet.
- Total loss due to Commission & Fees: The total fee and commission (from the “Trades” sheet) compared to the total portfolio cost.
- Total loss due to Exchange Rate charges: The total difference between the rate charged by the brokerage (documented in the “Trades” sheet) compared to Google Finance rate – which is the mid market rate.
- How much yearly and monthly income the portfolio value translates to (Based on the Safe Withdraw Rate in the “Projection” sheet.)
Pretty useful if you’re a data geek like myself.
That’s not all though, there’s also details at individual ticker level.
Ticker Level Information
At the ticker level, it shows you all of the stock tickers that you hold currently along with the number of shares of each.
On top of that, there are a few important variables that are calculated for you for each ticker:
- Current price of the ticker from Google Finance (and STI ETF Price from Bloomberg)
- Total value in SGD using today’s Google Finance exchange rate
- Percentage Allocation of the Portfolio
- The Dollar Cost Average price in the original currency of the ticker (DCA Price OCY)
- The Dollar Cost Average price in SGD
- The total cost in the original currency (Total Cost Basis OCY)
- The total cost in SGD (Total Cost Basis SGD)
- Your current rate of return for that ticker (XIRR)
Nifty! Best of all, all of this information is calculated based on the “Trades” sheet (which we’ll touch on next) so you don’t have to touch this sheet much at all! Well except when you need to …
Adding New Tickers
If you’d like to track other tickers that’s not already on the sheet, you will need to add a new row and copy over the formulas from the other row depending on what currency the new ticker is.
You will need to ensure:
- The currency is correctly selected
- The conversion rate formula is correct
- The portfolio value and cost calculations includes the new ticker
If you are adding cash to the portfolio without using it to purchase shares right away or if you’ve sold some shares and are then accounting for the cash being added to the portfolio, you’ll need to update the “QTY” column for the SGD (or if you need USD, you’ll need to add that row as well.)
This sheet is where all the magic happens. This is where all your trades are documented and thus it’s the most important sheet to get right. The green columns are where you are meant to make changes & input information.
Let’s break it down in detail so you know how to fill this out.
For each of the trades, you are entering a few things:
- Date: The date that you made the trade. You should be entering this at the exact same time as the trade gets executed to ensure that tracking is accurate.
- Ticker: The ticker of the asset you are purchasing. This ticker should match the ticker in Google Finance as well as the “Summary” sheet so that all the calculations can be automated.
- Price: The exact price of the ticker when you made the trade (in the ticker’s currency.)
- Currency: The original currency of the price.
- Rate: The exchange rate charged by your brokerage to exchange SGD to the currency used for the trade. This is used to calculate your exchange rate loss. This number should be provided by your broker.
- SGD Equivalent: This is the price in SGD (Price x Rate.)
- QTY: The quantity of shares that was purchased or sold. If it’s a BUY trade, then the QTY should be positive. If it’s a SELL trade, then the QTY should be negative.
- Total OCY: This is the total cost of the trade (excluding brokerage fee) in the original currency (Price x QTY).
- Total SGD: This is the total cost of the trade (excluding brokerage fee) in SGD (Price x Rate x QTY)
- Commission: This is the commission charged by the brokerage. Sometimes you might have to figure this out by deducting the total cost of the trade with either Total OCY or Total SGD to find out the brokerage fee.
- Dividend (SGD): If the ticker sometimes pay dividends, you can enter the dividend here. Example in row 40 for just accounting for the dividend. If your platform reinvests dividend for you, you can put it into the same row as the trade (i.e. Row 4)
- DPS: This is dividend per share (not Damage Per Second for you gamers out there). You can calculate this by getting the total dividend payout and dividing it by the total shares you hold.
- Cashflow: This is the total value of the trade. Usually equal to Total SGD + Commission.
- XE Rate: This is only relevant for any trades in foreign currency. It is the exchange rate from Google Finance at the time of the trade. This allows us to calculate the difference between the Google Finance rate (mid market rate) against the rate charged by the brokerage (the “Rate” column.)
- Rate Diff: (XE Rate – Rate) divided by the XE Rate.
- Rate Loss: Total amount rate loss of the trade in SGD.
- Commission %: The percentage of Commission to the Cashflow of the trade.
The last row in this sheet should contain Today’s Date and the Total Value of the portfolio. Always enter your trades above this row as this row is used in the daily XIRR calculation by assuming that today’s XIRR rate is based on you selling your entire portfolio today.
That’s it! If you have questions regarding this sheet do post your comments below and I’ll get back to you!
The coolest sheet in the template. This is where all the beautiful graphs gets drawn.
In this sheet, you’ll see 4 different chart but you can always add more information on your own.
The existing charts are:
|Portfolio Value and Cost Over Time
|Allows you to visualise how much your portfolio is worth vs how much you’ve put into the portfolio.
|Total Return and Annual Return (XIRR) Over Time
|Allows you to see the total return against the annual return over time. I’ve also included the daily average lines for each to see the longer term trend rather than focusing on just the daily values.
The subsequent 2 charts are just splitting out the gains and charting them out over time in their own chart so it’s easier to see.
This sheet gets updated automatically everyday, you don’t have to do anything except setting it up the first time.
In order to set this sheet up for yourself, you’ll need to clear every row except the last row.
Every column in the last row in the sheet contains a formula that inputs the portfolio snapshot for the current day.
Every night, a script will run that creates a new row with the formula and freezes the current day’s information in place so that the graph grows automatically each day.
You can view the script that will be run by going to “Tools > Script editor”. I wouldn’t touch the script in here unless you know what you are doing, but essentially the function “UpdatePortfolioValue()” copies the last row of the “Charting” sheet and pastes it into a new row, then goes to the previous row and freezes the values inside. This essentially takes a snapshot of your portfolio each day.
I have scheduled this function to run daily before midnight before the date changes to lock in the portfolio state.
If you want to track additional items daily, all you have to do is add a new column to the “Charting” sheet so that it gets copied with everything else on a daily basis.
The last interesting sheet, but necessary. Some time in 2018 Google Finance no longer returns the price of some tickers, usually Singapore listed stocks – and that includes the STI ETF – when it still does for the other stock tickers.
Therefore, updating the STI ETF price is not as easy as the other stock tickers, so I created a workaround.
The workaround involves pulling the price from Bloomberg’s website. Basically find the URL of the stock that you want to track and put the link into the formula. For example the URL for SPDR Straits Times Index ETF (or ES3.SI) is: https://www.bloomberg.com/quote/STTF:SP so use this formula:
If you want the quote for Nikko AM Singapore STI ETF then the URL is: https://www.bloomberg.com/quote/DBSSTI:SP and you’ll need this formula:
[Update 26-Sep-2020] As of 16-Sep-2020 the above Bloomberg website no longer works and will return N/A if we use the formula (as pointed out by many commenters below.) After looking around I couldn’t find an alternative solution, however one commenter – Bryan (Thank you for your hard work!) – managed to find another workaround that is still working. I’ve added his formula into the template and below. Basically we’re using the CNBC website now instead of Bloomberg. However, this could also stop working any time once CNBC realises a lot of people are using their site to automatically update spreadsheets… so we might run into this issue again.
I think this method should be usable for other stocks as well if you cannot find it on Google Finance. The only drawback here is that if Bloomberg changes the CSS or the webpage structure of their website, then this formula may not work anymore – but it’s the easiest way to get stock price for now. My personal method is more complex and is much harder for others to use.
This sheet’s primary purpose is to store the updated price of STI ETF which can then be referenced from the “Summary” sheet.
This is a fun sheet for me to do some projections on when I will reach FIRE. I highlighted the fields that you can play with in Green.
The sheet will simulate, based on the interest rate provided, how long it will take for you to reach your FIRE amount.
Here are the variables that you can adjust and what they mean:
- Rate of Return: The growth rate of the stock market (after accounting for inflation.) I assume 8% annual growth.
- Saving Amount: Your saving amount per month at the start of the simulation.
- Yearly Increment: The amount your monthly saving will increase on a yearly basis.
- Bonus: The amount of bonus you will be investing each year. I assume that bonus is paid once every 12 months and is calculated in multiples of your monthly salary.
- Starting Portfolio Amount: The amount you already have in your portfolio at the start of the simulation.
- Target Income (Monthly): The monthly passive income you need after you reach FIRE.
- Safe Withdrawal Rate: The percentage of your portfolio you are targeting to withdraw each year. The recommended amount is a number below 4%. I am currently targeting 3.33% to be conservative.
That’s it! Once you have all this keyed in, it will calculate the portfolio value you’ll need and determine roughly how long it will take for you to save the amount based on the rate of return that you entered.
It will also take your current portfolio amount to tell you that based on your current portfolio, which year you are roughly at within the simulation and how many years is left on your FIRE journey.
This is just a fun sheet for me to look at once in a while. I usually like to see that I’m ahead by looking at the “Current Year” value. If I have only started investing for 2 years but “Current Year” is saying a number larger than that, it means that I’ve saved more quickly than the simulation expected.
This is a very simple – single rate of return – simulation, so don’t take this as anything more than “just for fun.”
Now, to the final sheet.
This sheet is the most recent sheet that I’ve added to the template. It’s meant to help us benchmark our investment portfolio against other investments or investment funds.
Normally when we look at purchasing Unit Trusts or Mutual Funds, they will quote their return by year. For example the discrete yearly returns in the case of IWDA on iShares website are:
- 2015: -0.77%
- 2016: 7.73%
- 2017: 22.45%
- 2018: -8.65%
- 2019: 27.76%
And so on.
However, our portfolio XIRR in the “Summary” sheet shows the rate of return from the start of our investment journey until today, which could include multiple years taken together. This cannot be used to compare with the yearly return that is quoted on many financial instruments or other investment portfolio.
This is why I created this sheet.
The sheet assumes that at the start of each year, we decided to buy our entire portfolio at cost or the value of the portfolio, whichever is higher.
Then as the year goes on, we continue to invest using the trades in the “Trades” sheet.
Finally at the end of the year at 31-Dec, we liquidate the entire portfolio and calculate the return for the year.
This will give us the total rate of return for that particular year which can then be used to compare ourselves with investment funds, index performance, or other people (main reason!)
Note though that the number is really useful if you have been investing for the entire year, so if you started investing in the middle of 2016, then your 2016 number isn’t really going to be that useful for comparison. I hope that makes sense.
Adding a new year
In order to add a new year, copy the Date and Cashflow columns and change the dates to the new year’s date.
Then add the new year to the left and copy over the XIRR formula while making sure to change the columns in the formula to point to the new columns you’ve just added.
There you have it – the spreadsheet I’ve been using regularly to track my portfolio.
I hope that you’ll find it useful.
Let me know if you think I could add or improve the sheet and I’ll see if I can add it in!
Until next time!