Trading Treasuries (nominal and TIPS)

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
nonnie
Posts: 3010
Joined: Thu Mar 13, 2008 8:05 pm

Re: Help with trading Treasuries and a few CDs

Post by nonnie »

Kevin M wrote: Fri Aug 05, 2022 1:13 pm
nonnie wrote: Fri Aug 05, 2022 12:40 pm I note with a quick look today there are 18 month and 24 month Treasuries paying a higher YTM than 3.05 and 3.3 respectively. Should I cancel my CD order and buy the Treasuries? Specific issue suggestions?
Thanks for any help, I would really appreciate it.
If you can cancel the CD orders, I would, and then buy Treasuries with higher yields instead.

There are four Treasuries maturing 2/15/2024 (about 18 months) with yields from 3.112% to 3.321%. I would buy the one with the highest yield.

There are four maturing 8/15/2024, with yields from 3.094% to 3.206%, so not higher than 3.3%, but if you want to do it, I would go for the highest yield, as usual.

Liquidity is not a concern if you are 100% sure you will not sell before maturity. Otherwise, I'd give up a few basis points for the extra liquidity of Treasuries.

Kevin
Thanks, I was just about to PM you to ask you to take a look at this post. I will call the bond desk and see what I need to do.

With regard to my first question--anyone, please- is there an easy way to figure out buying something a say 3.025 yield today vs. wait 14 days to buy something with a 3.125 yield? I don't think I'll ever get into buying at this level of detail but I'd like to know if it's possible to calculate.


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

Re: Help with trading Treasuries and a few CDs

Post by Kevin M »

nonnie wrote: Fri Aug 05, 2022 1:29 pm
Kevin M wrote: Fri Aug 05, 2022 1:13 pm
nonnie wrote: Fri Aug 05, 2022 12:40 pm I note with a quick look today there are 18 month and 24 month Treasuries paying a higher YTM than 3.05 and 3.3 respectively. Should I cancel my CD order and buy the Treasuries? Specific issue suggestions?
Thanks for any help, I would really appreciate it.
If you can cancel the CD orders, I would, and then buy Treasuries with higher yields instead.

There are four Treasuries maturing 2/15/2024 (about 18 months) with yields from 3.112% to 3.321%. I would buy the one with the highest yield.

There are four maturing 8/15/2024, with yields from 3.094% to 3.206%, so not higher than 3.3%, but if you want to do it, I would go for the highest yield, as usual.

Liquidity is not a concern if you are 100% sure you will not sell before maturity. Otherwise, I'd give up a few basis points for the extra liquidity of Treasuries.

Kevin
Thanks, I was just about to PM you to ask you to take a look at this post. I will call the bond desk and see what I need to do.

With regard to my first question--anyone, please- is there an easy way to figure out buying something a say 3.025 yield today vs. wait 14 days to buy something with a 3.125 yield? I don't think I'll ever get into buying at this level of detail but I'd like to know if it's possible to calculate.

Nonnie
Yes, it is possible to calculate, but I don't have time right now. Gut feeling is that giving up 10 basis points to get it 14 days earlier is worth it, especially if it's a Treasury that you would buy now.

More importantly, I think I would include at least 50% TIPS in the ladders for inflation protection. If the rep discourages you because of negative yields, he/she does not have a deep enough understanding of fixed income. If you do this in a taxable account, just plan for the taxes that will need to be paid on the inflation adjustments, which you do not receive in cash.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
jeffyscott
Posts: 13438
Joined: Tue Feb 27, 2007 8:12 am

Re: Help with trading Treasuries and a few CDs

Post by jeffyscott »

nonnie wrote: Fri Aug 05, 2022 12:40 pm

My questions--the CDs have settlement dates of 8/10/22 and 8/17/22. In the meantime my funds are sitting in FDZXX earning around 2%. What if I had bought Treasuries with slightly lower yields yesterday with slightly lower YTM. How could I figure the difference between a slightly lower Treasury yield that started earning yesterday instead of waiting 5-12 days to purchase the CDs. Does that make sense?
There's a couple inexact ways you might look at that.

One is that earning about 1% less is about 27 cents less per day per $10,000.

Another would be to look at it as, for example, earning 2% for, let's say it's 7 or 8 days, and then 3% for, let's say it's 2 years. The 7-8 days is about 1% of the total time, so your average rate is about 2.99%, as you'd earn 1% less for 1% of the time.

An exact way would be to figure out the actual YTM for each scenario.
drzzzzz
Posts: 1047
Joined: Sat Sep 22, 2012 9:56 pm

Re: Trading Treasuries (nominal and TIPS)

Post by drzzzzz »

Kevin M wrote: Fri Aug 05, 2022 12:55 pm Took a quick look at bond ETFs, and saw that short-term TIP fund prices were down more than usual lately (meaning yields up). Sure enough, TIPS yields are up nicely today. I bought 10 more each of the 1/15/2023 and the 4/15/2023 in my IRA, and bought 100 of the 1/15/2023 for one of my adult children in her taxable account.

Kevin
hi kevin
i am sure it is somewhere in this very long thread, but can you summarize inflation factor on TIPS and is higher or lower better or irrelevant? Also can you discuss yield to maturity with TIPS and what does a negative yield to maturity mean - how do you integrate the two numbers or are they not related? thanks
nonnie
Posts: 3010
Joined: Thu Mar 13, 2008 8:05 pm

Re: Help with trading Treasuries and a few CDs

Post by nonnie »

jeffyscott wrote: Fri Aug 05, 2022 2:15 pm
nonnie wrote: Fri Aug 05, 2022 12:40 pm

My questions--the CDs have settlement dates of 8/10/22 and 8/17/22. In the meantime my funds are sitting in FDZXX earning around 2%. What if I had bought Treasuries with slightly lower yields yesterday with slightly lower YTM. How could I figure the difference between a slightly lower Treasury yield that started earning yesterday instead of waiting 5-12 days to purchase the CDs. Does that make sense?
There's a couple inexact ways you might look at that.

One is that earning about 1% less is about 27 cents less per day per $10,000.

Another would be to look at it as, for example, earning 2% for, let's say it's 7 or 8 days, and then 3% for, let's say it's 2 years. The 7-8 days is about 1% of the total time, so your average rate is about 2.99%, as you'd earn 1% less for 1% of the time.

An exact way would be to figure out the actual YTM for each scenario.
Thank you. I need to digest this a bit later.
Can you answer my question about picking the highest YTM --which I saw listed today as the 4th bill down on a 2/15/24 at 3.232 with a higher 3rd party price vs the first bill that showed up at 3.125. Should I always pick the first listing vs. highest yield. I'm very confused about this especially the effect of the "third party price."

Is it always pick the highest YTM or always pick the 1st listing for that particular date because of other factors.

thanks to anyone who can help me with this
User avatar
jeffyscott
Posts: 13438
Joined: Tue Feb 27, 2007 8:12 am

Re: Help with trading Treasuries and a few CDs

Post by jeffyscott »

nonnie wrote: Fri Aug 05, 2022 3:31 pm
jeffyscott wrote: Fri Aug 05, 2022 2:15 pm
nonnie wrote: Fri Aug 05, 2022 12:40 pm

My questions--the CDs have settlement dates of 8/10/22 and 8/17/22. In the meantime my funds are sitting in FDZXX earning around 2%. What if I had bought Treasuries with slightly lower yields yesterday with slightly lower YTM. How could I figure the difference between a slightly lower Treasury yield that started earning yesterday instead of waiting 5-12 days to purchase the CDs. Does that make sense?
There's a couple inexact ways you might look at that.

One is that earning about 1% less is about 27 cents less per day per $10,000.

Another would be to look at it as, for example, earning 2% for, let's say it's 7 or 8 days, and then 3% for, let's say it's 2 years. The 7-8 days is about 1% of the total time, so your average rate is about 2.99%, as you'd earn 1% less for 1% of the time.

An exact way would be to figure out the actual YTM for each scenario.
Thank you. I need to digest this a bit later.
Can you answer my question about picking the highest YTM --which I saw listed today as the 4th bill down on a 2/15/24 at 3.232 with a higher 3rd party price vs the first bill that showed up at 3.125. Should I always pick the first listing vs. highest yield. I'm very confused about this especially the effect of the "third party price."

Is it always pick the highest YTM or always pick the 1st listing for that particular date because of other factors.

thanks to anyone who can help me with this
Since I generally don't care much about any liquidity difference or coupon difference, I would most likely take the highest YTM that matures around the time I am looking for.

I have no idea what "third party price" is. A search for "Fidelity third party price", found some links that might explain it? I mostly use Schwab and don't recall ever seeing that there.
evelynmanley
Posts: 1022
Joined: Tue Sep 21, 2010 9:13 am

Re: Help with trading Treasuries and a few CDs

Post by evelynmanley »

nonnie wrote: Fri Aug 05, 2022 3:31 pm
jeffyscott wrote: Fri Aug 05, 2022 2:15 pm
nonnie wrote: Fri Aug 05, 2022 12:40 pm

My questions--the CDs have settlement dates of 8/10/22 and 8/17/22. In the meantime my funds are sitting in FDZXX earning around 2%. What if I had bought Treasuries with slightly lower yields yesterday with slightly lower YTM. How could I figure the difference between a slightly lower Treasury yield that started earning yesterday instead of waiting 5-12 days to purchase the CDs. Does that make sense?
There's a couple inexact ways you might look at that.

One is that earning about 1% less is about 27 cents less per day per $10,000.

Another would be to look at it as, for example, earning 2% for, let's say it's 7 or 8 days, and then 3% for, let's say it's 2 years. The 7-8 days is about 1% of the total time, so your average rate is about 2.99%, as you'd earn 1% less for 1% of the time.

An exact way would be to figure out the actual YTM for each scenario.
Thank you. I need to digest this a bit later.
Can you answer my question about picking the highest YTM --which I saw listed today as the 4th bill down on a 2/15/24 at 3.232 with a higher 3rd party price vs the first bill that showed up at 3.125. Should I always pick the first listing vs. highest yield. I'm very confused about this especially the effect of the "third party price."

Is it always pick the highest YTM or always pick the 1st listing for that particular date because of other factors.

thanks to anyone who can help me with this
Kevin M patiently walked me through buying T-bills recently, and some of your questions are addressed in this thread:

viewtopic.php?p=6662541#p6662541
User avatar
Topic Author
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Trading Treasuries (nominal and TIPS)

Post by Kevin M »

drzzzzz wrote: Fri Aug 05, 2022 2:38 pm
Kevin M wrote: Fri Aug 05, 2022 12:55 pm Took a quick look at bond ETFs, and saw that short-term TIP fund prices were down more than usual lately (meaning yields up). Sure enough, TIPS yields are up nicely today. I bought 10 more each of the 1/15/2023 and the 4/15/2023 in my IRA, and bought 100 of the 1/15/2023 for one of my adult children in her taxable account.

