PRICEMAT Function in Google Sheets: Formula, Examples & Troubleshooting
Price a maturity-interest security with PRICEMAT in Google Sheets. Work through issue, settlement and maturity dates, audit accrued interest, and compare required yields.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =PRICEMAT(settlement,maturity,issue,rate,yield,basis) calculates a price for a security whose interest is paid at maturity. Keep the original issue date: it determines interest earned before settlement as well as the amount payable at maturity.
Verification: NOT_TESTED_IN_GOOGLE_SHEETS. Google help and its public example outputs were reviewed October 17, 2026. The new worked model is independently calculated, not a fresh native run.
Syntax and dates in the correct order
=PRICEMAT(settlement, maturity, issue, rate, yield, [day_count_convention])
| Argument | Meaning |
|---|---|
settlement |
Buyer settlement date |
maturity |
Security redemption date |
issue |
Original issue date |
rate |
Annual interest rate on the security |
yield |
Required annual yield |
day_count_convention |
Optional basis, default 0 |
Basis 0 is US NASD 30/360; 1 is Actual/Actual; 2 is Actual/360; 3 is Actual/365; 4 is European 30/360. Google recommends genuine date values. There is no coupon-frequency argument: the security pays interest at maturity rather than periodically. Google PRICEMAT reference.
The function’s date order is not chronological order. The first argument is settlement, while issue is third. In the example below, the chronology is issue, settlement, maturity.
Example: buy after interest has begun accruing
| Cell | Label | Entry |
|---|---|---|
| B2 | Settlement | =DATE(2026,4,15) |
| B3 | Maturity | =DATE(2027,1,15) |
| B4 | Issue | =DATE(2026,1,15) |
| B5 | Annual interest rate | 6% |
| B6 | Required annual yield | 5% |
| B7 | Basis | 0 |
- In B9 enter
=PRICEMAT(B2,B3,B4,B5,B6,B7). - Display at least six decimal places while auditing. Expected quoted price: 100.668675 per 100.
- Keep the dates as date cells, not quoted text.
- Distinguish the quoted price from an amount including accrued interest.
For these ordinary mid-month dates, the basis-0 intervals are 90 days from issue to settlement, 360 from issue to maturity, and 270 from settlement to maturity. The independent audit is:
=(100+100*B5*360/360)/(1+B6*270/360)-100*B5*90/360
The maturity payment in this model is 106. Its value at settlement using the required simple yield over the remaining three-quarters of a convention year is 102.168675. Subtract 1.5 accrued interest to obtain the quoted price 100.668675.
These components explain why substituting settlement for issue would change the answer: it would remove the pre-settlement accrued interest and alter the maturity interest.
Cross-check against Google’s published example
Google’s published example settles April 1, 2011, matures March 31, 2015, was issued January 1, 2011, and uses interest 4.5%, yield 2.5%, and basis 0. The published price is 107.1704545. A 30/360 audit uses 1,530 issue-to-maturity days, 1,440 settlement-to-maturity days, and 90 issue-to-settlement days:
(100 + 100*0.045*1530/360)/(1+0.025*1440/360)
- 100*0.045*90/360
That independently reproduces the displayed price and supports the accrued-interest subtraction used in the new example. Published Google PRICEMAT result.
Advanced application: required-yield sensitivity
Enter 4%, 5%, and 6% in D2:D4. In E2 enter =PRICEMAT($B$2,$B$3,$B$4,$B$5,D2,$B$7) and fill down.
| Required yield | Expected quoted price |
|---|---|
| 4% | 101.412621 |
| 5% | 100.668675 |
| 6% | 99.935407 |
The contractual maturity payment remains 106. Raising the required yield lowers the settlement value of that payment; the accrued-interest component is unchanged because the issue date, settlement date, interest rate, and basis have not changed.
For a separately specified face amount of 10,000 in B11, =B11*B9/100 models a quoted-price amount of 10,066.867470. The modeled accrued-interest amount is =B11*1.5/100, or 150. Their sum is 10,216.867470, before any fees or other settlement adjustments. Keep the quoted price and these separately modeled cash amounts in different cells.
Errors and troubleshooting
Google publishes #NUM! for the same April 1, 2011 settlement when issue is changed to January 1, 2012, after settlement. Published invalid-date example.
Check these points before replacing errors with zero:
- Issue should not be after settlement; maturity should follow settlement for this model.
- Annual interest and annual yield are different inputs. Enter 6% and 5%, not 6 and 5.
- Use the agreed basis consistently. Actual days and 30/360 convention days are not interchangeable.
- Do not add a frequency argument copied from PRICE. PRICEMAT’s sixth argument is the basis.
- Do not interpret a per-100 price as the total amount for a position.
The reference does not establish every invalid-input error token or cross-year Actual/Actual detail. This guide avoids unsupported error mappings and uses a transparent basis-0 example rather than importing another engine’s implementation.
Availability and related guides
Google currently documents PRICEMAT. Its introduction date, earliest functioning version, and any retirement date remain unestablished. Native Google execution and other-product parity have not been verified. PRICE addresses periodic interest; PRICEMAT addresses interest paid at maturity, so changing only the function name is not a valid conversion.
Related: DATE, DAYS360, and ROUND.
References checked October 17, 2026: Google PRICEMAT contract and published example.