2017 Relative Tax Efficiency

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
JustinR
Posts: 672
Joined: Tue Apr 27, 2010 11:43 pm

Re: 2017 Relative Tax Efficiency

Post by JustinR » Sun May 27, 2018 6:26 am

Suggestion: Add a row for "After Foreign Tax Credit" after row 17 that is "US taxes" minus "Foreign Tax Paid".

That would give you a comparison of which funds to put in your tax-deferred accounts versus taxable.

Another suggestion: Add a $ in front of the numbers for rows that are actual dollar values (i.e. rows 8, 14, 16, 17). It's difficult to tell what numbers are percentages vs dollars vs regular numbers.

User avatar
triceratop
Moderator
Posts: 5468
Joined: Tue Aug 04, 2015 8:20 pm
Location: la la land

Re: 2017 Relative Tax Efficiency

Post by triceratop » Sun May 27, 2018 1:12 pm

JustinR wrote:
Sun May 27, 2018 6:26 am
Suggestion: Add a row for "After Foreign Tax Credit" after row 17 that is "US taxes" minus "Foreign Tax Paid".

That would give you a comparison of which funds to put in your tax-deferred accounts versus taxable.
This is a fantastic idea. One might even title it "Tax Efficiency", as a percentage of assets. I also agree it should follow row 17, would row 18 work? Since it's so important, perhaps we should make that entire row a boldfaced font?
"To play the stock market is to play musical chairs under the chord progression of a bid-ask spread."

drk
Posts: 690
Joined: Mon Jul 24, 2017 10:33 pm
Location: Seattle

Re: 2017 Relative Tax Efficiency

Post by drk » Sun May 27, 2018 1:58 pm

triceratop wrote:
Sun May 27, 2018 1:12 pm
JustinR wrote:
Sun May 27, 2018 6:26 am
Suggestion: Add a row for "After Foreign Tax Credit" after row 17 that is "US taxes" minus "Foreign Tax Paid".

That would give you a comparison of which funds to put in your tax-deferred accounts versus taxable.
This is a fantastic idea. One might even title it "Tax Efficiency", as a percentage of assets. I also agree it should follow row 17, would row 18 work? Since it's so important, perhaps we should make that entire row a boldfaced font?
It's such useful information that you may also want to italicize it.

JustinR
Posts: 672
Joined: Tue Apr 27, 2010 11:43 pm

Re: 2017 Relative Tax Efficiency

Post by JustinR » Sun May 27, 2018 6:42 pm

triceratop wrote:
Sun May 27, 2018 1:12 pm
JustinR wrote:
Sun May 27, 2018 6:26 am
Suggestion: Add a row for "After Foreign Tax Credit" after row 17 that is "US taxes" minus "Foreign Tax Paid".

That would give you a comparison of which funds to put in your tax-deferred accounts versus taxable.
This is a fantastic idea. One might even title it "Tax Efficiency", as a percentage of assets. I also agree it should follow row 17, would row 18 work? Since it's so important, perhaps we should make that entire row a boldfaced font?
lol I'm dumb. Thanks.

AlohaJoe
Posts: 3559
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

Re: 2017 Relative Tax Efficiency

Post by AlohaJoe » Mon Jun 25, 2018 6:23 am

The spreadsheet seems to have two small errors.

In cell C54 it shows that DLS has a tax efficiency of 0.06; the correct number is 0.02. The rank of DLS is correct but the number is wrong. I believe that 0.06 is actually the tax efficiency of DGS.
In cell C57 it shows that DGS has a tax efficiency of 0.27; the correct number is 0.06. I believe that 0.27 is actually the tax efficiency of DES.

So it looks like every for WisdomTree is shifted over a bit; I took a quick look at the SORT(TRANSPOSE()) but it wasn't immediately obvious why it is showing the wrong numbers.

User avatar
triceratop
Moderator
Posts: 5468
Joined: Tue Aug 04, 2015 8:20 pm
Location: la la land

Re: 2017 Relative Tax Efficiency

Post by triceratop » Mon Jun 25, 2018 11:49 am

AlohaJoe wrote:
Mon Jun 25, 2018 6:23 am
The spreadsheet seems to have two small errors.

In cell C54 it shows that DLS has a tax efficiency of 0.06; the correct number is 0.02. The rank of DLS is correct but the number is wrong. I believe that 0.06 is actually the tax efficiency of DGS.
In cell C57 it shows that DGS has a tax efficiency of 0.27; the correct number is 0.06. I believe that 0.27 is actually the tax efficiency of DES.