Kevin
hi kevin
i am sure it is somewhere in this very long thread, but can you summarize inflation factor on TIPS and is higher or lower better or irrelevant? Also can you discuss yield to maturity with TIPS and what does a negative yield to maturity mean - how do you integrate the two numbers or are they not related? thanks
Some people prefer a lower index ratio (aka, inflation factor), because there's more chance to earn more than the original real yield if there is enough deflation over the entire term to maturity. This is because you receive no less than the face value at maturity. Others, me included, don't worry much about this, since we think that much deflation is unlikely. I do think there may be slightly higher demand for lower inflation factors, based on small yield differences I've seen.

A negative real yield simply means that the unadjusted price is over par (100), and you will receive par at maturity. So if you paid 101 for a 1-year TIPS, your real yield would be roughly -1%. The actual yield depends on the coupon rate. For a 0.125% coupon, the real yield at 101 for 1 year is -0.87%.

EDIT: The above paragraph would only be true for a zero-coupon TIPS. The price will be 100 when real yield = coupon rate. Take the 1/15/2024 TIPS as an example. The real ask yield today was +0.301%, but the price was 100.464. So although it will mature at 100, the loss in real principal is more than made up by the 0.625% coupon payments, hence the positive real yield.

The real yield has nothing to do with the index ratio; note that I referred to unadjusted price above--the adjusted price is the unadjusted price times the index ratio. A higher index ratio results in a higher adjusted price, but does not affect the real yield. A larger increase in the index ratio will result in a larger nominal return, but does not affect the real return.

The reason I don't mind slightly negative real yields is that the expected return on a nominal Treasury of same maturity also is negative, but with more risk of lower real return due to higher inflation.

Kevin
Last edited by Kevin M on Fri Aug 05, 2022 8:23 pm, edited 1 time in total.
If I make a calculation error, #Cruncher probably will let me know.
nonnie
Posts: 3010
Joined: Thu Mar 13, 2008 8:05 pm

Re: Help with trading Treasuries and a few CDs

Post by nonnie »

evelynmanley wrote: Fri Aug 05, 2022 4:07 pm
nonnie wrote: Fri Aug 05, 2022 3:31 pm
jeffyscott wrote: Fri Aug 05, 2022 2:15 pm
nonnie wrote: Fri Aug 05, 2022 12:40 pm

My questions--the CDs have settlement dates of 8/10/22 and 8/17/22. In the meantime my funds are sitting in FDZXX earning around 2%. What if I had bought Treasuries with slightly lower yields yesterday with slightly lower YTM. How could I figure the difference between a slightly lower Treasury yield that started earning yesterday instead of waiting 5-12 days to purchase the CDs. Does that make sense?
There's a couple inexact ways you might look at that.

One is that earning about 1% less is about 27 cents less per day per $10,000.

Another would be to look at it as, for example, earning 2% for, let's say it's 7 or 8 days, and then 3% for, let's say it's 2 years. The 7-8 days is about 1% of the total time, so your average rate is about 2.99%, as you'd earn 1% less for 1% of the time.

An exact way would be to figure out the actual YTM for each scenario.
Thank you. I need to digest this a bit later.
Can you answer my question about picking the highest YTM --which I saw listed today as the 4th bill down on a 2/15/24 at 3.232 with a higher 3rd party price vs the first bill that showed up at 3.125. Should I always pick the first listing vs. highest yield. I'm very confused about this especially the effect of the "third party price."

Is it always pick the highest YTM or always pick the 1st listing for that particular date because of other factors.

thanks to anyone who can help me with this
Kevin M patiently walked me through buying T-bills recently, and some of your questions are addressed in this thread:

viewtopic.php?p=6662541#p6662541
Thank you so much and thanks to Kevin, I will study the thread for the the upcoming purchases -- if ally Bank ever decides to send our $$ to Fidelity.
I also had forgotten about The Finance Buff guide.
User avatar
Topic Author
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Help with trading Treasuries and a few CDs

Post by Kevin M »

nonnie wrote: Fri Aug 05, 2022 3:31 pm Can you answer my question about picking the highest YTM --which I saw listed today as the 4th bill down on a 2/15/24 at 3.232 with a higher 3rd party price vs the first bill that showed up at 3.125. Should I always pick the first listing vs. highest yield. I'm very confused about this especially the effect of the "third party price."

Is it always pick the highest YTM or always pick the 1st listing for that particular date because of other factors.

thanks to anyone who can help me with this
For what it's worth, I already gave you my opinion on this in my first reply to you. As I said, I would choose the one with the highest yield. I ignore 3rd party price.

The only thing that might be slightly confusing is why different Treasuries of same maturity have different yields. As jeffyscott mentioned, liquidity is one reason. Duration is another, but it doesn't really explain the differences generally (I've checked). Investors have different preferences that affect the yields. Some investors might prefer high coupon issues, because they want the cash flow. Others might prefer zero-coupon issues because they eliminate reinvestment risk. Neither of these is a concern for me, so I just pick the highest yield.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
nonnie
Posts: 3010
Joined: Thu Mar 13, 2008 8:05 pm

Re: Help with trading Treasuries and a few CDs

Post by nonnie »

Kevin M wrote: Fri Aug 05, 2022 5:52 pm
nonnie wrote: Fri Aug 05, 2022 3:31 pm Can you answer my question about picking the highest YTM --which I saw listed today as the 4th bill down on a 2/15/24 at 3.232 with a higher 3rd party price vs the first bill that showed up at 3.125. Should I always pick the first listing vs. highest yield. I'm very confused about this especially the effect of the "third party price."

Is it always pick the highest YTM or always pick the 1st listing for that particular date because of other factors.

thanks to anyone who can help me with this
For what it's worth, I already gave you my opinion on this in my first reply to you. As I said, I would choose the one with the highest yield. I ignore 3rd party price.

The only thing that might be slightly confusing is why different Treasuries of same maturity have different yields. As jeffyscott mentioned, liquidity is one reason. Duration is another, but it doesn't really explain the differences generally (I've checked). Investors have different preferences that affect the yields. Some investors might prefer high coupon issues, because they want the cash flow. Others might prefer zero-coupon issues because they eliminate reinvestment risk. Neither of these is a concern for me, so I just pick the highest yield.

Kevin
I'm very sorry, I wasn't ignoring your answer or looking for a better one. I actually followed your advice from that response, cancelled the CDs and purchased from the two groups you referenced. When I called the bond trading desk to get help/reassurance that I was buying correctly, the person who answered said bond desk was very busy and she could help me. I picked the first bond, she followed along and I made my purchase. When I went to purchase the second bond and asked her the question about YTM and third party price because I am genuinely confused, she told me I was "doing fine" "seemed to know what I was doing" and to proceed.

There are a lot of moving parts, a lot to learn and I'm probably over-worrying about this however a $400K+ purchase is stressful to me. You mentioned above--high-coupon vs. zero-coupon, I'm not there yet to even be thinking about this --especially your statement "zero-coupon issues...eliminate reinvestment risk."

Previously when I've looked at Treasuries of the same maturity, I thought the one with the highest yield was always listed first (maybe I was mistaken about this?). Today when I looked it wasn't the case and so I asked the question.

As a first time Treasury buyer, even though I plan to hold to maturity, it's disconcerting to see the "paper" losses in just one day. I realize things are moving very fast right now.

I really appreciate your help and time, Kevin and I'm sorry for any misunderstanding,
Nonnie
User avatar
jeffyscott
Posts: 13438
Joined: Tue Feb 27, 2007 8:12 am

Re: Help with trading Treasuries and a few CDs

Post by jeffyscott »

nonnie wrote: Sat Aug 06, 2022 12:08 amThere are a lot of moving parts, a lot to learn and I'm probably over-worrying about this however a $400K+ purchase is stressful to me. You mentioned above--high-coupon vs. zero-coupon, I'm not there yet to even be thinking about this --especially your statement "zero-coupon issues...eliminate reinvestment risk."
Let's say you are choosing between a 5 year zero coupon bond and a 5 year bond with a 3% coupon, you are investing $100K and both have a 3% YTM. With the zero coupon bond, the entire $100K is invested for the 5 year period, so there is nothing to reinvest, you know exactly what you will have 5 years from now (about $115,927) and the entire $100K will earn the 3% YTM for the full 5 year period. There is nothing to reinvest during that 5 years, so no "reinvestment risk".

With the 3% coupon bond, you would be getting paid the coupon twice per year, so every 6 months you would get $1500 in cash. When each coupon is paid out, you would need to reinvest it. The yield that you will earn on the reinvested coupon payments was unknown at the time you bought the original bond, that uncertainty is the "reinvestment risk". You do know that you will get $3000 per year and $100K at maturity, so at least $115,000. You might earn more or less than $927 on the reinvested coupons, so you may end up with more or less than you would have with the zero coupon.
Previously when I've looked at Treasuries of the same maturity, I thought the one with the highest yield was always listed first (maybe I was mistaken about this?). Today when I looked it wasn't the case and so I asked the question.
I think it depends on how you did the search. I'm more familiar with Schwab and sometimes it will list search results by maturity and other times by YTM. For example, if I click on the 3 year treasury yield on the overview page, it will order them by YTM, but if I search for 2-4 year treasuries it will order them by maturity date. You can reorder the search by clicking on the column.

