Investing
Analyzing U.S. Treasury Bill Investments with Microsoft Excel
Excel provides three add-in financial functions for analyzing United States Treasury bills: TBILLEQ, which calculates the bond-equivalent yields; TBILLPRICE, which calculates the price of a Treasury bill; and TBILLYIELD, which calculates the yield on a Treasury bill.
The Treasury bill functions use a set of standard arguments: the settlement date, the maturity date, the discount rate, and the price. The settlement date specifies the date the bill is settled, or purchased. The maturity date specifies the date the bill matures, or expires. (You may enter these date arguments either as text strings enclosed in quotation marks or as serial date values.) The discount rate specifies the annual discount rate used to price the bill. The price specifies the price per $100 of face value.
NOTE: Excel uses only the integer portion of the arguments you supply to the Treasury bill functions. If you enter an argument with decimal values, Excel truncates the argument to just its integer component.
All three functions return an error value if the settlement or maturity date isn?t a valid date, if the discount rate is less than zero, if the settlement date falls after the maturity date, or if the maturity date isn?t within one year of the settlement date.
TBILLEQ
The TBILLEQ function calculates the bond-equivalent yield for a Treasury bill given its settlement date, maturity date, and a discount rate. It uses the following syntax:
TBILLEQ(settlement,maturity,discount)
For example, if you want to calculate the equivalent bond yield on a Treasury bill if the settlement date is April 8, 2001, the maturity date is July 15, 2001, and the discount rate is 3%, you use the following formula:
=TBILLEQ(?4/8/2001?,?7/15/2001?,.03)
The function returns the value .03067, or 3.067%.
TBILLPRICE
The TBILLPRICE function calculates the price per $100 of face value for a Treasury bill given the settlement date, the maturity date, and the discount rate. It uses the following syntax:
TBILLPRICE(settlement,maturity,rate)
For example, if you want to calculate the price on a Treasury bill if the settlement date is April 8, 2001, the maturity date is July 15, 2001, and the discount rate is 3%, you use the following formula:
=TBILLPRICE(?4/8/2001?,?7/15/2001?,.03)
The function returns the value 99.1833, which means that you would pay $99.1833 for each $100 of Treasury bill face value.
TBILLYIELD
The TBILLYIELD function calculates the yield delivered by a Treasury bill given the settlement date, maturity date, and price. It uses the following syntax:
TBILLYIELD(settlement,maturity,price)
For example, if you want to calculate the yield on a Treasury bill if the settlement date is April 8, 2001, the maturity date is July 15, 2001, and the price is 99.1833, you use the following formula:
=TBILLYIELD(?4/8/2001?,?7/15/2001?,99.1833)
The function returns the value 0.0302482, which is equivalent to 3.0248%.
Stephen L. Nelson is the author of more than 100 books including the MBA?s Guide to Microsoft Excel (from which this article was created). More recently, Nelson has been writing about forming limited liability companies and setting up LLCs including Do-it-yourself llc incorporation and llc formation kits for all fifty states. Contact him at http://www.llcsexplained.com |
Stephen Nelson
Tags: bonds, treasury bill, investing, Similar articles
An Analysis of Journal Communications (JRN)
Journal Communications (JRN) is comprised of seven essentially separate businesses: The Milwaukee Sentinel, Community Newspapers, Television Stations, Radio Stations, Telecommunications, Printing Services, and Direct Marketing. Read more →An Annuity Based Pension Might Just be the Answer
Of all types of income generating investments, annuities are some of the most controversial. There is a body of opinion that says they are a complete waste of time and you would do much better if you were to place the capital sum on the stockmarket or invest in property. Read more →Annuity Brokers
Potential annuity holders need to shop around for insurance companies to know the rates of return available on different types of annuities. People may seek the help of annuity brokers. Read more →Annuity Quotes
To gain an understanding of annuities, we need to start at the beginning. In the year 1740, the Presbyterian Church began to use annuities in order to aid widows and the priestly order. Read more →Aphorism
The easiest and surest way for a fund to achieve the top quartile in investment performance among peer funds is to achieve the bottom quartile in expenses.
John Bogle