The YIELDMAT function in Google Sheets is a powerful tool used to compute the annual yield of a security that pays interest at maturity. This function is especially useful for investors looking to evaluate the profitability of bonds or other fixed-income securities. By using this function, you can quickly determine how much yield you can expect based on the price you pay for the security and other relevant parameters.

Syntax

YIELDMAT(settlement, maturity, issue, rate, price, [basis])

• settlement: The date on which the security is purchased.
• maturity: The expiration date when the security reaches the end of its term.
• issue: The date on which the security is issued.
• rate: The annual interest rate of the security.
• price: The purchase price of the security.
• basis: (Optional) The type of day count basis to use. If omitted, it defaults to 0 (US (NASD) 30/360).

Example #1

=YIELDMAT("2023-01-01", "2025-01-01", "2022-01-01", 0.05, 950)

This example calculates the yield for a security that matures on January 1, 2025. The settlement date is January 1, 2023, the issue date is January 1, 2022, and the security has an annual rate of 5% with a purchase price of $950. The expected yield would be approximately 5.237% if evaluated based on the input data.

Example #2

=YIELDMAT("2023-05-15", "2024-05-15", "2023-01-01", 0.04, 975, 1)

In this case, the function calculates the yield of a security bought on May 15, 2023, with a maturity date of May 15, 2024, and an annual interest rate of 4%. The purchase price is $975. The yield results in approximately 4.051%.

Example #3

=YIELDMAT("2024-07-01", "2025-07-01", "2024-01-01", 0.03, 920, 2)

This example computes the yield for a security with a settlement date of July 1, 2024, maturing a year later. It has an annual rate of 3% and a purchase price of $920. The function would return about 3.914%.

Error handling

• VALUE!: This error occurs when one of the function parameters is in an incorrect format, such as a non-date format for settlement, maturity, or issue dates.
• NUM!: This indicates that one or more of the inputs are outside of a valid range, such as negative prices or rates.
• N/A!: This error arises when the function is unable to calculate a yield due to insufficient information or inappropriate parameters.

Conclusion

The YIELDMAT function is an essential tool for anyone dealing with fixed-income securities. By providing a straightforward method to calculate the annual yield based on various parameters, investors can make informed decisions regarding their investments. Mastery of this function can lead to more strategic financial planning and increased profitability in fixed-income portfolios.