(edited to correct return calculations, thanks #cruncher)
User avatar
Topic Author
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Help with trading Treasuries and a few CDs

Post by Kevin M »

nonnie wrote: Sat Aug 06, 2022 12:08 am I'm very sorry, I wasn't ignoring your answer or looking for a better one.
No problem. Just wanted to make sure you saw it.

<snip>
nonnie wrote: Sat Aug 06, 2022 12:08 am There are a lot of moving parts, a lot to learn and I'm probably over-worrying about this however a $400K+ purchase is stressful to me. You mentioned above--high-coupon vs. zero-coupon, I'm not there yet to even be thinking about this --especially your statement "zero-coupon issues...eliminate reinvestment risk."

Previously when I've looked at Treasuries of the same maturity, I thought the one with the highest yield was always listed first (maybe I was mistaken about this?). Today when I looked it wasn't the case and so I asked the question.
jeffyscott gave you a good answer on reinvestment risk.

At Fidelity, bond search results are sorted by maturity by default. You can sort by various columns, including ask yield to maturity, by clicking on the underlined column header. If you've filtered to a single month, this might be reasonable, but you'll probably get the ones maturing toward the end of the month at the top if the yield curve is upward sloping. For example, when I search Treasuries for 08/2023 to 08/2023, then sort by ask yield, the ones maturing 8/31/2023 are at the top. But, the yields for the 8/31 issues are only slightly higher than the highest yield 8/15 issue, so I probably would go for the 8/15, but it really doesn't matter much.
nonnie wrote: Sat Aug 06, 2022 12:08 am As a first time Treasury buyer, even though I plan to hold to maturity, it's disconcerting to see the "paper" losses in just one day. I realize things are moving very fast right now.
If you plan to hold to maturity, you should just ignore the values shown in your holdings, since you will receive what you signed up if you hold to maturity. If yields continue to increase, you probably will see losses, since that's the way bonds work (yield up, price down). Any of us who have been buying Treasuries lately are seeing paper losses.

Have you considered my input on TIPS? I really think it would be desirable to have some inflation protection in your fixed income, with inflation as high as it's been lately (around 9% year over year). Say you buy a 1-year TIPS (e.g., 7/15/2023), for which the yield on Friday was -0.1%; let's just call it 0%. The nominal yield for a 7/15/2023 nominal Treasury was 3.2% on Friday; let's just call it 3% to make the math easy. If inflation averages 6% over the next year, you will earn about 6% nominal on the TIPS, and -3% nominal on the nominal Treasury. If inflation averages about 3%, you will earn about the same nominal return on each.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
evelynmanley
Posts: 1022
Joined: Tue Sep 21, 2010 9:13 am

Re: Help with trading Treasuries and a few CDs

Post by evelynmanley »

jeffyscott wrote: Sat Aug 06, 2022 7:45 am
nonnie wrote: Sat Aug 06, 2022 12:08 amThere are a lot of moving parts, a lot to learn and I'm probably over-worrying about this however a $400K+ purchase is stressful to me. You mentioned above--high-coupon vs. zero-coupon, I'm not there yet to even be thinking about this --especially your statement "zero-coupon issues...eliminate reinvestment risk."
<<Let's say you are choosing between a 5 year zero coupon bond and a 5 year bond with a 3% coupon, you are investing $100K and both have a 3% YTM. With the zero coupon bond, the entire $100K is invested for the 5 year period, so there is nothing to reinvest, you know exactly what you will have 5 years from now (about $115,927) and the entire $100K will earn the 3% YTM for the full 5 year period. There is nothing to reinvest during that 5 years, so no "reinvestment risk".

With the 3% coupon bond, you would be getting paid the coupon twice per year, so every 6 months you would get $1500 in cash. When each coupon is paid out, you would need to reinvest it. The yield that you will earn on the reinvested coupon payments was unknown at the time you bought the original bond, that uncertainty is the "reinvestment risk". You do know that you will get $3000 per year and $100K at maturity, so at least $115,000. You might earn more or less than $927 on the reinvested coupons, so you may end up with more or less than you would have with the zero coupon.<<

Would you mind walking us through your calculations? How did you come up with the $115,927 amount for the 5 year zero coupon bond? I understand the $115, but not the $927.

Thank you!
User avatar
jeffyscott
Posts: 13438
Joined: Tue Feb 27, 2007 8:12 am

Re: Help with trading Treasuries and a few CDs

Post by jeffyscott »

evelynmanley wrote: Sat Aug 06, 2022 2:44 pm Would you mind walking us through your calculations? How did you come up with the $115,927 amount for the 5 year zero coupon bond? I understand the $115, but not the $927.
The YTM is 3%, so return would be 3% per year and with no coupons being paid out, that would compound to $115,927.

$100,000 x 1.035 = $115,927.41
dbr
Posts: 46137
Joined: Sun Mar 04, 2007 8:50 am

Re: Help with trading Treasuries and a few CDs

Post by dbr »

jeffyscott wrote: Sat Aug 06, 2022 3:04 pm
evelynmanley wrote: Sat Aug 06, 2022 2:44 pm Would you mind walking us through your calculations? How did you come up with the $115,927 amount for the 5 year zero coupon bond? I understand the $115, but not the $927.
The YTM is 3%, so return would be 3% per year and with no coupons being paid out, that would compound to $115,927.

$100,000 x 1.035 = $115,927.41
The hidden convention is that YTM is defined in a compound growth model with annual compounding.

Similar things are done with quotes of return on investments generally that really are quotes of compound annual growth rate which by the nature of the model assume all distributions are reinvested. In the case of zeros there are no dividends to reinvest so that fits.
nonnie
Posts: 3010
Joined: Thu Mar 13, 2008 8:05 pm

Re: Help with trading Treasuries and a few CDs

Post by nonnie »

Kevin M wrote: Sat Aug 06, 2022 1:42 pm
nonnie wrote: Sat Aug 06, 2022 12:08 am I'm very sorry, I wasn't ignoring your answer or looking for a better one.
No problem. Just wanted to make sure you saw it.

<snip>
nonnie wrote: Sat Aug 06, 2022 12:08 am There are a lot of moving parts, a lot to learn and I'm probably over-worrying about this however a $400K+ purchase is stressful to me. You mentioned above--high-coupon vs. zero-coupon, I'm not there yet to even be thinking about this --especially your statement "zero-coupon issues...eliminate reinvestment risk."

Previously when I've looked at Treasuries of the same maturity, I thought the one with the highest yield was always listed first (maybe I was mistaken about this?). Today when I looked it wasn't the case and so I asked the question.
jeffyscott gave you a good answer on reinvestment risk.

At Fidelity, bond search results are sorted by maturity by default. You can sort by various columns, including ask yield to maturity, by clicking on the underlined column header. If you've filtered to a single month, this might be reasonable, but you'll probably get the ones maturing toward the end of the month at the top if the yield curve is upward sloping. For example, when I search Treasuries for 08/2023 to 08/2023, then sort by ask yield, the ones maturing 8/31/2023 are at the top. But, the yields for the 8/31 issues are only slightly higher than the highest yield 8/15 issue, so I probably would go for the 8/15, but it really doesn't matter much.
nonnie wrote: Sat Aug 06, 2022 12:08 am As a first time Treasury buyer, even though I plan to hold to maturity, it's disconcerting to see the "paper" losses in just one day. I realize things are moving very fast right now.
If you plan to hold to maturity, you should just ignore the values shown in your holdings, since you will receive what you signed up if you hold to maturity. If yields continue to increase, you probably will see losses, since that's the way bonds work (yield up, price down). Any of us who have been buying Treasuries lately are seeing paper losses.

Have you considered my input on TIPS? I really think it would be desirable to have some inflation protection in your fixed income, with inflation as high as it's been lately (around 9% year over year). Say you buy a 1-year TIPS (e.g., 7/15/2023), for which the yield on Friday was -0.1%; let's just call it 0%. The nominal yield for a 7/15/2023 nominal Treasury was 3.2% on Friday; let's just call it 3% to make the math easy. If inflation averages 6% over the next year, you will earn about 6% nominal on the TIPS, and -3% nominal on the nominal Treasury. If inflation averages about 3%, you will earn about the same nominal return on each.

Kevin
Yep, we're planning on holding to maturity and I KNOW intellectually that's how it works but I need a few days :-). I planned very carefully, that's why it took so long and was complicated especially deciding on maturities. I ended up opening a joint account with a one year ladder for our quarterly expenses and we shall see how that works .

We used Buckingham for part of a year back in 2011 and were invested in a TIPS fund that did very well --can't remember how long we held before Cardiff Park sold it. We've never held individual TIPS. I mostly understand TIPS, thanks for your very clear example. My SO is adamant about no TIPS although I'm still trying to convince him. He is avidly following financial info on inflation projections and prognostications, unusual for him. He's more at the 3% inflation average, I'm just glad he's taking an interest! We're still waiting for retirement accounts to transfer in from Ally. We shall see.

Thanks again for your help and to everyone else also. It sure feels good to get this finished and on my way to understanding bond purchases.

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

Re: Trading Treasuries (nominal and TIPS)

Post by Kevin M »

I thought I'd share some observations about the yields, prices, amount paid, and real and nominal returns so far on some TIPS I've bought lately. Here are records for my purchases of the 1/15/2023 TIPS.

Image

The last row is for my Friday purchase, which settles Monday 8/8. The "yield now" (column S) is -0.73%, and the yield (I paid, column N) is -0.77%. That difference of about 4 basis points is basically the large/small-qty spread, since the Friday quotes represent best prices, which typically are for larger quantities. Unless specified, all prices and yields are ask quotes, which I use to see what the buying opportunities look like compared to previous purchases.

Note that the Principal Amt (total amount minus accrued interest) I paid on Friday (8/8 settlement) is more than I paid for the 7/7 and 7/13 settlements, even though the Friday price was lower (and yield higher). This is because the magnitude of the positive inflation adjustment is larger than that of the negative price change. Let's look at this in more detail for the second row, the 7/7/2022 settlement.

The real G/L % is simply bid price now divided by ask price paid minus 1 = 100.203 / 101.280 - 1 = -1.06%. The last 2 columns are nominal gain/loss in dollars and percent. The G/L % is the value now divided by the principal amount minus 1, so 12,728.29 / 12,712.69 - 1 = 15.60, which is +0.12% gain.

The change in reference CPI between 7/7 (settlement) and 8/8 (settlement for Friday trades) was 1.20% (= 293.20261 / 289.72584 - 1), which is larger in magnitude than the -1.06% real loss, hence the small positive nominal gain.

Note that I use bid price for the gain/loss calculations, since I want the G/L to represent what I would get if I sold.

I calculate Value now (column Z) as bid price times index ratio times quantity divided by 100. I get the index ratios for each TIPS by doing a lookup on the Fidelity quotes, which conveniently include "inflation factor", which is the index ratio for the settlement date. We can also calculate index ratio by dividing the settlement ref CPI by the dated date ref CPI and subtracting 1 for each TIPS, which I've done in my spreadsheet, but it's convenient to just pull from the Fidelity quotes sheet. I download the TIPS quotes every day, so this works for me.

EDIT: See strikeout in previous paragraph. As per my signature, #Cruncher pointed this error out to me in a PM. It's correct in my spreadsheet, but I had the percent return formula stuck in my head when composing this reply.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
evelynmanley
Posts: 1022
Joined: Tue Sep 21, 2010 9:13 am

Re: Trading Treasuries (nominal and TIPS)

Post by evelynmanley »

I purchased the following T-bills on May 20, 2022;

1. CUSIP 912796XP9
Price 99.698
YTM .995
Maturity 8/30/22
(in my SEP, quantity 72k,principal 71,782.88)

2. CUSIP912796T66
Price 99.688
YTM 1.012
Maturity 9/1/22
(in my Roth, quantity 55k, principal 54,828.22)

Since the Treasury rates are so much higher now, would it make sense to sell these and purchase either T-bills or T-notes at a higher rate, even though the maturity dates are only 23 and 25 days away? These were my first and so far only Treasury purchases, so I don't have any experience selling. These are funds I don't need to access for 5-7 years, maybe longer. Is it correct that the interest I'll make on these, if held to maturity, is $388.90?

Thank you in advance for your input!
User avatar
indexfundfan
Posts: 3961
Joined: Tue Feb 20, 2007 10:21 am
Contact:

Re: Trading Treasuries (nominal and TIPS)

Post by indexfundfan »

