TBILLPRICE Function

The TBILLPRICE function in Excel calculates the price of the treasury bill with a face value of $100. Treasury bills, or just the T-bills, are fixed-income securities with a maturity of fewer than 365 days or less. As a result, they help diversify the asset allocation and reduce the portfolio's overall risk.

The government generally raises money in two different ways:

• In the form of treasury bonds with maturity ranging from 3 to 30 years, paying a regular coupon payment for long-term projects, such as infrastructure.
• For short-term obligations, in the form of Treasury bills with maturity ranging from one month to 365 days issued at a discount price.

The treasury bills are usually sold in the denomination of $100 or even $1000. However, they do not pay monthly, quarterly, annual, or semi-annual coupon payments.

Instead, these money market instruments are issued at a discount price and, at the time of maturity, pay the entirety of the face value of the T-bill. 

For example, suppose the treasury bill is issued at $95 with a face value of $100. At the end of maturity, the investor will receive $100 with the $5 of the accrued interest on the T-bills since they do not make coupon payments.

The broker application can purchase the T-bills directly from TreasuryDirect or the secondary market.

Several factors can influence the treasury bill rates, such as:

• Demand from the investors and supply from the government of the T-bills.
• The T-bill rates may rise during the economic boom and fall during the recession.
• An increase or decrease in rates by the FED ultimately causes an increase or decrease in the treasury bill rates.
• High inflation will lead to falling treasury prices and higher yields, while on the other hand, low inflation will lead to rising treasury prices and lower results.

Based on all this understanding about treasury bills, we will see how you can use the TBILLPRICE function to calculate the price of the treasury bill per $100 face value.

What is the TBILLPRICE function in Excel?

The TBILLPRICE is categorized as a FINANCIAL function that calculates the price of the treasury bills based on the settlement date, maturity date, and the T-bill discount rate.

Suppose you purchase a T-bill with a settlement date of 5th August 2022 and maturity after six months, i.e., on 1st February 2023.

The discount rate on the bond is equal to 6.74%. Therefore, based on the three inputs, when you use the TBILLPRICE function, you will get a result similar to $96.63.

Thus, when the T-bill is redeemed with a face value of $100 at the end of the maturity period, you, as an investor, will get $3.37.

An important thing to remember about fixed-income securities, such as the Treasury bills or the T-bonds, is that they are backed by the full faith and credit of the US government. 

On the other hand, when you buy corporate bonds, there is a high probability that the company may fail to pay off the borrowed amount to its investors. However, the same is not valid with the T-bonds or the treasury bills.

The syntax for the TBILLPRICE function is:

=TBILLPRICE(settlement, maturity, discount)

where,

• settlement - (required) the settlement date for the treasury bill
• maturity - (required) the maturity date for the treasury bill
• discount - (required) the discount rate in percentage for the T-bill

TBILLPRICE Function Formula

What is the formula based on which the TBILLPRICE function calculates the price of the treasury bill?

Manual calculations are possible even if the function does not exist in Excel. The price is calculated using the below formula:

= 100 x (1 - (discount rate x d) / 360)

where,

• discount rate = discount rate on the treasury bill
• d = difference between the settlement date and the maturity date

For example, the difference between 11th August 2022(settlement) and 19th December 2022(maturity) equals 130 days. Therefore, the discount rate on the T-bill is equal to 5.57%. Substituting the values in the formula, we get:

= 100 x (1 - (5.57% x 130) / 360)

= 100 x (1 - (7.241) / 360)

= 100 x ((360 - 7.241) / 360)

= 100 x 0.979886

= 97.9886 or $97.99

Thus, using the formula, you can easily calculate the price of the treasury bill if the function does not exist. Unfortunately, though, the process has been present in all Excel versions since 2003.

How to use the TBILLPRICE Function?

If you are an Excel wizard, you might already know the different methods to use the function. But, on the other hand, if you have just started this journey to become an Excel master, we have your back.

In this section, we will explore two different methods for using the Excel function.

a) Method #1: From the function's library

When you use the function from the library, Excel allows you to input the arguments in an interacting interface, i.e., the dialog boxes. To use the process from the library, please follow the steps below:

