PRICEMAT function

The PRICEMAT function in Excel calculates the price of a security with an irregular maturity (such as a Treasury bill or other discount instruments) based on its face value, the yield to maturity, and the time to maturity. It is typically used for pricing short-term securities like Treasury bills that do not make periodic interest payments.

Syntax

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

Parameters

1. settlement: The bond’s settlement date, which is the date after issuance when the bond is traded to the buyer. This must be a valid date in Excel.
2. maturity: The bond’s maturity date, which is the date when the bond will expire and the issuer will pay the face value to the bondholder. This must also be a valid date in Excel.
3. rate: The bond’s annual coupon rate expressed as a decimal. For example, for a 5% coupon, use 0.05.
4. yld: The bond’s annual yield expressed as a decimal. For example, for a 4% yield, use 0.04.
5. [basis] (optional): The day count basis to use in the calculation. This is a numerical value that determines how the day count is computed:
   • 0 = US (NASD) 30/360
   • 1 = Actual/actual
   • 2 = Actual/360
   • 3 = Actual/365
   • 4 = European 30/360

   The default is 0 (US 30/360).

How It Works

The PRICEMAT function calculates the price of a discounted security using the formula:

$$\text{Price} = \frac{\text{Face Value}}{(1 + \text{Yield} \times \frac{\text{Days to Maturity}}{360})}$$

Where:

• Face Value is typically $100 for securities priced at a discount.
• Yield is the annual yield to maturity (as a decimal).
• Days to Maturity is the number of days from the settlement date to the maturity date.

Example

Let’s say you have a Treasury bill with the following information:

• Settlement Date: 2025-01-15
• Maturity Date: 2026-01-15
• Coupon Rate: 0% (as it is a discount instrument)
• Yield to Maturity: 4% (0.04)
• Day count basis: 0 (US 30/360)

To calculate the price of the Treasury bill, you would use the following formula:

=PRICEMAT("2025-01-15", "2026-01-15", 0, 0.04)

This formula will return the price of the Treasury bill based on the given information.

Important Notes

• Settlement and maturity dates must be valid Excel dates.
• Rate is typically set to 0 for discount instruments (Treasury bills).
• Yld is the yield to maturity expressed as a decimal.
• The [basis] is optional, and if not provided, it defaults to 0 (US 30/360).

Summary

The PRICEMAT function is useful for calculating the price of securities that are issued at a discount, such as Treasury bills, based on their yield to maturity, settlement date, and maturity date. By understanding the inputs, investors can use this function to price short-term debt instruments.