evelynmanley wrote: Sun Aug 07, 2022 9:18 am I purchased the following T-bills on May 20, 2022;

1. CUSIP 912796XP9
Price 99.698
YTM .995
Maturity 8/30/22
(in my SEP, quantity 72k,principal 71,782.88)

2. CUSIP912796T66
Price 99.688
YTM 1.012
Maturity 9/1/22
(in my Roth, quantity 55k, principal 54,828.22)

Since the Treasury rates are so much higher now, would it make sense to sell these and purchase either T-bills or T-notes at a higher rate, even though the maturity dates are only 23 and 25 days away? These were my first and so far only Treasury purchases, so I don't have any experience selling. These are funds I don't need to access for 5-7 years, maybe longer. Is it correct that the interest I'll make on these, if held to maturity, is $388.90?

Thank you in advance for your input!
Assuming you purchased them at auction, they will mature at par value $100. So yes, your total interest is $388.90.

It seems like your question is whether to reallocate the funds to longer term instruments? I can only say this depends on your own circumstances. Personally, I think the current sweet spot for fixed income is in the one to two year range. You may also consider short term TIPS; prices were quite compelling on Friday.
My signature has been deleted.
evelynmanley
Posts: 1022
Joined: Tue Sep 21, 2010 9:13 am

Re: Trading Treasuries (nominal and TIPS)

Post by evelynmanley »

indexfundfan wrote: Sun Aug 07, 2022 9:39 am
evelynmanley wrote: Sun Aug 07, 2022 9:18 am I purchased the following T-bills on May 20, 2022;

1. CUSIP 912796XP9
Price 99.698
YTM .995
Maturity 8/30/22
(in my SEP, quantity 72k,principal 71,782.88)

2. CUSIP912796T66
Price 99.688
YTM 1.012
Maturity 9/1/22
(in my Roth, quantity 55k, principal 54,828.22)

Since the Treasury rates are so much higher now, would it make sense to sell these and purchase either T-bills or T-notes at a higher rate, even though the maturity dates are only 23 and 25 days away? These were my first and so far only Treasury purchases, so I don't have any experience selling. These are funds I don't need to access for 5-7 years, maybe longer. Is it correct that the interest I'll make on these, if held to maturity, is $388.90?

Thank you in advance for your input!
Assuming you purchased them at auction, they will mature at par value $100. So yes, your total interest is $388.90.

It seems like your question is whether to reallocate the funds to longer term instruments? I can only say this depends on your own circumstances. Personally, I think the current sweet spot for fixed income is in the one to two year range. You may also consider short term TIPS; prices were quite compelling on Friday.
Thank you. Is it a common thing for people to sell existing Treasuries and then buy higher? Is it an easy process at Vanguard? I don't know what to expect. How do you know if or how much money you will lose by selling early?
User avatar
#Cruncher
Posts: 3975
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Trading Treasuries (nominal and TIPS)

Post by #Cruncher »

evelynmanley wrote: Sun Aug 07, 2022 9:18 am I purchased the following T-bills on May 20, 2022;
...
Since the Treasury rates are so much higher now, would it make sense to sell these and purchase either T-bills or T-notes at a higher rate, even though the maturity dates are only 23 and 25 days away?
If you sold these bills now, your price would reflect these higher rates, not the rates when you purchased them. You can't sell them today at a price corresponding to their yield when you purchased them. Row 5 below shows the prices Friday from TreasuryDirect's Fed Invest Historical Prices. Row 7 shows the annual percentage yield (APY) corresponding to those prices.

Code: Select all

Row      Col A      Col B      Col C   Formula in Column B
  1  Settlement  8/08/2022
  2      Mature  8/30/2022  9/01/2022
  3   Principal     72,000     55,000
  4        Days         22         24  =B2-$B1
  5       Price  99.855556  99.845500
  6        Gain     104.00      84.98  =B3*(1-B5/100)
  7         APY      2.43%      2.38%  =(100/B5)^(365/B4)-1
evelynmanley, in same post, wrote:Is it correct that the interest I'll make on these, if held to maturity, is $388.90?
That's what you'd make overall versus the original purchase. Row 6 above shows approximately what you'll make by holding to maturity versus selling on Friday.
User avatar
Topic Author
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Trading Treasuries (nominal and TIPS)

Post by Kevin M »

indexfundfan wrote: Sun Aug 07, 2022 9:39 am Assuming you purchased them at auction, they will mature at par value $100. So yes, your total interest is $388.90.
It doesn't matter whether they were purchased at auction or on the secondary market--the price will be 100 at maturity regardless.
If I make a calculation error, #Cruncher probably will let me know.
User avatar
indexfundfan
Posts: 3961
Joined: Tue Feb 20, 2007 10:21 am
Contact:

Re: Trading Treasuries (nominal and TIPS)

Post by indexfundfan »

Kevin M wrote: Sun Aug 07, 2022 12:48 pm
indexfundfan wrote: Sun Aug 07, 2022 9:39 am Assuming you purchased them at auction, they will mature at par value $100. So yes, your total interest is $388.90.
It doesn't matter whether they were purchased at auction or on the secondary market--the price will be 100 at maturity regardless.
Yes I know. I guess I put my punctuation poorly. The total interest or gain is $388.90 assuming they were bought at auction.
My signature has been deleted.
evelynmanley
Posts: 1022
Joined: Tue Sep 21, 2010 9:13 am

Re: Trading Treasuries (nominal and TIPS)

Post by evelynmanley »

#Cruncher wrote: Sun Aug 07, 2022 10:06 am
evelynmanley wrote: Sun Aug 07, 2022 9:18 am I purchased the following T-bills on May 20, 2022;
...
Since the Treasury rates are so much higher now, would it make sense to sell these and purchase either T-bills or T-notes at a higher rate, even though the maturity dates are only 23 and 25 days away?
If you sold these bills now, your price would reflect these higher rates, not the rates when you purchased them. You can't sell them today at a price corresponding to their yield when you purchased them. Row 5 below shows the prices Friday from TreasuryDirect's Fed Invest Historical Prices. Row 7 shows the annual percentage yield (APY) corresponding to those prices.

Code: Select all

Row      Col A      Col B      Col C   Formula in Column B
  1  Settlement  8/08/2022
  2      Mature  8/30/2022  9/01/2022
  3   Principal     72,000     55,000
  4        Days         22         24  =B2-$B1
  5       Price  99.855556  99.845500
  6        Gain     104.00      84.98  =B3*(1-B5/100)
  7         APY      2.43%      2.38%  =(100/B5)^(365/B4)-1
evelynmanley, in same post, wrote:Is it correct that the interest I'll make on these, if held to maturity, is $388.90?
That's what you'd make overall versus the original purchase. Row 6 above shows approximately what you'll make by holding to maturity versus selling on Friday.
Thank you very much, Cruncher. When buying a T-bill on secondary, to determine yield I use this formula from Kevin (in a different thread):

>>Looking on the secondary market at Vanguard, I see a Tbill maturing 8/11/2022 with a yield of 0.882% and price of 99.773 for the quantity you are interested in (120). So you could buy this today and get this yield and price for sure. The indicative yield is based on secondary market pricing, which of course will change a bit by the auction close on 5/9.

You also know exactly how much you'd earn if you bought today, which is 100 - 99.773 = 0.227 per $100 worth.

So for $100K, you would pay 99,773.00 = 100,000 / 100 * 0.227, and you would earn 227.00.

Doing the same calculation for $120,000, you would earn $272.40.

Kevin<<

What I'm confused about is where the YTM comes into play when we are trying to determine which bill to purchase. It doesn't seem to me to be the same as the yield when using the above formula.
User avatar
Topic Author
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Trading Treasuries (nominal and TIPS)

Post by Kevin M »

evelynmanley wrote: Sun Aug 07, 2022 9:18 am I purchased the following T-bills on May 20, 2022;

1. CUSIP 912796XP9
Price 99.698
YTM .995
Maturity 8/30/22
(in my SEP, quantity 72k,principal 71,782.88)

2. CUSIP912796T66
Price 99.688
YTM 1.012
Maturity 9/1/22
(in my Roth, quantity 55k, principal 54,828.22)

Since the Treasury rates are so much higher now, would it make sense to sell these and purchase either T-bills or T-notes at a higher rate, even though the maturity dates are only 23 and 25 days away? These were my first and so far only Treasury purchases, so I don't have any experience selling. These are funds I don't need to access for 5-7 years, maybe longer. Is it correct that the interest I'll make on these, if held to maturity, is $388.90?

Thank you in advance for your input!
The first thing to understand is that your Treasuries now are at the market yields and prices. So selling and re-buying the same Treasuries will incur a loss due to the bid/ask spread; i.e., you will pay a higher price to buy than you will receive to sell.

So the question I'd ask is do I have a better opportunity to invest the money in something else? Or perhaps my objectives have changed since the purchase; e.g., I've realized I want to invest in a longer maturity Treasury. I would look forward to make this decision; i.e., I would compare to the existing yield to maturity (2.05% for bill1 and 2.04% for bill 2) to other opportunities, regardless of how much I've earned since the original purchase. Still, you want to understand the difference, so we can look at that.

To evaluate the difference between selling now and holding to maturity, we can look at the internal rate of return (IRR) for the two scenarios. IRR will be slightly higher than the original YTM if held to maturity.

To determine how much you'd get for selling you want to use the bid price for the appropriate minimum quantity. I'll use the best bid prices pulled from Fidelity on Friday afternoon; you might have received a slightly lower bid price for your quantities, but the large/small-quantity spreads for T bills are quite small, so this will give you a good idea.

If you know how to use a spreadsheet, this is fairly simple to set up. The results are below. The first of each set of tables is for having sold on Friday, and the second set is for holding to maturity (price = 100).

Image

First, as a sanity check, note that the IRR for the hold to maturity case (cell B9) for the first bill is 1.09%, which is slightly more than the original YTM of 0.995%, as expected. Now, looking at cell B4 shows that the IRR for having sold on Friday is 0.79%, so less than what you'll earn if you hold to maturity. The ask yield for this bill was 2.05% on Friday, so that's the yardstick I'd use to compare alternative investments.

For the second bill, the IRR for Friday sale would have been 0.80%, so lower than the 1.10% if held to maturity. The ask yield for this one was 2.04% on Friday.

The values in columns D and E are the cash flow setup to use the XIRR function to calculate the IRR. For example, the formula in cell B4 is

Code: Select all

=XIRR(E2:E3, D2:D3)
I used your dates and values for the 5/20/2022 settlements. To get the bid price you could do a search on the CUSIP at Fidelity, and get the bid price for your quantity. I had already downloaded the Fidelity 0-6m Treasury quotes into the same spreadsheet, so I did a lookup on that to get the bid prices; e.g., formula in cell F3 is

Code: Select all

=VLOOKUP(B1, '0-6m'!A:H, COLUMN($H$1),0)
Again, you can just enter the bid price manually if you don't understand a formula like this.

