PRICE Function in Google Sheets: Formula, Examples & Troubleshooting
Estimate a regular coupon bond price in Google Sheets with PRICE, audit discounted cash flows, and compare yield scenarios on a per-100 basis.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Short answer
PRICE estimates the price of a security paying regular coupons from its coupon rate, required yield, dates, redemption value, and payment frequency. In the annual-coupon example below, a 6% coupon with a 5% yield gives an independently expected quote of 101.859410431 on a 100 redemption basis.
Verification: NOT_TESTED_IN_GOOGLE_SHEETS. New examples are independently discounted cash-flow calculations. Google’s public example supplies a separate documented formula/output control, not a fresh native calculation. These are formula demonstrations, not a recommendation to buy or sell a security.
Syntax and argument order
=PRICE(settlement, maturity, rate, yield, redemption, frequency, [day_count_convention])
settlement is the delivery date; maturity is the redemption date. rate is the annual coupon rate and yield the annual required yield. redemption supplies the redemption value. Frequency is 1, 2, or 4 payments per year. Basis defaults to 0: US NASD 30/360. Other documented codes are 1 Actual/Actual, 2 Actual/360, 3 Actual/365, and 4 European 30/360. Use date values rather than ambiguous date strings. Google PRICE reference.
The examples consistently use redemption 100. This keeps the quote scale visible: a result near 102 is not a price of 102 for an arbitrary face amount.
Example: a two-year annual-coupon bond
Enter:
| Cell | Meaning | Entry |
|---|---|---|
| B2 | Settlement | =DATE(2026,1,15) |
| B3 | Maturity | =DATE(2028,1,15) |
| B4 | Annual coupon rate | 6% |
| B5 | Required annual yield | 5% |
| B6 | Redemption basis | 100 |
| B7 | Payments per year | 1 |
| B8 | Day-count basis | 0 |
In B10 enter:
=PRICE(B2,B3,B4,B5,B6,B7,B8)
Expected result: 101.859410431. The independent model starts at an annual coupon boundary, with two full future coupon periods and no intervening accrued-interest fraction. It assumes a regular schedule ending on maturity.
Reproduce the quote from cash flows
The coupon is 6 per 100 of face value each year. There are two future receipts:
| Time after settlement | Receipt | Present-value calculation | Expected value |
|---|---|---|---|
| 1 year | 6 | =6/(1+5%) |
5.714285714 |
| 2 years | 106 | =106/(1+5%)^2 |
96.145124717 |
| Total | Sum of present values | 101.859410431 |
The final receipt includes both the 6 coupon and 100 redemption. Omitting either piece gives the wrong quote. The explicit arithmetic audit is =6/(1+5%)+106/(1+5%)^2.
For a hypothetical face amount of 10,000, =B10/100*10000 scales this quote to 10185.941043084. That is a scaling calculation under the example’s assumptions, not a complete broker settlement amount with fees or a general accrued-interest calculation.
Advanced application: a yield sensitivity table
Put 4%, 5%, 6%, and 7% in D2:D5. In E2 enter =PRICE($B$2,$B$3,$B$4,D2,$B$6,$B$7,$B$8) and fill through E5.
| Required yield | Independently expected quote |
|---|---|
| 4% | 103.772189349 |
| 5% | 101.859410431 |
| 6% | 100.000000000 |
| 7% | 98.191981832 |
With all cash flows fixed, raising the discount rate lowers their present value. The 6% row equals 100 because the coupon rate matches the required yield in this full-period model. This is a numerical sanity check, not evidence that every irregular security follows the same simplified formula.
Use full-precision cells for scenario differences. Display rounding to two decimals is suitable for a summary, but early rounding can distort a later inversion or comparison.
Google’s published price control
Google’s public sheet explicitly prints =PRICE(B1,B2,B3,B4,B5,B6,B7). Its inputs are settlement February 15, 2008, maturity November 15, 2017, coupon 5.75%, yield 6.5%, redemption 100, frequency 2, and basis 0. The displayed result is 94.63436162. Published PRICE example.
An independent regular-coupon audit discounts twenty coupons of 2.875 at a half-year rate of 3.25%, starting half a coupon period after settlement, discounts the final redemption, and subtracts 1.4375 of accrued coupon. It gives 94.634361621. That agreement supports this quoted-price interpretation; it is not proof of every date-boundary or irregular-coupon rule.
Troubleshooting and limits
- Quote off by a large factor: inspect the redemption scale and whether 6% was entered as 0.06 rather than 6.
- Plausible but different result: compare coupon frequency, day-count basis, settlement, and maturity with the source instrument.
- Date problem: use DATE-created values; a formula containing date-like slash notation can perform arithmetic instead of constructing the intended date.
- Expected a total cash settlement: distinguish the quoted price scale from face-value scaling, accrued interest, and transaction charges.
- Unusual instrument: irregular first/last coupons, negative yields, exact invalid-input error codes, and every month-end convention were not established here. Do not extrapolate these regular positive-yield examples to them.
Software, versions, and references
This guide follows Google’s PRICE contract and public example. Similar function names in other products do not establish identical validation or historical behavior. Introduction and retirement dates were not established.
Related: DATE, POWER, and SUM.
First-party references reviewed October 17, 2026: Google PRICE help and its public example above.