Vanguard Fund Tax Analysis & Efficiency (2018 to 2023)

Have a question about your personal investments? No matter how simple or complex, you can ask it here.
Post Reply
Topic Author
retiringwhen
Posts: 4669
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Vanguard Fund Tax Analysis & Efficiency (2018 to 2023)

Post by retiringwhen »

I know there are threads out there for more general Tax Efficiency discussions, but I have been maintaining a multi-year Tax Efficiency workbook driven off the Vanguard published ICI files and I figured someone would find this useful. I have been using and perfecting this tool for the last 3 years as part of my annual tax and income planning efforts.

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.
You will find 2023's file here: 2023 YoY Vanguard Tax Efficiency
  • 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 sheet has annual efficiency calculations for the years 2018 through 2023 for all active Vanguard funds (ETFs, Mutual funds too).

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.
The following is an overview of the different tabs provided. Please make a personal copy to customize your tax situation and funds of interest.

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
There is much more detail in the ICI layouts, feel free to investigate those pages too, as I haven't brought every single value into my analysis sheets.
Topic Author
retiringwhen
Posts: 4669
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Vanguard Fund Tax Analysis & Efficiency (2018 to 2023)

Post by retiringwhen »

I also have a file for SPDR ETFs for 2022. I am currently waiting for their 2023 ICI files to be released.

You can find it here: 2022 SPDR Tax Efficiency

If have access to any fund company ICI reports for any years, I am interested in collecting them and creating a more generic tool.

ICI is the Investment Company Institute and they publish a standard for these files. They are becoming an industry wide tool, mostly for advisors.

you can see more detail here: 2023 Year-End Tax Reporting
Topic Author
retiringwhen
Posts: 4669
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Vanguard Fund Tax Analysis & Efficiency (2018 to 2023)

Post by retiringwhen »

The Vanguard 2023 Data has been updated to include the February 7th revision of the Primary Layout with latest REIT fund values.

See the file here: https://docs.google.com/spreadsheets/d/ ... sp=sharing
Phaedrus
Posts: 67
Joined: Sat Feb 27, 2010 6:31 pm

Re: Vanguard Fund Tax Analysis & Efficiency (2018 to 2023)

Post by Phaedrus »

This is cool. Thank you for making this and sharing!
Topic Author
retiringwhen
Posts: 4669
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Vanguard Fund Tax Analysis & Efficiency (2018 to 2023)

Post by retiringwhen »

Phaedrus wrote: Tue Feb 13, 2024 12:20 am This is cool. Thank you for making this and sharing!
:sharebeer
User avatar
beyou
Posts: 6803
Joined: Sat Feb 27, 2010 2:57 pm
Location: If you can make it there

Re: Vanguard Fund Tax Analysis & Efficiency (2018 to 2023)

Post by beyou »

So how does this methodology compare to the tax cost numbers published by M* across many fund families ?
I realize you can customize to a personal tax bracket and state taxation, but if one wants to compare broadly across any/all funds,
M* seems to tell me at least on a relative basis how to do tax location. Yeah it is not good enough to predict my personal tax obligations, but I have a method to calculate my quarterly estimated tax and worked well for last year.

Looking at the tax drag, I only see equity not fixed income funds, though you seems to have most/all funds on the fund list tab.
From this, seems that low dividend growth oriented US funds are the low tax winners, and as you get into international and value with higher dividends those are the more tax-unfriendly options, which is pretty intuitive. What I wonder is how much impact comes from the estimated qualified vs non-qualified dividend estimates. Based the high percentage of qualified dividend I keep developed markets in my taxable (and also because taxable is most of my holdings so I need to find more asset classes for my taxable accounts than for my IRA).
ofckrupke
Posts: 950
Joined: Mon Jan 10, 2011 1:26 pm

Re: Vanguard Fund Tax Analysis & Efficiency (2018 to 2023)

Post by ofckrupke »

Thank you for this. I've found the MM Optimizer to be a great resource as well. It's hard to overstate the difference between composing and occasionally updating a private worksheet to keep track of a small set of funds of personal interest and the challenges of scope and clarity through self-documentation involved in your published works.

I don't believe that CA exemption is properly implemented, specifically for fund income from CA-issued muni bonds.

California's condition for exemption of interest income from its own municipal bonds embedded in dividends from mutual funds or ETFs is similar to a subset case of its general rule that at all four quarterly accountings, at least 50% of the fund's assets must be CA-exempt at the individual-security level. So for example a fund holding say 40% t-bills and 60% munis, of which latter part a fifth were issued by CA, would satisfy the criterion at 40+(60*0.2) = 52%. If satisfied at all quarterly accountings then the combined income from the t-bills and the CA munis would be CA exempt. But in for example the "2023 1099 Stats" tab, this condition is not being imposed correctly, with obvious consequence for funds like VMLUX, VWIUX, VWLUX which are shown to have nonzero exemption from CA taxation - in proportion to their CA income fraction which is in the 7-9% range, rather than being 100% CA-taxable on grounds of the 50% asset threshold not being met.

n.b. I believe that Minnesota imposes a similar condition.
Topic Author
retiringwhen
Posts: 4669
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Vanguard Fund Tax Analysis & Efficiency (2018 to 2023)

Post by retiringwhen »

ofckrupke wrote: Tue Feb 13, 2024 12:00 pm Thank you for this. I've found the MM Optimizer to be a great resource as well. It's hard to overstate the difference between composing and occasionally updating a private worksheet to keep track of a small set of funds of personal interest and the challenges of scope and clarity through self-documentation involved in your published works.

I don't believe that CA exemption is properly implemented, specifically for fund income from CA-issued muni bonds.

California's condition for exemption of interest income from its own municipal bonds embedded in dividends from mutual funds or ETFs is similar to a subset case of its general rule that at all four quarterly accountings, at least 50% of the fund's assets must be CA-exempt at the individual-security level. So for example a fund holding say 40% t-bills and 60% munis, of which latter part a fifth were issued by CA, would satisfy the criterion at 40+(60*0.2) = 52%. If satisfied at all quarterly accountings then the combined income from the t-bills and the CA munis would be CA exempt. But in for example the "2023 1099 Stats" tab, this condition is not being imposed correctly, with obvious consequence for funds like VMLUX, VWIUX, VWLUX which are shown to have nonzero exemption from CA taxation - in proportion to their CA income fraction which is in the 7-9% range, rather than being 100% CA-taxable on grounds of the 50% asset threshold not being met.

n.b. I believe that Minnesota imposes a similar condition.

Thanks for that feedback. NJ has a similar rule (>80% for the years) and in fact, I apply it manually because the ICI files do not address this unique situation. I think I will have to do the same for CA. MN should actually be applied correctly as MN is marked as not meeting the threshold in the ICI file. This is a very complicated area to get right. I cannot promise a quick fix, but will put it on the backlog for me to address.

See column 10 in the Secondary Layout File.
Last edited by retiringwhen on Tue Feb 13, 2024 12:21 pm, edited 1 time in total.
Topic Author
retiringwhen
Posts: 4669
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Vanguard Fund Tax Analysis & Efficiency (2018 to 2023)

Post by retiringwhen »

beyou wrote: Tue Feb 13, 2024 10:48 am So how does this methodology compare to the tax cost numbers published by M* across many fund families ?
I realize you can customize to a personal tax bracket and state taxation, but if one wants to compare broadly across any/all funds,
M* seems to tell me at least on a relative basis how to do tax location. Yeah it is not good enough to predict my personal tax obligations, but I have a method to calculate my quarterly estimated tax and worked well for last year.

Looking at the tax drag, I only see equity not fixed income funds, though you seems to have most/all funds on the fund list tab.
From this, seems that low dividend growth oriented US funds are the low tax winners, and as you get into international and value with higher dividends those are the more tax-unfriendly options, which is pretty intuitive. What I wonder is how much impact comes from the estimated qualified vs non-qualified dividend estimates. Based the high percentage of qualified dividend I keep developed markets in my taxable (and also because taxable is most of my holdings so I need to find more asset classes for my taxable accounts than for my IRA).
I believe the primary benefit is personal tax rates, with the most important part being some degree of correctness for State taxes (caveat, comment above!)

I also use it alot to understand just how taxation impacts specific funds both for real world decision making and theory/discussion. I made my peace with allocation / location decisions years ago (my funds are stuck where they are without significant tax consequences!) So they really don't factor for me much anymore, but this tool could aid someone who is interested in those questions.
ofckrupke
Posts: 950
Joined: Mon Jan 10, 2011 1:26 pm

Re: Vanguard Fund Tax Analysis & Efficiency (2018 to 2023)

