Quick Navigation
PRICE Function
Summary
The Excel PRICE function calculates the price per $100 face value of a bond or security that pays periodic interest. It factors in settlement date, maturity date, coupon rate, yield, redemption value, payment frequency, and day count basis for precise bond valuation.
Syntax
PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| settlement | Date |
Yes | Security's settlement date - date after issue when traded to buyer |
| maturity | Date |
Yes | Security's maturity date - when security expires |
| rate | Number |
Yes | Annual coupon interest rate |
| yld | Number |
Yes | Annual yield to maturity |
| redemption | Number |
Yes | Redemption value per $100 face value |
| frequency | Number |
Yes | Number of coupon payments per year: 1=annual, 2=semiannual, 4=quarterly |
| basis | Number |
No | Day count basis: 0=US 30/360, 1=Actual/actual, 2=Actual/360, 3=Actual/365, 4=European 30/360 |
Using the PRICE Function
PRICE is essential for bond traders, portfolio managers, and financial analysts valuing fixed-income securities. Use it to determine fair market value based on current yield and time to maturity, accounting for coupon payments and day count conventions.
Common PRICE Examples
Semiannual Coupon Bond Pricing
=PRICE(DATE(2008,2,15),DATE(2017,11,15),0.0575,0.065,100,2,0)Calculates price of bond settling Feb 15, 2008 maturing Nov 15, 2017 with 5.75% coupon, 6.5% yield, $100 redemption, semiannual payments, 30/360 basis. Result: $94.63
Quarterly Coupon Bond
=PRICE("1/15/2025","12/15/2030",0.04,0.045,100,4,1)Prices quarterly-paying corporate bond using actual/actual day count
Annual Pay Government Bond
=PRICE(DATE(2024,6,1),DATE(2034,6,1),0.03,0.0325,100,1,1)Values 10-year Treasury with annual coupons using actual/actual basis
Frequently Asked Questions
Common Errors and Solutions
#NUM!
Cause: yld<0, rate<0, redemption≤0, frequency not 1/2/4, basis<0/>4, or settlement≥maturity
Solution: Verify all numeric parameters are valid and settlement < maturity
#VALUE!
Cause: settlement or maturity not valid dates
Solution: Use DATE() function: DATE(2025,1,15) instead of text dates
#NAME?
Cause: PRICE misspelled
Solution: Check spelling: must be PRICE (all caps)
Notes
- Dates stored as serial numbers (Jan 1, 1900 = 1)
- Settlement is purchase date, maturity is expiration date
- Formula handles both N>1 and N=1 coupon periods differently
- Truncates settlement, maturity, frequency, basis to integers
- Essential for fixed income portfolio valuation
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in:
Content last reviewed: December 11, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+