The PRICEMAT function in Excel is designed to compute the price per $100 face value of a security that pays interest at maturity. This function is particularly useful for investors seeking to understand the yield and pricing of different securities in the market, allowing them to make informed investment decisions.

Syntax

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

• settlement: The date when the investor buys the security.
• maturity: The date when the security matures.
• rate: The annual interest rate of the security.
• yld: The annual yield of the security.
• redemption: The redemption value per $100 face value, typically $100.
• frequency: The number of coupon payments per year (1 for annual, 2 for semi-annual, etc.).
• [basis]: An optional parameter that defines the day count basis for interest calculations.

Example #1

=PRICEMAT("2023-10-01", "2024-10-01", 0.05, 0.04, 100, 1)

This formula calculates the price of a security that matures on October 1, 2024, with a 5% interest rate and a 4% yield. The result could be, for instance, $102.50 based on the data provided.

Example #2

=PRICEMAT("2023-10-01", "2025-10-01", 0.06, 0.05, 100, 2)

This example computes the price of a security maturing on October 1, 2025, with a 6% interest rate and a 5% yield, potentially resulting in a price of $103.75.

Example #3

=PRICEMAT("2023-10-01", "2023-12-31", 0.04, 0.03, 100, 1)

Here, the calculation is for a security maturing on December 31, 2023, with a 4% interest rate and 3% yield, which might give a result of $101.00.

Error handling

• VALUE!: This error occurs if any of the parameters are not of the correct data type (e.g., dates are entered as text).
• NUM!: This error indicates that one or more of the arguments are invalid, such as a settlement date being after the maturity date.
• N/A: This error happens when the yield cannot be calculated for the given parameters, typically due to missing data.

Conclusion

The PRICEMAT function is an invaluable tool for investors analyzing securities that pay interest at maturity. By allowing users to calculate the price per $100 face value based on various financial parameters, this function aids in making informed decisions regarding their investments. Utilizing this tool effectively can lead to more strategic investment choices in the financial markets.