The formula for the settle sell cash flow uses the bid price and quantity; e.g., for cell E3 is this:

Code: Select all

=B2*F3*10
Hope this helps,

Kevin
If I make a calculation error, #Cruncher probably will let me know.
evelynmanley
Posts: 1022
Joined: Tue Sep 21, 2010 9:13 am

Re: Trading Treasuries (nominal and TIPS)

Post by evelynmanley »

Wow, Kevin, I can't believe you took the time to make that chart and explain it. You are amazing. I know other people will benefit greatly from your efforts. I'm sorry to say it's way over my head, I'm just a total newbie when it comes to Treasuries, and I've never used spreadsheets either. As I mentioned above, I've been using the formula you gave me in the other thread (viewtopic.php?p=6662541#p6662541) to determine yield on T-bills, but I'm still confused about where YTM comes into play and how it correlates to the yield using your formula. Hope that makes sense. And again, thank you for all your help.
User avatar
Topic Author
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Trading Treasuries (nominal and TIPS)

Post by Kevin M »

indexfundfan wrote: Sun Aug 07, 2022 12:53 pm
Kevin M wrote: Sun Aug 07, 2022 12:48 pm
indexfundfan wrote: Sun Aug 07, 2022 9:39 am Assuming you purchased them at auction, they will mature at par value $100. So yes, your total interest is $388.90.
It doesn't matter whether they were purchased at auction or on the secondary market--the price will be 100 at maturity regardless.
Yes I know. I guess I put my punctuation poorly. The total interest or gain is $388.90 assuming they were bought at auction.
I'm still confused. We know the purchase prices and we know the maturity values, so I don't see the relevance of purchase at auction or on secondary. We actually know it must be secondary, because there were no bills issued on 5/20/2022 (closest was 5/19/2022, and none of those matured on 8/30).
If I make a calculation error, #Cruncher probably will let me know.
User avatar
Topic Author
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Trading Treasuries (nominal and TIPS)

Post by Kevin M »

evelynmanley wrote: Sun Aug 07, 2022 2:18 pm Wow, Kevin, I can't believe you took the time to make that chart and explain it. You are amazing. I know other people will benefit greatly from your efforts. I'm sorry to say it's way over my head, I'm just a total newbie when it comes to Treasuries, and I've never used spreadsheets either. As I mentioned above, I've been using the formula you gave me in the other thread (viewtopic.php?p=6662541#p6662541) to determine yield on T-bills, but I'm still confused about where YTM comes into play and how it correlates to the yield using your formula. Hope that makes sense. And again, thank you for all your help.
When we say "yield" without any qualifier, we mean "yield to maturity (YTM)". When we are buying, we look at the ask yield to maturity. The ask yield is provided to you by the broker, so you don't need to calculate it.

The bottom line for you is that you will earn a higher return if you hold to maturity than if you sell now, and you are earning about 2% on this bills going forward (or whatever the ask yield is when you check). So you would use about 2% as the figure to compare to other investment opportunities in making your hold or sell decision.

Internal rate of return (IRR) shows your actual annualized return based on the cash flows. All you really need to know is that it may be a little different than the yield to maturity.

Consider this simple example. You invest 100 and receive 101 one year (365 days) later. You know your return is 1.00% (1/100). In this case, IRR and YIELD are almost exactly the same:

Code: Select all

IRR	1.0000000000000%
YTM	1.0000000000000%

IRR	0.99999999999999%
YTM	1.00000000000000%
The second set of numbers adds a decimal place, so we can see the tiny difference.

For spreadsheet folks, I use 1 as the frequency parameter in the YIELD function for bills with less than 52 weeks to maturity, since this matches what Treasury publishes for yields, and is what gives us 1.00% for the above situation.

We know bonds mature at 100, not 101, but I chose the numbers to make the math easy, and the YIELD function works with this redemption value.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
nonnie
Posts: 3010
Joined: Thu Mar 13, 2008 8:05 pm

Re: Trading Treasuries (nominal and TIPS)

Post by nonnie »

#Cruncher wrote: Sun Aug 07, 2022 10:06 am
evelynmanley wrote: Sun Aug 07, 2022 9:18 am I purchased the following T-bills on May 20, 2022;
...
Since the Treasury rates are so much higher now, would it make sense to sell these and purchase either T-bills or T-notes at a higher rate, even though the maturity dates are only 23 and 25 days away?
If you sold these bills now, your price would reflect these higher rates, not the rates when you purchased them. You can't sell them today at a price corresponding to their yield when you purchased them. Row 5 below shows the prices Friday from TreasuryDirect's Fed Invest Historical Prices. Row 7 shows the annual percentage yield (APY) corresponding to those prices.

Code: Select all

Row      Col A      Col B      Col C   Formula in Column B
  1  Settlement  8/08/2022
  2      Mature  8/30/2022  9/01/2022
  3   Principal     72,000     55,000
  4        Days         22         24  =B2-$B1
  5       Price  99.855556  99.845500
  6        Gain     104.00      84.98  =B3*(1-B5/100)
  7         APY      2.43%      2.38%  =(100/B5)^(365/B4)-1
evelynmanley, in same post, wrote:Is it correct that the interest I'll make on these, if held to maturity, is $388.90?
That's what you'd make overall versus the original purchase. Row 6 above shows approximately what you'll make by holding to maturity versus selling on Friday.
I'm a new Treasury buyer also and trying to understand, so I'm jumping in to Evelyn's thread. I don't understand what you've said above.( I understand the difference between selling now vs maturity date proceeds, I think) I thought if she held to maturity she'd earn $388.90 as she said but then you added "That's what you'd make overall versus the original purchase. I don't understand the "overall" part and I know I'm missing something in the comparisons.

Then you say "Row 6 above shows approximately what you'll make by holding to maturity versus selling on Friday." Here's where I'm confused.
If held to maturity, $388.90 would be earned, that doesn't change. What does Row 6 "Gain--$188.68" represent--what would have been gained by selling on Friday vs held to maturity or some sort of other comparison/difference?
Am I hopelessly confused?

Thanks,
Nonnie
MisterMister
Posts: 526
Joined: Thu Nov 01, 2018 9:50 pm

Re: Trading Treasuries (nominal and TIPS)

Post by MisterMister »

nonnie wrote: Sun Aug 07, 2022 3:07 pm
#Cruncher wrote: Sun Aug 07, 2022 10:06 am
evelynmanley wrote: Sun Aug 07, 2022 9:18 am I purchased the following T-bills on May 20, 2022;
...
Since the Treasury rates are so much higher now, would it make sense to sell these and purchase either T-bills or T-notes at a higher rate, even though the maturity dates are only 23 and 25 days away?
If you sold these bills now, your price would reflect these higher rates, not the rates when you purchased them. You can't sell them today at a price corresponding to their yield when you purchased them. Row 5 below shows the prices Friday from TreasuryDirect's Fed Invest Historical Prices. Row 7 shows the annual percentage yield (APY) corresponding to those prices.

Code: Select all

Row      Col A      Col B      Col C   Formula in Column B
  1  Settlement  8/08/2022
  2      Mature  8/30/2022  9/01/2022
  3   Principal     72,000     55,000
  4        Days         22         24  =B2-$B1
  5       Price  99.855556  99.845500
  6        Gain     104.00      84.98  =B3*(1-B5/100)
  7         APY      2.43%      2.38%  =(100/B5)^(365/B4)-1
evelynmanley, in same post, wrote:Is it correct that the interest I'll make on these, if held to maturity, is $388.90?
That's what you'd make overall versus the original purchase. Row 6 above shows approximately what you'll make by holding to maturity versus selling on Friday.
I'm a new Treasury buyer also and trying to understand, so I'm jumping in to Evelyn's thread. I don't understand what you've said above.( I understand the difference between selling now vs maturity date proceeds, I think) I thought if she held to maturity she'd earn $388.90 as she said but then you added "That's what you'd make overall versus the original purchase. I don't understand the "overall" part and I know I'm missing something in the comparisons.

Then you say "Row 6 above shows approximately what you'll make by holding to maturity versus selling on Friday." Here's where I'm confused.
If held to maturity, $388.90 would be earned, that doesn't change. What does Row 6 "Gain--$188.68" represent--what would have been gained by selling on Friday vs held to maturity or some sort of other comparison/difference?
Am I hopelessly confused?

Thanks,
Nonnie
$188.68 is how much more is earned by holding to the original term versus selling early. Likewise, it is how much evelyn will lose (vs the original maturity) by selling early. So she would lose $188.68 of the $388.90 in the early sale. Of course she can reinvest for the remaining term but that would not be beneficial for such a short term vs holding to term. She would need to go to a longer term.

EDIT: With short-term T-Bills, she would need about 2.4% (#Crunchers results) to make up the difference. But right now yields for securities maturing around 9/1/2022 are only about 2%. So not a good trade, though short-term TIPs are an option that's already been mentioned (still a longer term, of course).
Last edited by MisterMister on Sun Aug 07, 2022 3:28 pm, edited 3 times in total.
nonnie
Posts: 3010
Joined: Thu Mar 13, 2008 8:05 pm

Re: Trading Treasuries (nominal and TIPS)

Post by nonnie »

Kevin M wrote: Sun Aug 07, 2022 2:06 pm
evelynmanley wrote: Sun Aug 07, 2022 9:18 am I purchased the following T-bills on May 20, 2022;

1. CUSIP 912796XP9
Price 99.698
YTM .995
Maturity 8/30/22
(in my SEP, quantity 72k,principal 71,782.88)

2. CUSIP912796T66
Price 99.688
YTM 1.012
Maturity 9/1/22
(in my Roth, quantity 55k, principal 54,828.22)

Since the Treasury rates are so much higher now, would it make sense to sell these and purchase either T-bills or T-notes at a higher rate, even though the maturity dates are only 23 and 25 days away? These were my first and so far only Treasury purchases, so I don't have any experience selling. These are funds I don't need to access for 5-7 years, maybe longer. Is it correct that the interest I'll make on these, if held to maturity, is $388.90?

Thank you in advance for your input!
The first thing to understand is that your Treasuries now are at the market yields and prices. So selling and re-buying the same Treasuries will incur a loss due to the bid/ask spread; i.e., you will pay a higher price to buy than you will receive to sell.

So the question I'd ask is do I have a better opportunity to invest the money in something else? Or perhaps my objectives have changed since the purchase; e.g., I've realized I want to invest in a longer maturity Treasury. I would look forward to make this decision; i.e., I would compare to the existing yield to maturity (2.05% for bill1 and 2.04% for bill 2) to other opportunities, regardless of how much I've earned since the original purchase. Still, you want to understand the difference, so we can look at that.

To evaluate the difference between selling now and holding to maturity, we can look at the internal rate of return (IRR) for the two scenarios. IRR will be slightly higher than the original YTM if held to maturity.

To determine how much you'd get for selling you want to use the bid price for the appropriate minimum quantity. I'll use the best bid prices pulled from Fidelity on Friday afternoon; you might have received a slightly lower bid price for your quantities, but the large/small-quantity spreads for T bills are quite small, so this will give you a good idea.

If you know how to use a spreadsheet, this is fairly simple to set up. The results are below. The first of each set of tables is for having sold on Friday, and the second set is for holding to maturity (price = 100).

Image

First, as a sanity check, note that the IRR for the hold to maturity case (cell B9) for the first bill is 1.09%, which is slightly more than the original YTM of 0.995%, as expected. Now, looking at cell B4 shows that the IRR for having sold on Friday is 0.79%, so less than what you'll earn if you hold to maturity. The ask yield for this bill was 2.05% on Friday, so that's the yardstick I'd use to compare alternative investments.

For the second bill, the IRR for Friday sale would have been 0.80%, so lower than the 1.10% if held to maturity. The ask yield for this one was 2.04% on Friday.

The values in columns D and E are the cash flow setup to use the XIRR function to calculate the IRR. For example, the formula in cell B4 is

Code: Select all

=XIRR(E2:E3, D2:D3)
I used your dates and values for the 5/20/2022 settlements. To get the bid price you could do a search on the CUSIP at Fidelity, and get the bid price for your quantity. I had already downloaded the Fidelity 0-6m Treasury quotes into the same spreadsheet, so I did a lookup on that to get the bid prices; e.g., formula in cell F3 is

Code: Select all

=VLOOKUP(B1, '0-6m'!A:H, COLUMN($H$1),0)
Again, you can just enter the bid price manually if you don't understand a formula like this.

The formula for the settle sell cash flow uses the bid price and quantity; e.g., for cell E3 is this:

Code: Select all

=B2*F3*10
Hope this helps,

Kevin
WOW, this is so great although I think I'm getting ahead of myself in knowledge I need to know. I haven't used the LOOKUP feature for a dozen years but I think I'm going to need it for a spreadsheet to track my Treasuries. For the future, I would like the ability to have a spreadsheet that could do calculations similar to this. In other words, take a Treasury I currently hold and figure out if it makes sense to redeem early and purchase CUSIP X-- could be 1-2-3+ months in the future. Here's where my head gets fuzzy--could your spreadsheet be adapted by using any CUSIP on Line 1 and comparing then on Line 6 entering one I currently hold figure out whether it is advantageous to sell prior to maturity?

Thanks much.
nonnie
Posts: 3010
Joined: Thu Mar 13, 2008 8:05 pm

Re: Trading Treasuries (nominal and TIPS)

Post by nonnie »

I would like suggestions on the best way to track Treasury purchases (made at Fidelity). I'm sure there's an easy spreadsheet most folks use. I have a dozen Treasuries recently purchased to track. What are the essential things that should be tracked for each Treasury? I'm using purchase, symbol, description, YTM, quantity, price, accrued interest, total bond price, total purchase price. 9I haven't completely researched tax treatment but as I understand it, (remember I've never purchased Treasuries before) accrued interest paid is not part of my basis as that's included in my coupon payment.) Anything else I should be tracking

I DL'd my account history and then copied and pasted the purchases into a separate spreadsheet. There must be an easier way especially since I want a hyperlink to each Treasury. I'm sure there's a thread somewhere on this.

Thanks for help,
Nonnie
User avatar
Topic Author
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Trading Treasuries (nominal and TIPS)

Post by Kevin M »

nonnie wrote: Sun Aug 07, 2022 3:20 pm WOW, this is so great although I think I'm getting ahead of myself in knowledge I need to know. I haven't used the LOOKUP feature for a dozen years but I think I'm going to need it for a spreadsheet to track my Treasuries. For the future, I would like the ability to have a spreadsheet that could do calculations similar to this. In other words, take a Treasury I currently hold and figure out if it makes sense to redeem early and purchase CUSIP X-- could be 1-2-3+ months in the future. Here's where my head gets fuzzy--could your spreadsheet be adapted by using any CUSIP on Line 1 and comparing then on Line 6 entering one I currently hold figure out whether it is advantageous to sell prior to maturity?

Thanks much.
The first two paragraphs in my reply are more important than the tables and calculations with regard to selling before maturity:
Kevin M wrote:The first thing to understand is that your Treasuries now are at the market yields and prices. So selling and re-buying the same Treasuries will incur a loss due to the bid/ask spread; i.e., you will pay a higher price to buy than you will receive to sell.

So the question I'd ask is do I have a better opportunity to invest the money in something else? Or perhaps my objectives have changed since the purchase; e.g., I've realized I want to invest in a longer maturity Treasury. I would look forward to make this decision; i.e., I would compare to the existing yield to maturity (2.05% for bill1 and 2.04% for bill 2) to other opportunities, regardless of how much I've earned since the original purchase.
So with respect to "figure out whether it is advantageous to sell prior to maturity", it depends more on what you'll do with the money if you sell early than on how much you've earned so far.

One can certainly develop a spreadsheet that shows what you would earn if you sell your Treasury holdings, as well as other things one might be interested in. I've done exactly that; the table I showed was just a simplified sheet I threw together to show how this can be done for a particular Treasury.

I've been meaning to do a post on what I do to track my Treasury purchases, so I'll try to get to that soon. It actually might require several posts.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
Topic Author
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Trading Treasuries (nominal and TIPS)

Post by Kevin M »

nonnie wrote: Sun Aug 07, 2022 3:34 pm I would like suggestions on the best way to track Treasury purchases (made at Fidelity). I'm sure there's an easy spreadsheet most folks use. I have a dozen Treasuries recently purchased to track. What are the essential things that should be tracked for each Treasury? I'm using purchase, symbol, description, YTM, quantity, price, accrued interest, total bond price, total purchase price. 9I haven't completely researched tax treatment but as I understand it, (remember I've never purchased Treasuries before) accrued interest paid is not part of my basis as that's included in my coupon payment.) Anything else I should be tracking

I DL'd my account history and then copied and pasted the purchases into a separate spreadsheet. There must be an easier way especially since I want a hyperlink to each Treasury. I'm sure there's a thread somewhere on this.

Thanks for help,
Nonnie
You don't really need to track the accrued interest in a spreadsheet. It will be accounted for on your 1099-INT and other supplemental tax info you'll receive. This is true of pretty much everything regarding Treasuries; I only track them because it's interesting to me, and I like to know how the yields I'm getting compare to what I've gotten in the past, and for TIPS I want to calculate seasonally adjusted yields, which aren't provided by the broker. At any rate, accrued interest is included in the Fidelity transaction download, so it's there if you need it.

Instead of copy/paste, you can simply import the Fidelity transaction history into a sheet, then edit it, or use lookup or filter functions in other sheets to get what you're interested in into a less cluttered sheet. Lately I've been downloading my transactions on or soon after the trade, and limiting the transactions to the date of interest, and sometimes even the "symbol", which in our case is a CUSIP. This gives me a small download file that I can easily edit after importing to remove the extraneous stuff (and I use macros to do the cleanup for me to speed it up).

I use Google Sheets, which enables loading things directly from the web in some cases, as well as importing from one spreadsheet to another, but I'm sure you can do most of it with Excel. As examples of something I might load directly from the web are some of #Cruncher's TIPS tables on his eyebonds.info website.

I know of no "hyperlink" to retrieve Treasury data, such as prices and yields. You might be able to import from the WSJ Treasury web site; I used to have that in a spreadsheet using IMPORTHTML, but the link doesn't work anymore--they've changed their website since then.

What I do is just download quotes from Fidelity every trading day, or any day I'm interested in updating my spreadsheets. I have different saved searches for TIPS, 0-6m Treasuries, and nominal Treasuries to calculate BEI for the TIPS. It only takes a couple of minutes to run a saved search, export the data, and import it into my Google sheet. That's part of what I plan on walking through sometime soon.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
nonnie
Posts: 3010
Joined: Thu Mar 13, 2008 8:05 pm

Re: Trading Treasuries (nominal and TIPS)

Post by nonnie »

Kevin M wrote: Sun Aug 07, 2022 4:42 pm
nonnie wrote: Sun Aug 07, 2022 3:34 pm I would like suggestions on the best way to track Treasury purchases (made at Fidelity). I'm sure there's an easy spreadsheet most folks use. I have a dozen Treasuries recently purchased to track. What are the essential things that should be tracked for each Treasury? I'm using purchase, symbol, description, YTM, quantity, price, accrued interest, total bond price, total purchase price. 9I haven't completely researched tax treatment but as I understand it, (remember I've never purchased Treasuries before) accrued interest paid is not part of my basis as that's included in my coupon payment.) Anything else I should be tracking

I DL'd my account history and then copied and pasted the purchases into a separate spreadsheet. There must be an easier way especially since I want a hyperlink to each Treasury. I'm sure there's a thread somewhere on this.

Thanks for help,
Nonnie
You don't really need to track the accrued interest in a spreadsheet. It will be accounted for on your 1099-INT and other supplemental tax info you'll receive. This is true of pretty much everything regarding Treasuries; I only track them because it's interesting to me, and I like to know how the yields I'm getting compare to what I've gotten in the past, and for TIPS I want to calculate seasonally adjusted yields, which aren't provided by the broker. At any rate, accrued interest is included in the Fidelity transaction download, so it's there if you need it.

Instead of copy/paste, you can simply import the Fidelity transaction history into a sheet, then edit it, or use lookup or filter functions in other sheets to get what you're interested in into a less cluttered sheet. Lately I've been downloading my transactions on or soon after the trade, and limiting the transactions to the date of interest, and sometimes even the "symbol", which in our case is a CUSIP. This gives me a small download file that I can easily edit after importing to remove the extraneous stuff (and I use macros to do the cleanup for me to speed it up).

I use Google Sheets, which enables loading things directly from the web in some cases, as well as importing from one spreadsheet to another, but I'm sure you can do most of it with Excel. As examples of something I might load directly from the web are some of #Cruncher's TIPS tables on his eyebonds.info website.

I know of no "hyperlink" to retrieve Treasury data, such as prices and yields. You might be able to import from the WSJ Treasury web site; I used to have that in a spreadsheet using IMPORTHTML, but the link doesn't work anymore--they've changed their website since then.

What I do is just download quotes from Fidelity every trading day, or any day I'm interested in updating my spreadsheets. I have different saved searches for TIPS, 0-6m Treasuries, and nominal Treasuries to calculate BEI for the TIPS. It only takes a couple of minutes to run a saved search, export the data, and import it into my Google sheet. That's part of what I plan on walking through sometime soon.

Kevin
Thanks, Kevin. Good to know I'm on the right track importing the transaction sheet (I always want to make sure I'm not reinventing the wheel). I didn't do it all right after the trades, so there's a bit more editing-- but now I know better I'm still learning the capabilities of the Fidelity website.
I pretty much track everything in my life on spreadsheets so I want to have this all in one place for our five accounts. I'll experiment later with Google Sheets

For "hyperlink" I meant the CUSIP provided in my purchase -- it's a hot link/hyperlink there but when importing history, it is not.
YOU BOUGHT UNITED STATES TREAS NTS NOTE 2.50000% 08/15/2023 (Cash)Close Popup
Symbol 912828VS6
Description UNITED STATES TREAS NTS NOTE 2.50000% 08/15/2023

I've been pasting this into my spreadsheet to have the data--I'm not yet sure how to use it. Aside from our 1 yr ladder for cash needs, next maturity date is 6 months by which time I'll probably have forgotten everything. I'm not yet ready to do the analysis of IRR on selling prior to maturity that was discussed in another thread.

I think I'm trying to learn too much at once! At one time I knew how to edit posts here so I didn't have to repeat the entire thread (snip) so perhaps I'll try that next. The one thing I really hate about getting older is how much harder it is to remember something if you don't do it frequently enough. When younger I could remember from month to month and even longer, now I'm lucky if I remember how to use a function from week to week!

Thanks again, now on to figuring out how to totally switch to Fidelity one-stop shop thread and figure out if we need CMA accounts or not.
User avatar
Topic Author
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Trading Treasuries (nominal and TIPS)

Post by Kevin M »

nonnie wrote: Sun Aug 07, 2022 5:09 pm Thanks, Kevin. Good to know I'm on the right track importing the transaction sheet (I always want to make sure I'm not reinventing the wheel). I didn't do it all right after the trades, so there's a bit more editing-- but now I know better I'm still learning the capabilities of the Fidelity website.
I pretty much track everything in my life on spreadsheets so I want to have this all in one place for our five accounts. I'll experiment later with Google Sheets

When I first started my Treasury tracking spreadsheet, I had to load transaction history for much more than just Treasury trades. Google Sheets provides a FILTER function that can be used to extract just the Treasury trades. It looks like Excel provides something like it, but apparently only for Office 365.

TIP: You can download transaction history for all accounts at Fidelity, and then use the Account column to filter or extract the Treasuries for a particular account into another sheet.

In the Security Description column of the Fidelity transactions download, all Treasuries start with "UNITED STATES TREAS". In my case, it's just enough to filter for the left 2 characters = "UN". I just downloaded my transactions for the last 10 days for all accounts, and imported them into a new sheet starting at A1; there are about 70 rows of transactions, and the other text that gets included. The following FILTER formula extracts just the Treasury transactions (starting in a cell below all of the imported stuff, in my case, cell A92):

Code: Select all

=FILTER(A1:M75, LEFT(E1:E75, 2)="UN")
Adding a header and doing a little formatting, I end up with this:

Image

If you have any Treasury interest payments in the selected date range, those would be included with the above FILTER, but you can do additional filtering or just manually delete those rows if you don't want them.

I can then add things I'm interested with more formulas. I can extract coupon rate, maturity date, and type (nominal or TIPS) from the description, and I can calculate settlement date from run date (which is the trade date).
nonnie wrote: Sun Aug 07, 2022 5:09 pm For "hyperlink" I meant the CUSIP provided in my purchase -- it's a hot link/hyperlink there but when importing history, it is not.
YOU BOUGHT UNITED STATES TREAS NTS NOTE 2.50000% 08/15/2023 (Cash)Close Popup
Symbol 912828VS6
Description UNITED STATES TREAS NTS NOTE 2.50000% 08/15/2023

I've been pasting this into my spreadsheet to have the data--I'm not yet sure how to use it. Aside from our 1 yr ladder for cash needs, next maturity date is 6 months by which time I'll probably have forgotten everything. I'm not yet ready to do the analysis of IRR on selling prior to maturity that was discussed in another thread.
You don't need to copy/paste that, since all the info you need is in the transaction history.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
nonnie
Posts: 3010
Joined: Thu Mar 13, 2008 8:05 pm

Re: Trading Treasuries (nominal and TIPS)

Post by nonnie »

Kevin M wrote: Sun Aug 07, 2022 6:50 pm
nonnie wrote: Sun Aug 07, 2022 5:09 pm Thanks, Kevin. Good to know I'm on the right track importing the transaction sheet (I always want to make sure I'm not reinventing the wheel). I didn't do it all right after the trades, so there's a bit more editing-- but now I know better I'm still learning the capabilities of the Fidelity website.
I pretty much track everything in my life on spreadsheets so I want to have this all in one place for our five accounts. I'll experiment later with Google Sheets