So it looks like every for WisdomTree is shifted over a bit; I took a quick look at the SORT(TRANSPOSE()) but it wasn't immediately obvious why it is showing the wrong numbers.
Yeah, fixed. Thanks.

That rank list is such a bother to keep updated when I add new funds.
"To play the stock market is to play musical chairs under the chord progression of a bid-ask spread."

AlohaJoe
Posts: 3559
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

Re: 2017 Relative Tax Efficiency

Post by AlohaJoe » Mon Jun 25, 2018 12:22 pm

triceratop wrote:
Mon Jun 25, 2018 11:49 am
AlohaJoe wrote:
Mon Jun 25, 2018 6:23 am
The spreadsheet seems to have two small errors.

In cell C54 it shows that DLS has a tax efficiency of 0.06; the correct number is 0.02. The rank of DLS is correct but the number is wrong. I believe that 0.06 is actually the tax efficiency of DGS.
In cell C57 it shows that DGS has a tax efficiency of 0.27; the correct number is 0.06. I believe that 0.27 is actually the tax efficiency of DES.

So it looks like every for WisdomTree is shifted over a bit; I took a quick look at the SORT(TRANSPOSE()) but it wasn't immediately obvious why it is showing the wrong numbers.
Yeah, fixed. Thanks.

That rank list is such a bother to keep updated when I add new funds.
Based on your great work, I've created an updated version of the spreadsheet that makes it easier to add new funds: https://goo.gl/K7QMrp

This only supports two types, "International" and "US" but it is hopefully easy to see how to add new types.

User avatar
triceratop
Moderator
Posts: 5468
Joined: Tue Aug 04, 2015 8:20 pm
Location: la la land

Re: 2017 Relative Tax Efficiency

Post by triceratop » Mon Jun 25, 2018 12:25 pm

AlohaJoe wrote:
Mon Jun 25, 2018 12:22 pm
triceratop wrote:
Mon Jun 25, 2018 11:49 am
AlohaJoe wrote:
Mon Jun 25, 2018 6:23 am
The spreadsheet seems to have two small errors.

In cell C54 it shows that DLS has a tax efficiency of 0.06; the correct number is 0.02. The rank of DLS is correct but the number is wrong. I believe that 0.06 is actually the tax efficiency of DGS.
In cell C57 it shows that DGS has a tax efficiency of 0.27; the correct number is 0.06. I believe that 0.27 is actually the tax efficiency of DES.

So it looks like every for WisdomTree is shifted over a bit; I took a quick look at the SORT(TRANSPOSE()) but it wasn't immediately obvious why it is showing the wrong numbers.
Yeah, fixed. Thanks.

That rank list is such a bother to keep updated when I add new funds.
Based on your great work, I've created an updated version of the spreadsheet that makes it easier to add new funds: https://goo.gl/K7QMrp

This only supports two types, "International" and "US" but it is hopefully easy to see how to add new types.
This is really cool. Thanks a bunch!
"To play the stock market is to play musical chairs under the chord progression of a bid-ask spread."

AlohaJoe
Posts: 3559
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

Re: 2017 Relative Tax Efficiency

Post by AlohaJoe » Mon Jun 25, 2018 12:45 pm

triceratop wrote:
Mon Jun 25, 2018 12:25 pm
This is really cool. Thanks a bunch!
Maybe you can help me figure out why EMGF, GSIE, GSLC, and REET do so surprisingly well (well, surprising to me, at least). Maybe after I've slept on it, I'll figure out what I've done wrong :oops:

User avatar
triceratop
Moderator
Posts: 5468
Joined: Tue Aug 04, 2015 8:20 pm
Location: la la land

Re: 2017 Relative Tax Efficiency

Post by triceratop » Mon Jun 25, 2018 12:46 pm

I got almost no sleep the past few days too -- I'll definitely take a deep dive into that puzzle when I'm rested.
"To play the stock market is to play musical chairs under the chord progression of a bid-ask spread."

LocusCoeruleus
Posts: 7
Joined: Wed May 24, 2017 10:00 pm

Re: 2017 Relative Tax Efficiency

Post by LocusCoeruleus » Mon Jun 25, 2018 9:24 pm

