fbpx

Sharing My Portfolio Tracking Spreadsheet

Reading Time: 10 minutes

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:

FIRE-Path Lion Portfolio Tracking Google Sheets Template

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:

  1. Summary
  2. Trades
  3. Charting
  4. ES3.SI
  5. Projection
  6. Yearly XIRR

Let’s start first with the Summary sheet.

Summary

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

Screenshot of the portfolio summary section.
Portfolio Summary

At the portfolio level, you get to see:

  1. Total Portfolio Value: How much the portfolio is worth today by adding the current value of all the tickers and cash together.
  2. Total Cost of the Portfolio: How much money was used to purchase the current portfolio.
  3. Total Gain/Loss Amount & Percentage: Portfolio Value minus the Cost of the Portfolio.
  4. Current XIRR of the Portfolio: Annual rate of return of the portfolio based on the trades in the “Trades” sheet.
  5. Total loss due to Commission & Fees: The total fee and commission (from the “Trades” sheet) compared to the total portfolio cost.
  6. 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.
  7. 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

Screenshot of the ticker level information.
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:

  1. Current price of the ticker from Google Finance (and STI ETF Price from Bloomberg)
  2. Total value in SGD using today’s Google Finance exchange rate
  3. Percentage Allocation of the Portfolio
  4. The Dollar Cost Average price in the original currency of the ticker (DCA Price OCY)
  5. The Dollar Cost Average price in SGD
  6. The total cost in the original currency (Total Cost Basis OCY)
  7. The total cost in SGD (Total Cost Basis SGD)
  8. 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:

  1. The currency is correctly selected
  2. The conversion rate formula is correct
  3. The portfolio value and cost calculations includes the new ticker

Adding Cash

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.)

Trades Sheet

Screenshot of the Trades sheet.
Trades sheet. Enter information in the columns highlighted in GREEN.

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:

  1. 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.
  2. 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.
  3. Price: The exact price of the ticker when you made the trade (in the ticker’s currency.)
  4. Currency: The original currency of the price.
  5. 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.
  6. SGD Equivalent: This is the price in SGD (Price x Rate.)
  7. 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.
  8. Total OCY: This is the total cost of the trade (excluding brokerage fee) in the original currency (Price x QTY).
  9. Total SGD: This is the total cost of the trade (excluding brokerage fee) in SGD (Price x Rate x QTY)
  10. 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.
  11. 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)
  12. 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.
  13. Cashflow: This is the total value of the trade. Usually equal to Total SGD + Commission.
  14. 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.)
  15. Rate Diff: (XE Rate – Rate) divided by the XE Rate.
  16. Rate Loss: Total amount rate loss of the trade in SGD.
  17. 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!

Charting Sheet

Total returns and annual returns chart.

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:

ChartDescription
Portfolio Value and Cost Over TimeAllows 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 TimeAllows 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.

ES3.SI Sheet

The last interesting sheet, but necessary. Some time in 2018 Google Finance no longer returns the price of ES3.SI 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.

This sheet’s primary purpose is to store the updated price of STI ETF which can then be referenced from the “Summary” sheet.

How this is updated is via a script in “Tools > Script editor” called “UpdateSTIIndexPrice()” which grabs the ES3.SI price from an API provided by Alpha Vantage (not sponsored.)

In order for this script to work, you will need to get your own API Key from Alpha Vantage and replace the API Key into the line that says “ENTER_API_KEY_HERE.”

This script requests for the latest price from Alpha Vantage and replaces the STI ETF Price in the “ES3.SI” sheet.

Make sure that this script runs before the snapshot script so that the price is update. Although sometimes the API request fails and the price isn’t updated, though that should be rare and Google Sheets will inform you when it fails so you can manually update the price.

Projection Sheet

FIRE Projection Chart

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.

Change the GREEN cells to match your situation.

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:

  1. Rate of Return: The growth rate of the stock market (after accounting for inflation.) I assume 8% annual growth.
  2. Saving Amount: Your saving amount per month at the start of the simulation.
  3. Yearly Increment: The amount your monthly saving will increase on a yearly basis.
  4. 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.
  5. Starting Portfolio Amount: The amount you already have in your portfolio at the start of the simulation.
  6. Target Income (Monthly): The monthly passive income you need after you reach FIRE.
  7. 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.

Yearly XIRR

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:

  1. 2015: -0.77%
  2. 2016: 7.73%
  3. 2017: 22.45%
  4. 2018: -8.65%
  5. 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!)

The numbers are examples, clearly.

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.

That’s it!

Conclusion

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!

FPL

