How to use the TBILLYIELD function
What is the TBILLYIELD function?
The TBILLYIELD function calculates the yield for a Treasury bill.
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.
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 |
TBILLYIELD function Syntax
TBILLYIELD(settlement, maturity, pr)
TBILLYIELD function Arguments
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.
TBILLYIELD function example 1
Formula in cell C6:
TBILLYIELD function example 2
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 instead of using cell references.
For example,
Date arguments are truncated to integers.
TBILLYIELD function not working
The TBILLYIELD function returns:
- #VALUE! error if settlement or maturity is not a valid data type.
- #NUM! error if pr <=0'
How is the TBILLYIELD function calculated
Calculation formula:
DSM = days between settlement to maturity ignoring maturity date that is more than a year after settlement.
Functions in 'Financial' category
The TBILLYIELD function function is one of 29 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