How to Use the PRICEMAT and YIELDMAT Functions in Google Sheets for Securities Paying Interest at Maturity

Valuing fixed-income securities requires understanding how and when cash flows occur. While many standard corporate and sovereign bonds pay periodic coupon interest (such as semi-annual or annual disbursements), short-term debt instruments, certificates of deposit (CDs), and specific commercial paper pay their entire accumulated interest in a single lump sum when the instrument matures. In Google Sheets, pricing these instruments and assessing their annualised return cannot be handled accurately by standard coupon functions like PRICE or YIELD. Instead, financial analysts rely on two specialised formulas: PRICEMAT and YIELDMAT.

This guide explains the mathematical mechanics, syntax requirements, day-count conventions, and real-world spreadsheet implementations of both PRICEMAT and YIELDMAT in Google Sheets.

Understanding Securities Paying Interest at Maturity

Unlike zero-coupon bonds (which are sold at a deep discount and pay zero nominal interest rate) or coupon-bearing debentures (which distribute regular interest payments across their lifespan), maturity-interest securities carry an explicit coupon rate that accumulates from the issue date and is disbursed entirely alongside the principal redemption at maturity.

Because there are no intermediate cash flows, the pricing and yield formulas evaluate the exact fraction of time between the initial issue date, the transaction settlement date, and the ultimate maturity date. Using standard periodic formulas for these instruments introduces substantial compounding and day-count errors into your portfolio valuations.

Syntax and Parameter Requirements

The PRICEMAT Function

The PRICEMAT function computes the clean price per £100 (or $100) nominal face value of a security that pays interest solely at maturity:

=PRICEMAT(settlement, maturity, issue, rate, yield, [day_count_convention])

The YIELDMAT Function

Conversely, the YIELDMAT function solves for the expected annual yield of a maturity-interest security given its purchase price:

=YIELDMAT(settlement, maturity, issue, rate, price, [day_count_convention])

Detailed Argument Breakdown

• settlement: The date on which the buyer acquires and pays for the security (must be on or after the issue date and strictly before the maturity date).
• maturity: The final date on which the security expires and the principal and accumulated interest are repaid.
• issue: The original date when the security was created and began accruing interest.
• rate: The annual coupon interest rate specified on the security at issuance (expressed as a decimal or percentage, e.g., 0.045 for 4.5%).
• yield (PRICEMAT): The required annual discount rate or market yield expected by the investor.
• price (YIELDMAT): The price paid per 100 face value units of the security.
• day_count_convention (Optional): The financial day-count basis used to calculate accrued days and year lengths:
  • 0 or omitted: US (NASD) 30/360.
  • 1: Actual/Actual (exact calendar days divided by exact days in the year).
  • 2: Actual/360 (common in money market paper and commercial notes).
  • 3: Actual/365 (standard UK money market convention).
  • 4: European 30/360.

Step-by-Step Practical Modelling in Google Sheets

Scenario: Pricing a Corporate Note Paying Interest at Maturity

Suppose your treasury department purchases a short-term commercial note with the following parameters:

• Issue Date: 15 January 2026
• Settlement Date: 15 April 2026
• Maturity Date: 15 January 2027
• Issue Coupon Rate: 5.25% (0.0525)
• Required Market Yield: 4.75% (0.0475)
• Convention: Actual/365 (Option 3)

To calculate the fair market price in Google Sheets, structure your cells as follows:

A1: Parameter        | B1: Value
A2: Issue Date       | B2: 2026-01-15
A3: Settlement Date  | B3: 2026-04-15
A4: Maturity Date    | B4: 2027-01-15
A5: Annual Rate      | B5: 5.25%
A6: Market Yield     | B6: 4.75%
A7: Day Count Basis  | B7: 3
A8: Fair Clean Price | B8: =PRICEMAT(B3, B4, B2, B5, B6, B7)

Google Sheets evaluates the equation and returns approximately 100.38 per 100 nominal value. Because the required market yield (4.75%) is lower than the note’s contractual coupon rate (5.25%), the instrument trades at a premium to par.

Calculating Market Yield with YIELDMAT

If the same security is offered on the secondary market at an ask price of £99.85, enter the YIELDMAT formula to verify the true annualised return:

=YIELDMAT(B3, B4, B2, B5, 99.85, B7)

This formula returns 5.46%, confirming that acquiring the paper below par delivers an effective yield exceeding the nominal coupon rate.

Day-Count Convention Impact Comparison

Selecting the correct day-count basis is essential when pricing short-term money market securities, as slight variations in day counting produce significant differences on large capital sums:

Basis Code	Day-Count Convention	Primary Asset Classes	Formula Example
0	US (NASD) 30/360	US corporate bonds, standard municipal debt	=PRICEMAT(B3, B4, B2, B5, B6, 0)
1	Actual/Actual	US Treasury bonds and notes, sovereign gilts	=PRICEMAT(B3, B4, B2, B5, B6, 1)
2	Actual/360	US Treasury bills, commercial paper, Euronotes	=PRICEMAT(B3, B4, B2, B5, B6, 2)
3	Actual/365	UK treasury bills, sterling deposits, Commonwealth paper	=PRICEMAT(B3, B4, B2, B5, B6, 3)
4	European 30/360	Eurobonds, continental European corporate debt	=PRICEMAT(B3, B4, B2, B5, B6, 4)

Common Errors and Troubleshooting

• #NUM! Error: Occurs when settlement >= maturity or settlement < issue. Verify that date sequences strictly follow chronological order: issue <= settlement < maturity.
• #VALUE! Error: Occurs if dates are supplied as raw unparsed strings that Google Sheets interprets as text. Always supply date arguments using cell references containing formatted dates or the DATE(year, month, day) wrapper.
• Discrepancies with Broker Statements: Brokers often quote the dirty price (which includes accrued interest already owed to the seller). PRICEMAT returns the clean price. To reconcile, calculate accrued interest separately and add it to the clean price.