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

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

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

where:
  settlement = the settlement date after purchasing the T-Note/T-Bond 
  maturity = the maturity date of the T-Note/T-Bond 
  rate = the coupon rate
  yld = the YTM (Yield To Maturity)
  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

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

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

You can follow these steps to use the PRICE() function:

1. Start Excel or other spreadsheet tool.

2. Enter the following formula in a cell:

=PRICE("2025-04-30", "2027-04-30", 0.0375, 0.03795, 100, 2, 1)

3. Take the output:

99.9141

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 PRICE() function matches well the price in this example.

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

⇒ YIELD() Spreadsheet Function for T-Note/T-Bond

⇐ Spreadsheet Functions for T-Note/T-Bond

⇑ Spreadsheet Functions for T-Note/T-Bond

⇑⇑ US Treasury Securities