TBILLEQ Function
It calculates the Bond Equivalent Yield (BEY) for the treasury bills, also called the T-bills.
What is the TBILLEQ Function?
The TBILLEQ function in Excel calculates the bond equivalent yield(BEY) for the treasury bills, also called the T-bills.
The bond equivalent yield can be used to calculate the annualized gain for the fixed income securities with maturities less than a year.
It allows you to have a fair comparison, for instance, between long-term bonds and zero coupon bonds/T-bills that are about to mature in less than a year.
With the help of comparison results from other fixed-income securities, you can easily make the best decisions to build up your portfolio as an investor. But what are treasury bills?
Treasury bills, or T-bills, are money market instruments usually issued at a discount price from their face value and are redeemed at face value (par value) after maturity. For example, if you purchase a T-bill for $96, you will get $100 at maturity.
The additional $4 you receive is the accrued interest earned on the T-bills. They do not make any regular interest payments. They imitate the characteristics of a zero coupon bond, with the only exception being that the former has a maturity period of less than a year.
A T-bill can have a maturity period ranging from 1 month to 364 days, where an increasing period tends to give a greater yield to the investors.
The treasury bills can be purchased directly from TreasuryDirect or the secondary market through the broker application.
The interest rates are the most important economic factors affecting the T-bills' price. The interest rates have an inverse effect on the debt instrument, i.e., when the interest rates go up, the cost of the T-bills falls.
On the other hand, when the interest rates go down, the price of the T-bills goes up.
Based on this general idea about the Treasury bills, we will see how you can use the TBILLEQ function to calculate the bond equivalent yield (BEY) for the treasury bills so that you can compare it with fixed-income securities based on annual profits.
Key Takeaways
- As you know, treasury bills are short-term financial instruments with a maturity of less than one year. If you input the maturity date greater than 365 days post settlement date, you will get the #NUM! Error.
- Another reason to get the #NUM! Error is when you input the settlement date more significant than the maturity date.
- A non-numeric value such as a text string will return the #VALUE! Error for the function. Similarly, if the maturity and settlement argument does not date, you will likely get the #VALUE! Error.
- If you get the result in decimal digits, click Home > General drop-down box > select Percentage. After selecting the result, you can also use another keyboard shortcut of Ctrl + Shift + %.
Bond equivalent yield (BEY) Formula
What is bond equivalent yield generally, and how do you calculate it?
A bond equivalent yield is a benchmark that allows investors to calculate the annualized gains in the form of percentages for short-term fixed-income securities.
The formula for the bond equivalent yield of a T-bill is:
= (Face value - Buying price) / (Buying Price) x (365/d)
where,
- Face value = face value or the par value of the treasury bill.
- Buying price = the amount that the investor pays for the treasury bill
- d = days until the maturity of the treasury bill
The (Face value - Buying price) / Buying Price part of the formula calculates the return on the investment, while the 365/d component of the procedure returns the annualized returns.
Suppose you purchase a zero coupon bond at $95.23 with a face value of $100. The bond will mature after 177 days, after which the investor will earn the amount equal to the face value of the zero coupon bond.
To find the return on the bond, we will use just the first portion of the formula, i.e.,
= (Face value - Buying price) / Buying price
By substituting the values, we get
= (100 - 95.23) / 95.23
= 4.77 / 95.23 = 0.050089 or 5.01%
Thus, the return on the T-bill is equal to 5.01%. Next, we will use the other half of the formula to find the annualized return, i.e.,
= 365 / d
By substituting the value, we get the result as
= 365 / 177= 2.062147
Finally, by multiplying both results, the bond equivalent yield will be
= 5.01% x 2.062147 = 0.103291 or 10.33%
Using these manual calculations, you can easily compare different fixed-income securities for their annualized returns and make informed decisions on what to add to your portfolio.
Note
Do not confuse the BEY formula and the result that TBILLEQ returns. The TBILLEQ function is based on an entirely different formula that you will find in the subsequent section.
It is a generalized formula for fixed-income securities with short-term tenure of less than a year, paying monthly, quarterly, or semi-annual coupon payments.
understanding The TBILLEQ function
The TBILLEQ is categorized as a financial function that finds the bond equivalent yield (BEY) for the treasury bill.
To calculate the TBILLEQ for a treasury bill, you require four things:
- The settlement date for the security
- The maturity date for the security
- The purchase price for the T-bill
- Face value(par value) after the maturity
The settlement date is the T+2 day after the purchase of a security, whereas the maturity date is when the investor gets repaid their original investment along with accrued interest.
Both components are easy to understand. But why do we need the T-bill's purchase price and face value?
The latter two components are needed to calculate the discount rate for treasury bills.
The difference between the face value and the amount the investor pays for the T-bill is called the discount rate for the treasury bill, which is usually expressed in percentages.
Suppose that the price of the treasury bill is $97.43, and the face value is $100. Then the discount rate will be calculated using the formula:
Discount % = (List Price - Selling Price) / List Price * 100
where,
- List price = face value of the T-bill.
- Selling price = the amount that the investor pays for the debt instrument
Thus, the discount rate in percentage would be =(100-97.43)/100*100, equal to 2.57%. We would get the bond equivalent yield of 2.63% using the function.
We would get the bond equivalent yield of 2.63% using the function.
What if the function did not exist? What would be the formula if you wanted to make similar calculations?
In that case, the equivalent formula for the TBILLEQ function is:
= (365 x discount rate) / (360 - (discount rate x d))
where,
- Discount Rate = discount rate on the treasury bill
- d = difference between the settlement and maturity date in days
For example, the difference between 11th August 2022 and 19th December 2022 is 130 days. The discount rate on the T-bill is 2.57%. Using the formula, the manual calculations will be:
= (365 x 2.57%) / (360 - (2.57% x 130)
= (9.3805) / (360 - (3.341)) = (9.3805) / (356.659)
= 0.026301 or 2.63%
If you don't want to make all those manual calculations, your best bet is to use the TBILLEQ function!
How to use the TBILLEQ function
To use the function, you have two options - either use it from the functions library or as a worksheet formula. In this section, we will explore both methods and then you can decide what works best for you.
Method #1: From the functions library
When you use the function from the library, Excel describes every argument in the syntax while you input it into the dialog box. If you are a beginner, all this information about how the function works helps.
1. Select the cell where you intend to get the function result.
2. Click on Formulas > Financial > Select the TBILLEQ function from the drop-down list.
3. This will open up the dialog box, as illustrated below:
4. As you can see, the dialog box explains the 'settlement' argument as the Treasury bill's settlement data is expressed as a serial date number.
If you input a date here in mm-dd-yyyy format, the function will not accept it as a valid argument and return an error.
Hence, you should either directly reference the cells that contain a date in the text boxes or input the serial numbers.
We will input 44784 and 44974, corresponding to 11th August 2022 and 19th December 2022, for 'Settlement' and 'Maturity,' respectively.
5. The preview for the bond equivalent yield for the arguments equals 0.026301033.
6. If you click on Ok, you will get the same result in the selected cell.
WSO Tip
If you need to know what the serial numbers for the date are, the easiest method is just to input the date in the spreadsheet and press the Ctrl + tilde symbol (~) key on the keyboard.
Method #2: As a worksheet formula
The easiest method is where you don't have to worry whether the dates are in mm-dd-yyyy format or serial numbers.
Suppose that you have the data in Excel, as illustrated below:
To use the function as a worksheet formula, you begin with the equal sign in the selected cell, type the function name and finally input the arguments inside the parentheses.
The formula you will use to calculate the bond equivalent yield (BEY) will be =TBILLEQ(C2, C3, C4), which gives the result 0.026301.
If you need the result in percentage, all you need to do is press the keyboard shortcut Alt + H + P to change the number format to percentage, and then Alt + H + 0 (twice) to shift decimals away from zero.
Example for the TBILLEQ function
Suppose you have two T-bills and one Treasury bond with varying days to maturity. First, you must decide what fixed-income instrument to add to your portfolio based on the yield they offer to the investor.
The data looks as illustrated below:
Since we are comparing the T-bills with the bond, it is necessary to find one standard metric: the bond equivalent yield for the T-bills.
To calculate the bond equivalent yield(BEY), we will use the formula =TBILLEQ(C3, C4, C5) in cell C7, which gives us the result of 0.06451.
Similarly, we can use the same formula, i.e., =TBILLEQ(F3, F4, F5) in cell F7, giving the result 0.06366.
Changing both the results into percentage format using the Alt + H + P and then Alt + H + 0 (twice) to shift the zeros away from the decimal point, we get
Now we have the bond equivalent yields for both the T-bills. So all that is left is to calculate the yield on the T-bond you want to purchase, paying out a coupon rate of 6%.
The formula to calculate the yield will be =YIELD(I3, I4, I5, I6, I7,1), which after changing the formatting to percentage, gives us the result as 6.31%.
As you can see, based on our options, the highest yield is offered by T-bill 1. Hence, the investor should prefer to add T-bill 1 to their portfolio.
If you want to read more about the YIELD function, check out our dedicated article that will help calculate the yield on the bonds!
or Want to Sign up with your social account?