TBILLYIELD Excel Formula

Author Zaheer    Category Formulas     Tags ,

If your work is engage in computing data particularly with financial matters, learning how to use the program of Microsoft Excel could help you to make your task easier and worry free. The program works in accordance with syntax, that’s why it is essential to identify and study the different functions and formulas associated to it. Every Excel’s function has its corresponding formula and codes to use. So, for you to use the program successfully you must be familiar with these diverse functions.

One of the functions that you need to be familiar with in using Excel for financial computation is the TBILLYIELD function. This function deals with the computation of the yield return of Treasury bill. Since Excel is a formula program, the syntax to use in utilizing the function is:

=TBILLYIELD(settlement, maturity, pr)

Where in:

  • settlement – this is the exact date of  the settlement. This refers to the date incurred after the issuance date for the Treasury bill was sold to the purchaser.
  • maturity – this is the date of redemption or the date when the yield will mature or the expiration date of the Treasury Bill.
  • pr – this is the cost of the Treasury bill per $100 par value

The important thing to remember about this function is that, the date value should be placed in DATE function. Once they are not keyed in or entered in the DATE function there is a huge possibility that it could be used in other calculation and will only create inaccurate result. So, to avoid this kind of mistakes all the dates used in the computation must be specific.

Common Error

There are mistakes incorporated in using the function. But all the errors obtained during the process are all manageable once the user knows where to correct them. So, to have an idea about the usual errors, they are written below:

  • #VALUE! – This usually comes out when the entered value in the settlement and maturity are considered not valid.
  • #NUM! – Once the price is less than O, this is the result shown in the computation.
  • #NUM! – This error appears if the settlement is greater than or equivalent to the value of maturity, or when the value of maturity is greater than 1 year after the date of the settlement.


To start using TBILLYIELD function, you may study first the given situation to have an idea on how to do it on Excel.

What is the yield on treasury that has settlement date of September 25, 2011 and maturity date on December 25, 2011 with a cost of $98 per $100 par value?

In this problem the given value are as follows:

settlement – September 25, 2011

maturity – December 25, 2011

pr – $98

To use Excel in the computation do the following procedure:

  • Enter the given data having their own cell on the Excel worksheet.
  • Click a blank cell and enter the syntax of TBILLYIELD function.
  • For the value on the arguments, click the reference cell.
  • Once all the data are placed, press the enter tab.
  • Immediately the answer will come out from the blank cell. For this given problem, the answer is 0.0807 or 8.07%. Therefore, 8.07 is the return yield.
  • If error comes out, check the formula and value that was entered.

Post comment