You will build a single workbook that holds your trades, FX costs, dividends, withholding and SGD return.
By now you have pieces of a record in several places: 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. Rachel had all of these and still could not answer a simple question from her father: how much did your overseas investments make last year, and what did they cost you? The answer was spread across five files.
This project pulls it into one workbook you update through the year. It takes about 45 minutes to build and load with a year of data, and a few minutes a month after that.
The brief is one workbook with four tabs: trades, dividends, account details and a summary. Load it with one year of your own data. The summary tab should give three numbers for the year without any extra work: your total return in Singapore dollars, your total FX costs and your total withholding tax.
Use any spreadsheet program you like. Keep it in your records folder from lesson 7.1, so your executor can find it through the note from lesson 7.3.
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, and the Singapore dollar amount that left or entered your account.
Add one more column for the estimated FX cost of each conversion: the Singapore dollar amount times your measured spread from lesson 2.1, Where the FX cost hides in a US trade. It is an estimate, and labelling it so in the column header keeps you honest.
Rachel's two rows from lesson 7.2 went in like this, with a made-up spread of 0.5%: 15 January, buy US$3,000, rate S$1.34, S$4,020 out, FX cost about S$20.10; 15 July, buy US$3,000, rate S$1.36, S$4,080 out, FX cost about S$20.40.
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 to a Singapore resident it should show 30%, as lesson 3.1 explained. Any row that shows something else is worth a question to your broker.
Rachel's row: 15 December, US$60 gross, US$18 withheld, US$42 net, rate S$1.35, S$56.70. The check column showed 30%.
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. Most of this comes straight from your broker check sheet in lesson 1.4 and your W-8BEN check in lesson 3.2.
The W-8BEN expiry column is the one you will come back to. Highlight any date within the next six months.
The summary tab draws from the other three with formulas, so it updates as you add rows.
Total SGD return uses XIRR on a list built from the trades and dividends tabs plus the year-end value of each holding, as in lesson 7.2. Total FX costs adds up the estimated FX cost column on the trades tab, plus any conversion of dividends if you converted them. Total withholding adds up the tax withheld column on the dividends tab, converted to Singapore dollars.
For Rachel's made-up year, the summary showed an XIRR of about 10.6%, total FX costs of about S$40.50, and total withholding of US$18, about S$24.30 at S$1.35.
A finished workbook has all four tabs filled with a full year of your own data, formulas on the summary tab that do not need retyping, and a W-8BEN date highlighted if it is coming up. Someone else could open it and understand your overseas investments without asking you a question.
The summary is also where the surprises show up. Rachel's surprise was that her FX costs and her withholding together came to more than her commission for the whole year, which she had spent weeks comparing between brokers. Hakim's was that one small dividend had been converted to Singapore dollars and straight back to US dollars, paying the spread twice for nothing, exactly the pattern from lesson 2.2.
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, load one year of your own data, and write the three numbers on the summary tab that surprised you most.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).