Taxable Equivalent Yield (TEY)

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
User avatar
Topic Author
Kevin M
Posts: 11285
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Taxable Equivalent Yield (TEY)

Post by Kevin M » Wed May 02, 2018 11:51 am

I thought there would be a Wiki article on this, but couldn't find one, and I'm too lazy to write one myself--posting is easier. Rather than repeat this in various threads where it's relevant, I thought I'd put my thoughts on it in one post.

--------------------------------- EDIT NOTES ---------------------------------
  • May 02, 2018. Original post.
  • Oct 24, 2019. Added formulas for funds that are partially exempt from state tax, such as Federal Money Market fund.
--------------------------------- END EDIT NOTES ---------------------------------

When buying fixed-income securities (Treasuries, in-state municipal bonds, out-of-state munis, corporate bonds, CDs, etc) or bond or money market funds in taxable accounts, you want to compare yields on an taxable-equivalent basis, since each of these may be taxed differently. What matters is the yield you have left to spend after you pay income taxes. Of course inflation is a factor too, but here I'll just discuss the tax equivalence aspect.

You can compare either after tax yield, I'll call that TFY for Tax Free Yield, or equivalent before tax yield, which is called taxable-equivalent yield, or TEY. The convention seems to be to do the latter, probably because most yields we see quoted are taxable yields.

We can derive TEY for various types of securities by starting with their TFYs, setting them equal, then solving for TEY. For the non-itemizing case, which probably will be vast majority starting in 2018:

TFY = TEY * (1 - f - s) = Yt * (1 - f) = Ym * (1 - s)

Where f and s = federal and state marginal tax rates, Yt is Treasury yield, and Ym is out of state muni yield (in state muni yield = TFY, since there are no taxes). You pay federal and state taxes on fully taxable securities (TEY), federal taxes on Treasuries, and state taxes on out of state munis. So your yield for these is reduced by the factors shown in parentheses in the above equation.

Solving this equation for TEY in terms of TFY, Yt and Ym:

In-state-muni: TEY = TFY / (1 - f - s)

Treasury: TEY = Yt * (1-f) / (1 - f - s)

Out-of-state Muni: TEY = Ym * (1 - s) / (1 - f - s)

An intuitive way to think of this is that you first reduce the yield on the Treasury or out of state muni to it's after-tax value, then divide by the factor that you would apply to a fully tax-free security, like an in-state muni.

If you itemize deductions and fully deduct state income tax on Schedule A, the equations are slightly different. The after-tax value of a fully taxable security in this case is TFY = TEY * (1 - f - s + f*s), which can also be written as TFY = TEY * [ ( 1-f) * (1-s) ]. The latter form is more convenient in solving the equations for TEY. Again, setting the TFYs equal:

TFY = TEY * [ ( 1-f) * (1-s) ] = Yt * (1 - f) = Ym * (1 - s)

If you can do simple algebra in your head, you can see by inspection that the (1 - f) and (1 - s) terms in the numerators are cancelled out by the same term in the denominator when solving for TEY in terms of Yt or Ym, and we get:

In-state-muni: TEY = TFY / [ ( 1-f) * (1-s) ]

Treasury: TEY = Yt / (1-s)

Out-of-state Muni: TEY = Ym / (1-f)

These are the forms of the TEY equations that we see most often. I assume this is because usually investors who are concerned with fixed-income with some sort of tax exemption are likely to have itemized deductions and fully deducted state income taxes on Schedule A. However, with the income tax laws in effect for 2018, this is less likely to be the case, so the slightly more complicated equations are more likely to be applicable.

Thoughts, inputs or questions?

---------------- Oct 24, 2019 EDIT: Funds that are partially exempt from state income tax -------------------------------

I didn't include the formulas for a fund that is partially exempt from state tax when I first posted this. This would be the case for anyone who pays state income tax on income from Vanguard Federal Money Market (Fed MM) fund, which had about 78% of income exempt from state income tax for tax year 2018 (U.S. government obligations information: Important tax information for 2018). Since Fed MM is the settlement fund in a Vanguard Brokerage account, this is fund that many people might want to be able to compare to other funds on a taxable-equivalent basis.

These formulas also would be applicable if your state allows a state tax exemption for income from US Government Obligations (USGO) even if less than 50% of the assets were in USGO at each quarter-end for the year, which I think is all states except CA, CT, and NY (I am a CA resident, so I tend to forget about this case). For example, many people would get a partial state tax exemption on Prime MM, which had about 28% of income from USGO in 2018, and about 37% of assets in USGO on Dec 31, 2018 (but not residents of CA, CT or NY).

