PRICE Function
Calculates the price of a security that pays periodic interest.
Syntax
Parameters
| Parameter | Description | Required |
|---|---|---|
| settlement | Date the security is purchased. | Required |
| maturity | Date the security expires. | Required |
| rate | Annual coupon rate. | Required |
| yld | Annual yield to maturity. | Required |
| redemption | Redemption value per $100 face value. | Required |
| frequency | Number of coupon payments per year (1, 2, or 4). | Required |
| basis | Day-count convention (0 = US 30/360, default). | Optional |
Basic Example
Price of a semi-annual bond
=PRICE(DATE(2024,4,1), DATE(2034,4,1), 0.05, 0.06, 100, 2)A 10-year bond with a 5% coupon and 6% yield, priced at about $92.64 per $100 face value.
Advanced Examples
Example 1: Annual pay bond
European bondPrice of an annual-pay bond
=PRICE(DATE(2024,4,1), DATE(2034,4,1), 0.05, 0.06, 100, 1, 1)How PRICE Works
PRICE calculates the present value of all future coupon payments plus the redemption value, discounted at the yield to maturity. It assumes the bond pays regular periodic interest.
Important Notes & Limitations
Settlement must be before maturity.
Frequency must be 1, 2, or 4.
For bonds with irregular coupon periods, use ODDFPRICE or ODDLPRICE.
Common Errors & Fixes
#NUM! errorInvalid dates, rates, or frequency.Fix: Verify all inputs.
#VALUE! errorDates are not recognized.Fix: Use DATE function or valid date serial numbers.
Download Practice File
PRICE
pricePractice PRICE with Real Data
Download a sample CSV file with pre-populated data and practice exercises for the PRICE function. Works in both Excel and Google Sheets.
File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps