Enrollment for the FMVA™ Certification Program is now OPEN!

YIELD Function

Calculates the yield on a security that pays a periodic interest

What is the YIELD Function?

The YIELD Function is categorized under Financial functions. It will calculate the yield on a security that pays periodic interest. The function is generally used to calculate the bond yield.

As a financial analyst, we often calculate the yield on a bond to determine the income that would be generated in a year. Yield is different from the rate of return as the latter return is the gain already earned while former is the prospective return.

 

Formula

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

This function uses the following arguments:

  1. Settlement (required argument) – It is the settlement date of the security. It is a date after the security is traded to the buyer that is after the issue date.
  2. Maturity (required argument) – It is the maturity date of the security. It is the date when the security expires.
  3. Rate (required argument) – It is the annual coupon rate.
  4. Pr (required argument) – It is the price of security per $100 face value.
  5. Redemption (required argument) – It is the redemption value per $100 face value.
  6. Frequency (required argument) – It is the number of coupon payments per year. It must be either of the following: 1 – Annually, 2 – Semi-annually, 4 – Quarterly
  7. [basis] (optional argument) – It specifies the financial day count basis that is used by security. The possible values are:

 

BasisDay Count basis
0 or omittedUS(NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360

 

The settlement and maturity dates should be supplied to the YIELD function as either:

  • References to cells containing dates; or
  • Dates returned from formulas.

 

How to use the YIELD Function in Excel?

As a worksheet function, YIELD can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, and let us consider an example:

 

Example

Suppose we are given the following data:

  • Settlement date: 01/01/2017
  • Maturity date: 6/30/2019
  • Rate of interest: 10%
  • Price per $100 FV: $101
  • Redemption value: $100
  • Payment terms: Quarterly

We can use the function to find out the yield. The formula to use will be:

 

YIELD Function

 

We get the result below:

 

YIELD Function - Example

 

In the above example:

  1. We used as basis the US (NASD) 30/360 day basis.
  2. As recommended by Microsoft, the date arguments were entered as references to cells containing dates.

 

Few things to remember about the YIELD Function:

  1. #NUM! error – Occurs when:
    • The settlement date provided is greater than or equal to the maturity date.
    • We provided invalid numbers for the rate, pr, redemption, frequency or [basis] arguments. That is, we provided rate < 0; pr ≤ 0; redemption ≤ 0; frequency is any number other than 1, 2 or 4; or [basis] is any number other than 0, 1, 2, 3 or 4.
  1. #VALUE! error – Occurs when:
    • Any of the arguments provided is non-numeric.
    • The settlement and maturity dates provided are not valid dates.
  1. The result from the Excel RATE function appears to be the value 0 or appears as a percentage but shows no decimal places. The problem is often due to the formatting of the cell containing the function. If this is the case, fix the problem by formatting the cell to show a percentage with decimal places.
  2. The settlement date is the date a buyer purchases a coupon, such as a bond. The maturity date is the date when a coupon expires. For example, a 30-year bond is issued on January 1, 2010 and is purchased by a buyer six months later. The issue date would be January 1, 2010, the settlement date would be July 1, 2010, and the maturity date would be January 1, 2040, which is 30 years after the January 1, 2010 issue date.

 

Click here to download the sample Excel file

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial modeling. To learn more, check out these additional resources:

Free Excel Tutorial

To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.  

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!