When I first started my Treasury tracking spreadsheet, I had to load transaction history for much more than just Treasury trades. Google Sheets provides a FILTER function that can be used to extract just the Treasury trades. It looks like Excel provides something like it, but apparently only for Office 365.

TIP: You can download transaction history for all accounts at Fidelity, and then use the Account column to filter or extract the Treasuries for a particular account into another sheet.

In the Security Description column of the Fidelity transactions download, all Treasuries start with "UNITED STATES TREAS". In my case, it's just enough to filter for the left 2 characters = "UN". I just downloaded my transactions for the last 10 days for all accounts, and imported them into a new sheet starting at A1; there are about 70 rows of transactions, and the other text that gets included. The following FILTER formula extracts just the Treasury transactions (starting in a cell below all of the imported stuff, in my case, cell A92):

Code: Select all

=FILTER(A1:M75, LEFT(E1:E75, 2)="UN")
Adding a header and doing a little formatting, I end up with this:

Image

If you have any Treasury interest payments in the selected date range, those would be included with the above FILTER, but you can do additional filtering or just manually delete those rows if you don't want them.

I can then add things I'm interested with more formulas. I can extract coupon rate, maturity date, and type (nominal or TIPS) from the description, and I can calculate settlement date from run date (which is the trade date).
nonnie wrote: Sun Aug 07, 2022 5:09 pm For "hyperlink" I meant the CUSIP provided in my purchase -- it's a hot link/hyperlink there but when importing history, it is not.
YOU BOUGHT UNITED STATES TREAS NTS NOTE 2.50000% 08/15/2023 (Cash)Close Popup
Symbol 912828VS6
Description UNITED STATES TREAS NTS NOTE 2.50000% 08/15/2023

