Site Overlay

My Excel IRR Portfolio Summary

I get this question frequently – “How do you track your portfolio? Can you share me the template?” and the truth is – I am more than happy to do so.

Excel Then vs. Today

Personally, I stumbled upon the amazing wonders of what Excel IRR can do, during my early days of investment (or rather, baby step on venturing beyond fixed deposits :P) when I landed on Fundsupermart Malaysia’s thread in the Lowyat Forum.

One of the forumer was kind enough to share their portfolio excel template, which I took it as the base and subsequently took and developed it further over the years.

This, was how it looked like back then. It started as a simple tracker for Unit Trust funds, capturing line of transactions, manual pricing updates to track the current portfolio value, which in return gives us an idea of the actual fund performance through excel’s own XIRR formula.

And over years, it morphed into a monster which expanded the coverage to multi-currency support, expanding beyond the typical Unit Trust funds onto worldwide stock market and dividend supports.

Still, I won’t consider the above excel some “success story” or “the next Facebook” – but it’s good enough, serves my need, and at the same time helps other whom prefers to stick with Excel but wants to minimise the manual updates required to track their portfolio.

What functionalities are there?

As this excel started FROM tracking Fundsupermart Malaysia’s fund, despite that I have technically “retired” from unit trust investments (except PRS), I will still try to maintain support for Fundsupermart for two reasons:

  1. Sentimental value 😀
  2. They’re still providing the cheapest unit trust, at least when compared to Bank / Agents model. Although small, unit trust fees will still eat into your long term returns.

In terms of feature – as this is mainly a “beautified” Excel – don’t have your hopes too high up but in high level, the Excel supports:

  • Tracking of Overall Portfolio performance (IRR/ROI)
  • Tracking of Individual Fund/Stock performance (IRR/ROI)
  • Tracking of Realised / Unrealised portfolio gain/loss
  • Auto-update of currency exchanges rate
  • Auto-update of fund unit prices, supporting:
    • Fundsupermart Malaysia
    • Fundsupermart Singapore
    • Fundsupermart Hong Kong
    • Global Stock Listings (as long as it’s listed on Yahoo Finance) – such as KLSE, NYSE, LSE, NASDAQ, SGX, HKEX, …..
  • Auto-capture of portfolio / fund snapshot for analysis / reporting purposes
  • Basic analytics via PivotTable / PivotChart
  • Easy-to-track Portfolio Allocation (e.g. by investment vehicle and country allocation)

The list will continuously evolve (especially my own “DEV” version) – though for stability (or security) purposes I generally do not release new version unless there are significant changes, so don’t be surprised if you see some missing features compared to the version I am using.

For example, my own version have expanded coverage to auto StashAway portfolio fetcher, dividend logs, scheduled job to run updater in background, scheduler for auto-input of new transactions, etc. But these are generally not released as it’s still in development stage and typically have unresolved fundamental issues – i.e. how to avoid the need of hard-coding username/password. I generally release major version every 2-3 years, but usually depending on mood.

Why not Google Sheets?

The next most frequently-asked question that I always get – once I established the fact that I track my portfolio using Excel, is “why not move it to Google Sheets? You can do it online / via mobile phone”

They’re not wrong. Google Sheets are way more powerful and capable than they are 5 years ago. But the truth is? I am lazy. I am lazy to re-design the entire piece onto Google Sheet or other applications – especially since it is still working well for me. Ironically, these are the exact thing I hated during my career which I totally hated managing Resistance to Change during IT Project deployments. Oh well, giving myself the taste of my own medicine 😀

Jokes aside – if one day the Excel / VBA scripting stops working out of the blue and no longer meet my needs, then perhaps I will start venturing out on exploring others’ open source solution or recreating entire thing elsewhere. But until that happen – it’s here to stay, at least for my own use.

Do I have to pay?

That’s the good news! I am providing this excel template as part of my giving-back to the community – in another word, it’s FREE!

Unfortunately, in exchange, I won’t be able to provide you 1-to-1 support/hands on/training and I am expecting that you will have at least basic knowledge to navigate around Excel. I’ve tried my best to keep it as simple as possible, added some basic navigation guides (on how to add funds etc.) within the Excel itself.

The Excel contains VBA scripts for its core functions to work (e.g. Price Fetcher, Portfolio Snapshot), and are completely open source. So if you have programming / IT background, feel free to take it further and develop/customise your own version by forking a new branch – as long as you attribute the credits back to me whenever applicable.

How can I get it?

Just a few important notes before we get there:

  • If you’re currently using my previous v2.2 stable, you can use the “Polarzbearz’s FSM-to-Spreadsheet Conversion Tool” and go to the section for “2.2 to 3.1 FILE CONVERSION”. Otherwise, you can re-import and convert latest FSMOne historical data using the same tool.
  • If you’re using the Stock Updater feature, get your stock codes from Yahoo Finance, applicable for all markets (NYSE, KLSE & HKEX).
  • If you want to use Currency Updater, there’s a few step required to first get yourself an API key via https://app.exchangerate-api.com/, and update “Reference Data” >> cell D5 (API key)
  • This file contains sample data in “Investment Details”, “Portfolio Snapshot”, “Fund Snapshot”, “Fund Snapshot Exclusion”, “Fund Factsheet”. UPDATE/REMOVE as required before proceeding!!!!
  • No changes should be required for “Currency”, “Fund Info”, “Fund Allocation”, “Portfolio Summary”, “Fund Pivot (Profit)”, “Fund Pivot (IRR)” as these are all automated updates. Do not touch anything unless you know what you’re doing
  • As this is a free tool, no guarantees whatsoever will be provided. Best-effort-basis support will be given to those who needs it, but would be best if you are comfortable navigating around Excel
  • This version is only fully tested on Microsoft Excel 2016 and above (incl. Office 365), on Windows 10 PC. Unfortunately I don’t have a Mac to test it so you’ll be on your own

Download

2.2C Stable Versionhttps://bit.ly/polarzbearzPortfolioSummary2c (this is the previous stable version patched for 2020 FSMOne)
3.1C Latest Version (this one)https://bit.ly/polarzbearzPortfolioSummaryv31c
FSM Conversion Tool (only compatible with v3.1)https://bit.ly/polarzbearzPortfolioSummaryConversionToolv31c

If you have enjoyed my works and would like to support me, feel free to use any of my referral links here or buy me a coffee here!

Patch Notes

v3.1c [1 Nov 2020]: Hotfix for FSM Malaysia price fetcher since their new layout basicalaly broke the script.

Read more here

v3.1b [23 Oct 2020]: Sneaked in a small change to add additional major stock exchanges (NASDAQ, SGX, LSE) and also added one generic “Others” for other worldwide stock exchanges. As long as it is listed in Yahoo Finance, you can fetch the price.

Read more here

Footnotes

If you have been using my Excel for a long time – I thank you for your support and hope that it have helped you in your journey towards financial freedom.

If you’re new and are looking for some way to keep track of your multitude of portfolios – I hope this Excel would be useful for you. If not, there are plenty of other solutions out there and just don’t stop finding – you will surely be able to find something that fits your needs.

Cheers,
Gracie

Leave a Reply

Your email address will not be published. Required fields are marked *