The TBILLYIELD function in Excel returns the yield of a US Treasury bill from the price you pay for it. You give it the purchase date, the maturity date, and the price per $100 of face value.
A Treasury bill doesn’t pay interest along the way. You buy it for less than its face value and get the full amount back at maturity, and TBILLYIELD turns that difference into an annual rate you can compare across bills.
In this tutorial, I’ll show you four examples, including how to compare several bills with one formula and how to get the yield when you only know what you paid or the quoted discount rate.
TBILLYIELD doesn’t accept a whole range of dates or prices (it returns #VALUE!), so Example 2 also shows the small fix that lets one formula handle several bills.
Follow along with the example file
TBILLYIELD Function Examples.xlsx
TBILLYIELD Function Syntax
Here is the syntax of the TBILLYIELD function:
=TBILLYIELD(settlement,maturity,pr)
- settlement – The date you buy the bill, when it’s settled and transferred to you. This comes after the bill’s original issue date.
- maturity – The date the bill matures and pays back its face value.
- pr – The price you pay per $100 of face value, such as 98.05.
All three arguments are required. The pr argument always works per $100, so if you only know the total amount you paid, you need to convert it first (Example 3 shows how).
The maturity date can be at most one year after the settlement date. If it’s even one day later than that, TBILLYIELD returns #NUM!, which isn’t a problem for real Treasury bills since the longest one runs for 52 weeks.
The TBILLYIELD function works in Excel 365, 2024, 2021, 2019, 2016, and Excel for Mac. The spilling formula in Example 2 needs Excel 2021 or later (including Microsoft 365), and that example also includes a version for Excel 2019 or earlier.
When to Use the TBILLYIELD Function
Use this function when you need to:
- Find the annual yield on a Treasury bill from its price.
- Compare bills with different terms on the same yearly basis.
- Work out the yield on the actual dollar amount you paid.
- Turn a quoted discount rate into the yield you earn.
Let me show you a few practical examples of how to use this function.
Example 1: Calculate the Yield on a Treasury Bill
Let’s start with a simple example.
Below are the details of a 26-week Treasury bill bought at 98.05 per $100 of face value, and I want its yield in cell B6.
Here is the formula:
=TBILLYIELD(B2,B3,B4)
The formula returns 3.93%. You pay 98.05 and get 100 back at maturity, so you earn 1.95 for every $100 of face value.
TBILLYIELD divides that 1.95 by the 98.05 you paid, then turns it into a yearly rate using a 360-day year.
There are 182 days between November 5, 2026, and May 6, 2027, so the yield is 1.95 ÷ 98.05 × 360 ÷ 182, or about 3.93%.
The result comes back as a decimal (about 0.0393), so format the cell as a percentage to read it as 3.93%.
Important: Enter the price as a number per $100, like 98.05. If you enter it as 0.9805, TBILLYIELD treats it as a price of less than one dollar and returns a yield of almost 20,000%.
If the settlement date is on or after the maturity date, or the price is 0 or less, TBILLYIELD returns #NUM!. If a date is text that Excel can’t read as a date, you get #VALUE!.
A price above 100 doesn’t give an error. Instead, you get a negative yield, because you’d get back less than you paid.
Example 2: Compare Treasury Bills of Different Terms
Now let’s compare a few bills at once.
Below are five Treasury bills bought on the same day, from a 4-week bill to a 52-week bill, with the settlement date in G2, and I want the yield on each one in column D.
Enter this formula in cell D2:
=TBILLYIELD(G2,+B2:B6,+C2:C6)
This one formula spills the five yields into D2:D6. It pairs each maturity date in column B with the price next to it in column C, and uses the same settlement date from G2 for every bill.
The + before each range matters here. Like some other functions that won’t spill, TBILLYIELD doesn’t accept a range reference directly, so =TBILLYIELD(G2,B2:B6,C2:C6) returns #VALUE!.
The + turns each range into a list of values, so the function can calculate one yield per row.
The spilling formula needs D2:D6 to be empty. If anything is already in those cells, you’ll see #SPILL! instead, so clear them first.
The 4-week bill gives the highest yield at 3.96%, and the 52-week bill gives the lowest at 3.79%. Since every result is a yearly rate, you can compare a 4-week bill with a 52-week bill even though one matures 13 times sooner.
The 52-week bill matures 364 days after settlement, so it sits just inside the one-year limit from the syntax section.
If you are using Excel 2019 or earlier, enter the below formula in cell D2 and then copy it down to D6:
=TBILLYIELD($G$2,B2,C2)
The dollar signs keep the settlement date fixed on G2, while the maturity date and price references move down one row at a time.
Example 3: Find the Yield from the Amount You Paid
When you buy Treasury bills, you usually know the total you paid rather than the price per $100.
Below are the details of a Treasury bill purchase where I paid $24,760 for $25,000 of face value, and I want the yield in cell B7.
Here is the formula:
=TBILLYIELD(B2,B3,B5/B4*100)
The formula returns 3.83%. The B5/B4*100 part converts the total paid into a price per $100: $24,760 ÷ $25,000 × 100 = 99.04.
TBILLYIELD then works out the yield from that 99.04 price over the 91 days between November 12, 2026, and February 11, 2027.
Important: Don’t use the total amount (B5) as the price. TBILLYIELD would read $24,760 as the price per $100 and return -394.01% without any error, so the mistake is easy to miss.
If you’d rather work with the dollar amounts directly, the INTRATE function formula =INTRATE(B2,B3,B5,B4,2) gives you the same 3.83% for this purchase.
The last argument, 2, tells INTRATE to count the actual days on a 360-day year. That’s the same method TBILLYIELD uses, which is why the results match.
Example 4: Get the Yield from a Quoted Discount Rate
Treasury bills are often quoted with a discount rate instead of a price.
Below are the details of a 13-week Treasury bill quoted at a 3.85% discount rate, and I want the yield I’d earn on it in cell B6.
Here is the formula:
=TBILLYIELD(B2,B3,TBILLPRICE(B2,B3,B4))
The formula returns 3.89%. The TBILLPRICE function first turns the 3.85% discount rate into a price per $100 (about 99.03 for this bill), and TBILLYIELD then works out the yield from that price.
The yield comes out higher than the discount rate because the two are measured against different amounts.
The discount rate compares the discount (about 0.97) with the $100 face value, while the yield compares it with the roughly 99.03 you actually pay, which is a smaller number.
TBILLYIELD uses a 360-day year. If you need the bond-equivalent yield, which uses a 365-day year, the TBILLEQ function gives 3.94% for this same bill.
So these are four practical ways to use the TBILLYIELD function in Excel. It gives you the yearly return on a Treasury bill whether you start with the price, the total you paid, or a quoted discount rate.
For a bond that pays regular interest, use the YIELD function instead.
I hope you found this Excel tutorial useful.
Other Excel Articles You May Also Like:
