The TBILLPRICE function in Google Sheets is useful when you need to calculate the price of a US Treasury Bill based on its given discount rate.
This function works similarly with the PRICEDISC function but with US Treasury Bill conventions. A Treasury Bill or T-Bill is a government security that matures in a timespan of one year or less.
The rules for using the
TBILLPRICE function in Google Sheets are as follows:
- The function requires the settlement date, maturity date, and discount rate of the security in question.
- The output of this function is the price of the given T-Bill
Let’s take a look at a quick example!
In this example, let’s say you wanted to purchase a Treasury Bill security. We know that the settlement date, or the date when the security is delivered to you, is on January 1st, 2021. The T-Bill matures six months later on July 2nd, 2021. Given a discount rate of 3%, what is the price of the US Treasury Bill?
TBILLPRICE function, we can add all our provided details to return the price of the US Treasury bill. Later in the article, we’ll explore how to write this function ourselves in Google Sheets and test it out using real values in a sample worksheet.
The Anatomy of the TBILLPRICE Function
So the syntax (the way we write it) of the
TBILLPRICE function is as follows:
=TBILLPRICE(settlement, maturity, discount)
Let’s dissect this thing and understand what each of these terms means:
- = the equal sign is how we start any function in Google Sheets.
- TBILLPRICE() is our
TBILLPRICEfunction. It computes the price of a US Treasury Bill based on a given discount rate.
- settlement refers to the date after issuance of the security when the said security is delivered to the buyer.
- maturity refers to the security’s maturity date when the security can be redeemed at par value.
- discount refers to the discount rate of the bill when the buyer purchased it.
A Real Example of Using TBILLPRICE Function
Let’s look into an example of the
TBILLPRICE function being used in a Google Sheet spreadsheet.
In the worksheet shown below, we have set up a Treasury Bill Price calculator. We have our values for the settlement date, maturity date, and discount rate. With these values, we can then compute the price of our Treasury Bill.
We can see now that given the current details, the price of our Treasury Bill is $96.96. To get this value, we just need to use the following formula:
=TBILLPRICE(B1, B2, B3)
You can make a copy of the spreadsheet above using the link I have attached below.
In the second example below, we use the
TBILLPRICE function to compare two different treasury bill options. Using the
TBILLPRICE function, we now know that Option A is cheaper in price.
We have now shown multiple examples of the
TBILLPRICE function in use. If you’re ready to test out the
TBILLPRICE function in Google Sheets, let’s begin writing it ourselves!
How to Use TBILLPRICE Function in Google Sheets
- To set up our Treasury Bill Price calculator, we should set up the arguments needed.
- In cell B4, we can start typing in our function. We just simply type the equal sign ‘=‘ to begin the function, followed by ‘TBILLPRICE(‘.
- A tooltip box may appear with info on the
TBILLPRICEfunction. If necessary, we can click on the arrow on the top-right-hand corner of the box to minimize it.
- The next step is to type in the arguments we already placed in the sheet in cells B1:B3. Afterward, we can simply hit the Enter key to let the function evaluate.
Frequently Asked Questions (FAQ)
- Why does my formula return a #NUM! error?
TBILLPRICEfunction will not evaluate if the time between the settlement date and the maturity date is over a year. This is a limitation of the function since US Treasury Bills have a maturity of a year or less.
The discount argument must also be a positive value otherwise, the function will also return a #NUM! error.
- Why does my formula return a #VALUE! error?
If you receive a #VALUE! error, you can check if either the settlement date or the maturity date is invalid. You may write in your dates using the
DATEformula or use data validation to avoid bad input for your date arguments to ensure validity.
Alternatively, there may be a non-numeric value in one of your arguments that might be causing the error.
That’s all you need to know on how to use the
TBILLPRICE function in Google Sheets. This step-by-step guide shows how easy it is to compute the price of a US Treasury Bill given its discount rate.
You can now use the
TBILLPRICE functions in Google Sheets together with the various other Google Sheets formulas available to create great worksheets that work for you.
You can stay notified of new guides like this one by subscribing to our newsletter!