How to use the TBILLEQ function
What is the TBILLEQ function?
The TBILLEQ function calculates the equivalent bond yield for a Treasury bill. It converts the T-bill discount rate to an equivalent annual yield for comparison to bonds.
Table of Contents
1. Introduction
What is a treasury bill?
A treasury bill, or T-bill, is a short-term U.S. government debt obligation backed by the Treasury Department with a maturity of 1 year or less that is sold at a discount from par in regular auctions.
What is the the equivalent bond yield for a Treasury bill?
The equivalent bond yield approximates the annual yield on a T-bill if held to maturity. It accounts for the fact that T-bills are issued at a discount and redeemed at par. The equivalent yield allows comparison between T-bills and coupon bonds.
What is the difference between TBILLYIELD an TBILLEQ fuctions?
TBILLYIELD calculates a T-bill's discount yield based on the purchase price.TBILLYIELD calculates the straight T-bill discount yield.
TBILLEQ converts T-bill discount yields to an annualized bond equivalent yield for comparison. TBILLEQ provides the equivalent bond yield. TBILLEQ lets you compare T-bill yields vs bond yields.
Excel functions for bonds and treasury bills
| Function | Description |
| TBILLEQ | Returns the bond-equivalent yield for a Treasury bill |
| TBILLPRICE | Returns the price per $100 face value for a Treasury bill |
| TBILLYIELD | Returns the yield for a Treasury bill |
| ACCRINT | Returns the accrued interest for a security that pays periodic interest |
| ACCRINTM | Returns the accrued interest for a security that pays interest at maturity |
| DURATION | Returns the annual duration of a security with periodic interest payments |
| MDURATION | Returns the Macauley modified duration for a security with an assumed par value of $100 |
| DISC | Returns the discount rate for a security |
| INTRATE | Returns the interest rate for a fully invested security |
How is the TBILLEQ function calculated?
Calculation formula:
TBILLEQ = (365 * rate)/(360-(rate*DSM))
DSM = days between settlement to maturity ignoring maturity date that is more than a year after settlement.
2. Syntax
TBILLEQ(settlement, maturity, discount)
| settlement | Required. The Treasury bill's settlement date which is the date after the issue date. |
| maturity | Required. The date when the security expires. |
| discount | Required. The Treasury bill's discount rate. |
What is the Treasury bill's settlement date?
The date after the issue date. A Treasury bill's settlement date is the date 1 business day after the auction when payment must be received and the bill is delivered to the buyer's account in exchange for the purchase price.
What is the Treasury bill's maturity?
The maturity date of a Treasury bill is the date when the bill's term expires, typically in 4, 8, 13, 26, or 52 weeks, and the Treasury repays the par value to the investor.
What is the Treasury bill's discount rate?
The Treasury bill's discount rate refers to the interest rate used to calculate the price of a T-bill when it is issued.
3. Example 1
Calculate the bond-equivalent yield for a Treasury bill with a settlement date of 05/15/2024, a maturity date of 08/15/2024, and a discount rate of 3.5%?
The TBILLEQ function has the following arguments:
- settlement: 05/15/2024 The settlement date for the Treasury bill.
- maturity: 08/15/2024 The maturity date for the Treasury bill.
- discount: 3.5% (0.035) The discount rate for the Treasury bill.
Formula in cell C6:
The formula in cell C6 returns 3.58% which represents the equivalent bond yield for a Treasury bill. The TBILLEQ function is calculated like this:
TBILLEQ = (365 * rate)/(360-(rate*DSM))
DSM = days between settlement to maturity ignoring maturity date that is more than a year after settlement.
Lets plug the arguments in to this math formula:
DSM = 8/15/2024 - 5/15/2024 = 92 days
(365*0.035)/(360-(0.035*92)) = 0.0358063792813499
3.58% is what we get in cell C6 as well.
4. Example 2
Find the bond-equivalent yield for a Treasury bill purchased on 07/01/2024 that matures on 10/01/2024 with a discount rate of 2.8%?
In this example, the TBILLEQ function is being used to calculate the bond-equivalent yield for a Treasury bill with the following details:
- Settlement date: 07/1/2024 (The date when the Treasury bill was purchased)
- Maturity date: 10/1/2024 (The date when the Treasury bill matures and the face value is paid)
- Discount rate: 2.8% or 0.028 (The discount rate at which the Treasury bill was purchased, expressed as a decimal fraction of the face value)
The formula in cell C6:
The TBILLEQ function and passing in the values from cells C2 (settlement date), C3 (maturity date), and C4 (discount rate) as arguments. The TBILLEQ function calculates the bond-equivalent yield using the following formula:
TBILLEQ = (365 * rate) / (360 - (rate * DSM))
rate is the discount rate (0.028 in this case)
DSM is the number of days between the settlement date and the maturity date, ignoring any maturity date that is more than a year after the settlement date.
In this example, the DSM (days between settlement and maturity) is calculated as: DSM = 10/1/2024 - 07/1/2024 = 92 days
Plugging in the values, the TBILLEQ calculation becomes:
TBILLEQ = (365 * 0.028) / (360 - (0.028 * 92))
= 10.22 / (360 - 2.576)
= 10.22 / 357.424 = 0.0285934912037244 or 2.86%
This matches the result displayed in cell C6 which represents the bond-equivalent yield for the given Treasury bill details.
5. Example 3
A Treasury bill is purchased on 11/01/2024 and matures on 02/01/2025. If the discount rate is 4.2%, what is the bond-equivalent yield?
The TBILLEQ function has the following arguments:
- settlement: 11/1/2024 The settlement date for the Treasury bill.
- maturity: 2/1/2025 The maturity date for the Treasury bill.
- discount: 4.2% (0.042) The discount rate for the Treasury bill.
Formula in cell C6:
The formula in cell C6 returns 4.3% which represents the equivalent bond yield for a Treasury bill. The TBILLEQ function is calculated like this:
TBILLEQ = (365 * rate)/(360-(rate*DSM))
DSM = days between settlement to maturity ignoring maturity date that is more than a year after settlement.
Lets plug the arguments in to this math formula:
DSM = 11/1/2024 - 2/15/2024 = 92 days
(365*0.042)/(360-(0.042*92)) = 0.0430453534604758
4.30% is what we get in cell C6 as well.
6. Tips and tricks
Treasury bills are issued at a discount from the face value, the interest paid is the face value - purchase price.
Keep in mind to use the DATE function if you enter dates in the function as constant (hard coded values) instead of using cell references.
For example,
Date and time arguments are truncated to integers meaning the time part is removed.
7. Why is the function not working?
The TBILLEQ function returns:
- #VALUE! error if settlement or maturity is not a valid data type.
- #NUM! error if
- discount <=0 (zero)
- settlement > maturity, or if maturity is more than a year after the settlement
Functions in 'Financial' category
The TBILLEQ function function is one of 27 functions in the 'Financial' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form