15 thoughts on “Sharing My Portfolio Tracking Spreadsheet”

  1. Hey FPL!
    Have you considered using stocks cafe? Up until a few months ago, I also tracked my portfolio using an excel spreadsheet. My set up was not as comprehensive as yours but even then I found it a hassle to update it for every new transaction/dividend 😅

    Stocks cafe helps me do that automatically now and I love that I don’t have to worry if my formulas have any typos lol. Do check it out!
    Here’s my referral link which gives you a Friend status for 2 months: https://stocks.cafe/user/tosignup?referral_code=joceyng

    Reply
    • Hey Joce, thanks for the suggestion! Stocks Cafe does look pretty powerful but unfortunately it doesn’t support stocks on London Stock Exchange yet (which are my largest holdings.) I also really like tinkering with spreadsheets (I know, super geeky) so I guess I’ll have to stick with my spreadsheet for now! 😀

      Reply
  2. Hi ,Thank you for the beautiful spreadsheet!
    I have adjusted the spreadsheet to have a portfolio of ES3, MBH and IWDA. However, I am not able to get the charting tab working where it only show today’s values instead of charting before midnight every day. I followed the instruction of “In order to set this sheet up for yourself, you’ll need to clear every row except the last row.”

    Here are some changes that i made personally:
    1.Adding a new ticker for MBH
    2. Using Yahoo Finance to track the price for ES3 and MBH instead of the one you used, Below are what I inputed in the summary tab for their current price:
    =ImportXML(“https://sg.finance.yahoo.com/quote/”&A5&”/history?p=”&A5, “//tbody/tr[1]/td[6]”)
    =ImportXML(“https://sg.finance.yahoo.com/quote/”&A4&”/history?p=”&A4, “//tbody/tr[1]/td[6]”)

    May I know how do i resolve this so that I can do the proper charting for everyday? 🙂
    (Have already tried for 3 days but could not find a solution >< )

    Reply
    • Hey Damien! Glad you like the spreadsheet! I’ll try to help you through this, would you mind sharing your sheet? If you prefer not the share then I’ll need to understand a bit more about the issues you’re facing.

      One thing to check, do you have the “UpdatePortfolioValue()” function in “Tools > Script editor” scheduled to run daily? This will copy the last row in the Charting sheet and paste it in a new row and then past the value back into the previous row so you get the daily snapshot of your portfolio on a daily basis.

      If that is already scheduled then I’ll need to understand what behaviour you’re seeing 🙂

      Reply
        • No problem! You can get the function to run daily by clicking on the “Clock” button between the “Save” and “Play” button in the Script Editor screen. You’ll have to authorize the script to run on your sheet and then you can set up a trigger that runs daily at a certain time and execute the function. Let me know if you can find it.

          Reply
          • Thanks! It should be running fine now.

            I’ve got another question though if you don’t mind – I started DCA investing a couple of years ago, but haven’t been really keeping track of my investments and payouts. I thought I should do it properly at the start of this year as part of my NY resolution (lol). I now have a couple of holdings in STI, SBS bonds, Philips APAC Div Reit, while the majority of my cash is in a Citi Priority account earning interest.

            So my questions are:

            (i) Would you recommend that I withdraw most of my cash in the Citi account to invest in the international component of the portfolio, or just leave it there to earn interest and going forward invest in the international component until it matches the allocation I want?

            (ii) How should I use your tracksheet to record pre-existing investments? My understanding is that your tracksheet is premised on recording each and every trade, but it’s difficult for me to do that now.

            Thanks in advance – your blog is really helpful for newbies like me!

          • Hey Nic, great to hear the sheet is working for you now! It can be a bit complex to set up the first time, but glad you’ve got it running.

            For (i) this really depends on your risk appetite as well as your individual situation right now. Only you will be able to tell whether you are OK to risk the cash, remember investments is not a guarantee! Select an allocation that you are comfortable with, have some cash cushion for emergency and then build your portfolio towards that allocation. 🙂

            ( ii ) Yes the sheet is ideal if you’re starting to track from scratch. You have two choices, 1. don’t worry about the past, just track from today onwards. 2. If you’d like to track from the past, you’ll need to manually get the share price history of all your tickers and work backwards using your trading date. It’s possible (that’s what I did when I first set this sheet up 1 year after I started, but it’s very tedious.)

            Hope that makes sense! I’m glad you found my blog useful! Do let me know if you have any further questions.

  3. Hi, thank you for helping me with this! I have made a copy of the spreadsheet and attached it below:
    https://docs.google.com/spreadsheets/d/1-_8DcKRh172QuWrQh_gZzXNbgdf4CirnGRYvFON92ro/edit?usp=sharing

    Here are the problems that I am facing:
    1. The XIRR column in the Summary tab only shows “#VALUE!”
    2. Unable to set the daily charting where it only shows today’s value
    3. For the projection tab, I am not able to get an accurate value for the FIRE year as I think the formula for “FIRE Year” is limited to less than 15 years only?
    4. Unable to set the Yearly XIRR tab (Only record today’s value as well)

    Here are the changes I made for your reference:
    -Using Yahoo finance to track the current price for ES3 and MBH in the Summary Tab
    -Adding a new ticker for MBH
    -Removal of the ES3 Tab and the code to track ES3 in the code editor

    Really appreciate your help for this because I feel that your spreadsheet is quite good and will be able to document my investing journey as well 🙂

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.