You will build one workbook that holds your trades, FX costs, dividends, withholding and SGD return.
Keep your overseas investment records in one workbook and update it through the year. Its summary tab should then give you three numbers with no extra work: your total return in Singapore dollars, your total FX costs and your total withholding tax. Building it and loading a year of data takes about 45 minutes. After that, keeping it up takes a few minutes a month.
By now you have the pieces, and so did Rachel. They had a broker check sheet from lesson 1.4, a cost calculator from lesson 2.4, W-8BEN dates from lesson 3.2, a folder of statements from lesson 7.1 and an XIRR sheet from lesson 7.2. Their father asked how much the overseas investments made last year and what they cost. Rachel couldn't answer because the answer was spread across 5 files.
The workbook has four tabs: trades, dividends, account details and summary. Any spreadsheet program will do. Save it in your records folder from lesson 7.1 so your executor can find it through the note from lesson 7.3.
Use one row per trade. The columns are: date broker holding buy or sell quantity price in US dollars total in US dollars including commission and fees the exchange rate you actually converted at the Singapore dollar amount that left or entered your account
Add one more column for the estimated FX cost of each conversion. This is the SGD amount multiplied by your measured spread, the one you worked out in lesson 2.1, "Where the FX cost hides in a US trade". The figure is an estimate, so put the word "estimate" in the column header. That keeps you honest about what the number is.
Rachel entered the two buys from lesson 7.2 using a spread of 0.5%, which is a made-up figure. On 15 January they bought US$3,000 at a rate of S$1.34. S$4,020 left the account, and the FX cost was about S$20.10. On 15 July they bought another US$3,000 at S$1.36. S$4,080 left the account, and the FX cost was about S$20.40.
Use one row per dividend. The columns are date, broker, holding, gross amount in US dollars, tax withheld, net amount, the rate used to convert or value it, and the net amount in Singapore dollars.
Add a check column that divides the tax withheld by the gross amount. For US dividends paid to a Singapore resident, this should show 30%, as explained in lesson 3.1. Any row showing a different rate is worth raising with your broker.
Rachel had one dividend, on 15 December: US$60 gross, US$18 withheld and US$42 net. At S$1.35, the net amount comes to S$56.70. The check column showed 30%.
Use one row per account. The columns are broker brand, legal entity, regulator and licence evidence, protection scheme, W-8BEN expiry date and transfer-out method. You already have most of this from the broker check sheet in lesson 1.4 and the W-8BEN check in lesson 3.2.
The W-8BEN expiry column is the one you will keep coming back to. Set it to highlight any date within the next six months.
The summary tab uses formulas that draw on the other three tabs, so it updates as you add rows. It holds three figures.
Your total SGD return is XIRR on a list built from the trades and dividends tabs, plus the year-end value of each holding. Use the method from lesson 7.2. Total FX costs is the sum of the estimated FX cost column on the trades tab. If you converted any dividends, add those conversions too. Total withholding is the sum of the tax withheld column on the dividends tab, converted to SGD.
Rachel's summary showed an XIRR of about 10.6%, FX costs of about S$40.50, and withholding of US$18, which is about S$24.30 at S$1.35. FX costs and withholding together came to more than the commission Rachel paid for the whole year. That surprised them, because they had spent weeks comparing commissions between brokers.
Surprises tend to show up in the summary. Hakim found one small dividend that had been converted to SGD and straight back to USD. That meant paying the spread twice for nothing, the pattern from lesson 2.2.
Your workbook is finished when all four tabs hold a full year of your own data, the summary formulas need no retyping, and any W-8BEN date coming up is highlighted. To test it, ask whether someone else could open it and understand your overseas investments without asking you a question.
Build the workbook, load one year of your own data, and look hard at the three summary numbers before you write down which ones surprised you most.
Build the workbook and put in one year of your own data. On the summary tab, write down the three numbers that surprised you most.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).