I've been pasting this into my spreadsheet to have the data--I'm not yet sure how to use it. Aside from our 1 yr ladder for cash needs, next maturity date is 6 months by which time I'll probably have forgotten everything. I'm not yet ready to do the analysis of IRR on selling prior to maturity that was discussed in another thread.
You don't need to copy/paste that, since all the info you need is in the transaction history.

Kevin
Perfect! Is there a way to bookmark this response? I would like to be able to easily refer to it when I get ready to implement (I've put off doing my chores all day because money matters are way more fun!)
User avatar
jeffyscott
Posts: 13438
Joined: Tue Feb 27, 2007 8:12 am

Re: Trading Treasuries (nominal and TIPS)

Post by jeffyscott »

nonnie wrote: Sun Aug 07, 2022 7:10 pm Perfect! Is there a way to bookmark this response?
Just click on the subject line for the specific post and that will change the URL to that specific post, in this case that will be: viewtopic.php?p=6813592#p6813592

Then you can save that url somewhere or create a bookmark with the browser.
User avatar
Topic Author
Kevin M
Posts: 15750
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Trading Treasuries (nominal and TIPS)

Post by Kevin M »

nonnie wrote: Sun Aug 07, 2022 7:10 pm Perfect! Is there a way to bookmark this response? I would like to be able to easily refer to it when I get ready to implement (I've put off doing my chores all day because money matters are way more fun!)
Sure. One way is to click the title of the reply, which will give you the URL for that reply in your browser address bar, then drag the URL to your bookmarks bar. Or create the bookmark however you usually do.
If I make a calculation error, #Cruncher probably will let me know.
MisterMister
Posts: 526
Joined: Thu Nov 01, 2018 9:50 pm

Re: Trading Treasuries (nominal and TIPS)

Post by MisterMister »

Since spreadsheets for tracking securities have been the recent subtopic, I justed wanted to contribute that, for Fidelity and Google Sheets users, you can put a formula like this in your spreadsheet:

Code: Select all

=hyperlink("https://fixedincome.fidelity.com/ftgw/fi/FIBondDepthOfBook?cusip="&A27,"Prices")
(Replace A27 with a cell containing the CUSIP for the security).

This does not dynamically insert data into your spreadsheet but does give you a manual hyperlink you can use to get the current bid/ask prices of the security.

It also works for Excel, but in the case of Excel you have to log in to Fidelity each and every time you use the link.
MisterMister
Posts: 526
Joined: Thu Nov 01, 2018 9:50 pm

Re: Trading Treasuries (nominal and TIPS)

Post by MisterMister »

Kevin M wrote: Sun Aug 07, 2022 6:50 pm ...
When I first started my Treasury tracking spreadsheet, I had to load transaction history for much more than just Treasury trades. Google Sheets provides a FILTER function that can be used to extract just the Treasury trades. It looks like Excel provides something like it, but apparently only for Office 365.
...
True, it can't be done with a formula in older Excel versions. But at least as far back as 2010, Excel has a Filter button that can do the same thing manually (menu: Data/Filter, then click the filter button above the security description). Use Text Filters and Begins With.
User avatar
#Cruncher
Posts: 3975
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Trading Treasuries (nominal and TIPS)

Post by #Cruncher »

Reader Alert: This post gets "into the weeds" of calculating returns. Most readers of this thread will probably want to skip it. :wink:
Kevin M wrote: Sun Aug 07, 2022 2:55 pmWhen we say "yield" without any qualifier, we mean "yield to maturity (YTM)". ... Internal rate of return (IRR) shows your actual annualized return based on the cash flows. All you really need to know is that it may be a little different than the yield to maturity. Consider this simple example. You invest 100 and receive 101 one year (365 days) later. You know your return is 1.00% (1/100). In this case, IRR and YIELD are almost exactly the same:

Code: Select all

...
IRR	0.99999999999999%
YTM	1.00000000000000%
... we can see the tiny difference. For spreadsheet folks, I use 1 as the frequency parameter in the YIELD function for bills with less than 52 weeks to maturity, since this matches what Treasury publishes for yields, and is what gives us 1.00% for the above situation.
Kevin, we need to distinguish the concepts of internal rate of return (IRR) and yield-to-maturity (YTM) from the functions used to compute them. The small discrepancy in your example arises from the internal working of the functions, XIRR and YIELD. In Excel for example, XIRR
uses an iterative technique for calculating XIRR. Using a changing rate (starting with guess), XIRR cycles through the calculation until the result is accurate within 0.000001 percent.
I assume Google Sheets -- which you use -- works similarly, but apparently with more precision. Here is what I get in Excel. In the last row I show how the internal rate of return that XIRR produces (annual percentage yield or APY) can be calculated exactly for the case of a Treasury bill that has no coupon.

Code: Select all

 26    Col A         Col B   Formula in Column B
 27  Amounts          Date
 28     -100      8/8/2022
 29      101      8/8/2023
 30     Days           365  =B29-B28
 31     XIRR   0.99999994%  =XIRR(A28:A29,B28:B29)
 32    YIELD   1.00000000%  =YIELD(B28,B29,0,-A28,A29,1,1)
 33      APY   1.00000000%  =(A29/-A28)^(365/B30)-1
Now I'll go further "into the weeds" to discuss how the Treasury computes YTM for Treasury bills that mature in 26 weeks or less. The calculation instructions are in this TreasuryDirect PDf. I'll illustrate with three T-bill auctions, 13-week and 26-week both issued 8/4/2022; and the 4-week issued 1/22/2004 that is used in the instruction PDF. (This last one illustrates a case using a year of 366 days.)

Code: Select all

Row            Col A       Col B       Col C       Col D  Formula in Col B -- Copied Right to Col D
  1           Issued    8/4/2022    8/4/2022   1/22/2004
  2          Matures   11/3/2022    2/2/2023   2/19/2004
  3    Discount rate      2.490%      2.850%      0.800%
  4     Days elapsed          91         182          28  =B2-B1
  5     Days in year         365         365         366  =DATE(YEAR(B1)+1,MONTH(B1),DAY(B1))-B1
  6            Price   99.370583   98.559167   99.937778  =ROUND(100*(1-B3*B4/360),6)
  7  Investment Rate      2.541%      2.932%      0.814%  =(100/B6-1)*(B5/B4)
  8            YIELD      2.541%      2.932%      0.812%  =YIELD(B1,B2,0,B6,100,1,1)
  9   Price for XIRR  -99.370583  -98.559167  -99.937778  =-B6
 10  Redemp for XIRR  100.000000  100.000000  100.000000
 11             XIRR      2.565%      2.953%      0.815%  =XIRR(B9:B10,B1:B2)
 12              APY      2.565%      2.953%      0.815%  =(100/B6)^(365/B4)-1
Notes:
  • Row 3: Discount rate is the "High Rate" from the auction results report. It is actually the lowest bid rate that allows the Treasury to sell all the bills it's offering.
  • Row 4: The Treasury assumes 366 days for the case in column D (the example from the instruction PDF).
  • Row 7: The Treasury calls its calculation of YTM "Investment Rate". The instruction PDF calls it "Coupon Equivalent Yield".
  • Row 8: The YIELD function, with frequency parameter of 1, produces approximately the same result. (It's off a little bit in the last case with a 366 day year.)
  • Row 9 & 10: To use the XIRR function we have to construct a range with price as a negative number and the redemption value of 100.
  • Row 11: XIRR produces a rate slightly higher than Investment Rate or YIELD.
  • Row 12: Shows how we can calculate the same result as XIRR without having to construct the range in rows 9 & 10.
And now I'll go one step further into the weeds and discuss how the Treasury computes YTM for bills that mature in more than 26 weeks. My jaw dropped when I first saw on page 2 of the instruction PDF that the calculation used a quadratic equation. But on reflection, I see that this is necessary to compute an "Investment Rate" that is comparable to the YTM quoted on Treasury bonds with coupons and without using trial-and-error iteration. I illustrate this with three 52-weeks bills: the one in the instruction PDF, one issued 7/14/2022 and one issued 1/30/2020 to illustrate a 366 day year.

Code: Select all

Row            Col A       Col B       Col C       Col D   Formulas in Col B Copied Right to Col D
  1           Issued    6/7/1990   7/14/2022   1/30/2020
  2          Matures    6/6/1991   7/13/2023   1/28/2021
  3    Discount rate      7.650%      2.960%      1.490%
  4     Days elapsed         364         364         364  =B2-B1
  5     Days in year         365         365         366  =DATE(YEAR(B1)+1,MONTH(B1),DAY(B1))-B1
  6            Price   92.265000   97.007111   98.493444  =ROUND(100*(1-B3*B4/360),6)
  7                a    0.248630    0.248630    0.247268  =B4/(2*B5)-0.25
  8                b    0.997260    0.997260    0.994536  =B4/B5
  9                c   -0.083835   -0.030852   -0.015296  =1-100/B6
 10  Investment Rate      8.237%      3.070%      1.532%  =(-B8+(B8^2-4*B7*B9)^0.5)/(2*B7)
 11            YIELD      8.238%      3.070%      1.532%  =YIELD(B1,B2,0,B6,100,2,1)
 12   Price for XIRR  -92.265000  -97.007111  -98.493444  =-B6
 13  Redemp for XIRR  100.000000  100.000000  100.000000
 14             XIRR      8.407%      3.094%      1.534%  =XIRR(B12:B13,B1:B2)
 15              APY      8.407%      3.094%      1.534%  =(100/B6)^(365/B4)-1
Notes:
  • Rows 7 to 9: These are the three intermediate variables needed to solve the quadratic equation in row 10.
  • Row 11: The YIELD function, now with a frequency parameter of 2, produces almost the same result.
  • Rows 14 & 15: The annual percentage yield (APY) produced with and without the XIRR function is noticeably higher than the Investment Rate. (Except for the third case in column D where the existence of a 366 day year by coincidence makes the result closer.) This is to be expected, since the Investment Rate corresponds to coupon bonds where the YTM is compounded semi-annually.
Last edited by #Cruncher on Mon Aug 08, 2022 10:21 am, edited 1 time in total.
prettybogle
Posts: 383
Joined: Sun Mar 01, 2015 9:44 am

Re: Trading Treasuries (nominal and TIPS)

Post by prettybogle »

Thanks to this thread, we have now bought considerable amount of 13 month t-bills with 3.24% yield. I don't really see any reason why anyone would lock the money in CDs for 2, 3 or 5 years just to get 3% when t-bills are giving 3+% in just 1 year !!!
User avatar
jeffyscott
Posts: 13438
Joined: Tue Feb 27, 2007 8:12 am

Re: Trading Treasuries (nominal and TIPS)

Post by jeffyscott »

prettybogle wrote: Mon Aug 08, 2022 9:12 am Thanks to this thread, we have now bought considerable amount of 13 month t-bills with 3.24% yield. I don't really see any reason why anyone would lock the money in CDs for 2, 3 or 5 years just to get 3% when t-bills are giving 3+% in just 1 year !!!
Because you don't know what you'll get over the next 5 years, it could be more or less than what a 5 year CD would pay (currently 3.4% for a 5 year brokered CD). I'll likely do some of both (and some TIPS), as soon as money from my broken 2% CD arrives at the brokerage.
ModifiedDuration
Posts: 1393
Joined: Sat Dec 05, 2015 3:33 pm

Re: Trading Treasuries (nominal and TIPS)

Post by ModifiedDuration »

prettybogle wrote: Mon Aug 08, 2022 9:12 am Thanks to this thread, we have now bought considerable amount of 13 month t-bills with 3.24% yield. I don't really see any reason why anyone would lock the money in CDs for 2, 3 or 5 years just to get 3% when t-bills are giving 3+% in just 1 year !!!
Treasury Bills by definition have a maximum maturity of 52 weeks (and sell at a discount to par with no coupon payments).

Sounds like you bought a Treasury Note or a Treasury Bond with 13 months left to maturity, which will have semi-annual coupon payments.
prettybogle
Posts: 383
Joined: Sun Mar 01, 2015 9:44 am

Re: Trading Treasuries (nominal and TIPS)

Post by prettybogle »

ModifiedDuration wrote: Mon Aug 08, 2022 9:27 am
prettybogle wrote: Mon Aug 08, 2022 9:12 am Thanks to this thread, we have now bought considerable amount of 13 month t-bills with 3.24% yield. I don't really see any reason why anyone would lock the money in CDs for 2, 3 or 5 years just to get 3% when t-bills are giving 3+% in just 1 year !!!
Treasury Bills by definition have a maximum maturity of 52 weeks (and sell at a discount to par with no coupon payments).

Sounds like you bought a Treasury Note or a Treasury Bond with 13 months left to maturity, which will have semi-annual coupon payments.
Sorry for Typo. Yes I purchased 11 month t-bills
BBBob
Posts: 220
Joined: Tue Aug 11, 2015 12:25 pm

Re: Trading Treasuries (nominal and TIPS)

Post by BBBob »

Thank you so much, Kevin and the rest of you folks. I am still trying to get my head around all the TIP nuances, and have three embarrassingly basic questions:

1. Is the posted YTM real or nominal?
(I assume it is nominal, and that the inflation rate is assumed to stay constant for the remaining term. And I assume that If inflation goes down, the YTM goes down as well, but how much?)

2. Does a YTM of .323 mean 0.323%

3. Is the "TIPS for Dummies" approach to simply go for the highest YTM at the desired maturity date?

Again...thank you all!!
Bob
JayB
Posts: 659
Joined: Sat May 28, 2022 9:57 am

Re: Trading Treasuries (nominal and TIPS)

Post by JayB »

This may have been asked about in this long thread before, and if so, I apologize in advance.

I tried to buy some nominal Treasury bonds through my Fidelity brokerage online account on Friday as I have done numerous times in the past. These were limit orders (the only kind Fidelity allows for these bonds) with the limit price pre-filled in by the bond purchase dialog, with apparently little or no room to alter that limit price (I've tried). At any rate, I had to submit the order THREE times in order to get a fill.

The first two times, the orders sat there as pending until they were automatically "verified canceled" after 20 minutes. The rejections happened even though I submitted the orders within 2-3 seconds of hitting the BUY button (after quickly filling in the quantity desired and verifying the order on a subsequent screen). It was obvious that these rejections occured because the market price had moved above the limits, even though the displayed limits matched the market price exactly when the orders were submitted.

I called up the Fixed Income desk at Fidelity and at first all they could say that the rejections happened due to market volatility. When I pressed the fixed income specialist on the phone, he admitted that in fast-moving bond markets, THE QUOTED PRICES THAT WE INITIALLY RECEIVE ARE ALREADY STALE due to a lag in the price display process. This happens even if bond orders are placed through a phone representative.

So it appears that the only way I can get a T bond purchase order fill is if the market price is stable from a few moments before order submission to a few moments after, or if the price moves below the limit during the next 20 minutes. It took three tries for this situation to occur, making the purchase process quite time consuming and frustrating.

Have others had similar problems with getting bond orders filled at Fidelity?
Last edited by JayB on Mon Aug 08, 2022 10:23 am, edited 1 time in total.
User avatar
Doc
Posts: 10606
Joined: Sat Feb 24, 2007 12:10 pm
Location: Two left turns from Larry

Re: Trading Treasuries (nominal and TIPS)

Post by Doc »

JayB wrote: Mon Aug 08, 2022 10:13 am I tried to buy some nominal Treasury bonds through my Fidelity brokerage online account yesterday as I have done numerous times in the past.
Yesterday? The market was closed on Sunday. That may have been the problem. The order was going to sit there until today.
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.
Post Reply