PRICE function

PRICE returns a bond's clean price per $100 of face value given its settlement date, maturity date, coupon rate, yield, redemption value, and payment frequency.

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

Generate a PRICE formula

Describe what you need. The generator will reach for PRICE where PRICE is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How PRICE reads its arguments
settlementrequiredmaturityrequiredraterequiredyldrequiredredemptionrequiredfrequencyrequiredbasisoptionalPRICE →
ArgumentRequiredDescription
settlementRequiredThe date (a real date or a date-returning formula) the bond changes hands; if it isn't a valid date, PRICE returns #VALUE!, and it must fall before maturity or PRICE returns #NUM!.
maturityRequiredThe date the bond's principal is repaid and the last coupon is paid; like settlement, an unrecognized date value produces #VALUE!.
rateRequiredThe bond's annual coupon rate as a decimal (0.045 for 4.5%); a negative rate returns #NUM!.
yldRequiredThe bond's annual required yield as a decimal, used to discount future cash flows; a negative value returns #NUM!.
redemptionRequiredThe redemption value per $100 of face value received at maturity (100 for par); it accepts any non-negative number, including values above or below 100 for premium/discount redemption.
frequencyRequiredThe number of coupon payments per year — 1 (annual), 2 (semiannual), or 4 (quarterly) only; any other integer returns #NUM!.
basisOptionalOptional day-count convention: 0 or omitted = US 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360; a value outside 0–4 returns #NUM!.

Returns

A single decimal number representing the bond's price per $100 face value.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Price the bond funding a region's restock order

Order IDRegionRepUnitsUnit PriceOrder Date
ORD-1001WestJ. Alvarez12024.52026-01-15
ORD-1002EastM. Chen8519.752026-04-15
ORD-1003NorthS. Patel60322026-01-15
=PRICE(F2,DATE(2031,1,15),0.045,0.05,100,2)

Result: 97.81

The finance team uses the West region order date in F2 (2026-01-15) as the settlement date for a 5-year, 4.5%-coupon bond redeemed at par with a 5% required yield, paid semiannually. Because settlement falls exactly on a coupon anniversary, PRICE discounts 10 even semiannual coupons of 2.25 plus the 100 redemption at 2.5% per period, landing at 97.81 per 100 face value.

2. Compare the same bond priced on an Actual/365 basis

Order IDRegionRepUnitsUnit PriceOrder Date
ORD-1001WestJ. Alvarez12024.52026-01-15
ORD-1002EastM. Chen8519.752026-04-15
ORD-1003NorthS. Patel60322026-01-15
=PRICE(F3,DATE(2031,1,15),0.045,0.05,100,2,3)

Result: 97.79

This time settlement is the East region order date in F3 (2026-04-15), which falls mid-way between two coupon dates, and basis is switched from the default 30/360 to 3 (actual/365). PRICE now counts real calendar days for the partial first period, which shifts the accrued-interest adjustment slightly and moves the price to about 97.79 instead of a clean coupon-date value.

3. Reprice the bond under a quarterly coupon schedule

Order IDRegionRepUnitsUnit PriceOrder Date
ORD-1001WestJ. Alvarez12024.52026-01-15
ORD-1002EastM. Chen8519.752026-04-15
ORD-1003NorthS. Patel60322026-01-15
=PRICE(F2,DATE(2030,1,15),0.045,0.05,100,4)

Result: 98.20

Keeping the West region date in F2 as settlement but shortening maturity to a 4-year bond and switching frequency to 4 means PRICE now discounts 16 quarterly coupons of 1.125 at a 1.25% quarterly rate. The shorter maturity and more frequent compounding push the price up slightly to 98.20.

Common errors

Which PRICE error are you seeing?
PRICE returned an error#NUM!
Confirm settlement predates maturity and pass frequency as exactly 1, 2, or 4 and basis as 0 through 4 (or omit basis).
#VALUE!
Wrap the source cell in DATEVALUE or reformat the column as real dates so PRICE receives a serial date number.
#NAME?
Enable the Analysis ToolPak in Excel Options > Add-ins, or open the workbook in a current Excel/Google Sheets version where PRICE is built in.
ErrorWhy it happensHow to fix it
#NUM!settlement is on or after maturity, frequency is anything other than 1, 2, or 4, or basis falls outside 0–4.Confirm settlement predates maturity and pass frequency as exactly 1, 2, or 4 and basis as 0 through 4 (or omit basis).
#VALUE!settlement or maturity is text that Excel or Sheets can't parse as a date, such as a string pulled from a mis-formatted Order Date column.Wrap the source cell in DATEVALUE or reformat the column as real dates so PRICE receives a serial date number.
#NAME?PRICE is called in an Excel version (pre-2007) where the function lived in the Analysis ToolPak add-in and that add-in isn't enabled.Enable the Analysis ToolPak in Excel Options > Add-ins, or open the workbook in a current Excel/Google Sheets version where PRICE is built in.

Tips and when to use something else

  • PRICE returns price per $100 of face value, not per share or per dollar invested — multiply by (face value / 100) to convert to actual bond price.
  • If settlement isn't on a coupon date, PRICE bakes in an accrued-interest adjustment automatically; you don't need to subtract accrued interest yourself.
  • If you just need the present value of a fixed stream of periodic payments (like a loan or annuity) rather than a bond's clean price, use PV instead of PRICE.
  • To go the other direction — solving for yield when you already know the price — use YIELD's sibling logic via RATE for simple loans, or keep the same coupon/redemption structure and adjust yld until PRICE matches your target.

Related functions

PVReturns the present value of an investment based on a series of regular payments and a constant interest rate.FVFV returns the future value of a series of regular cash flows at a constant interest rate, modeling compound growth over time.RATERATE calculates the interest rate per period for an investment or loan given regular periodic payments and present/future values.NPVNPV returns the net present value of a series of cash flows discounted to their present value today, helping you decide if an investment creates value.XNPVXNPV returns the net present value of cash flows occurring at irregular intervals, discounting each flow by actual elapsed time.

Frequently asked questions

Why does PRICE need settlement and maturity dates instead of just years to maturity?
Bond pricing depends on the exact day-count between cash flows, not just a rounded year count, so PRICE uses real calendar dates plus a basis argument to compute fractional periods precisely. This lets it price bonds that settle mid-coupon-period accurately.
Can frequency be 3 for three payments a year?
No — PRICE only accepts 1, 2, or 4, matching annual, semiannual, and quarterly coupon schedules. Passing 3 or any other value returns #NUM!.
Does PRICE give me the price I'd actually pay including accrued interest?
PRICE returns the clean (quoted) price, not the dirty price a buyer pays. If you need the settlement amount including accrued interest, add ACCRINT's output to PRICE's result separately.
Why does typing =PRICE(...) show #NAME? in my Excel file?
In Excel versions before 2007, PRICE lived in the Analysis ToolPak add-in and wasn't loaded by default. Enabling that add-in, or opening the file in a modern Excel or Google Sheets version, resolves it.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17