The Google Sheets function PRICEMAT is a powerful tool designed to determine the price of a financial security that pays interest at maturity. This function is particularly useful for investors and financial analysts who need to assess the present value of a bond or similar security based on its yield to maturity. By inputting the necessary parameters, users can effortlessly compute the price of securities, enabling informed investment decisions.

Syntax

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

• settlement: The date when the security is purchased.
• maturity: The date when the security matures.
• rate: The annual coupon rate of the security.
• yld: The expected yield for the security.
• redemption: The amount the security will be redeemed at maturity.
• [basis]: (optional) The type of day count basis to use. If omitted, it defaults to 0.

Example #1

=PRICEMAT("2023-10-01", "2025-10-01", 0.05, 0.04, 1000)

This function calculates the price of a security purchased on October 1, 2023, that matures on October 1, 2025, with an annual interest rate of 5% and an expected yield of 4%, redeeming at $1,000. The result will show the present value based on these inputs.

Example #2

=PRICEMAT("2024-01-01", "2026-01-01", 0.06, 0.05, 2000)

This example assesses the price of a security bought on January 1, 2024, maturing on January 1, 2026, with a coupon rate of 6% and an expected yield of 5%, redeeming at $2,000. The output will indicate how much this security is worth today.

Example #3

=PRICEMAT("2023-05-15", "2025-05-15", 0.07, 0.06, 1500, 1)

In this instance, the function calculates the price of a security purchased on May 15, 2023, maturing on May 15, 2025, with a 7% interest rate, an expected yield of 6%, redeeming for $1,500, using the actual/actual basis. The result provides a current price based on these parameters.

Error handling

• VALUE!: This error occurs if any of the parameters are non-numeric when they should be, such as using text instead of numbers.
• NUM!: This happens when the input values are incompatible, for example, if the maturity date precedes the settlement date.
• REF!: This error indicates an invalid reference, usually caused by cells that contain errors or are deleted.

Conclusion

In summary, the PRICEMAT function in Google Sheets is an invaluable asset for financial professionals and investors alike. By effectively calculating the price of a security that pays interest at maturity based on the expected yield, users can make more informed decisions regarding their investments. Understanding the parameters and potential errors associated with this function ensures accurate and efficient use in financial modeling.