What is TBILLYIELD in Google Sheets?
TBILLYIELD in Google Sheets calculates the annual yield of a US Treasury bill based on its settlement date, maturity date, and the price paid for the security. A Treasury bill, also called as T-bill, is a short term loan given to the Government for which you get paid back more than you invested in a period of weeks or months. Here, you buy the T-bill at a discount, and instead of getting monthly interest, when it matures, you receive the full amount of the bill. The TBILLYIELD function in Google Sheets helps calculate the annual return, also called yield, on that investment. It depends on the purchase price, maturity date, and discount rate. This helps you know your profit.
The general syntax is =TBILLYIELD(settlement, maturity, price). The settlement date is when the buyer takes possession, the maturity date is when the bill is redeemed, and the price is the cost per $100 face value. As a simple example, someone buys a T-bill with a settlement date of 01-Jan-2025, maturity date of 01-Mar-2025, and $96.50 is the price at which the security is bought We use TBILLYIELD to find the annualized yield as follows:
=TBILLYIELD(DATE(2025,1,1), DATE(2025,3,1), 96.5).
The annual yield is 0.2213 (around 22.13% annual yield).
Key Takeaways
- TBILLYIELD in Google Sheets calculates the annualized yield of a U.S. Treasury Bill (T-bill) based on its settlement date, maturity date, and discount rate.
- The function is useful for investors to quickly compare the profitability of different T-bills without doing manual calculations.
- The syntax of the TBILLYIELD function is as follows:
=TBILLYIELD(settlement, maturity, price)
- You can combine TBILLYIELD with logical functions like IF to decide whether a T-bill meets your required return threshold.
Syntax
The TBILLYIELD in Google Sheets formula is as shown below:
=TBILLYIELD(settlement, maturity, price)
Parameters
- settlement: (required): The date on which the treasury bill is purchased and ownership transfers.
- maturity: (required): The date on which the security can be redeemed at its face value.
- price: (required): The price at which the security is bought.
How to Use TBILLYIELD function in Google Sheets?
The TBILLYIELD function in Google Sheets calculates the yield of a U.S. Treasury bill (T-bill) based on its settlement date, maturity date, and discount rate. It is commonly used in finance to quickly estimate the return an investor can expect from short-term government securities.
There are two ways to use the TBILLYIELD function in Google Sheets:
- Enter TBILLYIELD manually
- From the Google Sheets menu
Enter TBILLYIELD Manually
To use TBILLYIELD manually, let’s go through a simple example.
Step 1: Take a Google Sheet and enter the required details: Settlement Date, Maturity Date, and price. To understand how to enter the function manually, we’ll calculate the yield on a T-bill purchased on Mar 1, 2020, maturing on Jun 1, 2020, at a price of 98.
Step 2: Click on the cell where you want the result to appear, here, its B4. Type the following formula:
=TBILLYIELD(B1, B2, B3)
Here:
- B1 is the settlement date (Mar 1, 2020).
- B2 is the maturity date (Jun 1, 2020).
B3 is the price.
Step 3: Press Enter. The function will return the annualized yield of the Treasury bill.
We click on % to get the percentage yield value.
Entering TBILLYIELD Through the Menu Bar
- Go to the Insert tab and choose Function → Financial.
- Scroll down to select TBILLYIELD.
- For the arguments, enter correct cell references.
- Press Enter to get the yield result.
Examples
The primary purpose of TBILLYIELD is to calculate the yield on U.S. Treasury bills based on their settlement date, maturity date, and price. This function helps investors quickly determine the return on short-term government securities without manually applying complex financial formulas.
Example #1
Suppose you want to calculate the annualized yield of a Treasury bill. The settlement date and maturity date are 1-Jan 2020 and 21st Jan 2020, respectively. Instead of manually applying the yield formula to calculate the yield, we can use TBILLYIELD to compute it.
Step 1: Enter the details with the settlement date, maturity date, and other details. In this case, assume the settlement date is 01-Jan-2025 and the maturity date is 21-Jan-2025 (20 days later).
Step 2: Click on the cell where you want the result to appear and type the following formula:
=TBILLYIELD(B1,B2,B3)
- B1 = Settlement Date (01-Jan-2025)
- B2 = Maturity Date (21-Jan-2025)
- B3 = 95 price
Step 3: Press Enter. The function will calculate the annualized yield for the Treasury bill.
By using TBILLYIELD, we quickly calculated the return of a Treasury bill which is 9.47% annually, without manually converting the discount into annualized yield. This is especially useful when analyzing multiple bills with different maturities and prices.
Example #2
In this example, we consider two Treasury bills, one for 60 days and another for 120 days. Both have the same value of $1,000 but with different purchase prices. Let us see which gives the better annualized yield.
Step 1: Enter all the details in a Google Sheet.
- Settlement Date: 2020-05-01
- Maturity Dates: 2020-06-30 (60 days) and 2020-08-29 (120 days)
- Purchase Prices: 98 and 95.
Step 2: Use TBILLYIELD for both:
=TBILLYIELD(A2, B2, C2) – For 60-day bill
=TBILLYIELD(A3, B3, C3) – For 120-day bill
Press Enter to get both yields.
The 60-day bill annualizes to around 12% while the 120-day bill annualizes to around 15%. You can instantly compare without manually calculating yields.
Example #3
In this example, we must invest in a T-bill if its yield is greater than 9%. You buy a T-bill for with a settlement date of 1-Apr-2022. The maturity date is 1-July-2022. The price is 95.5. Let us calculate the yield.
Step 1: Enter all the details in a sheet.
Enter settlement = 2022-04-01, maturity = 2025-07-1, price 95.5.
Step 2: Use the following formula with IF:
=IF(TBILLYIELD(A2, B2, C2) > 0.09, “Good Investment”, “Not good”)
Press Enter. Sheets will calculate the yield and return a decision text.
If the yield works out to be greater than 9%, the cell will display “Good Investment”. This saves time when screening multiple T-bills quickly.
Important Things To Note
- Use the DATE function, TO_DATE function, or a cell reference to a valid date to enter the settlement and maturity dates, rather than entering them as text.
- The maturity date must be no more than one year after the settlement date.
- The function uses a 360-day year for its calculations, which is standard for Treasury bills.
- TBILLYIELD is equivalent to using YIELDDISC with US Treasury Bill conventions.
Frequently Asked Questions (FAQs)
The yield is usually around the hundreds value as it represents the price per $100 face value of the T-bill that was traded. Since investors earn by buying at a lower price and receiving the full value at maturity, the purchase price is always less than 100. For instance, if a T-bill has a face value of $1,000, it might be sold for $970. In TBILLYIELD, we enter the price argument as 98 (below 100).
Some of the functions similar to TBILLYYIELD include the following:
YIELDDISC: This is used to calculate the annual yield of a discount (non-interest-bearing) security, based on price value.
YIELD: Calculates the annual yield of a security that pays periodic interest, such as a US Treasury Bond, based on price value.
Understanding how different factors influence yield is very helpful when you wish to understand T-bills to see if they are profitable enough for you.
One can adjust the price or maturity date to see how the yield changes. The increase in purchase price often reduces the yield. As we pay more, the overall return is reduced. Similarly, the maturity date if extended, may increase the yield, if the price stays the same.
One of the important points to be noted when using TBILLYIELD is date formatting. It is essential to use a consistent format for the dates, which is recognized by Google Sheets.
One can use the DATE function for formatting.
=DATE(2023, 2, 1)
This ensures Google Sheets takes the date in the right DATE format.
Also check your sheet’s settings as it is important to check if it compatible to use in the formula.
Download Template
This article must help understand TBILLYIELD Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is TBILLYIELD Function in Google Sheets. We learn how to use TBILLYIELD function in google sheets with its syntax and examples. You can learn more from the following articles. –
Leave a Reply