Function: PRICEMAT

by | Aug 22, 2019 | Excel Formulas

PRICEMAT

The Excel PRICEMAT function is a Financial formula that calculates and returns the price per $100 face value of a security that pays interest at maturity. In this guide, we’re going to show you how to use the PRICEMAT function and also go over some tips and error handling methods.

Supported versions

  • All Excel versions

Excel PRICEMAT Function Syntax

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

Arguments

settlement The security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
maturity The security's maturity date. The maturity date is the date when the security expires.
issue A serial date number expresses security's issue date.
rate The security's interest rate at date of issue.
yld The security's annual yield.
[basis]

Optional. The type of day count basis to use.

0 or omitted: US (NASD) 30/360

  • 1: Actual/actual
  • 2: Actual/360
  • 3: Actual/365
  • 4: European 30/360

PRICEMAT Function Example

The following example shows how to calculate the price per $100 face value of a security issued on 1/1/2019, with a settlement date of April 31st, 2019, and maturity date of February 2nd 2024. The annual rate of interest is 5.0%, and the annual yield rate is 6.0%. The payments will be made based on the US (NASD) 30/360 day count.

Download Workbook

Tips

  • Microsoft recommends using the DATE or other functions which can return a date serial number.
  • Excel keeps date and time values as numbers. Excel assumes that Jan 1st, 1900 is 1, and every subsequent date value is based on this. While whole numbers represent days, decimals represent time values. For example; 1/1/2018 is equal to 43101, and 12:00 is equal to 5.
  • Please see Day count convention article in Wikipedia to learn more about the [basis] types.
  • Settlement, maturity, and basis are truncated to integers.
  • Other price-related functions:
    • PRICE returns the price per $100 face value of a security that pays periodic interest.
    • PRICEDISC returns the price per $100 face value of a discounted security.
    • YIELD returns the yield on a security that pays periodic interest.
    • DURATION returns the Macauley duration for an assumed par value of $100.

Common Issues

  • If settlement, maturity or issue are not valid dates, the PRICEMAT function returns the #VALUE! error value.
  • If rate < 0, PRICEMAT returns the #NUM! error value.
  • The PRICEMAT returns the #NUM! error value if yld < 0.
  • If [basis] < 0 or if [basis] > 4, PRICEMAT returns the #NUM! error value.
  • If settlement ≥ maturity, PRICEMAT returns the #NUM! error value.