TBill Yields - What's the actual yield?

Have a question about your personal investments? No matter how simple or complex, you can ask it here.
Post Reply
Topic Author
LookinAround
Posts: 902
Joined: Tue Mar 27, 2018 5:41 am

TBill Yields - What's the actual yield?

Post by LookinAround »

I bought a secondary market Tbill w/Vanguard. Trying to understand the real YTM.
  • 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

    Image
nalor511
Posts: 5058
Joined: Mon Jul 27, 2015 1:00 am

Re: TBill Yields - What's the actual yield?

Post by nalor511 »

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
User avatar
Kevin M
Posts: 15787
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: TBill Yields - What's the actual yield?

Post by Kevin M »

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.
If I make a calculation error, #Cruncher probably will let me know.
MGBMartin
Posts: 1145
Joined: Thu Nov 04, 2021 11:09 am

Re: TBill Yields - What's the actual yield?

Post by MGBMartin »

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
Bad spellers of the world untie | Autocorrect is my worst enema
User avatar
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: TBill Yields - What's the actual yield?

Post by #Cruncher »

Kevin M wrote: Wed Jan 25, 2023 12:03 pmMaybe TbillYield is actually calculating discount rate?
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)
Topic Author
LookinAround
Posts: 902
Joined: Tue Mar 27, 2018 5:41 am

Re: TBill Yields - What's the actual yield?

Post by LookinAround »

#Cruncher wrote: Wed Jan 25, 2023 4:12 pm
Kevin M wrote: Wed Jan 25, 2023 12:03 pmMaybe TbillYield is actually calculating discount rate?
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)
Thanks much for that!
ConstantChrysalis
Posts: 60
Joined: Sun Aug 15, 2021 5:53 pm

Re: TBill Yields - What's the actual yield?

Post by ConstantChrysalis »

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
nalor511
Posts: 5058
Joined: Mon Jul 27, 2015 1:00 am

Re: TBill Yields - What's the actual yield?

Post by nalor511 »

ConstantChrysalis 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
The asterisk at the bottom shows that quotes after market close are invalid (basically guesses). Try it during market open hours
User avatar
Kevin M
Posts: 15787
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: TBill Yields - What's the actual yield?

Post by Kevin M »

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 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).

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.
ConstantChrysalis
Posts: 60
Joined: Sun Aug 15, 2021 5:53 pm

Re: TBill Yields - What's the actual yield?

Post by ConstantChrysalis »

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?
User avatar
Kevin M
Posts: 15787
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: TBill Yields - What's the actual yield?

Post by Kevin M »

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
If I make a calculation error, #Cruncher probably will let me know.
Post Reply