(Here I'll just derive the formulas assuming no federal deduction for state income tax on marginal income, which is the most common case, either because of the high standard deduction or hitting the $10K deduction limit for SALT.)

For such a fund, the state tax rate on partially state exempt income is s * (1 - se), where s is the marginal state tax rate and se is the state-tax-exempt portion of fund income. For example, for 2018, se was about 78% for Fed MM, so 1-se = 1 - 0.78 = 0.22; i.e., your state would tax about 22% of your Fed MM income. We don't know what se will be for 2019, so I just assume it will be the same as for 2018 in estimating TEY for 2019.

So the after-tax yield (ATY) factor for income that is partially exempt from state tax is:

ATY factor = (1 - f - s * (1-se))

where f = marginal fed tax rate, s = marginal state tax rate, and se = state-exempt percentage of income.

This can also be written as:

ATY factor = (1 - f - s + s * se)

(Note that here I'm using ATY for after-tax yield, while in the original post I used TFY for tax-free yield. They mean the same thing in the formulas here, but I think after-tax yield might be more meaningful to people.)

You multiply the partially-state-tax-exempt fund yield (Ypse) by the ATY factor to get the after tax yield:

ATY = Ypse * (1 - f - s * (1-se))

As derived in the original post, the TEY factor is just the ATY factor divided by (1 - f - s):

TEY factor = (1 - f - s * (1-se)) / (1 - f - s)

And TEY is:

TEY = Ypse * TEY factor = Ypse * (1 - f - s * (1-se)) / (1 - f - s)

Example.

Using se = 0.78 for Fed MM, current compound yield of 1.87%, and my estimated marginal tax rates of 27% Fed and 8% state,

ATY factor = (1 - f - s * (1-se)) = (1 - 0.27 - 0.08 * (1 - 0.78)) = 0.7124.

My estimated compound after-tax yield on Fed MM is 1.87% * 0.7124 = 1.33%

And:

TEY factor = ATF factor / (1 - f - s) = (1 - f - s * (1-se)) / (1 - f - s) = 0.7124 / (1 - 0.27 - 0.08) = 1.096.

My estimated compound TEY for Fed MM is 1.87% * 1.096 = 2.05%

Kevin
Last edited by Kevin M on Thu Oct 24, 2019 5:54 pm, edited 1 time in total.
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

UpperNwGuy
Posts: 3084
Joined: Sun Oct 08, 2017 7:16 pm

Re: Taxable Equivalent Yield (TEY)

Post by UpperNwGuy » Wed May 02, 2018 2:23 pm

I have always had a hard time thinking in terms of taxable equivalent yields. I much prefer comparing after tax yields. There really isn't any more math involved.

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

Re: Taxable Equivalent Yield (TEY)

Post by triceratop » Wed May 02, 2018 2:27 pm

UpperNwGuy wrote:
Wed May 02, 2018 2:23 pm
I have always had a hard time thinking in terms of taxable equivalent yields. I much prefer comparing after tax yields. There really isn't any more math involved.
It's also something you can compare to inflation.

Not sure it matters to me that my before-tax yield on my CD breaks even with inflation.
"To play the stock market is to play musical chairs under the chord progression of a bid-ask spread."

User avatar
Topic Author
Kevin M
Posts: 11285
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Taxable Equivalent Yield (TEY)

Post by Kevin M » Wed May 02, 2018 4:43 pm

triceratop wrote:
Wed May 02, 2018 2:27 pm
UpperNwGuy wrote:
Wed May 02, 2018 2:23 pm
I have always had a hard time thinking in terms of taxable equivalent yields. I much prefer comparing after tax yields. There really isn't any more math involved.
It's also something you can compare to inflation.

Not sure it matters to me that my before-tax yield on my CD breaks even with inflation.
If that works better for you, then do it that way. It's not "not any more math involved", it's probably less math, because it's easier for you to think about it that way--probably true for most people. Note that the equation I used to derive the TEYs is the equation for the after-tax yields! And I started with that exactly because it is easier for people to understand that.

The thing is that expressing yields as TEYs is a widespread convention--when I do a web search on tax free yield, all of the top hits are to tax-equivalent yield calculators or articles--and I just wanted to have a single reference to point people to if they want to understand it.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

Pigeye Brewster
Posts: 401
Joined: Thu Oct 05, 2017 7:33 pm

Re: Taxable Equivalent Yield (TEY)

Post by Pigeye Brewster » Wed May 02, 2018 5:08 pm

Kevin M wrote:
Wed May 02, 2018 11:51 am
I thought there would be a Wiki article on this, but couldn't find one, and I'm too lazy to write one myself--posting is easier. Rather than repeat this in various threads where it's relevant, I thought I'd put my thoughts on it in one post.

When buying fixed-income securities (Treasuries, in-state municipal bonds, out-of-state munis, corporate bonds, CDs, etc) or bond or money market funds in taxable accounts, you want to compare yields on an taxable-equivalent basis, since each of these may be taxed differently. What matters is the yield you have left to spend after you pay income taxes. Of course inflation is a factor too, but here I'll just discuss the tax equivalence aspect.

You can compare either after tax yield, I'll call that TFY for Tax Free Yield, or equivalent before tax yield, which is called taxable-equivalent yield, or TEY. The convention seems to be to do the latter, probably because most yields we see quoted are taxable yields.

We can derive TEY for various types of securities by starting with their TFYs, setting them equal, then solving for TEY. For the non-itemizing case, which probably will be vast majority starting in 2018:

TFY = TEY * (1 - f - s) = Yt * (1 - f) = Ym * (1 - s)

Where f and s = federal and state marginal tax rates, Yt is Treasury yield, and Ym is out of state muni yield (in state muni yield = TFY, since there are no taxes). You pay federal and state taxes on fully taxable securities (TEY), federal taxes on Treasuries, and state taxes on out of state munis. So your yield for these is reduced by the factors shown in parentheses in the above equation.

Solving this equation for TEY in terms of TFY, Yt and Ym:

In-state-muni: TEY = TFY / (1 - f - s)

Treasury: TEY = Yt * (1-f) / (1 - f - s)

Out-of-state Muni: TEY = Ym * (1 - s) / (1 - f - s)

An intuitive way to think of this is that you first reduce the yield on the Treasury or out of state muni to it's after-tax value, then divide by the factor that you would apply to a fully tax-free security, like an in-state muni.

If you itemize deductions and fully deduct state income tax on Schedule A, the equations are slightly different. The after-tax value of a fully taxable security in this case is TFY = TEY * (1 - f - s + f*s), which can also be written as TFY = TEY * [ ( 1-f) * (1-s) ]. The latter form is more convenient in solving the equations for TEY. Again, setting the TFYs equal:

TFY = TEY * [ ( 1-f) * (1-s) ] = Yt * (1 - f) = Ym * (1 - s)

If you can do simple algebra in your head, you can see by inspection that the (1 - f) and (1 - s) terms in the numerators are cancelled out by the same term in the denominator when solving for TEY in terms of Yt or Ym, and we get:

In-state-muni: TEY = TFY / [ ( 1-f) * (1-s) ]

Treasury: TEY = Yt / (1-s)

Out-of-state Muni: TEY = Ym / (1-f)

These are the forms of the TEY equations that we see most often. I assume this is because usually investors who are concerned with fixed-income with some sort of tax exemption are likely to have itemized deductions and fully deducted state income taxes on Schedule A. However, with the income tax laws in effect for 2018, this is less likely to be the case, so the slightly more complicated equations are more likely to be applicable.

Thoughts, inputs or questions?

Kevin
This is an excellent post. Well done. :beer

The only input I have is to add that the TEY calculation for Agency bonds will vary by the issuer. FHLB and FFCB bonds are exempt from state income tax, so one would use the Treasury formula you have shown. The other common Agencies (FNMA, FHLMC, GNMA, etc.) are fully taxable like corporates.

User avatar
Topic Author
Kevin M
Posts: 11285
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Taxable Equivalent Yield (TEY)

Post by Kevin M » Wed May 02, 2018 5:32 pm

Pigeye Brewster wrote:
Wed May 02, 2018 5:08 pm
This is an excellent post. Well done. :beer
Thanks!
The only input I have is to add that the TEY calculation for Agency bonds will vary by the issuer. FHLB and FFCB bonds are exempt from state income tax, so one would use the Treasury formula you have shown. The other common Agencies (FNMA, FHLMC, GNMA, etc.) are fully taxable like corporates.
Sure, you should apply the relevant equation depending on the taxation of the particular fixed-income security. I just used the ones that come up most often in threads I've been involved in lately.

You also can extend the equations to include things like Net Investment Income Tax (NIIT), which I started to do, but decided to leave that for subsequent discussion if anyone wanted to bring it up. It has been brought up in other threads and PMs in which TEY has come up.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
FiveK
Posts: 8210
Joined: Sun Mar 16, 2014 2:43 pm

Re: Taxable Equivalent Yield (TEY)

Post by FiveK » Wed May 02, 2018 5:40 pm

Kevin M wrote:
Wed May 02, 2018 5:32 pm
You also can extend the equations to include things like Net Investment Income Tax (NIIT)....
Would that need to be separate from "f = federal marginal tax rate"?

Your write-up as is seems well done and inclusive of all tax effects, provided one knows "f" (and "s") for one's specific circumstances.

User avatar
Artsdoctor
Posts: 4210
Joined: Thu Jun 28, 2012 3:09 pm
Location: Los Angeles, CA

Re: Taxable Equivalent Yield (TEY)

Post by Artsdoctor » Wed May 02, 2018 6:14 pm

Most would use tax-equivalent yield, or TEY, as you note above. The idea would be to compare apples to apples: when you're looking at a bond yield of a municipal bond of any sort or certain federal government bonds, you'd want to compare those rates to a fully taxable bond.

Your calculations are very detailed, but the most important thing is for the investor to actually know the marginal tax rate for investments. This is often easier said than done. The married couple filing jointly might know that they're now in the 24% marginal bracket but if their AGI is above $250,000, they'll be subject to the 3.8% NIIT. Likewise, retirees may not know marginal investment rates if they're receiving social security and/or if they're subject to IRMAA; they may gain more than realize by having tax-exempt income even if it's factored into their MAGI. Fortunately, fewer people will be subject to the AMT which in the past made calculating marginal rates next to impossible before the end of the year. And lastly, your marginal rate one year may not resemble the marginal rate the next year, so you might want to keep that in mind when buying even intermediate-term bonds--your calculations may change significantly with very little AGI change. All in all, you have to start somewhere, but I wouldn't get too fixated on a few basis points here or there.

User avatar
Topic Author
Kevin M
Posts: 11285
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Taxable Equivalent Yield (TEY)

Post by Kevin M » Wed May 02, 2018 7:28 pm

FiveK wrote:
Wed May 02, 2018 5:40 pm
Kevin M wrote:
Wed May 02, 2018 5:32 pm
You also can extend the equations to include things like Net Investment Income Tax (NIIT)....
Would that need to be separate from "f = federal marginal tax rate"?

Your write-up as is seems well done and inclusive of all tax effects, provided one knows "f" (and "s") for one's specific circumstances.
I think you may be right about that.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
Topic Author
Kevin M
Posts: 11285
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Taxable Equivalent Yield (TEY)

Post by Kevin M » Wed May 02, 2018 7:38 pm

Artsdoctor wrote:
Wed May 02, 2018 6:14 pm
Your calculations are very detailed, but the most important thing is for the investor to actually know the marginal tax rate for investments. This is often easier said than done.<snip>
Agreed. I often mention that my expected marginal federal tax rate is 27% for 2018, and people say, "there is no 27% tax bracket". I then explain that my taxable ordinary income is in the 12% bracket, but that qualified dividends (QD) stacked on top of that spans the 0% and 15% QD/LTCG tax rates, so each marginal dollar of ordinary income is taxed at 12%, and an additional dollar of QD/LTCG is taxed at 15% instead of 0%, for a marginal rate of 12% + 15% = 27%. So 27% is the appropriate federal marginal tax rate to use for TEY calculations in deciding what type of fixed income to use at various maturities.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

MIretired
Posts: 809
Joined: Fri Sep 06, 2013 12:35 pm

Re: Taxable Equivalent Yield (TEY)

Post by MIretired » Wed May 02, 2018 7:46 pm

triceratop wrote:
Wed May 02, 2018 2:27 pm
UpperNwGuy wrote:
Wed May 02, 2018 2:23 pm
I have always had a hard time thinking in terms of taxable equivalent yields. I much prefer comparing after tax yields. There really isn't any more math involved.
It's also something you can compare to inflation.

Not sure it matters to me that my before-tax yield on my CD breaks even with inflation.
I might say that that is good as a real profitability measure. But what about the fairness of the b4 tax yield being a better indicator of expected inflation, to what ever the extent at that maturity.
Edit: Of course that's a separate thing, and easily looked at without converting any yields.

User avatar
Topic Author
Kevin M
Posts: 11285
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Taxable Equivalent Yield (TEY)

Post by Kevin M » Wed May 02, 2018 8:00 pm

Kevin M wrote:
Wed May 02, 2018 7:28 pm
FiveK wrote:
Wed May 02, 2018 5:40 pm
Kevin M wrote:
Wed May 02, 2018 5:32 pm
You also can extend the equations to include things like Net Investment Income Tax (NIIT)....
Would that need to be separate from "f = federal marginal tax rate"?

Your write-up as is seems well done and inclusive of all tax effects, provided one knows "f" (and "s") for one's specific circumstances.
I think you may be right about that.
EDIT: As I discuss in a subsequent reply, if you are paying NIIT, you probably also are paying AMT, which limits the benefit of the state income tax deduction. This along with the limitations on itemizing state and local taxes and other phaseouts makes it unlikely that the subsequent analysis in this reply is correct for taxpayers subject to NIIT.

But I'm not sure for the case where you itemize and fully deduct state income taxes. This is where the +f*s term comes from, and that's because itemized deductions are subtracted on 1040 line 40 before calculating tax on 1040 line 44. NIIT is calculated using Form 8960, and from scanning through it, I don't see that state income tax is deducted anywhere, unless maybe part of it in Part II, but I have no expertise or experience here. The NIIT is reported on Form 1040 line 62, and as far as I can tell, the state tax deduction has no impact on this.

So without 100% certainty, I think you have for fully taxable (TEY):

TFY = TEY * (1 - f - s + f*s - n) = TEY * [ (1-f) * (1-s) - n ]

So for Treasuries:

TFY = Yt * (1-s) = TEY * [ (1-f) * (1-s) - n ]

so,

TEY = Yt * (1-s) / [ (1-f) * (1-s) - n ]

And unless my algebra is failing me, the (1-s) terms do not cancel out as they do if there is no NIIT. Ditto for the (1-f) terms in calculating TEY for munis.

For the case of not itemizing, there is no f*s term, and therefore no simplification due to terms cancelling out (without NIIT), so you could just lump NIIT in with f if you want.

Kevin
Last edited by Kevin M on Thu May 03, 2018 9:44 am, edited 1 time in total.
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
FiveK
Posts: 8210
Joined: Sun Mar 16, 2014 2:43 pm

Re: Taxable Equivalent Yield (TEY)

Post by FiveK » Wed May 02, 2018 8:37 pm

Kevin M wrote:
Wed May 02, 2018 8:00 pm
NIIT is calculated using Form 8960, and from scanning through it, I don't see that state income tax is deducted anywhere, unless maybe part of it in Part II, but I have no expertise or experience here. The NIIT is reported on Form 1040 line 62, and as far as I can tell, the state tax deduction has no impact on this.
Interesting wrinkle.

A portion of state and local income taxes may be deducted from investment income to reach net investment income, and it is indeed Part II (specifically, 9b) where that happens. One way to determine "a portion" is to use the ratio of investment income to adjusted gross income: that same fraction of state and local income taxes may be subtracted, regardless of whether one itemizes.

Don't know how (if at all) the $10K limit on itemized deductions for state and local taxes (that includes property taxes) will apply to the NIIT subtraction for 2018 and beyond.

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

Re: Taxable Equivalent Yield (TEY)

Post by ofckrupke » Wed May 02, 2018 9:43 pm

FiveK wrote:
Wed May 02, 2018 8:37 pm
Don't know how (if at all) the $10K limit on itemized deductions for state and local taxes (that includes property taxes) will apply to the NIIT subtraction for 2018 and beyond.
I don't see how it could. The NIIT is a completely separate tax from the regular tax and AMT and which like AMT depends on some numbers from the regular return and after computation is added to the regular tax near the end of form 1040.
Additionally, the string "net investment income" appears nowhere in the sections of the TCJA pertaining to personal income taxation.

A complication does emerge from the fact that one pays no state tax on treasury interest that nonetheless counts as investment income for NIIT.
In computing the line 9b deduction I subtract state-exempt income from the investment-income-numerator when dividing by AGI; this reduces the deduction, which then increases the tax relative to ignoring the matter, and IMO helps meet the "reasonable method" standard mentioned in the instructions for form 8960.

I am another who computes after-tax yield rather than thinking in TEY. So I just use the straight 3.8% for treasuries and treasury bond funds, and 3.8%*(1-s) for other taxables, for the effective marginal rate associated with the NIIT.

User avatar
Topic Author
Kevin M
Posts: 11285
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Taxable Equivalent Yield (TEY)

Post by Kevin M » Thu May 03, 2018 9:39 am

Another thing that occurred to me is that if you're paying NIIT and state income tax, you're probably also paying AMT, which essentially offsets some or possibly all of the state income tax deduction. At even higher income levels, itemized deductions are partially phased out, further limiting the benefit from a state income tax deduction. And of course the SALT limit of $10K on state and local taxes is likely to hit taxpayers with incomes at this level, again reducing any benefit from the state tax deduction.

When any of these kick in, you are no longer getting the full benefit of the state income tax deduction, and the equations for the itemizing (and fully deducting state taxes) case do not apply. I'd probably just use tax software to determine my marginal tax rates at that point (probably a good idea no matter what).

This is why I qualified using these equations for the itemizing case with "fully deduct state income tax".

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

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

Re: Taxable Equivalent Yield (TEY)

Post by ofckrupke » Thu May 03, 2018 10:19 am

Kevin M wrote:
Thu May 03, 2018 9:39 am
Another thing that occurred to me is that if you're paying NIIT and state income tax, you're probably also paying AMT, which essentially offsets some or possibly all of the state income tax deduction. At even higher income levels, itemized deductions are partially phased out, further limiting the benefit from a state income tax deduction. And of course the SALT limit of $10K on state and local taxes is likely to hit taxpayers with incomes at this level, again reducing any benefit from the state tax deduction.
On the first point, yes, but AMT and NIIT are completely separately calculated taxes, so whether or not is one has nonzero AMT does not affect the NIIT's deduction for state taxation.
And if one has nonzero AMT then the Pease limitation is inconsequential to a TEY calculation.
While Pease and PEP are abated for the duration of TCJA, the set of tax units subject to AMT under TCJA will be much smaller because its exemption phaseout is pushed back to much, much higher income levels.

As for the SALT limitation: yes, if one will cap out then at the margin there's no federal deduction for state taxation so for tax-equivalency of returns it's like being subject to AMT or taking the standard deduction or the case of an investment with state-exempt interest.
But that's for the regular tax, which as per my post above has no interaction with, and no impact on, the deduction for state taxation of investment income within the NIIT calculation.

User avatar
FiveK
Posts: 8210
Joined: Sun Mar 16, 2014 2:43 pm

Re: Taxable Equivalent Yield (TEY)

Post by FiveK » Thu May 03, 2018 11:52 am

Kevin M wrote:
Thu May 03, 2018 9:39 am
I'd probably just use tax software to determine my marginal tax rates at that point (probably a good idea no matter what).
+1

Algebra is great when it works, but tax law functions aren't always well-behaved. ;)

retiringwhen
Posts: 1601
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Taxable Equivalent Yield (TEY)

Post by retiringwhen » Fri May 03, 2019 6:59 am

Kevin M. I just referenced this thread in another Muni post this morning. Has anyone considered putting this into the Wiki? This is very useful info that needs to be referenced regularly as it comes up all the time.

User avatar
House Blend
Posts: 4674
Joined: Fri May 04, 2007 1:02 pm

Re: Taxable Equivalent Yield (TEY)

Post by House Blend » Fri May 03, 2019 10:15 am

UpperNwGuy wrote:
Wed May 02, 2018 2:23 pm
I have always had a hard time thinking in terms of taxable equivalent yields. I much prefer comparing after tax yields. There really isn't any more math involved.
+1. In some sense it is only reframing the issue, but I agree that after tax yield offers a better perspective.

What matters is what you get to keep after taxes, not how much you would need to artificially boost the yield so as to enable fair comparisons in a world without taxes.

User avatar
Topic Author
Kevin M
Posts: 11285
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Taxable Equivalent Yield (TEY)

Post by Kevin M » Fri May 03, 2019 11:50 am

retiringwhen wrote:
Fri May 03, 2019 6:59 am
Kevin M. I just referenced this thread in another Muni post this morning. Has anyone considered putting this into the Wiki? This is very useful info that needs to be referenced regularly as it comes up all the time.
Anyone who has the desire and energy can write or update a Wiki article. I used to do it, but it's too much work, and I don't like "negotiating" with others about the content.

So if you or anyone else wants to take it on, please go for it. As stated in my OP, I looked in the Wiki first, as I expected that there would be an article about something so fundamental.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
Riley15
Posts: 198
Joined: Wed May 11, 2016 9:21 pm

Re: Taxable Equivalent Yield (TEY)

Post by Riley15 » Sun Jul 07, 2019 10:17 pm

Kevin M wrote:
Wed May 02, 2018 11:51 am
I thought there would be a Wiki article on this, but couldn't find one, and I'm too lazy to write one myself--posting is easier. Rather than repeat this in various threads where it's relevant, I thought I'd put my thoughts on it in one post.

When buying fixed-income securities (Treasuries, in-state municipal bonds, out-of-state munis, corporate bonds, CDs, etc) or bond or money market funds in taxable accounts, you want to compare yields on an taxable-equivalent basis, since each of these may be taxed differently. What matters is the yield you have left to spend after you pay income taxes. Of course inflation is a factor too, but here I'll just discuss the tax equivalence aspect.

You can compare either after tax yield, I'll call that TFY for Tax Free Yield, or equivalent before tax yield, which is called taxable-equivalent yield, or TEY. The convention seems to be to do the latter, probably because most yields we see quoted are taxable yields.

We can derive TEY for various types of securities by starting with their TFYs, setting them equal, then solving for TEY. For the non-itemizing case, which probably will be vast majority starting in 2018:

TFY = TEY * (1 - f - s) = Yt * (1 - f) = Ym * (1 - s)

Where f and s = federal and state marginal tax rates, Yt is Treasury yield, and Ym is out of state muni yield (in state muni yield = TFY, since there are no taxes). You pay federal and state taxes on fully taxable securities (TEY), federal taxes on Treasuries, and state taxes on out of state munis. So your yield for these is reduced by the factors shown in parentheses in the above equation.

Solving this equation for TEY in terms of TFY, Yt and Ym:

In-state-muni: TEY = TFY / (1 - f - s)

Treasury: TEY = Yt * (1-f) / (1 - f - s)

Out-of-state Muni: TEY = Ym * (1 - s) / (1 - f - s)

An intuitive way to think of this is that you first reduce the yield on the Treasury or out of state muni to it's after-tax value, then divide by the factor that you would apply to a fully tax-free security, like an in-state muni.

If you itemize deductions and fully deduct state income tax on Schedule A, the equations are slightly different. The after-tax value of a fully taxable security in this case is TFY = TEY * (1 - f - s + f*s), which can also be written as TFY = TEY * [ ( 1-f) * (1-s) ]. The latter form is more convenient in solving the equations for TEY. Again, setting the TFYs equal:

TFY = TEY * [ ( 1-f) * (1-s) ] = Yt * (1 - f) = Ym * (1 - s)

If you can do simple algebra in your head, you can see by inspection that the (1 - f) and (1 - s) terms in the numerators are cancelled out by the same term in the denominator when solving for TEY in terms of Yt or Ym, and we get:

In-state-muni: TEY = TFY / [ ( 1-f) * (1-s) ]

Treasury: TEY = Yt / (1-s)

Out-of-state Muni: TEY = Ym / (1-f)

These are the forms of the TEY equations that we see most often. I assume this is because usually investors who are concerned with fixed-income with some sort of tax exemption are likely to have itemized deductions and fully deducted state income taxes on Schedule A. However, with the income tax laws in effect for 2018, this is less likely to be the case, so the slightly more complicated equations are more likely to be applicable.

Thoughts, inputs or questions?

Kevin
Kevin,

Thanks for the great explanation. But to expand on this to other types investments except bonds, I am trying to determine the comparable TEY between say a Dividend/REIT Funds which are taxed at the Federal Dividend Rate for Qualified Dividends which is less than the income tax rate on Bonds.

So how would be go about calculating a equivalent TEY for a given qualified Dividend yield compared to a Bond or a CD that is fully taxable?

MikeG62
Posts: 2350
Joined: Tue Nov 15, 2016 3:20 pm
Location: New Jersey

Re: Taxable Equivalent Yield (TEY)

Post by MikeG62 » Mon Jul 08, 2019 8:29 am

Great post Kevin. This should be made a sticky at the top of the Investing Theory page.
Real Knowledge Comes Only From Experience

User avatar
Topic Author
Kevin M
Posts: 11285
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Taxable Equivalent Yield (TEY)

Post by Kevin M » Mon Jul 08, 2019 12:58 pm

Riley15 wrote:
Sun Jul 07, 2019 10:17 pm
Kevin,

Thanks for the great explanation. But to expand on this to other types investments except bonds, I am trying to determine the comparable TEY between say a Dividend/REIT Funds which are taxed at the Federal Dividend Rate for Qualified Dividends which is less than the income tax rate on Bonds.

So how would be go about calculating a equivalent TEY for a given qualified Dividend yield compared to a Bond or a CD that is fully taxable?
I'm not sure that would be a meaningful comparison, since a significant component of your expected return on stock fund is the capital return.

A money market fund or individual fixed-income security held to maturity has no capital return component, so as long as you're comparing fixed income of similar risk, the comparison is meaningful. For example, I use TEY mainly to compare different money market funds (e.g., taxable, Treasury, national muni, state muni) and savings accounts, all of which have close to no risk.

A bond fund has a capital return component, but the longer you hold it, the less significance the capital return has. Still, I would think about the capital return component and the associated risk when comparing bond funds to each other or to money market funds, for example.

At any rate, if for whatever reason you want to compare the yield that is delivered solely in the form of qualified dividends to a yield that is taxed at your ordinary marginal tax rates on a taxable equivalent basis, I guess I'd derive it this way:

TFY = TEY * (1 - f - s) = Yq * (1 - fq - sq)

where Yq is the qualified dividend yield, fq = federal marginal tax rate on QD and sq = state marginal tax rate on QD.

Solving for TEY in terms of Yq:

TEY = Yq * (1 - fq - sq) / (1 - f - s)

So you would multiply Yq by the factor (1 - fq - sq) / (1 - f - s) to get this version of TEY.

As an intuitive check, fq < f, and I assume sq <= s, so the numerator would be larger than the denominator, and TEY would be larger than Yq, as expected.

The fq rate probably is 0% or 15%, unless your income is high enough to push your QD/LTCG into the 20% bracket. My state, CA, doesn't offer a privileged rate for QD/LTCG, so sq = s for me.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

kardan
Posts: 65
Joined: Sun Apr 28, 2019 9:56 am

Re: Taxable Equivalent Yield (TEY)

Post by kardan » Mon Jul 08, 2019 2:26 pm

Kevin M wrote:
Wed May 02, 2018 7:38 pm
I often mention that my expected marginal federal tax rate is 27% for 2018, and people say, "there is no 27% tax bracket". I then explain that my taxable ordinary income is in the 12% bracket, but that qualified dividends (QD) stacked on top of that spans the 0% and 15% QD/LTCG tax rates, so each marginal dollar of ordinary income is taxed at 12%, and an additional dollar of QD/LTCG is taxed at 15% instead of 0%, for a marginal rate of 12% + 15% = 27%. So 27% is the appropriate federal marginal tax rate to use for TEY calculations in deciding what type of fixed income to use at various maturities.

Kevin
I believe this comment could be misconstrued by some people. While it may be true for you, it is somewhat of a special case. For it to apply you would have to have QD and/or LTCG that straddle the 0%/15% threshold so that an additional dollar of taxable yield pushed more of your QD/LTCG into the 15% bracket. For many people in the 12% bracket, this will not be the case so their marginal rate will only be 12%. This speaks well to the comments indicating that knowing your marginal rate may be the hardest part of calculating TEY.

prd1982
Posts: 265
Joined: Sun Jan 08, 2017 4:43 pm

Re: Taxable Equivalent Yield (TEY)

Post by prd1982 » Mon Jul 08, 2019 2:46 pm

If I wanted to determine the after tax effect, I would bring up last year's H&R Block tax file, add a fake 1099 for $1,000 and subtract the additional Fed & State tax to determine the after tax amount. This handles the marginal ordinary vs. investment rates, NIIT, AMT, and state issues. I really think a lot of people don't appreciate how useful having a desk top version of their tax prep software can be.

User avatar
Topic Author
Kevin M
Posts: 11285
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Taxable Equivalent Yield (TEY)

Post by Kevin M » Mon Jul 08, 2019 3:24 pm

kardan wrote:
Mon Jul 08, 2019 2:26 pm
Kevin M wrote:
Wed May 02, 2018 7:38 pm
I often mention that my expected marginal federal tax rate is 27% for 2018, and people say, "there is no 27% tax bracket". I then explain that my taxable ordinary income is in the 12% bracket, but that qualified dividends (QD) stacked on top of that spans the 0% and 15% QD/LTCG tax rates, so each marginal dollar of ordinary income is taxed at 12%, and an additional dollar of QD/LTCG is taxed at 15% instead of 0%, for a marginal rate of 12% + 15% = 27%. So 27% is the appropriate federal marginal tax rate to use for TEY calculations in deciding what type of fixed income to use at various maturities.

Kevin
I believe this comment could be misconstrued by some people. While it may be true for you, it is somewhat of a special case. For it to apply you would have to have QD and/or LTCG that straddle the 0%/15% threshold so that an additional dollar of taxable yield pushed more of your QD/LTCG into the 15% bracket. For many people in the 12% bracket, this will not be the case so their marginal rate will only be 12%. This speaks well to the comments indicating that knowing your marginal rate may be the hardest part of calculating TEY.
It could only be misconstrued if not read carefully, as I explained (underlined here) exactly the qualification you mention (also underlined).

I don't know how common it is, but I would expect it to be fairly common for retirees (no earned income, so relatively low, but decent, ordinary income) with significant qualified dividends (and/or LTCG of course). Of course that requires a decent amount of stock holdings in taxable accounts. That is my situation.

The 25% marginal tax rate (10% + 15%) that applied before the most recent tax changes has been mentioned by many others in Bogleheads posts over the years.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
Topic Author
Kevin M
Posts: 11285
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Taxable Equivalent Yield (TEY)

Post by Kevin M » Mon Jul 08, 2019 3:29 pm

prd1982 wrote:
Mon Jul 08, 2019 2:46 pm
If I wanted to determine the after tax effect, I would bring up last year's H&R Block tax file, add a fake 1099 for $1,000 and subtract the additional Fed & State tax to determine the after tax amount. This handles the marginal ordinary vs. investment rates, NIIT, AMT, and state issues. I really think a lot of people don't appreciate how useful having a desk top version of their tax prep software can be.
Yeah, that's the best way to determine your marginal tax rates, as already mentioned upthread:

Kevin M wrote:
Thu May 03, 2018 9:39 am
Another thing that occurred to me is that if you're paying NIIT and state income tax, you're probably also paying AMT, which essentially offsets some or possibly all of the state income tax deduction. At even higher income levels, itemized deductions are partially phased out, further limiting the benefit from a state income tax deduction. And of course the SALT limit of $10K on state and local taxes is likely to hit taxpayers with incomes at this level, again reducing any benefit from the state tax deduction.

When any of these kick in, you are no longer getting the full benefit of the state income tax deduction, and the equations for the itemizing (and fully deducting state taxes) case do not apply. I'd probably just use tax software to determine my marginal tax rates at that point (probably a good idea no matter what).

This is why I qualified using these equations for the itemizing case with "fully deduct state income tax".
Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

prd1982
Posts: 265
Joined: Sun Jan 08, 2017 4:43 pm

Re: Taxable Equivalent Yield (TEY)

Post by prd1982 » Mon Jul 08, 2019 3:41 pm

Kevin M wrote:
Mon Jul 08, 2019 3:29 pm

Yeah, that's the best way to determine your marginal tax rates, as already mentioned upthread:

Kevin
Sorry, missed that. My eyes gazed over while looking at all the math.

User avatar
Topic Author
Kevin M
Posts: 11285
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Taxable Equivalent Yield (TEY)

Post by Kevin M » Mon Jul 08, 2019 6:20 pm

^No problem. It doesn't hurt to repeat good info like that.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
Riley15
Posts: 198
Joined: Wed May 11, 2016 9:21 pm

Re: Taxable Equivalent Yield (TEY)

Post by Riley15 » Wed Jul 31, 2019 4:47 pm

Kevin M wrote:
Mon Jul 08, 2019 12:58 pm
Riley15 wrote:
Sun Jul 07, 2019 10:17 pm
Kevin,

Thanks for the great explanation. But to expand on this to other types investments except bonds, I am trying to determine the comparable TEY between say a Dividend/REIT Funds which are taxed at the Federal Dividend Rate for Qualified Dividends which is less than the income tax rate on Bonds.

So how would be go about calculating a equivalent TEY for a given qualified Dividend yield compared to a Bond or a CD that is fully taxable?
I'm not sure that would be a meaningful comparison, since a significant component of your expected return on stock fund is the capital return.

A money market fund or individual fixed-income security held to maturity has no capital return component, so as long as you're comparing fixed income of similar risk, the comparison is meaningful. For example, I use TEY mainly to compare different money market funds (e.g., taxable, Treasury, national muni, state muni) and savings accounts, all of which have close to no risk.

A bond fund has a capital return component, but the longer you hold it, the less significance the capital return has. Still, I would think about the capital return component and the associated risk when comparing bond funds to each other or to money market funds, for example.

At any rate, if for whatever reason you want to compare the yield that is delivered solely in the form of qualified dividends to a yield that is taxed at your ordinary marginal tax rates on a taxable equivalent basis, I guess I'd derive it this way:

TFY = TEY * (1 - f - s) = Yq * (1 - fq - sq)

where Yq is the qualified dividend yield, fq = federal marginal tax rate on QD and sq = state marginal tax rate on QD.

Solving for TEY in terms of Yq:

TEY = Yq * (1 - fq - sq) / (1 - f - s)

So you would multiply Yq by the factor (1 - fq - sq) / (1 - f - s) to get this version of TEY.

As an intuitive check, fq < f, and I assume sq <= s, so the numerator would be larger than the denominator, and TEY would be larger than Yq, as expected.

The fq rate probably is 0% or 15%, unless your income is high enough to push your QD/LTCG into the 20% bracket. My state, CA, doesn't offer a privileged rate for QD/LTCG, so sq = s for me.

Kevin
Kevin, Thanks. Yes I agree with you that it's not a one to one comparison as comparing money market funds or short term funds that don't have a significant capital return component.

My thought is that some equities like Utilities also behave as intermediate type bonds and move with interest rate changes and generally have stable prices within minimum fluctuations over time. Of course capital return should be considered but generally over longer term it's the dividend yield that dominate overall returns similar to bonds. I guess REITS would be a little more complicated and have higher capital risks, perhaps more on the order of long term bonds.

But understanding the tax efficiency and getting an equivalent TEY for dividend dominated equities helps greatly when evaluating the higher risk compared to more traditional fixed income bonds.

User avatar
Topic Author
Kevin M
Posts: 11285
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Taxable Equivalent Yield (TEY)

Post by Kevin M » Thu Oct 24, 2019 5:55 pm

I updated the OP to include formulas for funds that are partially exempt from state income tax, since this is a very common situation.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

retiringwhen
Posts: 1601
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Taxable Equivalent Yield (TEY)

Post by retiringwhen » Tue Nov 05, 2019 9:11 pm

Kevin, et al,

I have created a Google Sheets spreadsheet that I believe provides a general purpose answer to the TEY question and the question so often asked on BH. What MM should I use?

See this page on my site and the link to the Google sheet. It uses the After-Tax Yield and TEY yield calculations generalized and applied to the four core Vanguard Money Markets (Federal, Prime, Treasury and Muni) plus the 4 state-specific fund (CA, NJ, NY, PA).

The trick is I created a tab and matrix for each state that provides what I believe are the most likely marginal rates. A person simply needs to look up their rates in the table and get an answer. The instructions tab describes the sheet in more detail. The best part is that I incorporated Kevin's functions to actively query the VG site so the rates are always up to date! I only need to update the % of US Gov't Obligations and state-specific info each year as the reports are published.

Comments, corrections, suggestions welcome, especially regarding marginal rates. I took my best stab at coming up with a list.

Vanguard Money Market After-Tax Yield Analyzer

js12337
Posts: 15
Joined: Sat Apr 12, 2014 9:20 pm

Re: Taxable Equivalent Yield (TEY)

Post by js12337 » Tue Nov 05, 2019 10:59 pm

:sharebeer

Thanks for contributing that worksheet!

User avatar
Topic Author
Kevin M
Posts: 11285
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Taxable Equivalent Yield (TEY)

Post by Kevin M » Wed Nov 06, 2019 1:43 pm

retiringwhen wrote:
Tue Nov 05, 2019 9:11 pm
Kevin, et al,

I have created a Google Sheets spreadsheet that I believe provides a general purpose answer to the TEY question and the question so often asked on BH. What MM should I use?
<snip>
Very cool!

The spreadsheet I use lists a bunch of bond and MM funds in the Current sheet, mostly Vanguard but also the main Fidelity MM funds. This sheet shows SEC yield, compound yield for MM funds, TEY, compound TEY for MM funds (for comparing to bank/CU APY), ATY, and compound ATY for MM funds.

The History sheet pulls historical SEC yields for selected Vanguard MM and short-term bond funds, and calculates historical TEY and ATY.

A reference sheet is used to specify the type of tax factor to apply to each fund; e.g., Treasury, Muni-state, Muni. I use "Federal-78%" for Fed MM fund, and variants of that for MM funds that have different percentages of USGO income. If no tax factor type is specified, "Taxable" is implied. Being a CA resident, I haven't generalized the calculations for residents of states that exempt state tax if less than 50% of the fund's assets were in USGO at the end of each quarter; i.e., states other than CA, NY and CT.

There also is a Treasury sheet that pulls historical Treasury yields from treasury.gov (using a script), calculates the expected yield for VUSXX using the 7-day average of the 1m, 2m, and 3m averages of the 1m, 2m, and 3m Treasury yields, for a selected period of time, and compares those historical values to the historical values for VUSXX SEC yield.

By default my marginal tax rates are used, but different rates can be entered temporarily for a specified number of minutes. I did it this way so if I forget to restore my tax rates, the spreadsheet will do it for me after the specified number of minutes has elapsed.

There are several scripts and an external spreadsheet that are used, so it's a bit difficult to share, but I think I've shared bits of it already.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

retiringwhen
Posts: 1601
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Taxable Equivalent Yield (TEY)

Post by retiringwhen » Wed Nov 06, 2019 9:13 pm

Kevin M wrote:
Wed Nov 06, 2019 1:43 pm
There also is a Treasury sheet that pulls historical Treasury yields from treasury.gov (using a script), calculates the expected yield for VUSXX using the 7-day average of the 1m, 2m, and 3m averages of the 1m, 2m, and 3m Treasury yields, for a selected period of time, and compares those historical values to the historical values for VUSXX SEC yield.
Well, I put some of your tools to good use, I hope. I am personally interested in how to represent data for simplicity and ease of use. Sort of a hobby that has arisen in my job, and various pursuits in life. I'll keep tweaking things along the way.

If you are willing to share the Treasury history queries, I would be interested in them. The XML parsing is more than I want to chew on right now, but I could put those queries to good use :beer I have another more general project for analyzing the usual characteristics of a broad range of Bond funds, and historical Alpha over Risk Free (Rx-Rf, I suppose?) calculations would be a good addition.

User avatar
Topic Author
Kevin M
Posts: 11285
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Taxable Equivalent Yield (TEY)

Post by Kevin M » Wed Nov 06, 2019 9:42 pm

retiringwhen wrote:
Wed Nov 06, 2019 9:13 pm
Kevin M wrote:
Wed Nov 06, 2019 1:43 pm
There also is a Treasury sheet that pulls historical Treasury yields from treasury.gov (using a script), calculates the expected yield for VUSXX using the 7-day average of the 1m, 2m, and 3m averages of the 1m, 2m, and 3m Treasury yields, for a selected period of time, and compares those historical values to the historical values for VUSXX SEC yield.
Well, I put some of your tools to good use, I hope. I am personally interested in how to represent data for simplicity and ease of use. Sort of a hobby that has arisen in my job, and various pursuits in life. I'll keep tweaking things along the way.

If you are willing to share the Treasury history queries, I would be interested in them. The XML parsing is more than I want to chew on right now, but I could put those queries to good use :beer I have another more general project for analyzing the usual characteristics of a broad range of Bond funds, and historical Alpha over Risk Free (Rx-Rf, I suppose?) calculations would be a good addition.
You can also get Treasury yields from FRED, either by downloading manually or using the FRED API in a script, but you must get each series (e.g., 1m, 2m, 3m, etc.) individually, and FRED doesn't have the most recent day of yields. Pulling from treasury.gov gets you all maturities in one go, and the most recent day of yields.

Here you go:

Code: Select all

/**
 * Retrieves Treasury yields from treasury.gov.
 * @param {201906} yearOrMonth [optional] YYYY for year, YYYYMM for month, or "all". Defaults to current month.
 * @param {"real"} type [optional] "real" for real yields, missing or any other value for nominal yields. 
 * @customfunction
 */
function treasuryGovYields(yearOrMonth, type) {
  /*
  Base URL for nominal yields:
  https://data.treasury.gov/feed.svc/DailyTreasuryYieldCurveRateData
  Base URL for real yields:
  https://data.treasury.gov/feed.svc/DailyTreasuryRealYieldCurveRateData
  Example URL for month: 
  https://data.treasury.gov/feed.svc/DailyTreasuryYieldCurveRateData?$filter=month(NEW_DATE)%20eq%206%20and%20year(NEW_DATE)%20eq%202019
  */
  var debug = false;
  if (debug) yearOrMonth = 201001;
  
  if (type)
    type =  type.toLowerCase();
  if (type == 'real')
    var url = 'https://data.treasury.gov/feed.svc/DailyTreasuryRealYieldCurveRateData';
  else {
    var type = 'nominal';
    var url = 'https://data.treasury.gov/feed.svc/DailyTreasuryYieldCurveRateData';  // Base URL--retrieves all if no filters.
  }
  
  // Determine year and/or month, and build URL string, with some argument checks.  
  if (!yearOrMonth) {                                   // If no argument 1, default to current month.
    var d = new Date();
    var year = d.getFullYear().toString();
    var month = d.getMonth()+1;                         // Change 0-11 to 1-12.
    month = month.toString();                           // URL works with single digit or 2-digit month.
    
    url += '?$filter=month(NEW_DATE)%20eq%20' + month + '%20and%20year(NEW_DATE)%20eq%20' + year;
  
  } else {                                               // Determine year and/or month from argument 1.
    yearOrMonth = yearOrMonth.toString();
    if (yearOrMonth.length == 4) {                       // Year only: YYYY.
      var year = yearOrMonth;
      url += '?$filter=year(NEW_DATE)%20eq%20' + year;
    } else if (yearOrMonth.length == 6) {                // Year and month: YYYYMM.
      var year = yearOrMonth.substring(0,4);
      var month = yearOrMonth.substring(4,6);
      url += '?$filter=month(NEW_DATE)%20eq%20' + month + '%20and%20year(NEW_DATE)%20eq%20' + year;
    } else if (yearOrMonth.toLowerCase() == 'all') {     // All.
      // URL without parameters works for all.
    } else {
      throw 'Invalid argument 1. Function treasuryGovYields expects argument 1 format of YYYY, YYYYMM or "all".';
    }
  }
  Logger.log(url);
  
  // Get response text and parse xml.
  var responseText = UrlFetchApp.fetch(url).getContentText();
  // Logger.log(responseText);
  var xml = responseText;
  var document = XmlService.parse(xml);
  /* XML entry looks like below (partial). Path is root -> 'entry' -> 'content' -> 'properties' -> data elements.
  <entry>
    <id>http://data.treasury.gov/Feed.svc/DailyTreasuryYieldCurveRateData(3286)</id>
    <title type="text"></title>
    <updated>2019-06-24T14:13:17Z</updated>
    <author>
      <name />
    </author>
    <link rel="edit" title="DailyTreasuryYieldCurveRateDatum" href="DailyTreasuryYieldCurveRateData(3286)" />
    <category term="TreasuryDataWarehouseModel.DailyTreasuryYieldCurveRateDatum" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <content type="application/xml">
      <m:properties>
        <d:Id m:type="Edm.Int32">3286</d:Id>
        <d:NEW_DATE m:type="Edm.DateTime">2010-02-22T00:00:00</d:NEW_DATE>
        <d:BC_1MONTH m:type="Edm.Double">0.059999998658895493</d:BC_1MONTH>
  */

  // Navigate XML to get the data elements.
  var root = document.getRootElement();
  var namespace = root.getNamespace();  // Need namespace to get elements by name.
  var children = document.getRootElement().getChildren('entry', namespace);  // 'entry' is name of the top element of each data elements set.
  // Logger.log(children.length);
  var i, j, content, data, value;
  var results = [];
  for (i = 0; i < children.length; i++) {
    content = children[i].getChild('content', namespace);  // 'content' is name of the grandparent element of each set of data elements.
    // Logger.log(content.getChildren()[0].getName());  // Just curiouse. It is 'properties'.
    data = content.getChildren()[0].getChildren();  // First child of content is parent of data elements.
    var row = [];
    for (j = 1; j < data.length; j++) {  // First data element (j == 0) is ID, which we don't need.
      // Logger.log(data[j].getName());
      value = data[j].getValue();
      if (j == 1) {
        value = new Date(value);
      } else {
        if (debug) Logger.log(value);
        if(value == '')   // Missing values are blank.
          value = 'N/A';  // Displayed as N/A in text view.
        else
          value = +value;
        if (debug) Logger.log(value);
      }
      if (j < (data.length - 1) || type == 'real')  // Last data element (j == data.length) only used for real yields.
        row.push(value);
    }
    results.push(row);
  }
  // if (debug) Logger.log(results);
  return results.sort(function(a, b){return a[0] - b[0]});
}
Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

retiringwhen
Posts: 1601
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Taxable Equivalent Yield (TEY)

Post by retiringwhen » Wed Nov 06, 2019 9:45 pm

Kevin M wrote:
Wed Nov 06, 2019 9:42 pm
Here you go:...
:sharebeer

Post Reply