|
The Excel TBILLYIELD Function
Basic Description
The Excel TBILLYIELD function calculates the yield of a Treasury Bill.
The syntax of the function is :
TBILLYIELD( settlement, maturity, pr )
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 price per $100 face value
|
Note that the settlement and maturity 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, it is possible that
that they may be interpreted incorrectly, due to the date system and date interpretation settings on
your computer. |
| - |
Although you can input the dates as serial numbers, this is not advised, as date serial
numbering varies across different computer systems. |
Excel Tbillyield Function Example
The following example shows the Excel Tbillyield function used to calculate the yield on a treasury bill
with settlement date 01-Feb-2011, maturity date 30-Jun-2011 and a price of $99 per $100 face value:
| |
A |
B |
| 1 |
Settlement Date: |
01-Feb-2011 |
| 2 |
Maturity Date: |
30-Jun-2011 |
| 3 |
=TBILLYIELD( B1, B2, 99 ) |
The Tbillyield function in the above example returns the yield 2.44%.
Note that, as recommended, the settlement and maturity dates in the above example have been supplied to the function
as references to cells containing dates.
Further examples of the Excel Tbillyield function can be found on the
Microsoft Office website.
Tbillyield Function Errors
If you get an error from the Tbillyield function, this is likely to be one of the following:
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 pr 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 Tbillyield 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
|
|
