How To Use TBILLYIELD Function in Google Sheets

The TBILLYIELD function in Google Sheets is useful when you need to compute the yield of a US Treasury Bill given its price.

A Treasury Bill or T-Bill is a government security that matures in a year or less. This function works similarly with the YIELDISC function but with US Treasury Bill conventions. 

The rules for using the TBILLYIELD function in Google Sheets are as follows:

  • The function requires the settlement date and maturity date of the given security We also need to provide the price at which the security was bought.
  • The function then outputs the final yield of the T-bill based on the price provided.

Let’s begin with a quick use-case of the TBILLYIELD function.

In this example, let’s say you wanted to purchase your own Treasury Bill security. The settlement date of the security, or the date when the security is delivered to you, is on January 1st, 2021. The bill matures six months later, on July 2nd, 2021. Given that the security was bought at a price of $95, what is the expected yield of your US Treasury Bill?

Using the TBILLYIELD function, we can input all our provided details to return the yield of the bill. Later in the article, we’ll learn how to write this function ourselves in Google Sheets and test it using real values in a sample worksheet.

 

 

The Anatomy of the TBILLYIELD Function

So the syntax (the way we write) of the TBILLYIELD function is as follows:

=TBILLYIELD(settlement, maturity, price)

Let’s dissect this thing and understand what each of these terms means:

  • = the equal sign is how we start any function in Google Sheets.
  • TBILLYIELD() is our TBILLYIELD function. It computes the yield of a US Treasury Bill based on a given price.
  • settlement refers to the settlement date of the security when the security is delivered to the buyer.
  • maturity refers to the maturity date of the security.
  • price refers to the security’s price.

 

A Real Example of Using TBILLYIELD Function

Let’s look at a practical example of the TBILLYIELD function being used in a Google Sheets spreadsheet.

As seen in the worksheet below, we set up a Treasury Bill Yield calculator. We have our values for the settlement date, maturity date, and price. With these given values, we can compute the yield of our US Treasury Bill.

Using the TBILLYIELD Function in Google Sheets to compute the yield of a specified treasury bill

Given the current details, the yield of our Treasury Bill is about 0.08. This means that the return on investment is 8% of whatever we spent on our bill.

We just need to use the following formula to get this result:

=TBILLYIELD(B1, B2, B3)

You can make your own copy of the worksheet above using the link attached below. 

If you’re ready to try out the TBILLYIELD function in Google Sheets, let’s start writing it using the guide in the next section!

 

 

How to Use TBILLYIELD Function in Google Sheets

  1. To set up our Treasury Bill Yield calculator, we should set up the arguments needed.
    Adding the value of the arguments of our TBILLYIELD Function in Google Sheets
  2. In cell B4, we can start typing out our function. We just simply input the equal sign ‘=‘ to begin the formula, followed by ‘TBILLPRICE(‘. 
  3. As seen below, info on the TBILLYIELD function may appear in the pop-up box. We can click on the arrow on the top-right-hand corner of the box to minimize it if needed.
    Typing the TBILLYIELD function into the Formula Bar

  4. The last step is to reference cells containing the values needed to compute the yield, as seen below. Afterward, simply hit Enter on your keyboard to let the function evaluate.
    Result of the TBILLYIELD Function in Google Sheets

 

 

Frequently Asked Questions (FAQ)

  1. Why does my formula return a #NUM! error?
    US Treasury Bills have a maturity of a year or less. Because of this, TBILLYIELD will return an error if the time between the settlement date and the maturity date is over a year. As seen in the example below, the maturity price is over 12 months after the settlement date, causing the formula to return an error.
    The TBILLYIELD requires a duration of a year or less between settlement date and maturity date

  2. Why does my formula return a #VALUE! error?
    You can first check if either the settlement date or maturity date is invalid. Invalid dates can be avoided by using the DATE formula or through the data validation option.
    Otherwise, there may be a non-numeric value in one of your arguments that might be causing the error.

That’s everything you need to know to start using the TBILLYIELD function in Google Sheets. This step-by-step guide shows how easy it is to compute the yield of a US Treasury Bill.

Feel confident in using the TBILLYIELD functions in Google Sheets together with the various other Google Sheets formulas available to create great worksheet solutions that work for you. 

Make sure to subscribe to our newsletter to be the first to know about the latest Google Sheets guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
1 comment
  1. I’m having trouble w/ TBILLYIELD(). It works fine for 4 week bills, but I get errors on 13 & 26 week bills compared to the official govt number:
    13 wk – Fed 1.640% – function 1.647%
    26 wk – Fed 2.160% – function 2.184%

    I’ve verified this w/ the underlying algorithm as well. Any ideas why?

Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like