YIELD

• How to use YIELD formula?
• Examples of using YIELD formula
• YIELD formula not working?
• Similar formulas to YIELD

Usage

Use the YIELD formula with the syntax shown below, it has 6 required parameters and 1 optional parameter:

=YIELD(settlement, maturity, rate, price, redemption, frequency, [day_count_convention])

1. settlement (required):
   The settlement date of the security, represented as a valid date or a reference to a cell containing a valid date.
2. maturity (required):
   The maturity date of the security, represented as a valid date or a reference to a cell containing a valid date.
3. rate (required):
   The annual coupon rate of the security, expressed as a decimal.
4. price (required):
   The price per $100 face value of the security.
5. redemption (required):
   The redemption value per $100 face value of the security.
6. frequency (required):
   The number of coupon payments per year. Must be one of the following values: 1, 2, or 4.
7. day_count_convention (optional):
   Optional. The day count convention to use for the calculation. If omitted, the default value of 0 (30/360 US) is used. Must be one of the following values: 0, 1, 2, 3, or 4.

Examples

Calculating bond yield

YIELD is commonly used to calculate the yield of a bond, which is the annualized return on investment. By providing the settlement date, maturity date, coupon rate, price, redemption value, and frequency of coupon payments, this function can return the yield of the bond.

Comparing bond yields

YIELD can be used to compare the yields of different bonds with similar characteristics. By calculating the yields of different bonds using this function, an investor can determine which bond offers the best return on investment.

Valuing fixed-income securities

YIELD can be used to value fixed-income securities, such as bonds and notes. By providing the settlement date, maturity date, coupon rate, price, redemption value, and frequency of coupon payments, this function can return the present value of the security.

Common Mistakes

Incorrect order of arguments

The arguments for the YIELD formula must be in the correct order. Make sure you are providing settlement, maturity, rate, price, redemption, frequency, and day_count_convention (if needed) in the correct order.

Providing text instead of numbers

Make sure you are providing numerical values for settlement, maturity, rate, price, redemption, and frequency. If any of these arguments are text or have incorrect formatting, the formula will not work.

Incorrect frequency

The frequency argument must be one of the following values: 1 for annually, 2 for semiannually, 4 for quarterly, or 12 for monthly. If any other value is provided, the formula will not work.

Missing arguments

Make sure you are providing all required arguments for the YIELD formula. If any arguments are missing, the formula will not work.

Invalid day count convention

If you are using the optional day_count_convention argument, make sure it is one of the following values: actual/actual, actual/360, actual/365, 30/360. If any other value is provided, the formula will not work.

Related Formulas

The following functions are similar to YIELD or are often used with it in a formula:

• PRICE

  The PRICE function calculates the price per $100 face value of a security that pays periodic interest. It is commonly used to determine the current value of a bond. The function takes the settlement date, maturity date, annual coupon rate, yield, redemption value, and frequency of coupon payments as input. It returns the price of the security, which is the sum of the present value of the coupon payments and the present value of the redemption value.

• DURATION

  The DURATION function calculates the Macauley duration of a security paying periodic interest, such as a US Treasury Bond, based on expected yield. The Macauley duration is a measure of the sensitivity of the price of the security to changes in interest rates. This function is commonly used in finance and investment analysis.

• NPV

  The NPV function calculates the net present value of a series of cash flows, discounted by a specified rate. It is commonly used to determine the present value of an investment, where the cash flows represent incoming and outgoing payments. The function takes a discount rate and one or more cash flow values as input.

• IRR

  The IRR function is used to calculate the Internal Rate of Return for a series of cash flows. The IRR is the interest rate at which the Net Present Value of the cash flows equals zero. It is commonly used to evaluate potential investments or projects.

Learn More

You can learn more about the YIELD Google Sheets function on Google Support.