@AlohaJoe - Thank you so much for the revision - it is now much easier to add rows for funds we are interested in.

I added a row for VIOV and it came up with a tax efficiency of 0.11 compared to 0.33 for IJS.

VHT, similarly hit a remarkable 0.11 tax efficiency. Do these numbers look right?

EDIT: Looks like the formula changes for the last 5 funds for some reason and is being divided by 3 for the # of shares / $10k.

Code: Select all

=10000/INDEX(GOOGLEFINANCE(A30, "price", DATE(2016, 12, 31)), 2, 2) / 3
If I remove the /3 at the end, the tax efficiency of VIOV (0.34) is now similar to IJS (0.33) and VHT is now 0.32.

Also what's the deal with picking a date of end of 2016 rather than end of 2017?

User avatar
triceratop
Moderator
Posts: 5468
Joined: Tue Aug 04, 2015 8:20 pm
Location: la la land

Re: 2017 Relative Tax Efficiency

Post by triceratop » Mon Jun 25, 2018 10:26 pm

LocusCoeruleus wrote:
Mon Jun 25, 2018 9:24 pm

Also what's the deal with picking a date of end of 2016 rather than end of 2017?
Please read the current thread in its entirety to find an answer to this question.
"To play the stock market is to play musical chairs under the chord progression of a bid-ask spread."

ofckrupke
Posts: 494
Joined: Mon Jan 10, 2011 2:26 pm

Re: 2017 Relative Tax Efficiency

Post by ofckrupke » Mon Jun 25, 2018 11:06 pm

triceratop wrote:
Mon Jun 25, 2018 10:26 pm
LocusCoeruleus wrote:
Mon Jun 25, 2018 9:24 pm

Also what's the deal with picking a date of end of 2016 rather than end of 2017?
Please read the current thread in its entirety to find an answer to this question.
+1. From beginning, to end. :wink:

User avatar
triceratop
Moderator
Posts: 5468
Joined: Tue Aug 04, 2015 8:20 pm
Location: la la land

Re: 2017 Relative Tax Efficiency

Post by triceratop » Mon Jun 25, 2018 11:08 pm

ofckrupke wrote:
Mon Jun 25, 2018 11:06 pm
triceratop wrote:
Mon Jun 25, 2018 10:26 pm
LocusCoeruleus wrote:
Mon Jun 25, 2018 9:24 pm

Also what's the deal with picking a date of end of 2016 rather than end of 2017?
Please read the current thread in its entirety to find an answer to this question.
+1. From beginning, to end. :wink:
:twisted:
"To play the stock market is to play musical chairs under the chord progression of a bid-ask spread."

LocusCoeruleus
Posts: 7
Joined: Wed May 24, 2017 10:00 pm

Re: 2017 Relative Tax Efficiency

Post by LocusCoeruleus » Tue Jun 26, 2018 6:22 pm

ofckrupke wrote:
Mon Jun 25, 2018 11:06 pm
triceratop wrote:
Mon Jun 25, 2018 10:26 pm
LocusCoeruleus wrote:
Mon Jun 25, 2018 9:24 pm

Also what's the deal with picking a date of end of 2016 rather than end of 2017?
Please read the current thread in its entirety to find an answer to this question.
+1. From beginning, to end. :wink:
I prefer time travel :sharebeer

ivk5
Posts: 329
Joined: Thu Sep 22, 2016 9:05 am

Re: 2017 Relative Tax Efficiency

Post by ivk5 » Wed Jun 27, 2018 1:42 am

LocusCoeruleus wrote:
Tue Jun 26, 2018 6:22 pm
ofckrupke wrote:
Mon Jun 25, 2018 11:06 pm
triceratop wrote:
Mon Jun 25, 2018 10:26 pm
LocusCoeruleus wrote:
Mon Jun 25, 2018 9:24 pm

Also what's the deal with picking a date of end of 2016 rather than end of 2017?
Please read the current thread in its entirety to find an answer to this question.
+1. From beginning, to end. :wink:
I prefer time travel :sharebeer
Having made the mistake myself, I’ll suggest this post: viewtopic.php?f=10&t=242137#p3888581

AlohaJoe
Posts: 3559
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

Re: 2017 Relative Tax Efficiency

Post by AlohaJoe » Wed Jun 27, 2018 9:41 am

JustinR wrote:
Tue May 15, 2018 2:12 am
So I tried putting in VNQ myself and I got 1.42. Can someone check my work?

