How to use the function YIELD() in Excel and other spreadsheet tools for US Treasury Notes/Bonds?

YIELD() is a function provided in Microsoft Excel, Apache OpenOffice and other spreadsheet tools. It allows you to calculate the YTM (Yield To Maturity) from a give price of a debt security that pays periodic interest. It can be used to calculate the YTM for a US Treasury Note/Bond (T-Note/T-Bond).

=YIELD(settlement, maturity, rate, price, redemption, frequency, basis)

where:
  settlement = the settlement date after purchasing the security
  maturity = the maturity date of the security
  rate = the coupon rate of the security
  price = the purchase price 
  redemption = the redemption value 
  frequency = number of coupon payments per year
  basis = the day count basis:
    0: US (NASD) 30/360
    1: Actual/actual
    2: Actual/360
    3: Actual/365
    4: European 30/360

We can try to apply the YIELD() to a US Treasury Note/Bond (T-Note/T-Bond) by setting:

redemption = $100.00 
frequency = 2, coupon paid semiannually
basis = 1: Actual/actual

1. Start Excel or other spreadsheet tool.

2. Enter the following formula in a cell:

=YIELD("2025-04-30", "2027-04-30", 0.0375, 99.9141, 100, 2, 1)

3. Take the output:

0.037950 (3.795% as the YTM - Yield To Maturity)

4. Compare it with the following 2-year T-Note auction example:

Term and Type of Security: 2-Year Note
CUSIP Number: 91282CMY4
Series: BA-2027 

Interest Rate: 3-3/4% (Coupon rate: 3.75%)
High Yield: 3.795% (the highest accepted YTM)
Price: 99.914113 (the lowest accepted price)
Accrued Interest per $1,000: None

Issue Date: 2025-04-30
Maturity Date: 2027-04-30

As you can see, the output of the YIELD() function matches well with the YTM in this example.

You can also use our online calculator to validate the result. Click this link "T-Note 91282CMY4 settled on 2025-04-30 with a price of $99.9141", you will see the YTM of 3.795% displayed.

⇒ US Treasury TIPS

⇐ PRICE() Spreadsheet Function for T-Note/T-Bond

⇑ Spreadsheet Functions for T-Note/T-Bond

⇑⇑ US Treasury Securities