The YIELD function in Google Sheets is a powerful financial tool that calculates the annual yield of securities, like US Treasury Bonds, which offer periodic interest payments. This allows investors to assess the return on their investments based on the price of the security, helping them make well-informed financial decisions.

Syntax

YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])

• settlement: The date when the security is purchased.
• maturity: The date when the security expires.
• rate: The annual interest rate of the security.
• pr: The price of the security.
• redemption: The amount paid at maturity, typically the face value of the security.
• frequency: The number of interest payments per year (e.g., 1 for annual, 2 for semiannual).
• basis: (Optional) The type of day count basis to use. Types include 0 for US (NASD) 30/360 and 1 for actual/actual.

Example #1

=YIELD(DATE(2023,1,1), DATE(2028,1,1), 0.05, 950, 1000, 2)

This function calculates the yield of a bond purchased on January 1, 2023, maturing on January 1, 2028, with a 5% interest rate, bought for $950, and redeemable for $1000, which is expected to give a yield of approximately 6.22%.

Example #2

=YIELD(DATE(2023,6,1), DATE(2033,6,1), 0.04, 980, 1000, 2)

Here, the YIELD function determines the yield for a bond acquired on June 1, 2023, that matures on June 1, 2033, at a 4% annual interest rate, with a purchase price of $980 and a redemption value of $1000, yielding around 4.38%.

Example #3

=YIELD(DATE(2024,1,15), DATE(2029,1,15), 0.03, 1025, 1000, 1)

This function call will calculate the yield on a bond purchased on January 15, 2024, maturing January 15, 2029, with a 3% interest rate, purchased for $1025, yield estimates to be about 2.69%.

Error handling

• VALUE!: Triggered when one or more arguments are of the incorrect data type, such as using text instead of dates.
• NUM!: Occurs if the formula parameters lead to invalid calculations, such as a negative frequency value.
• N/A: Indicates that the calculation cannot be performed when the parameters do not yield a numeric result, often due to an invalid date.

Conclusion

In summary, the YIELD function is an essential tool for investors looking to evaluate the annual return on bonds or similar securities. By understanding the function’s parameters and outputs, users can make better financial choices based on accurate yield calculations.