TBILLYIELD Function in Excel

In this article, you will learn about the TBILLYIELD function, its usage, syntax and how to use it in your excel spreadsheet.

TBILLYIELD Function in Excel

The TBILYIELD function in Excel returns the yield for a Treasury bill based on three arguments. They are the settlement date, a maturity date, and a price per $100.

Syntax

= TBILLYIELD(settlement, maturity, pr)

NOTE: Dates in the arguments needs to be entered by using the DATE function, or as results of other formulas or functions.

Arguments:

  • Settlement    – The Treasury bill’s settlement date.
  • Maturity    – The Treasury bill’s maturity date.
  • Pr    – The Treasury bill’s price per $100 face value.

Possible Errors and Usage notes

  • In Excel the dates are stored as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and any date after this would be the no. of days in total.
  • Settlement and maturity are truncated to integers.
  • When the arguments the settlement or maturity is not a valid date, the function returns the #VALUE! error value.
  • When pr ≤ 0, the function returns the #NUM! error value.
  • Either  settlement ≥ maturity, or if maturity is more than one year after settlement, the function returns the #NUM! error value.
  • The mathematical calculation for this is:

  ((100-pr)/Pr) * 360/DSM

where:

  • DSM = number of days from settlement to maturity, excluding any maturity date that is more than one calendar year after the settlement date.

How to use the TBILLYIELD Function in Excel?

Using this function in a WS is simple; all you need to do is enter the function as a formula of the cell in the formula bar.

Take a look at the given example

 to use the TBILLYIELD Function in Excel?

To find the yield of the treasury bill of the given data, enter the data in columns A, B, and C. And in column D enter the following formula.

Formula: = TBILLYIELD (A1,B1,C1)

Here, A1 refers to the cell name or the cell address.

When entered, the function gives you the yield of the treasury bill. Follow the same to other cells and you will get the yield of the treasury bill for the rest of the bills.