|
The Excel TBILLEQ Function
Basic Description
The Excel TBILLEQ function calculates the bond-equivalent yield for a Treasury Bill.
The syntax of the function is :
TBILLEQ( settlement, maturity, discount )
Where the arguments are as follows:
| settlement |
- |
The settlement date of the treasury bill (ie. the date that the bill is purchased)
|
| maturity |
- |
The maturity date of the treasury bill (must be greater than, and within one year of, the settlement date)
|
| pr |
- |
The treasury bill's percentage discount rate
|
Note that the date arguments should be supplied to the function as either:
- References to cells containing dates
or
- Dates returned from other functions or formulas
Warning:
| - |
If you attempt to supply the date arguments as text, they may be interpreted
incorrectly, due to the date system and date interpretation settings on your computer. |
| - |
You can input the dates as serial numbers. However, this is not advised, as date serial
numbering varies across different computer systems. |
Excel Tbilleq Function Example
The following example shows the Excel Tbilleq function used to calculate the bond-equivalent yield for a
treasury bill with settlement date 01-Feb-2011, maturity date 30-Jun-2011 and a discount of 2.5%:
| |
A |
B |
| 1 |
Settlement Date: |
01-Feb-2011 |
| 2 |
Maturity Date: |
30-Jun-2011 |
| 3 |
=TBILLEQ( B1, B2, 2.5% ) |
The above Tbilleq function returns the value 0.02561224, (i.e.
2.56%).
Note that, in the above example:
-
As recommended, the date arguments are supplied to the function as references to cells containing dates.
-
The discount rate of 2.5% is input as a percentage. However, this could be input as the simple numeric value 0.025.
Further examples of the Excel Tbilleq function can be found on the
Microsoft Office website.
Tbilleq Function Errors
The most common Excel Tbilleq function errors and their causes are listed in the following table:
Common Errors
|
#NUM!
|
-
|
Occurs if either:
| - | the settlement date is ≥ maturity date or the maturity
date is more than 1 year after the settlement date |
| or | |
| - |
The supplied discount argument is ≤ 0 |
|
|
#VALUE!
|
-
|
Occurs if either:
| - | any of the supplied arguments are non-numeric |
| or | |
| - | the settlement or the maturity arguments are not a valid dates |
|
|
#NAME?
|
-
|
Occurs when Analysis ToolPak add-in is not enabled in your Excel.
You will need to enable the add-in if you want to use the Excel Tbilleq function.
To do this in Excel 2003 :
- From the Tools drop-down menu, select the option Add-Ins ...
- An 'Add-Ins' window will pop up. From this, select the option Analysis ToolPak and click OK
To do this in Excel 2007 or Excel 2010 :
- Click the Microsoft button on the top left of your spreadsheet and select
the Excel Options button
- From the menu on the left hand side, select Add-Ins
- In the 'Manage:' box, select Excel Add-ins and click Go...
- An 'Add-Ins' window will pop up. From this, select the option Analysis ToolPak
and click OK
|
|
