Financial
PRICE Function in Excel
Returns the price per $100 face value of a security that pays periodic interest.
Syntax
- =PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])
Arguments
- settlement (required): Settlement date
- maturity (required): Maturity date
- rate (required): Annual coupon rate
- yld (required): Annual yield
- redemption (required): Redemption value per $100
- frequency (required): Payments per year
- basis (optional): Day count basis
Examples
- =PRICE("2/15/2024", "11/15/2027", 0.0575, 0.065, 100, 2) - Bond price - Result: Price
Excel PRICE function documentation
- PRICE returns the price per $100 face value of a security paying periodic interest.
- Syntax: =PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis]).
- Use PRICE when converting market-required yield into fair bond valuation.
- Related calculations often pair with [YIELD](/functions/yield/), [PV](/functions/pv/), [NPV](/functions/npv/), and [Financial functions](/categories/financial/).
PRICE inputs and conventions
- settlement and maturity must be valid serial dates.
- rate is coupon rate and yld is market yield for discounting coupon stream.
- frequency can be annual, semiannual, or quarterly based on coupon terms.
- basis specifies day-count convention; align with bond market documentation.
Step-by-step: value a coupon bond from target yield
- Step 1 — Capture settlement, maturity, coupon rate, and redemption terms.
- Step 2 — Input market yield assumption from treasury curve or credit spread model.
- Step 3 — Compute =PRICE(settlement,maturity,rate,yld,redemption,frequency,basis).
- Step 4 — Compare calculated price with quote to identify premium or discount.
- Step 5 — Run what-if analysis over multiple yield scenarios.
PRICE vs PV vs YIELD
- PRICE applies bond conventions directly and outputs clean price per 100.
- PV is flexible but requires manual coupon schedule modeling.
- YIELD reverses the problem by solving rate from observed price.
- In bond workflows, PRICE and YIELD are natural inverse tools.
Worked examples to copy
- Semiannual bond price: =PRICE(DATE(2026,7,1),DATE(2030,7,1),0.045,0.052,100,2,0).
- Quarterly coupon case: =PRICE(settle,mat,coupon,market_yield,100,4,1).
- Discount bond signal: PRICE below 100 when yld is above coupon.
- Premium bond signal: PRICE above 100 when yld is below coupon.
People also ask
- PRICE gives #VALUE! — Ensure all date arguments are valid dates.
- Why does price fall when yield rises? — Inverse bond price-yield relationship.
- Can PRICE include accrued interest? — Function returns price per convention; verify clean vs dirty quote handling.
- How to connect to return analysis? — Combine with [IRR](/functions/irr/) or scenario-based [NPV](/functions/npv/).
Common errors
- #NUM! if parameters invalid
Use cases
- Bond valuation
- Fixed income
- Investment analysis
Frequently asked questions
- What is PRICE function used for? PRICE calculates the price per $100 face value of a bond that pays periodic interest. Given the yield you require, it tells you what to pay for the bond. Essential for bond trading and portfolio management.
- Why do bond prices move opposite to yields? When market yields rise, existing bonds with lower coupons become less attractive, so their prices fall. PRICE shows this: same bond with yield 5% vs 6%: =PRICE(..., 0.05, ...) > =PRICE(..., 0.06, ...). Higher required yield = lower price.
- How do I calculate if a bond is trading at premium or discount? Compare PRICE to 100 (par value). If PRICE > 100, it's at a premium (coupon > yield). If PRICE < 100, it's at a discount (coupon < yield). A 6% coupon bond when yields are 5%: =PRICE(..., 0.06, 0.05, ...) > 100.
Editorial review
- Reviewed by Excel.Directory Editorial Team. Updated May 2026.
When to use PRICE
- Bond valuation — common Financial scenario for PRICE.
- Fixed income — common Financial scenario for PRICE.
- Investment analysis — common Financial scenario for PRICE.
PRICE in the Financial category
- Browse all Financial functions at /categories/financial/ for related formulas.
- PRICE syntax: =PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])
- settlement (required): Settlement date
- maturity (required): Maturity date
- rate (required): Annual coupon rate
- yld (required): Annual yield
- Confirm PRICE arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
Formula checklist before you copy down
- Confirm PRICE arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
- If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
- Spot-check three known input rows manually against expected output.
Related Excel functions
- YIELD (/functions/yield/): Returns the yield on a security that pays periodic interest.
- PRICEDISC (/functions/pricedisc/): Returns the price per $100 face value of a discounted security.
- PRICEMAT (/functions/pricemat/): Returns the price per $100 face value of a security that pays interest at maturity.
Errors to watch for
- #NUM! if parameters invalid — review causes on linked error pages in the directory.
Copy-paste audit workflow
- Enter PRICE on three test rows with known expected output documented on a QA tab.
- Fill down only after absolute references are locked on lookup tables and rate tables.
- Compare against manual calculation or a calculator for financial and statistical functions.
- Search this directory for comparison guides when choosing between similar functions in the same category.
PRICE worked examples to copy
- =PRICE("2/15/2024", "11/15/2027", 0.0575, 0.065, 100, 2) — Bond price. Expected result: Price.
PRICE reference summary for crawlers and offline review
- PRICE belongs to the Financial category in Excel. Returns the price per $100 face value of a security that pays periodic interest.
- Full syntax: =PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis]). Open /functions/price/ for parameters, FAQs, and related pages.
- Common mistakes: #NUM! if parameters invalid
- Pair this function with comparison guides when another Excel formula might fit the same task better.
- Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.