The workbook attempts to provide the following tools for your investigation:
- Provide a year over year tax cost and efficiency study across the entire Vanguard Fund Family that is longitudinal, not just one year.
- Structure to create reports that will estimate your taxes owed based upon actual distributions, and personal tax situation.
- Year over year report for taxes and distributions for one fund.
- Money Market returns based upon actual distributions and tax situation.
- Note, it is expected that Vanguard will update the 2023 ICI files to include final REIT numbers in early February, I will update after it is released.
The default configuration displays all ETFs active at the end of 2022 since an efficiency calculation must be based upon the end of year price.
- Three ETFs were introduced in 2023 that are not included in the list, but their data is available in the ICI files: VCRB, VPLS, and VTES.
Comments, bug reports, ideas, any feedback is welcome.
Summary of the Tabs in the Spreadsheet
- Tax Parameters - This is the place you customize your personal tax rates. The page is pretty self-explanatory, note the caveat on state-specific issues that may not be covered. Relative tax efficiency is calculated from these parameters. This allows the report to be customized to your particulars.
- YoY Tax Efficiency - This tab takes the all years of the efficiency calculations, brings the key values into one report and creates a historical average. Currently looks at years 2018 through 2023. All data is pulled from the 20xxTaxEfficiency tabs.
- 2023 MM Breakdown - This tab allows you to take one of the Vanguard Money Market Funds, specify a starting balance and it calculates a pre-tax and after-tax return for the fund if you simply reinvest dividends through the year. This is interesting, but it is also an independent data source for verifying my MM Optimizer algorithms (they matched within 2bps for 2023!)
- Fund 1099 History - This tab allows you to take any one Vanguard MF, ETF or Money Market, specify a starting number of shares (or distribution amount) and it calculates a pre-tax and after-tax return for the fund for each year in the database. Good to see what kind of distributions to expect over time and varying tax treatment as appropriate.
- 2023 1099 Stats - This tab allows you to create a table of any collection of Vanguard Funds, and create current years tax treatment. It is structure for a collection of accounts, but you can customize as you desire, adding or deleting groups and rows.
- 20xxTaxEfficiency - There is one for each year supported by downloads of the Vanguard ICI Files. If you wish to see results for a fund not listed, you can add or replace the symbol, but remember, the YoY tab relies on the data from each year's tab, so make sure the symbol added in all sheets if you want the YoY to work for the fund as well. I default the list to include ALL current Vanguard Retail ETFs. Mutual funds will work just as well. If you enter a symbol on a yearly tab for a year before the fund was created, it will just return empty results. Also note, the 10 year calculations are also left blank if the fund does not have a 10 year performance record.
- FundList - This is a recent scrape of more fund data from the Vanguard.com site used to find ticker symbols, fund ids, etc. It is needed to do the historical price and yield queries. the formula cell A1 should be copied into A2 once a year and updated to get the latest funds. I then manually copy and store the results there to reduce runtime errors and overall performance. This a gigantic workbook, all tricks to reduce calculations is important.
- 20xxVanPrimaryLayout - This is an import with minor formatting (and ensuring all zeros are correctly imported as empty to reduce formula complexities) of the ICI format report from Vanguard.com
- 20xxVanSecondaryLayout - This is an import with minor formatting (and ensuring all zeros are correctly imported as empty to reduce formula complexities) of the ICI format report from Vanguard.com