MS Excel: PRICEMAT function calculate the price per $100 face value

In traditional bond structures, interest is paid periodically (e.g., semiannually). However, some bonds—such as short-term notes, certificates of deposit, or zero-coupon instruments with accrued interest—pay interest only at maturity. Excel’s PRICEMAT function is designed specifically to calculate the price per $100 face value of such instruments.

This article walks through the PRICEMAT function, detailing its structure, use cases, and financial modeling insights.

The PRICEMAT function calculates the price of a security that pays interest at maturity, taking into account:

• Issue date

• Settlement date

• Maturity date

• Annual coupon rate

• Yield to maturity

• Day-count basis

  
  

Unlike PRICE, which assumes periodic coupon payments, PRICEMAT is tailored for bonds or notes where both principal and interest are repaid together at maturity.

Syntax

=PRICEMAT(settlement, maturity, issue, rate, yld, [basis])

Parameter Breakdown:

Argument	Description
settlement	The date the bond is purchased (after issue date, before maturity).
maturity	The bond's maturity (redemption) date.
issue	The bond's issue date.
rate	The bond's annual coupon interest rate.
yld	The bond's annual yield (market rate).
basis	[Optional] Day-count basis (0–4). Default is 0 (US 30/360).

Example: Pricing a Maturity-Based Bond

Scenario: You purchase a short-term note with:

• Issue Date: Jan 1, 2025

• Settlement Date: Mar 1, 2025

• Maturity Date: Oct 1, 2025

• Annual Coupon Rate: 6%

• Market Yield: 5%

• Day Count Basis: Actual/Actual (1)

=PRICEMAT(DATE(2025,3,1), DATE(2025,10,1), DATE(2025,1,1), 0.06, 0.05, 1)

Result: $97.72

This is the price per $100 face value the investor should pay, based on the market yield and interest to be received at maturity.

This function is ideal for pricing:

• Short-term government or corporate notes

• Single-payment bonds

• Certificates of deposit with fixed maturity interest

• Custom debt instruments that accrue interest but do not pay coupons periodically

In treasury or fixed-income modeling, PRICEMAT is valuable for yield curve construction and portfolio pricing.

Related Functions

Function	Purpose
PRICE	Prices bonds with periodic coupon payments
PRICEDISC	Prices zero-coupon/discount bonds
YIELDMAT	Calculates the yield on a security that pays at maturity
ACCRINTM	Calculates interest accrued at maturity

Use YIELDMAT to reverse-engineer the yield from a given price, forming a useful pair with PRICEMAT.

Common Errors & Tips

Error	Cause
#NUM!	Settlement outside issue–maturity range, or invalid rate/yield
#VALUE!	Invalid dates or non-numeric entries

Best Practices:

• Use Excel’s DATE() function to avoid ambiguous date inputs.

• Always confirm the day-count convention with your data provider or bond term sheet.

• Match basis with market standards:

  • 0 → US 30/360 (default)

  • 1 → Actual/Actual (common for sovereign debt)

  • 2 → Actual/360

  • 3 → Actual/365

  • 4 → European 30/360

Summary Table

Component	Value
Function Name	PRICEMAT
Primary Use Case	Pricing single-payment (maturity-only) securities
Typical Instruments	Notes, CDs, zero-coupon bonds with accrued interest
Key Variables	Settlement, maturity, issue, rate, yield, basis
Output	Price per $100 face value

Final Thoughts

The PRICEMAT function is essential for accurate valuation of single-payment instruments in Excel. It’s especially useful for short-duration instruments, non-standard bond structures, or custom debt products issued by private or government institutions.

Mastering PRICEMAT helps fixed-income professionals:

• Perform more accurate bond pricing

• Model custom debt terms

• Build flexible investment and treasury models