1. Suppose you have the data as illustrated below:
2. Select the cell in which you intend to get the result, which in this case is cell C6.
3. Click on Formulas > Financial > select the TBILLPRICE function from the drop-down menu.
4. This will open up the dialog box as illustrated below:
5. We will input the arguments in the dialog box per the function's syntax. For example, the settlement and maturity argument only accepts serial numbers for dates.
6. The function will return an error if you input the date in mm-dd-yyyy format. For example, you cannot input the date as 5th August 2022. Instead, you need to put the serial number corresponding to the date, which in this case, is 44778.
   We can override the situation by directly referencing the cells containing the date values, which in this case, the settlement and maturity are cells C2 and C3, respectively.
   By substituting the values in the dialog box, we get:
7. We already get a preview of the price of the Treasury bill, which equals $96.63.
8. When you click on Ok, you will get the same result in the selected cell.

b) Method #2: As a worksheet formula

The method that most Excel users prefer is using the function as a worksheet formula and using the function as a worksheet formula improves work efficiency and offers more flexibility in terms of using multiple functions together.

Suppose you have the data, as illustrated below:

We begin with an equal sign, type in the function name, and input the arguments inside the parentheses.

The formula will be, =TBILLPRICE(C2,C3,C4), giving us $96.63.

You must be wondering how we got the dollar sign before our result. All you need to do is use the keyboard shortcut of Alt + H + AN and select the currency as $.

TBILLPRICE Function Example

Let's assume you want to add a treasury bill to your portfolio. Unfortunately, your only information is that both are issued at the same discount rate but with varying maturity dates.

The data for the treasury bills is as illustrated below:

To calculate the price of the treasury bill, we will use the formula =TBILLPRICE(C3,C4,C5) in cell C7, which gives us the result of $97.99. Similarly, we will use the formula =TBILLPRICE(F3,F4,F5) in cell F7, which gives the effect of $97.18.

Let's be honest here. Since the settlement date and discount rate are equal for the two treasury bills, the maturity date is the only component affecting the price.

The greater the maturity date, the lesser the price returned for the T-Bill.

You can further calculate the yields for both treasury bills using the inputs present in the spreadsheet.

The TBILLYIELD function takes in three arguments, i.e., settlement, maturity, and price, to calculate the yield on the treasury bill.

The formula in cell C8 and F8 will be =TBILLYIELD(C3,C4,C7) and =TBILLYIELD(F3,F4,F7), respectively, which gives us the result:

The yield on T-bill 2 is 5.73%, while the yield on T-bill 1 is 5.68%. 

Again, since the T-bills do not pay a coupon payment but are issued at a discount price, whatever you would earn on the treasury bill would be the difference between the purchase price and the face value received at the time of maturity.

TBILLPRICE vs. PRICE

When you type in 'price' in an Excel spreadsheet, another function you would encounter is the PRICE function.

The PRICE function in Excel returns the price of a bond with a face value of $100, paying a periodic coupon payment to the investor.

The syntax for the function is:

=PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])

where,

• settlement = (required) the settlement date of the bond
• maturity = (required) the maturity date of the bond
• rate = (required) the interest rate that the bond pays to the investor, also called the coupon rate
• yld = (required) the annual yield on the bond
• redemption = (required) redemption value of the bond per $100 face value
• frequency = (required) number of coupon payments each year
• basis = (optional) day count basis, where 0 equals to default value.

Calculating the price of the treasury bill using the TBILLPRICE function is one thing, but can you get the same result using the PRICE function? What would be the expected result?

Suppose the data looks, as illustrated below:

First, we will calculate the price using the formula =TBILLPRICE(C2,C3,C9), which gives us the result of $98.18.

On the other hand, we will use the formula =PRICE(C2,C3,C4,C5,C6,1) in cell C11, which gives us the price of $97.37.

But wait! The prices are not matching, so what should we do?

We will use the solver function to reach the price of $98.18 by changing the variable cell C5, i.e., the annual yield on the fixed income security.

To use the solver function, we will use the keyboard shortcut Alt + A + Y3, which opens up the solver parameters dialog box.

We input the arguments as stated above and click on the Solve button, which not only gives the price as $98.18 in the cell C12, but also changes the annual yield to 4.12%

Pain in the backside going through all the calculations, which is what we wanted to highlight. That is why the TBILLPRICE function is necessary since you cannot quickly get the same result without references and helps from multiple functions!

Researched and Authored by Akash Bagul | Linkedin

Reviewed and edited by Parul Gupta | LinkedIn