TBILLPRICE
The TBILLPRICE function is classified under Excel Financial Functions. Functions List of the most important Excel functions for financial analysts. This cheat sheet covers hundreds of functions that are essential to know as an Excel analyst. The function will calculate the fair market value of a Treasury bill / bond.
In financial analysis, TBILLPRICE can be useful in determining the value of a bond. Bond Pricing Bond pricing is the science of calculating the issue price of a bond based on coupon, face value, yield and time to maturity. Bond pricing allows investors to decide whether or not an investment should be made. It helps calculate the FMV of a treasury bill when we receive the settlement, maturity and discount rate information.
Formula
TBILLPRICE = 100 x [1 – discount x DSM / 360 ]
or
where:
-
DSM = number of days from settlement to maturity, excluding any maturity date that is more than one calendar year after the settlement date.
The TBILLLPRICE function (function syntax) takes the following arguments:
1. Settlement (argument required) – This is the settlement date of the Treasury bill. The security’s settlement date is the date after the issue date when the Treasury bill is exchanged to the buyer.
2. Maturity (required argument) – This is the maturity date of the treasury bill. The maturity date is the date on which the treasury bill expires.
3. Discount (mandatory argument) – The discount rate for the Treasury bill.
Example on Excel
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
| Data | Description | |
| 3/31/2008 | Settlement date | |
| 6/1/2008 | Maturity date | |
| 9.0% | Percent discount rate | |
| Formula | Description | Result |
| =TBILLPRICE(A2,A3,A4) | The price for the Treasury bill, using the terms in A2, A3, and A4. | $ 98.45 |
Some notes on the TBILLPRICE function:
- #NUMBER! error – Occurs if:
- The settlement date is greater than or equal to the due date
- The maturity date is greater than 1 year after the settlement date
- The delivered discount argument is less than or equal to 0.
- #VALUE! error – Occurs if:
- All arguments provided are not numeric
- Settlement or maturity arguments are not valid dates.
- Settlement and maturity are truncated to whole numbers.
- The payment and due date arguments should be supplied to the function as follows:
- References to cells containing dates
- Dates returned by other functions or formulas
Questions and Answers
1. As a simple example, say you want to buy a $1,000 Treasury bill with 180 days to maturity, yielding 1.5%. Calculate the price!
Answer:
To calculate the price, take 180 days and multiply by 1.5 to get 270. Then, divide by 360 to get 0.75, and subtract 100 minus 0.75. The answer is 99.25. Because you’re buying a $1,000 Treasury bill instead of one for $100, multiply 99.25 by 10 to get the final price of $992.50.
Please note that Treasury doesn’t make separate interest payments on Treasury bills. Instead, the discounted price accounts for the interest that you’ll earn. For instance, in the example above, you’ll receive $1,000 at the end of the 180-day period. Because you only paid $992.50, the remaining $7.50 represents the interest on your investment over that time frame.
Treasury bill quotes can look complicated, but it’s pretty easy to figure out the price. With just a few simple calculations, you can convert quotes to Treasury-bill prices, and know what you’ll need to pay in order to invest.
Sources: PinterPandai, The Motley Fool, Math Works
Photo powered by Midjourney