Expense ratio: 0.12
Dividend distribution: =(0.00572+0.17301+0.41627+0.0077+0.23291+0.56039+0.0082+0.24832+0.59747+0.01214+0.36742+0.88404)
Next four rows as: 0

It's a little complicated because the distribution page has, among dividends, "return of capital" and "unrecap sec 1250 gains" which I'm unsure of what to categorize it as.
Return of capital doesn't get taxed. Unrecaptured 1250 gains have their own special tax treatment that the spreadsheet doesn't handle but if you call them short term gains it'll probably be close enough, especially since VNQ doesn't have many of these.

I got 1.10 for VNQ.

AlohaJoe
Posts: 3559
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

Re: 2017 Relative Tax Efficiency

Post by AlohaJoe » Wed Jun 27, 2018 9:44 am

triceratop wrote:
Mon Jun 25, 2018 12:46 pm
I got almost no sleep the past few days too -- I'll definitely take a deep dive into that puzzle when I'm rested.
As LocusCoeruleus's post shows, I didn't realise you had snuck in a "divide by 3" onto DES. I blindly copied that downward, which meant the yield of everything I added was cut by 1/3 :oops: .... which made them all look very, very good from a tax perspective.

I've updated my spreadsheet to fix that. I also added quite a few more ishares funds, since ishares puts together a nice, single PDF with all of the information for all of their funds for this. If Vanguard does something similar, my Googling failed to turn it up. :?

AlohaJoe
Posts: 3559
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

Re: 2017 Relative Tax Efficiency

Post by AlohaJoe » Wed Jun 27, 2018 9:46 am

LocusCoeruleus wrote:
Mon Jun 25, 2018 9:24 pm
EDIT: Looks like the formula changes for the last 5 funds for some reason and is being divided by 3 for the # of shares / $10k.

Code: Select all

=10000/INDEX(GOOGLEFINANCE(A30, "price", DATE(2016, 12, 31)), 2, 2) / 3
If I remove the /3 at the end, the tax efficiency of VIOV (0.34) is now similar to IJS (0.33) and VHT is now 0.32.
In an earlier post it was written: "Thanks to Longtermgrowth for pointing out that DES underwent a 3:1 split in November. The figures have been adjusted to correctly account for this."

I didn't realise that the 3:1 split was accomplished by adding that /3 in that column, so I blindly copied it down. You should remove the /3 for everything except DES. I've fixed my spreadsheet to do that.

LocusCoeruleus
Posts: 7
Joined: Wed May 24, 2017 10:00 pm

Re: 2017 Relative Tax Efficiency

Post by LocusCoeruleus » Wed Jun 27, 2018 8:25 pm

AlohaJoe wrote:
Wed Jun 27, 2018 9:44 am
triceratop wrote:
Mon Jun 25, 2018 12:46 pm
I got almost no sleep the past few days too -- I'll definitely take a deep dive into that puzzle when I'm rested.
As LocusCoeruleus's post shows, I didn't realise you had snuck in a "divide by 3" onto DES. I blindly copied that downward, which meant the yield of everything I added was cut by 1/3 :oops: .... which made them all look very, very good from a tax perspective.

I've updated my spreadsheet to fix that. I also added quite a few more ishares funds, since ishares puts together a nice, single PDF with all of the information for all of their funds for this. If Vanguard does something similar, my Googling failed to turn it up. :?
Thanks again for the revisions to the spreadsheet - certainly makes it a lot easier to add funds. Here are two pages with PDFs that I (and assuming others are) using:
VG QDI:
https://advisors.vanguard.com/VGApp/iip ... endfigures

VG Foreign Tax:

https://advisors.vanguard.com/VGApp/iip ... ?year=2017

Thanks to this spreadsheet, I've settled on US TSM with a SC & Health tilt (VTSAX/VHCIX/IJS) & munis in taxable. International & non-muni bonds go in tax advantaged. I went back & forth quite a bit on international on whether to place in taxable based on this spreadsheet, but found space in tax advantaged space for it where I can get the vanguard institutional intl fund for 0.09 ER. For the taxable funds I am using my chase private client account to autobuy every paycheck VTSAX & VHCIX without having to meet the minimum for the admirals share class. IJS I couldn't find a reasonable correlative mutual fund. Even though RZV tracks the same index but due to some unknown reason to me seems to lag IJS & VIOV (?tracking error).

