Microsoft Knowledge Base

Excel: TBILLYIELD()/TBILLPRICE() Functions Don't Correspond
Last reviewed: September 12, 1996
Article ID: Q120731
The information in this article applies to:
  • Microsoft Excel for Windows, versions 4.0, 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 4.0, 5.0

SYMPTOMS

The results of the TBILLPRICE() and TBILLYIELD() do not correspond: if you use the price returned by TBILLPRICE() for the pr argument in the TBILLYIELD() function, the returned discount rate is different from the discount argument used in the TBILLPRICE() formula.

CAUSE

TBILLPRICE() and TBILLYIELD() are not interchangeable, and they do not return corresponding results.

STATUS

This behavior is by design of Microsoft Excel. The non-corresponding results are expected behavior for these functions.

MORE INFORMATION

TBILLPRICE() and TBILLYIELD() have distinct purposes for Treasury Bond calculations. The key difference between these functions lies in the discount rate and yield. These two factors are neither the same for the functions, nor are they interchangeable. 

Factor          Definition of factor


DISCOUNT RATE   The amount by which the sale price of a note is less
                than its face value ($100), not the price paid. The
                purpose of discounting is to adjust the yield upward
                either in lieu of interest or in addition to the
                interest.

YIELD           The effective annual amount of income being accrued on
                an investment. This is based on the price paid for the
                TBill.

MORE INFORMATION

An investment in TBills will yield a return when held until maturity because TBills are issued at a discount from face value. The Treasury does not set the amount of the discount. The Federal Reserve auctions these negotiable bills to the highest bidder. With this technique, the Treasury lets the current market establish the yield each TBill will earn. The higher the discount (lower purchase price) of the TBill, the higher the yield.

TBILLPRICE() and TBILLYIELD() are available as part of the Analysis ToolPak add-in.

TBILLPRICE

If you provide the settlement date, maturity date, and discount rate, you can use this function to obtain the price per $100 face value of a Treasury bill.

TBILLYIELD

If you know the settlement date, maturity date, and Treasury bill price per $100 face value, this function returns the yield for a Treasury bill.

REFERENCES

"Money Market Calculations: Yields, Break-Evens, and Arbitrage," Marcia Stigum and John Mann.

"Function Reference," version 4.0, pages 429-430

"Running Excel 5 for Windows", page 516

KBCategory: kbusage
KBSubcategory:

Additional Reference Words: 4.00 5.00 TBILL bond



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 12, 1996
©1997 Microsoft Corporation. All rights reserved. Legal Notices.