PRICE Function

Calculates the price of a bond that pays periodic interest.

Category

Financial

Available in

All versions, Microsoft 365

Returns

A price per $100 face value

Want to practice PRICE in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

PRICE returns what a coupon-paying bond is worth given the yield the market demands. When the yield is above the coupon rate the bond trades below 100 (a discount); below the coupon rate, above 100 (a premium).

Prices are quoted per $100 of face value, so 95.10 means you pay $951 for a $1,000 bond.

Syntax

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

Arguments

ArgumentRequired?What it does
settlementYesThe date the buyer takes ownership (after the issue date).
maturityYesThe date the security is repaid.
rateYesThe annual coupon rate.
yldYesThe annual yield.
redemptionYesThe value paid at maturity per $100 of face value (usually 100).
frequencyYesCoupon payments per year: 1 = annual, 2 = semiannual, 4 = quarterly.
basisNoDay count: 0 or omitted = US 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360.

Examples

Example 1 — Pricing the corporate bond

Northstar's treasury buys a corporate bond: 5% coupon paid twice a year, maturing 11/15/2033, settling 3/15/2026. The market yield is 5.8%.

B8fx=PRICE(B1,B2,B3,B4,B5,B6,B7)
AB
1Settlement3/15/2026
2Maturity11/15/2033
3Coupon rate5%
4Yield5.8%
5Redemption100
6Frequency2
7Basis0
8Price95.10

Example 2 — Yield falls, price rises

If yields drop to 4.5%, below the 5% coupon, the bond trades at a premium.

=PRICE(DATE(2026,3,15),DATE(2033,11,15),5%,4.5%,100,2,0) Explain

Result: 103.21.

Common mistakes

  • #VALUE! or #NUM!. A date isn't a real date, settlement is on or after maturity, or frequency isn't 1, 2 or 4.
  • The price doesn't include accrued interest. PRICE is the clean price. Add ACCRINT for the amount actually paid.

Notes

  • Enter dates with DATE or as cells holding real dates. Text dates like "3/15/2026" typed straight into the formula can be misread on computers with other regional settings.
  • Related functions: YIELD, ACCRINT, DURATION, PRICEDISC, PRICEMAT.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.