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: 4915
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: 4915
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: 2023 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
Last edited by retiringwhen on Thu Mar 21, 2024 9:34 pm, edited 1 time in total.
Topic Author
retiringwhen
Posts: 4915
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: 77
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: 4915
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: 7309
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: 977
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: 4915
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: 4915
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: 977
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: 4915
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: 103
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: 4915
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: 103
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: 4915
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!
GammaPoint
Posts: 2676
Joined: Sun Aug 02, 2009 10:25 am
Location: Washington

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

Post by GammaPoint »

This was such a useful spreadsheet. Thanks so much for posting!
Topic Author
retiringwhen
Posts: 4915
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 have updated the SPDR YoY Tax Analysis document. You can find it here: https://docs.google.com/spreadsheets/d/ ... sp=sharing


It may be a bit rough, I have not completely verified the document. State Street has many esoteric funds that have odd characteristics to verify the sheet is working fine. Any feedback is helpful.
Topic Author
retiringwhen
Posts: 4915
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 have added 2023 WisdomTree to the same file that includes SPDR funds. I plan on adding the earlier years too, but after I try Simplify.

Thanks to the poster's above for the links!

I think I will be taking that strategy going forward. I will include all non-Vanguard funds in one file and Vanguard in another, mostly for ease of management. I may actually merge even them in the future, but for now will keep them separate.
Topic Author
retiringwhen
Posts: 4915
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

greybus wrote: Sat Feb 17, 2024 9:38 am I found the Vanguard spreadsheet for 2023, although I still cannot find the prior years
The ICI files have an "expiration date" that I think Vanguard takes as when they should be removed from public access. I think State Street does the same. I found these files about 5 years ago and started a local collection for my use then. For a while Vanguard would have 2-3 years up at a time, but they seemed to have removed all old ones in the past year.
greybus
Posts: 103
Joined: Mon Mar 25, 2013 10:13 pm

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

Post by greybus »

retiringwhen wrote: Thu Mar 21, 2024 9:32 pm I have updated the SPDR YoY Tax Analysis document. You can find it here: https://docs.google.com/spreadsheets/d/ ... sp=sharing


It may be a bit rough, I have not completely verified the document. State Street has many esoteric funds that have odd characteristics to verify the sheet is working fine. Any feedback is helpful.
I didn't look at the whole document but noticed that some of the numbers from the imported ICI seem to be off. For example, SPDW Foreign Tax Credit is 0.058185000 in the Excel sheet, but was transposed as 0.5818500000 in the Google Sheet. This made the ETF seem very tax efficient, such that it had a negative tax drag!
greybus
Posts: 103
Joined: Mon Mar 25, 2013 10:13 pm

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

Post by greybus »

Also, these are not entirely in the ICI format and are not an Excel sheet, but Blackrock (iShares) has these PDFs that appear to be an excerpt of the ICI spreadsheet. Maybe you may find it useful.

https://www.ishares.com/us/literature/t ... tamped.pdf
https://www.ishares.com/us/literature/t ... tamped.pdf
https://www.ishares.com/us/literature/t ... ummary.pdf
https://www.ishares.com/us/literature/t ... ummary.pdf
https://www.ishares.com/us/literature/t ... 067863.pdf
Topic Author
retiringwhen
Posts: 4915
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

greybus wrote: Sat Apr 06, 2024 5:42 pm Also, these are not entirely in the ICI format and are not an Excel sheet, but Blackrock (iShares) has these PDFs that appear to be an excerpt of the ICI spreadsheet. Maybe you may find it useful.

https://www.ishares.com/us/literature/t ... tamped.pdf
https://www.ishares.com/us/literature/t ... tamped.pdf
https://www.ishares.com/us/literature/t ... ummary.pdf
https://www.ishares.com/us/literature/t ... ummary.pdf
https://www.ishares.com/us/literature/t ... 067863.pdf
I will give them a look. The bad news is the secondary layout is not given, so the results will not address many state-specific stuff, but the core dividends, etc. will be included.
Topic Author
retiringwhen
Posts: 4915
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

greybus wrote: Sat Apr 06, 2024 5:36 pm
retiringwhen wrote: Thu Mar 21, 2024 9:32 pm I have updated the SPDR YoY Tax Analysis document. You can find it here: https://docs.google.com/spreadsheets/d/ ... sp=sharing


It may be a bit rough, I have not completely verified the document. State Street has many esoteric funds that have odd characteristics to verify the sheet is working fine. Any feedback is helpful.
I didn't look at the whole document but noticed that some of the numbers from the imported ICI seem to be off. For example, SPDW Foreign Tax Credit is 0.058185000 in the Excel sheet, but was transposed as 0.5818500000 in the Google Sheet. This made the ETF seem very tax efficient, such that it had a negative tax drag!
I saw that too and I was puzzled. Glad you found it was an import isssue. I will have to re-import and figure out what got screwed up. the excel to google sheets import is usually pretty solid.
Topic Author
retiringwhen
Posts: 4915
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

greybus wrote: Sat Apr 06, 2024 5:36 pm I didn't look at the whole document but noticed that some of the numbers from the imported ICI seem to be off. For example, SPDW Foreign Tax Credit is 0.058185000 in the Excel sheet, but was transposed as 0.5818500000 in the Google Sheet. This made the ETF seem very tax efficient, such that it had a negative tax drag!
It was an import error. I have fixed it for 2023 and 2022 SPDR funds. Thanks again for pointing out the error.

