How to use the TBILLYIELD function
What is the TBILLYIELD function?
The TBILLYIELD function calculates the yield for a Treasury bill.
Table of Contents
1. Introduction
What is the yield of a treasury bill?
The yield of a treasury bill (T-bill) is the annualized percentage return earned on investing in the T-bill, calculated based on the bill's purchase price at auction and its par value paid at maturity. For example, if a 26-week T-bill is purchased for $980 with a $1000 par value, and held to maturity, the $20 profit represents an annual yield of approximately 2.04% over the 6 month term.
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 other Excel functions are there 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 TBILLYIELD function calculated?
Calculation formula:
Text representation of the math formula behind the TBILLYIELD function: ((100-pr)/pr)*(360/DSM)
DSM = days between settlement to maturity ignoring maturity date that is more than a year after settlement.
pr = The Treasury bill's price per $100 face value.
2. Syntax
TBILLYIELD(settlement, maturity, pr)
| settlement | Required. The Treasury bill's settlement date which is the date after the issue date. |
| maturity | Required. The date when the security expires. |
| pr | Required. The Treasury bill's price per $100 face value (par amount). |
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 par amount or face value of a treasury bill?
The par amount or face value of a treasury bill is the amount the bill will be redeemed for at maturity, typically in denominations of $1,000 up to $5 million for institutional investors.
For example, a $1,000 par value T-bill will pay $1,000 at maturity.
3. Example 1
What is the yield of a Treasury Bill with a par value of $97, and 120 days until maturity?
The TBILLYIELD function has the following arguments:
- settlement: Today. The settlement date for the Treasury bill.
- maturity: Today + 120. The maturity date for the Treasury bill.
- Pr: $97 The par value of the Treasury bill.
Formula in cell C6:
The formula in cell C6 returns 9.28% which represents the yield for the given Treasury bill. The TBILLYIELD function is calculated like this:
((100-pr)/pr)*(360/DSM)
DSM = days between settlement to maturity ignoring maturity date that is more than a year after settlement.
pr = The Treasury bill's price per $100 face value.
Lets plug the arguments in to this math formula:
DSM = 8/15/2024 - 5/15/2024 = 92 days
Pr = 97
((100-97)/97)*(360/92) = 0.0927835051546392
9.28% matches the value in cell C6.
4. Example 2
What is the yield (discount rate) of a Treasury Bill with the following details?
- Settlement Date: January 1, 2025
- Maturity Date: September 28, 2025
- Price: $101
The TBILLYIELD function calculates the yield (discount rate) of a Treasury Bill based on the following arguments:
- settlement: The settlement date of the Treasury Bill, which is January 1, 2025, represented as a serial date number.
- maturity: The maturity date of the Treasury Bill, which is September 28, 2025, represented as a serial date number.
- pr: The price of the Treasury Bill, which is $101.
Formula in cell C6:
The formula in cell C6 returns -1.32%, which represents the yield for the given Treasury Bill. The TBILLYIELD function calculates the yield using the following formula:
((100 - pr) / pr) * (360 / DSM)
- DSM = Number of days between the settlement and maturity dates, ignoring maturity dates more than a year after the settlement date. In this case, DSM = 270 days.
- pr = The price of the Treasury Bill per $100 face value, which is 101.
Substituting the values, we get: ((100 - 101) / 101) * (360 / 270) = -0.0132013201320132 = -1.32%
This matches the value in cell C6, which is the yield (discount rate) for the given Treasury Bill.
5. Example 3
What is the yield of a Treasury Bill with a par value of $97, and 120 days until maturity?
The TBILLYIELD function has the following arguments:
- settlement: 3/1/2026. The settlement date for the Treasury bill.
- maturity: 2/28/2027. The maturity date for the Treasury bill.
- Pr: $98.5 The par value of the Treasury bill.
Formula in cell C6:
The formula in cell C6 returns 1.51%, which represents the yield for the given Treasury Bill. The TBILLYIELD function calculates the yield using the following formula:
((100 - pr) / pr) * (360 / DSM)
- DSM = Number of days between the settlement and maturity dates, ignoring maturity dates more than a year after the settlement date. In this case, DSM = 364 days.
- pr = The price of the Treasury Bill per $100 face value, which is 98.5.
Substituting the values, we get: ((100 - 98.5) / 98.5) * (360 / 364) = 0.0150610810509288 = 1.51%
This matches the value in cell C6, which is the yield (discount rate) for the given Treasury Bill.
6. Why is the function not working?
The TBILLYIELD function returns:
- #VALUE! error if settlement or maturity is not a valid data type.
- #NUM! error if pr <=0'
- If settlement ≥ maturity
- if maturity is more than one year after settlement
Functions in 'Financial' category
The TBILLYIELD 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