I got PTSD when I revisited the formula in the sheet. It’s a bit complicated. But here goes:
The main formula
The main formula is FV. Because we want to be able to model monthly contributions, we use monthly values for its arguments. Those are:
- monthly return
- investment duration in months
- monthly contributions (
× −1, as amounts deposited need to be input as negative)
- starting sum (
× −1, as amounts deposited need to be input as negative)
1. Monthly return
The average yearly stock market return is 7%. From this, we subtract:
- TER
- irrecoverable withholding tax (see below)
- income tax (see below)
This gives us the average yearly net return. Then we need to convert this value to a monthly return:
(1 + yearlyNetReturn)^(1 ÷ 12) - 1 = monthlyNetReturn
Irrecoverable withholding tax
- For this we assume the index contains 62% US companies.
- US dividend yield is 1.2%, and ex-US dividend yield is 2.5%.
- Level 1 (L1) withholding tax is what is withheld when dividends get transferred from the companies to the fund, and L2 WHT is what is withheld when dividends are transferred from the fund to us.
US-domiciled
There are two things reducing recovery of WHT: lost ex-US L1 WHT and DA-1 losses.
lostWHT = lostexUSL1WHT + lostDA1
ex-US dividends have an average level 1 withholding tax of 10% for funds based in the US, which is lost forever:
lostexUSL1WHT = exUSWeight × exUSDividendYield × exUSL1WHT_USFund
0.38 × 0.025 × 0.1 = 0.00095
We also get US withholding tax credited via DA-1. We don’t get any credit if the amount isn’t at least CHF 100 (ignored in this calculation), or, depending on our financial situation (mortgage, average tax rate etc.), we may not get the full amount credited. And we don’t get the credit immediately, so we lose out on stock market returns on that money:
lostDA1 = (USWeight × USDividendYield + exUSWeight × exUSDividendYield × (1 − exUSL1WHT_USFund)) × USL2WHT × (DA1CreditRate × yearlyStockMarketReturn × yearsUntilDA1Received + (1 − DA1CreditRate))
If we get 100% credited after 1 year (the optimum), we get:
lostDA1 = (0.62 × 0.012 + 0.38 × 0.025 × (1 − 0.1)) × 0.15 × (1 × 0.07 × 1 + (1 – 1)) = 0.000167895
And if we get the 90% credited after 1½ years, we get:
lostDA1 = (0.62 × 0.012 + 0.38 × 0.025 × (1 − 0.1)) × 0.15 × (0.90 × 0.07 × 1.5 + (1 – 0.90)) = 0.00046650825
IE-domiciled
For funds based in IE, US dividends have a level 1 withholding tax of 15% and ex-US dividends have an average level 1 withholding tax of 12%, both of which are lost forever:
lostWHT = USWeight × USDividendYield × USL1WHT + exUSWeight × exUSDividendYield × exUSL1WHT_IEFund
0.62 × 0.012 × 0.15 + 0.38 × 0.025 × 0.12 = 0.002256
Income tax
US-domiciled
lostToTaxes = (USWeight × USDividendYield + exUSWeight × exUSDividendYield × (1 − exUSAverageL1WHT)) × marginalTaxRate
IE-domiciled
lostToTaxes = (USWeight × USDividendYield × (1 − USL1WHT) + exUSWeight × exUSDividendYield × (1 − exUSL1WHT)) × marginalTaxRate
2. Investment duration in months
This should be self-explanatory. If not, let someone else handle your finances 
3. & 4. Monthly contributions and starting sum
Here, we subtract the buying fee and stamp tax.
IBKR
For simplicity’s sake we use automatic currency conversion (fee of 0.03%) and fixed pricing, although the calculation is still a bit complicated, as fixed pricing is at least USD 1 and at most 1% of trade value, but still depends on the number of shares (USD 0.005 per share). I left out the “max 1%” part as it only triggers with shares under USD 0.50.
netContribution = contribution × (1 − 0.0003) − MAX(1, (contribution × exchangeRateCHFUSD × (1 − 0.0003)) ÷ approximatePriceOfVT × 0.005) ÷ exchangeRateCHFUSD
Saxo
This can be cheaper if you are on a higher customer level. On the lowest level, the fee is 0.08%, but at least CHF 3. Stamp tax is 0.15%. I assumed we are buying ETFs in CHF on SIX, feel free to change this if you prefer to buy funds in other currencies.
netContribution = (contribution − MAX(3, contribution × 0.0008)) × (1 − 0.0015)
Putting it all together
The final formula now is:
FV((1 + (yearlyStockMarketReturn - TER - lostWHT - lostToTaxes))^(1 ÷ 12) - 1, investmentDurationInMonths, netMonthlyContribution × −1, netStartingSum × −1)