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
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?
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
TBILLYIELDfunction. 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.
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
- To set up our Treasury Bill Yield calculator, we should set up the arguments needed.
- In cell B4, we can start typing out our function. We just simply input the equal sign ‘=‘ to begin the formula, followed by ‘TBILLPRICE(‘.
- As seen below, info on the
TBILLYIELDfunction 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.
- 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.
Frequently Asked Questions (FAQ)
- Why does my formula return a #NUM! error?
US Treasury Bills have a maturity of a year or less. Because of this,
TBILLYIELDwill 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.
- 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.