User avatar
triceratop
Moderator
Posts: 5468
Joined: Tue Aug 04, 2015 8:20 pm
Location: la la land

Re: 2017 Relative Tax Efficiency

Post by triceratop » Wed Jun 27, 2018 8:46 pm

I had no idea a transpose was such a big deal to so many of my paying customers! :D
"To play the stock market is to play musical chairs under the chord progression of a bid-ask spread."

AlohaJoe
Posts: 3559
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

Re: 2017 Relative Tax Efficiency

Post by AlohaJoe » Wed Jun 27, 2018 9:08 pm

LocusCoeruleus wrote:
Wed Jun 27, 2018 8:25 pm
Even though RZV tracks the same index but due to some unknown reason to me seems to lag IJS & VIOV (?tracking error).
RZV doesn't track the same index as IJS & VIOV. One tracks the Pure Value index, the other tracks the Value index.

https://us.spindices.com/indices/equity ... -600-value
https://us.spindices.com/indices/equity ... pure-value

User avatar
triceratop
Moderator
Posts: 5468
Joined: Tue Aug 04, 2015 8:20 pm
Location: la la land

Re: 2017 Relative Tax Efficiency

Post by triceratop » Wed Jun 27, 2018 9:10 pm

IJS I couldn't find a reasonable correlative mutual fund. Even though RZV tracks the same index but due to some unknown reason to me seems to lag IJS & VIOV
Also, RZV isn't a mutual fund so I'm not sure why you would consider it in this context? :confused
"To play the stock market is to play musical chairs under the chord progression of a bid-ask spread."

User avatar
grabiner
Advisory Board
Posts: 22348
Joined: Tue Feb 20, 2007 11:58 pm
Location: Columbia, MD

Re: 2017 Relative Tax Efficiency

Post by grabiner » Wed Jun 27, 2018 9:51 pm

AlohaJoe wrote:
Wed Jun 27, 2018 9:08 pm
LocusCoeruleus wrote:
Wed Jun 27, 2018 8:25 pm
Even though RZV tracks the same index but due to some unknown reason to me seems to lag IJS & VIOV (?tracking error).
RZV doesn't track the same index as IJS & VIOV. One tracks the Pure Value index, the other tracks the Value index.

https://us.spindices.com/indices/equity ... -600-value
https://us.spindices.com/indices/equity ... pure-value
And that is a major difference. RZV is micro-cap and deep-value, so it should outperform traditional small-cap value indexes when small-cap and value outperform. It will underperform slightly in a tax-deferred account and break even in a taxable account if the two indexes do equally well, as it has higher expenses but lower tax costs.
Wiki David Grabiner

JustinR
Posts: 672
Joined: Tue Apr 27, 2010 11:43 pm

Re: 2017 Relative Tax Efficiency

Post by JustinR » Thu Jun 28, 2018 5:45 am

AlohaJoe wrote:
Wed Jun 27, 2018 9:41 am
JustinR wrote:
Tue May 15, 2018 2:12 am
So I tried putting in VNQ myself and I got 1.42. Can someone check my work?

Expense ratio: 0.12
Dividend distribution: =(0.00572+0.17301+0.41627+0.0077+0.23291+0.56039+0.0082+0.24832+0.59747+0.01214+0.36742+0.88404)
Next four rows as: 0

It's a little complicated because the distribution page has, among dividends, "return of capital" and "unrecap sec 1250 gains" which I'm unsure of what to categorize it as.
Return of capital doesn't get taxed. Unrecaptured 1250 gains have their own special tax treatment that the spreadsheet doesn't handle but if you call them short term gains it'll probably be close enough, especially since VNQ doesn't have many of these.

I got 1.10 for VNQ.
Thanks for the info! I get the same thing now.

User avatar
triceratop
Moderator
Posts: 5468
Joined: Tue Aug 04, 2015 8:20 pm
Location: la la land

Re: 2017 Relative Tax Efficiency

Post by triceratop » Mon Jul 23, 2018 8:30 pm

PSA: A number of people have recently formally requested, through Google, access to the Google spreadsheet document recently. Apart from being amusing that I see the real names of people requesting access, this is additionally not necessary; to personalize the spreadsheet make a copy which is consequently user-editable.
"To play the stock market is to play musical chairs under the chord progression of a bid-ask spread."

Post Reply