One of the frequently asked questions about ETF is the purchase cost. The question tries to find out the purchase cost as % of the purchase amount (just like the sales charge of unit trust funds), and also the minimum purchase amount required to keep the purchase cost below a target percentage.
The “How you can use exchange-traded funds” (PDF, 92KB) booklet from Vanguard’s “Learn About the Unique Benefits of Vanguard ETFs” web page has a table on page 7 that shows the calculation of the purchase cost, taking into account the share price, commission and spread. Using this table as a reference, I have made a spreadsheet for calculating the % purchase cost given a purchase amount, and the minimum purchase amount given a target % purchase cost. Four ETFs are included in the spreadsheet as examples. Each one of them is purchased from a different stock exchange (US, UK, SG, HK), using a Singapore-based broker with the lowest commission (to my knowledge).
Download the spreadsheet (XLS, 64KB).
The spreadsheet uses macros to calculate the minimum purchase amount. If you don’t feel comfortable of running macros on your PC, follow these steps to enable macro protection in Excel. If you choose to disable the macros, you will have to manually call the Tools > Goal Seek function in Excel to calculate minimum purchase amount.
Besides ETFs, you can also use the spreadsheet for shares. And if you use different broker, just adjust the commission formula to what your broker uses.