I still need to add the Wisdom Tree for earlier years.

Also, the Ishares, looks like I may be able to build something that has at least a Federal Tax view of the funds. That is going to take some work, but at least this year's ishares PDF appears to be parseable by excel.
greybus
Posts: 103
Joined: Mon Mar 25, 2013 10:13 pm

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

Post by greybus »

Thanks for all of the work you spend creating and updating this spreadsheet, it's appreciated!

And in case you were interested, I found 2023 Avantis ICI file. I've been looking for fund companies that are commonly mentioned here so reply if there are any other fund companies you are interested in

https://res.cloudinary.com/americancent ... -2023.xlsx
Topic Author
retiringwhen
Posts: 4915
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

greybus wrote: Sat Apr 06, 2024 9:49 pm Thanks for all of the work you spend creating and updating this spreadsheet, it's appreciated!

And in case you were interested, I found 2023 Avantis ICI file. I've been looking for fund companies that are commonly mentioned here so reply if there are any other fund companies you are interested in

https://res.cloudinary.com/americancent ... -2023.xlsx
Perfect! that is one set that I think will be very helpful!
User avatar
grabiner
Advisory Board
Posts: 35683
Joined: Tue Feb 20, 2007 10:58 pm
Location: Columbia, MD

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

Post by grabiner »

retiringwhen wrote: Tue Feb 13, 2024 12:16 pm 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.
The NJ rule is different. NJ does not tax capital gains on NJ tax-exempt securities. In a "qualified investment fund" invested 80% in these securities, the capital-gains tax is also eligible for proration; thus most or all of a capital gain distributed by a Treasury bond fund will be tax-exempt. However, NJ allows a prorated deduction for dividends regardless of the percentage held by the fund.
Wiki David Grabiner
Topic Author
retiringwhen
Posts: 4915
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've updated the Other funds file to include 2023 iShares and Avantis funds.

The iShares was a messy thing, I did quite a bit of cross-checking, but second eyes are a good thing. It does not have any state specific information, but all other tax calculations look to be supported.

Enjoy!

I will try to get older Wisdom Tree and iShares in in the future.

As a sample for looking at a specific asset class, I created a new tab called SmallCap Study that includes a broad sample of Small Cap ETFs for a head to head comparison.

Link here: https://docs.google.com/spreadsheets/d/ ... 2100616722
jcloudm
Posts: 45
Joined: Sun Jan 03, 2021 5:21 pm

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

Post by jcloudm »

Thanks, this is clearly a well automated approach that is better than the manual approach I took the past 3-4 years, so I've sunsetted my similar version.

I have a couple of thoughts/comments:
  • It would be great to have a version of these combined into one sheet. My reasoning is that one of the best utilities of these sheets is to compare different fund providers, and even having a tab where you could type in 2-5 tickers and have it populate the YoY data would be great.
  • I cannot, for the life of me, understand why Vanguard has regularly worse tax drag on its international funds compared to iShares. It's a multi-year trend of VXUS and VWO being 10-20bps worse than IXUS and IEMG.

    Edit: This post dives very deep into this problem: viewtopic.php?t=425731
VgSince1982-2
Posts: 11
Joined: Thu Aug 03, 2023 9:26 pm

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

Post by VgSince1982-2 »

Hello @retiringwhen,

I'm delighted to find this tool to compare the tax efficiency of funds and ETFs.

When I open and download the Google sheet (pulling it into Excel), I see only ETFs listed on the YoY tab. Is there a filter I need to clear to see the full list? Note that I have some errors when downloading to Excel, so I may have some underlying issues that need to be addressed.

L.
Topic Author
retiringwhen
Posts: 4915
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 note, I believe in the insturctions tab that i default entered just the ETFs to keep the list manageable.

you can update the yellow cells in column A to use mutual fund tickers in YoY Tax Efficiency, but if you do that, you should place them tickers in each of the 20XXTaxEfficiency tabs as well.

The supported ticker list can be found in tab FundList
Topic Author
retiringwhen
Posts: 4915
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

jcloudm wrote: Mon Jun 03, 2024 2:08 pm
  • It would be great to have a version of these combined into one sheet. My reasoning is that one of the best utilities of these sheets is to compare different fund providers, and even having a tab where you could type in 2-5 tickers and have it populate the YoY data would be great.
I plan on merging, but I have another project (adding some ETFs to my MM optimizer project) that is a current priority. I am hoping to come back to this later this summer.

My concerns are that I need to address are performance (that sheet is getting HUGE!) and a method to perform some additional validation on the non-Vanguard sources along with a scheme to validate the results of data merging/conversion. The other fund companies each have data files with some uniqueness I haven't fully automated/resolved.
VgSince1982-2
Posts: 11
Joined: Thu Aug 03, 2023 9:26 pm

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

Post by VgSince1982-2 »

retiringwhen wrote: Mon Jun 10, 2024 11:31 am I note, I believe in the insturctions tab that i default entered just the ETFs to keep the list manageable.

you can update the yellow cells in column A to use mutual fund tickers in YoY Tax Efficiency, but if you do that, you should place them tickers in each of the 20XXTaxEfficiency tabs as well.

The supported ticker list can be found in tab FundList
Thanks for the explanation. After adding in the tickers of interest at the top of each list, I now have the YoY and the 2023 that I'm looking for. Excellent tool!
Post Reply