# How to use the TBILLYIELD function

**What is the TBILLYIELD function?**

The TBILLYIELD function calculates the yield for a Treasury bill.

#### Table of Contents

## 1. Introduction

**What is the yield of a treasury bill?**

The yield of a treasury bill (T-bill) is the annualized percentage return earned on investing in the T-bill, calculated based on the bill's purchase price at auction and its par value paid at maturity. For example, if a 26-week T-bill is purchased for $980 with a $1000 par value, and held to maturity, the $20 profit represents an annual yield of approximately 2.04% over the 6 month term.

**What is a treasury bill?**

A treasury bill, or T-bill, is a short-term U.S. government debt obligation backed by the Treasury Department with a maturity of 1 year or less that is sold at a discount from par in regular auctions.

**What other Excel functions are there for bonds and treasury bills?**

Function |
Description |

TBILLEQ |
Returns the bond-equivalent yield for a Treasury bill |

TBILLPRICE |
Returns the price per $100 face value for a Treasury bill |

TBILLYIELD | Returns the yield for a Treasury bill |

ACCRINT | Returns the accrued interest for a security that pays periodic interest |

ACCRINTM | Returns the accrued interest for a security that pays interest at maturity |

DURATION | Returns the annual duration of a security with periodic interest payments |

MDURATION | Returns the Macauley modified duration for a security with an assumed par value of $100 |

DISC | Returns the discount rate for a security |

INTRATE | Returns the interest rate for a fully invested security |

**How is the TBILLYIELD function calculated?**

Calculation formula:

Text representation of the math formula behind the TBILLYIELD function: ((100-pr)/pr)*(360/DSM)

DSM = days between *settlement* to *maturity* ignoring *maturity* date that is more than a year after *settlement*.

pr = The Treasury bill's price per $100 face value.

## 2. Syntax

TBILLYIELD(*settlement*, *maturity*, *pr*)

settlement |
Required. The Treasury bill's settlement date which is the date after the issue date. |

maturity |
Required. The date when the security expires. |

pr |
Required. The Treasury bill's price per $100 face value (par amount). |

**What is the Treasury bill's settlement date?**

The date after the issue date. A Treasury bill's settlement date is the date 1 business day after the auction when payment must be received and the bill is delivered to the buyer's account in exchange for the purchase price.

**What is the Treasury bill's maturity?**

The maturity date of a Treasury bill is the date when the bill's term expires, typically in 4, 8, 13, 26, or 52 weeks, and the Treasury repays the par value to the investor.

**What is the par amount or face value of a treasury bill?**

The par amount or face value of a treasury bill is the amount the bill will be redeemed for at maturity, typically in denominations of $1,000 up to $5 million for institutional investors.

For example, a $1,000 par value T-bill will pay $1,000 at maturity.

## 3. Example 1

**What is the yield of a Treasury Bill with a par value of $97, and 120 days until maturity?**

The TBILLYIELD function has the following arguments:

- settlement: Today. The settlement date for the Treasury bill.
- maturity: Today + 120. The maturity date for the Treasury bill.
- Pr: $97 The par value of the Treasury bill.

Formula in cell C6:

The formula in cell C6 returns 9.28% which represents the yield for the given Treasury bill. The TBILLYIELD function is calculated like this:

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

DSM = days between *settlement* to *maturity* ignoring *maturity* date that is more than a year after *settlement*.

pr = The Treasury bill's price per $100 face value.

Lets plug the arguments in to this math formula:

DSM = 8/15/2024 - 5/15/2024 = 92 days

Pr = 97

((100-97)/97)*(360/92) = 0.0927835051546392

9.28% matches the value in cell C6.

## 4. Example 2

**What is the yield (discount rate) of a Treasury Bill with the following details?**

- Settlement Date: January 1, 2025
- Maturity Date: September 28, 2025
- Price: $101

The TBILLYIELD function calculates the yield (discount rate) of a Treasury Bill based on the following arguments:

- settlement: The settlement date of the Treasury Bill, which is January 1, 2025, represented as a serial date number.
- maturity: The maturity date of the Treasury Bill, which is September 28, 2025, represented as a serial date number.
- pr: The price of the Treasury Bill, which is $101.

Formula in cell C6:

The formula in cell C6 returns -1.32%, which represents the yield for the given Treasury Bill. The TBILLYIELD function calculates the yield using the following formula:

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

- DSM = Number of days between the settlement and maturity dates, ignoring maturity dates more than a year after the settlement date. In this case, DSM = 270 days.
- pr = The price of the Treasury Bill per $100 face value, which is 101.

Substituting the values, we get: ((100 - 101) / 101) * (360 / 270) = -0.0132013201320132 = -1.32%

This matches the value in cell C6, which is the yield (discount rate) for the given Treasury Bill.

## 5. Example 3

**What is the yield of a Treasury Bill with a par value of $97, and 120 days until maturity?**

The TBILLYIELD function has the following arguments:

- settlement: 3/1/2026. The settlement date for the Treasury bill.
- maturity: 2/28/2027. The maturity date for the Treasury bill.
- Pr: $98.5 The par value of the Treasury bill.

Formula in cell C6:

The formula in cell C6 returns 1.51%, which represents the yield for the given Treasury Bill. The TBILLYIELD function calculates the yield using the following formula:

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

- DSM = Number of days between the settlement and maturity dates, ignoring maturity dates more than a year after the settlement date. In this case, DSM = 364 days.
- pr = The price of the Treasury Bill per $100 face value, which is 98.5.

Substituting the values, we get: ((100 - 98.5) / 98.5) * (360 / 364) = 0.0150610810509288 = 1.51%

This matches the value in cell C6, which is the yield (discount rate) for the given Treasury Bill.

## 6. Why is the function not working?

The TBILLYIELD function returns:

- #VALUE! error if
*settlement*or*maturity*is not a valid data type. - #NUM! error if
*pr*<=0'- If settlement ≥ maturity
- if maturity is more than one year after settlement

### Functions in 'Financial' category

The TBILLYIELD function function is one of 27 functions in the 'Financial' category.

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse html character entities instead of less than and larger than signs.

< becomes < and > becomes >

How to add VBA code to your comment[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

How to add a picture to your comment:Upload picture to postimage.org or imgur

Paste image link to your comment.

Contact OscarYou can contact me through this contact form