Post by ofckrupke »

retiringwhen wrote: Tue Feb 13, 2024 12:16 pm This is a very complicated area to get right.
CA Franchise Tax Board's instructions for Schedule CA (540) could definitely be clearer on this point for the sake of those filing by hand.
Their Publication 1001 (2022 edition of 7/2023) does a much better job (page 7, mid-page under Taxable Interest Income) of disambiguating CA-exempt from federally-exempt but CA-taxable individual securities in the question of whether the fund asset % threshold is met.
Topic Author
retiringwhen
Posts: 4669
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Vanguard Fund Tax Analysis & Efficiency (2018 to 2023)

Post by retiringwhen »

ofckrupke wrote: Tue Feb 13, 2024 12:48 pm
retiringwhen wrote: Tue Feb 13, 2024 12:16 pm This is a very complicated area to get right.
CA Franchise Tax Board's instructions for Schedule CA (540) could definitely be clearer on this point for the sake of those filing by hand.
Their Publication 1001 (2022 edition of 7/2023) does a much better job (page 7, mid-page under Taxable Interest Income) of disambiguating CA-exempt from federally-exempt but CA-taxable individual securities in the question of whether the fund asset % threshold is met.
I interpret this rule to basically say if the CA Muni % is less than 50%, then the CA-specific munis are not exempt. Since there is a scheme in place to automate the threshold if Column 10 of the Secondary ICI file has a state abbreviation listed in the fund row, then the state's Muni's are NOT exempt.

I decided the most efficient method is to manually put CA in the that cell for the funds with CA-muni % >0 and less then 50%. I have updated all years to represent this model.

Note, this is not unique, I make a similar adjustment to column 9 as the NY,CA,CT exemption rule is not applied correctly either. Even in some years, it is wrong for other states as well.

The NJ rule is unique as it is related to different values (STCG and LTCG for USGO, and NJ Muni income, including CG) and a different percentage (80%) I had to write special logic.

I theory, these calcluations are now correct for CA. (and MN as Vanguard appears to specify their instances correctly).
greybus
Posts: 97
Joined: Mon Mar 25, 2013 10:13 pm

Re: Vanguard Fund Tax Analysis & Efficiency (2018 to 2023)

Post by greybus »

Where are you getting the Vanguard info that is in ICI format? I looked on the investor as well as the advisor sites, and it seems the info is not all grouped in one place in ICI format, and I often can't find prior years. Do you have a link or are you just gathering the info from multiple sources?

edit: nevermind, I found the Vanguard spreadsheet for 2023, although I still cannot find the prior years
https://advisors.vanguard.com/content/d ... Layout.xls


If I understand how you made your Google Sheets, you are copy/pasting from an ICI formatted spreadsheet, which is indeed easier.

I think Wisdomtree puts out their info in ICI format - maybe that's worth adding?

https://www.wisdomtree.com/investments/ ... eport.xlsx
https://www.wisdomtree.com/investments/ ... eport.xlsx
https://www.wisdomtree.com/investments/ ... eport.xlsx
https://www.wisdomtree.com/investments/ ... eport.xlsx

Additional Prior years. Located https://www.wisdomtree.com/investments/ ... 4252E4BC97 under Tax Supplements
Topic Author
retiringwhen
Posts: 4669
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Vanguard Fund Tax Analysis & Efficiency (2018 to 2023)

Post by retiringwhen »

Thanks I will give them a look in a couple days when I have some time. It is great that someone else published them.
greybus
Posts: 97
Joined: Mon Mar 25, 2013 10:13 pm

Re: Vanguard Fund Tax Analysis & Efficiency (2018 to 2023)

Post by greybus »

retiringwhen wrote: Sat Jan 20, 2024 8:17 pm If have access to any fund company ICI reports for any years, I am interested in collecting them and creating a more generic tool.
Simplify ETF also has ICI reports, but only for 2023.

https://www.simplify.us/tax-resource-center
https://www.simplify.us/sites/default/f ... port-.xlsx
https://www.simplify.us/sites/default/f ... eport.xlsx
Last edited by greybus on Sat Feb 17, 2024 10:11 am, edited 1 time in total.
Topic Author
retiringwhen
Posts: 4669
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Vanguard Fund Tax Analysis & Efficiency (2018 to 2023)

Post by retiringwhen »

+1 more!
Post Reply