# YIELD

In this comprehensive guide, we will explore the YIELD function in Excel, which is used to calculate the yield of a security that pays periodic interest. This function is particularly useful for investors and financial analysts who need to determine the yield on bonds or other fixed-income investments. We will cover the syntax, examples, tips and tricks, common mistakes, troubleshooting, and related formulae for the YIELD function.

## YIELD Syntax

The YIELD function in Excel has the following syntax:

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

Where:

• settlement is the date when the security is purchased or settled.
• maturity is the date when the security expires or matures.
• rate is the annual interest rate of the security.
• pr is the security’s price per \$100 face value.
• redemption is the security’s redemption value per \$100 face value.
• frequency is the number of interest payments per year (1 for annual, 2 for semi-annual, and 4 for quarterly).
• [basis] (optional) is the day count basis to be used (0 for 30/360, 1 for actual/actual, 2 for actual/360, 3 for actual/365, and 4 for European 30/360). If omitted, the default is 0.

## YIELD Examples

Let’s look at some examples of how to use the YIELD function in Excel:

Example 1: Calculate the yield of a bond with an annual interest rate of 5%, a purchase price of \$95, a redemption value of \$100, a settlement date of January 1, 2020, a maturity date of January 1, 2025, and semi-annual interest payments.

=YIELD(“1/1/2020”, “1/1/2025”, 0.05, 95, 100, 2)

This formula will return the yield of the bond, which is approximately 0.057 or 5.7%.

Example 2: Calculate the yield of a bond with an annual interest rate of 4%, a purchase price of \$102, a redemption value of \$100, a settlement date of June 1, 2020, a maturity date of June 1, 2030, and quarterly interest payments using the actual/actual day count basis.

=YIELD(“6/1/2020”, “6/1/2030”, 0.04, 102, 100, 4, 1)

This formula will return the yield of the bond, which is approximately 0.036 or 3.6%.

## YIELD Tips & Tricks

Here are some tips and tricks to help you effectively use the YIELD function in Excel:

• Ensure that the settlement and maturity dates are entered as valid Excel dates. You can use the DATE function to create a date value.
• Remember that the rate, pr, and redemption values should be expressed as decimals (e.g., 5% should be entered as 0.05).
• When comparing yields of different bonds, make sure to use the same day count basis for consistency.
• Use the YIELD function in conjunction with other financial functions, such as PRICE, DURATION, and MDURATION, to analyze the performance of fixed-income investments.

## Common Mistakes When Using YIELD

Here are some common mistakes to avoid when using the YIELD function in Excel:

• Entering the rate, pr, and redemption values as percentages instead of decimals.
• Using an incorrect day count basis, which can lead to inaccurate yield calculations.
• Not using valid Excel dates for the settlement and maturity dates.
• Forgetting to adjust the frequency value based on the number of interest payments per year.

## Why Isn’t My YIELD Function Working?

If your YIELD function is not working, consider the following troubleshooting steps:

• Check for any errors in the input values, such as incorrect dates, rates, or day count basis.
• Ensure that the settlement date is before the maturity date.
• Verify that the frequency value is set correctly based on the number of interest payments per year.
• Make sure that the rate, pr, and redemption values are entered as decimals, not percentages.

## YIELD: Related Formulae

Here are some related formulae that can be used in conjunction with the YIELD function to analyze fixed-income investments:

• PRICE: Calculates the price of a security that pays periodic interest.
• DURATION: Calculates the Macaulay duration of a security that pays periodic interest.
• MDURATION: Calculates the modified duration of a security that pays periodic interest.
• YIELD.DISC: Calculates the yield of a discounted security, such as a Treasury bill.
• YIELD.MAT: Calculates the yield of a security that pays interest at maturity, such as a zero-coupon bond.

## Related

### Hard to find or retain a good accountant? Try cloud accounting solution

Foreign business owners or management team always take financial transparency as a pre-condition for good decision making and sustainable profitability. However, achieving the visualization of

### Cloud Accounting Software Automates Compliance Service in China

Managing accounting compliance in China can be a challenging task for businesses, as it involves dealing with complex regulations and paperwork. However, the advent of

## Share it with your friends or colleagues

We are a boutique accounting and consulting firm servicing international businesses operating in China. We offers book keeping and business advisory service. We also help our clients select and implement SAAS business applications

Cloud technology significantly reduces cost foreign companies traditionally spent on tax compliance and ERP systems. Our cloud professionals can help with streamlining your management and controling structure, as well as advising you on how to reduce risks and maximize profits with software purposely built for Chinese business. Contact us today to learn more about our services.