How to use the TBILLPRICE function
What is the TBILLPRICE function?
The TBILLPRICE function calculates the par amount (face value) for a Treasury bill.
1. Introduction
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.
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 |
How is the TBILLPRICE function calculated?
Calculation formula: TBILLPRICE = 100 * (1-(discount*DSM/360))
DSM = days between settlement to maturity ignoring maturity date that is more than a year after settlement.
2. Syntax
TBILLPRICE(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 discount rate is the interest rate used to determine how much less than par the investor will pay at auction for a given bill based on its par value and term to maturity.
Treasury bills are issued at a discount from the face value, the interest paid is the face value - purchase price.
3. Example 1
Calculate the par amount of a Treasury bill with a settlement date of 05/15/2024, a maturity date of 08/15/2024, and a discount rate of 5.5%?
The TBILLPRICE 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: 5.5% (0.055) The discount rate for the Treasury bill.
Formula in cell C6:
The formula in cell C6 returns 98.59 which represents the par amount for a given Treasury bill. The TBILLPRICE function is calculated like this:
TBILLPRICE = 100*(1-discount*DSM/360)
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
100*(1-0.055*92/360) = 98.5944444444444
$98.59 matches the value in cell C6.
4. Example 2
Calculate the par amount of a Treasury bill with a settlement date of 07/01/2024 that matures on 120 days later with a discount rate of 2.8%?
This example demonstrates how to enter Excel dates in the TBILLPRICE function.
The TBILLPRICE function has the following arguments:
- settlement: 7/1/2024 The settlement date for the Treasury bill.
- maturity: 10/29/2024 The maturity date for the Treasury bill.
- discount: 2.8% (0.028) The discount rate for the Treasury bill.
Formula in cell C6:
The formula in cell C6 returns 99.07 which represents the par amount for a given Treasury bill. The DATE function lets you calculate an Excel date based on year, month, and day. Excel dates are different than regular dates, they are whole numbers from 1 to n. For example 1/1/1900 is 1. 1/2/1900 is 2 etc. This makes it easy for Excel to manipulate Excel dates like adding subtracting etc.
DATE(2024,7,1)+120 calculates the Excel date for 7/1/2024 which is 45474. Then add 120 days as given in the question to calculate the maturity date. This returns 45594 which corresponds to date 10/29/2024.
The TBILLPRICE function is calculated like this: TBILLPRICE = 100*(1-discount*DSM/360)
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 = 7/1/2024 - 10/29/2024 = 120 days
100*(1-0.028*120/360) = 99.0666666666667
$99.07 is the same value as the result in cell C6.
5. Example 3
Calculate the par amount of a Treasury bill with a settlement date of 11/01/2024, matures on 02/01/2025, and the discount rate is 4.2%?
The TBILLPRICE 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 98.93 which represents the par amount for a given Treasury bill. The TBILLPRICE function is calculated like this: TBILLPRICE = 100*(1-discount*DSM/360)
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/1/2025 = 92 days
100*(1-0.042*92/360) = 98.9266666666667
$98.93 is the same value as the result in cell C6.
6. Why is the function not working?
The TBILLPRICE 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 TBILLPRICE 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