PRICE Function in Microsoft Excel: Syntax, Examples & Troubleshooting

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

All arguments except basis are required. Settlement is the purchase settlement date and maturity is the final repayment date. rate is the annual coupon rate; yld is the annual yield. redemption is the maturity amount per 100 face value. Frequency accepts 1, 2 or 4 payments per year. Annual rates remain annual even when frequency is 2 or 4; do not halve them yourself and also select semiannual frequency.

The optional basis codes are 0 or omitted = US (NASD) 30/360; 1 = actual/actual; 2 = actual/360; 3 = actual/365; 4 = European 30/360. Use DATE formulas or genuine date values; date text can be ambiguous between locales. The dates and relevant frequency/basis inputs are truncated to integers as documented, so time-of-day fractions do not create intraday accrual.

Enter =DATE(2026,1,15) in B2, =DATE(2028,1,15) in B3, 5% in B4, 5% in B5, 100 in B6, 1 in B7 and 0 in B8.

1. Enter =PRICE(B2,B3,B4,B5,B6,B7,B8) in D2.
2. Expected result: 100.
3. Independently check 5/1.05+105/1.05^2, also 100.

Settlement is on a regular coupon boundary, so there is no accrued fraction in this fixture. The future cash flows are 5 after one year and 105 after two years. Matching coupon and yield at this boundary produces par.

=PRICE(B2,B3,5%,10%,100,1,0) is expected to give approximately 91.32231405. The independent calculation is 5/1.1+105/1.1^2. The coupon payments are unchanged; the higher yield discounts them more heavily.

A zero-coupon control, =PRICE(B2,B3,0,10%,100,1,0), has expected 82.64462810, from 100/1.1^2. Microsoft rejects negative coupon rates, not a rate of zero.

=PRICE(DATE(2026,7,15),DATE(2027,1,15),6%,8%,100,1,0)

Expected price: approximately 98.92307692. The 30/360 annual coupon period has 180 convention days accrued and 180 remaining, so its fraction is one-half. Microsoft’s one-coupon formula gives 106/(1+0.08×0.5)−3.

The discounted final payment is approximately 101.92307692, and accrued coupon interest is 3 per 100. Subtracting that accrual produces the quoted price. Do not discount the fractional final period with an unverified replacement equation: Microsoft explicitly documents a separate formula when only one coupon remains.

For face value 25000, the quoted principal-price component is price×25000/100. Any accrued interest and fees required in a settlement statement need separately identified components, rather than treating this quote as automatically the total cash paid.

Microsoft documents #VALUE! for invalid dates and #NUM! for negative coupon rate or yield, redemption zero or negative, frequency outside 1/2/4, basis outside 0–4, or settlement at or after maturity. Thus a zero yield is within the listed nonnegative-rate domain, while negative yield is not.

The formula assumes the supplied regular coupon structure. It does not fetch holiday-adjusted schedules, credit information, market quotes or odd-period contract terms. Keep rates, frequency, basis and quote scale visible in a model; these examples are mechanics rather than investment advice.

Microsoft’s reference checked on October 17, 2026 lists: Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016. This current support list does not establish the first release. The introduction version and last functioning version have not been established from the reviewed documentation. No retirement is asserted.

Example verification: Expected numerical results are independently calculated; text and shape expectations follow the documented contract. These examples have not been tested in Microsoft Excel. No screenshot or native execution is claimed.

DATE in Excel | PV in Excel | NPV in Excel | ROUND in Excel

• Microsoft PRICE reference, retrieved October 17, 2026.