- Pic 1: Was Vanguard's display of secondary market tbills available. I chose the one highlighted. Shows: worst price 99.76, worst YTM 4.38%
- Pic 2: Order details (pasted into Excel). To it's right, Excel's TBILLYIELD function which assumes face value 100. Excel shows yield is 4.32%.
- Pic 3: Transaction history: Shows purchase price was in fact 99.76 (minor rounding)
Vanguard showed worst YTM at 4.38% at price 99.76. My order shows a purchase price of 99.76 but Excel says yield 4.32%. Would like to understand the discrepancy. TIA
TBill Yields - What's the actual yield?
-
- Posts: 902
- Joined: Tue Mar 27, 2018 5:41 am
TBill Yields - What's the actual yield?
I bought a secondary market Tbill w/Vanguard. Trying to understand the real YTM.
Re: TBill Yields - What's the actual yield?
You can trust the YTM numbers when buying on the secondary market, as long as you're holding to maturity. I don't know what excel is doing, but VG/Fido don't mess around with quotes, so I'm thinking that rounding or "off by one day" error are likely happening with excel. I've bought many TBills on secondary, always get my expected YTM
Re: TBill Yields - What's the actual yield?
YIELD gives 4.38% (enter 0 for coupon, 1 for frequency, and 1 for day count convention).
TBILLYIELD gives 4.32%.
YIELDDISC give 4.38%.
Maybe TbillYield is actually calculating discount rate? I always use YIELD, and it agrees with Treasury published yields to 3 decimal places.
TBILLYIELD gives 4.32%.
YIELDDISC give 4.38%.
Maybe TbillYield is actually calculating discount rate? I always use YIELD, and it agrees with Treasury published yields to 3 decimal places.
If I make a calculation error, #Cruncher probably will let me know.
Re: TBill Yields - What's the actual yield?
I use the following formula for T-Bills I purchase at auction, should work the same for secondaries I think but I don’t buy secondaries.
100 - Price Paid / Price Paid X (365/Days to Maturity) X 100 = Yield
100 - Price Paid / Price Paid X (365/Days to Maturity) X 100 = Yield
Bad spellers of the world untie |
Autocorrect is my worst enema
Re: TBill Yields - What's the actual yield?
Microsoft's description of TBILLYIELD says it "Returns the yield for a Treasury bill". But it actually returns a value closer to the Discount Rate. However, it doesn't even do that correctly. See my post, Re: Help me understand my T-Bill Auction Results for more.
Row 8 in the table below shows the correct yield, aka "Investment Rate", using the formula from the Treasury's Price, Yield and Rate Calculations for a Treasury Bill. Rows 9-11 show how the TBILLEQ, YIELD, and YIELDDISC functions can also calculate this rate -- correct to 11 percentage places. Row 12 shows how the TBILLYIELD function returns a value closer to the Discount Rate (on row 7) than to the Investment Rate.
Code: Select all
Row Col A Col B
2 Settlement 1/25/2023
3 Maturity 2/14/2023
4 Price 99.7606
5 Days until mature 20 =B3-B2
6 Days in year 365 =DATE(YEAR(B2)+1,MONTH(B2),DAY(B2))-B2
7 Discount rate 4.30920000000% =(1-B4/100)*(360/B5)
8 Investment rate 4.37953460585% =(100/B4-1)*(B6/B5)
9 TBILLEQ 4.37953460585% =TBILLEQ(B2,B3,B7)
10 YIELD 4.37953460585% =YIELD(B2,B3,0,B4,100,1,1)
11 YIELDDISC 4.37953460585% =YIELDDISC(B2,B3,B4,100,1)
12 TBILLYIELD 4.31954098111% =TBILLYIELD(B2,B3,B4)
-
- Posts: 902
- Joined: Tue Mar 27, 2018 5:41 am
Re: TBill Yields - What's the actual yield?
Thanks much for that!#Cruncher wrote: ↑Wed Jan 25, 2023 4:12 pmMicrosoft's description of TBILLYIELD says it "Returns the yield for a Treasury bill". But it actually returns a value closer to the Discount Rate. However, it doesn't even do that correctly. See my post, Re: Help me understand my T-Bill Auction Results for more.
Row 8 in the table below shows the correct yield, aka "Investment Rate", using the formula from the Treasury's Price, Yield and Rate Calculations for a Treasury Bill. Rows 9-11 show how the TBILLEQ, YIELD, and YIELDDISC functions can also calculate this rate -- correct to 11 percentage places. Row 12 shows how the TBILLYIELD function returns a value closer to the Discount Rate (on row 7) than to the Investment Rate.Code: Select all
Row Col A Col B 2 Settlement 1/25/2023 3 Maturity 2/14/2023 4 Price 99.7606 5 Days until mature 20 =B3-B2 6 Days in year 365 =DATE(YEAR(B2)+1,MONTH(B2),DAY(B2))-B2 7 Discount rate 4.30920000000% =(1-B4/100)*(360/B5) 8 Investment rate 4.37953460585% =(100/B4-1)*(B6/B5) 9 TBILLEQ 4.37953460585% =TBILLEQ(B2,B3,B7) 10 YIELD 4.37953460585% =YIELD(B2,B3,0,B4,100,1,1) 11 YIELDDISC 4.37953460585% =YIELDDISC(B2,B3,B4,100,1) 12 TBILLYIELD 4.31954098111% =TBILLYIELD(B2,B3,B4)
-
- Posts: 60
- Joined: Sun Aug 15, 2021 5:53 pm
Re: TBill Yields - What's the actual yield?
I can calculate High/Discount and Investment rates just fine for T-bills at auction, but am having trouble with calculating the YTM that Vanguard shows in their secondary market T-Bill quotes. For example, this quote appeared after the close yesterday (1/25).
CUSIP: 912796Y60
Maturity 2/21/2023
Bid: 99.691, 4.519%
Ask: 99.693, 4.495%
I calculated the expected YTM using these different techniques and Excel functions, assuming a settlement date of today (1/26). Maybe I have the wrong assumption about how settlement dates work for quotes during trading and after close?? I get (using the same formulas as #cruncher):
Coupon Equivalent Yield (per Treasury formula), YIELDDISC, YIELD: 4.323080%
Discount Rate (per Treasury formula): 4.250769%
TBILLYIELD: 4.263859%
What am I not understanding correctly?
Speaking of settlement dates: I thought that Vanguard would take the money from my account on the settlement date specified by the Treasury schedule when purchased at auction. Instead, Vanguard pulled the money out before these dates. Why? Vanguard hasn’t yet answered my message about this.
CUSIP 912796V48, Treasury Settlement 1/19/2023, money withdrawn 1/17/2023
CUSIIP 912796Z77, TreasurySettlement 1/24/2023, money withdrawn 1/19/2023
CUSIP 912796Y60, Treasury Settlement 1/24/2023, money withdrawn 1/19/2023
CUSIP: 912796Y60
Maturity 2/21/2023
Bid: 99.691, 4.519%
Ask: 99.693, 4.495%
I calculated the expected YTM using these different techniques and Excel functions, assuming a settlement date of today (1/26). Maybe I have the wrong assumption about how settlement dates work for quotes during trading and after close?? I get (using the same formulas as #cruncher):
Coupon Equivalent Yield (per Treasury formula), YIELDDISC, YIELD: 4.323080%
Discount Rate (per Treasury formula): 4.250769%
TBILLYIELD: 4.263859%
What am I not understanding correctly?
Speaking of settlement dates: I thought that Vanguard would take the money from my account on the settlement date specified by the Treasury schedule when purchased at auction. Instead, Vanguard pulled the money out before these dates. Why? Vanguard hasn’t yet answered my message about this.
CUSIP 912796V48, Treasury Settlement 1/19/2023, money withdrawn 1/17/2023
CUSIIP 912796Z77, TreasurySettlement 1/24/2023, money withdrawn 1/19/2023
CUSIP 912796Y60, Treasury Settlement 1/24/2023, money withdrawn 1/19/2023
Re: TBill Yields - What's the actual yield?
The asterisk at the bottom shows that quotes after market close are invalid (basically guesses). Try it during market open hoursConstantChrysalis wrote: ↑Thu Jan 26, 2023 3:44 pm I can calculate High/Discount and Investment rates just fine for T-bills at auction, but am having trouble with calculating the YTM that Vanguard shows in their secondary market T-Bill quotes. For example, this quote appeared after the close yesterday (1/25).
CUSIP: 912796Y60
Maturity 2/21/2023
Bid: 99.691, 4.519%
Ask: 99.693, 4.495%
I calculated the expected YTM using these different techniques and Excel functions, assuming a settlement date of today (1/26). Maybe I have the wrong assumption about how settlement dates work for quotes during trading and after close?? I get (using the same formulas as #cruncher):
Coupon Equivalent Yield (per Treasury formula), YIELDDISC, YIELD: 4.323080%
Discount Rate (per Treasury formula): 4.250769%
TBILLYIELD: 4.263859%
What am I not understanding correctly?
Speaking of settlement dates: I thought that Vanguard would take the money from my account on the settlement date specified by the Treasury schedule when purchased at auction. Instead, Vanguard pulled the money out before these dates. Why? Vanguard hasn’t yet answered my message about this.
CUSIP 912796V48, Treasury Settlement 1/19/2023, money withdrawn 1/17/2023
CUSIIP 912796Z77, TreasurySettlement 1/24/2023, money withdrawn 1/19/2023
CUSIP 912796Y60, Treasury Settlement 1/24/2023, money withdrawn 1/19/2023
Re: TBill Yields - What's the actual yield?
For a purchase made today (1/26), settlement would be tomorrow (1/27), and that should be used in the YIELD calc (just use YIELD with freq = q and daycount = 1).ConstantChrysalis wrote: ↑Thu Jan 26, 2023 3:44 pm I calculated the expected YTM using these different techniques and Excel functions, assuming a settlement date of today (1/26).
For after hours quotes, they might be using settlement for tomorrow (Friday), which would be Monday 1/30.
If I make a calculation error, #Cruncher probably will let me know.
-
- Posts: 60
- Joined: Sun Aug 15, 2021 5:53 pm
Re: TBill Yields - What's the actual yield?
Thanks, I finally figured out how to calculate Vanguard’s price given their return. And yes, Kevin, a live quote today (Friday) assumes settlement on Monday. Here’s what I was doing wrong:
For T-bills at auction, the key input is the discount (high rate), and the price is calculated from that. The Treasury’s equations describe this calculation, and they also provide a calculation for Coupon Equivalent Yield (investment rate) to allow for better comparison between investment alternatives.
For the secondary market, Vanguard isn't quoting the discount/high rate, but the Investment rate, which they label YTM. There isn’t an equation published by the Treasury for this, but a little algebra gives:
P = 100 / (1 + (daysTillMaturity / daysInYear) * YTM))
For example, 912796V48 had an ask quote earlier of 4.643% YTM and a price of 98.992
P = 100 / ( 1 + (80 / 365) * .04643 ) = 98.9926
Now I’m wondering, what do traders on the secondary market actually trade to? I’m guessing they trade based on discount rates, with a tick of 0.001%, so the price and YTM values in the Vanguard quote are both derived values from that discount rate. Correct?
For T-bills at auction, the key input is the discount (high rate), and the price is calculated from that. The Treasury’s equations describe this calculation, and they also provide a calculation for Coupon Equivalent Yield (investment rate) to allow for better comparison between investment alternatives.
For the secondary market, Vanguard isn't quoting the discount/high rate, but the Investment rate, which they label YTM. There isn’t an equation published by the Treasury for this, but a little algebra gives:
P = 100 / (1 + (daysTillMaturity / daysInYear) * YTM))
For example, 912796V48 had an ask quote earlier of 4.643% YTM and a price of 98.992
P = 100 / ( 1 + (80 / 365) * .04643 ) = 98.9926
Now I’m wondering, what do traders on the secondary market actually trade to? I’m guessing they trade based on discount rates, with a tick of 0.001%, so the price and YTM values in the Vanguard quote are both derived values from that discount rate. Correct?
Re: TBill Yields - What's the actual yield?
Yield (to maturity) is the industry standard for quoting yields. Treasury uses the term investment rate for yield (not the other way around).
I don't think it's any more complicated than that. Price and yield can be thought of as two sides of the same coin. One can be derived from the other. However, since coupon and maturity also factor into the math, you can't compare Treasuries based on price, so we look at yields instead.
You might want to check out this thread: Trading Treasuries (nominal and TIPS) - Bogleheads.org
I don't think it's any more complicated than that. Price and yield can be thought of as two sides of the same coin. One can be derived from the other. However, since coupon and maturity also factor into the math, you can't compare Treasuries based on price, so we look at yields instead.
You might want to check out this thread: Trading Treasuries (nominal and TIPS) - Bogleheads.org
If I make a calculation error, #Cruncher probably will let me know.