YIELD Function Excel

This tutorial demonstrates how to use the Excel YIELD Function in Excel to calculate the bond yield.

Excel YIELD Function

YIELD Function Overview

The YIELD Function Calculates the bond yield.

To use the YIELD Excel Worksheet Function, select a cell and type:
yield formula syntax

(Notice how the formula inputs appear)

YIELD Function Syntax and Inputs:

settlement – It’s the settlement date of the security or the date at which the security is purchased. It’s the date that comes after the issuing date of the security.

maturity – It’s the date at which the bond or security expires, and the principal amount is paid back to bond or security holder.

rate – It’s the annual interest rate of the bond or security at which the coupon payments are processed.

pr – It’s the face value of the security or the bond. If this argument is omitted, then the YIELD function will assume the par value to be $1,000.

redemption – The bond’s or security’s redemption value per $100 face value that is reimbursed to bond or security holder on the redemption date.

frequency – It refers to the number of periodic coupon payments per year. The value of frequency for annual, semi-annual, and quarterly payments are 1, 2, and 4, respectively.

basis – OPTIONAL. It specifies the type of day counting to be used by the security or bond. Possible values can be:

Basis Day Count
0 US (NASD) 30/360
1 Actual/actual
2 Acutal/360
3 Acutal/365
4 European 30/360

If the basis argument is omitted, it assumes its default value i.e. US (NASD) 30/360.

What Is YIELD?

The Yield on security is the amount of cash earned on security in the form of interest or dividends. It is expressed as a percentage based on the face value of the security.

YIELD of a bond is calculated using the following formula if there is no more than one coupon period until the redemption.

YIELD Formula

Where:

DSR = number of days from the settlement date to the redemption date

E = number of days in coupon period in which the settlement date falls

A = number of days from the beginning of the coupon period to the settlement date.

If more than one coupon is remaining until the redemption, then YIELD is calculated through iterations. YIELD function uses the PRICE formula to solve for the yield using the Newton method (also known as the Newton-Raphson method).

What Is Excel YIELD Function?

The Excel YIELD function returns the yield on a security that pays periodic interest. It returns the value as a percentage.

Example 1

In this example, we calculate the yield on a security that was purchased on January 20, 2018, with an annual interest rate of 6%. Other details of the security are mentioned below:

The settlement date of the security is

Settlement = 1/20/2018

The Maturity date of the security is

Maturity = 10/15/2025

The annual coupon rate of the security is

Rate = 6%

The face value of the security is

Pr = $102

The redemption value of the security per $100 of face value is

Redemption = $100

The coupon payments are made quarterly, so the value of frequency is

Frequency = 4

And the payments are based on the following day count basis

Basis = 0

Excel YIELD Function Example 1

The YIELD function returns the value of

YIELD = 5.68%

Here the price of the security argument (pr) and redemption argument (redemption) is entered as the value per $100 regardless of the actual face value of the security. Also as recommended the values of the settlement and maturity date arguments are entered as a reference to the cells containing dates.

Example 2

Now let’s calculate the yield of a 10-year bond, which was issued on February 1, 2009, and was purchased by the investor three months later. Other details of the bond are mentioned below:

Here the bond was purchased after three months from the issuing date, so the settlement date is

Settlement = 5/1/2009

As this is a 10-year bond, the maturity of the bond is

Maturity = 2/1/2019

The annual coupon rate of the bond is

Rate = 8%

The face value of the bond is

Pr = $98

The redemption value of the bond per $100 of face value is

Redemption = $100

The coupon payments are made once a year, so the value of frequency is

Frequency = 1

The [basis] argument was not mentioned, so the default US (NASD) 30/360 day count basis was used

Basis = 0

Excel YIELD Function Example 2

The YIELD function calculates the yield of the 10-year bond to be

YIELD = 8.3%

As recommended the values of the settlement and maturity date arguments are entered as a reference to the cells containing dates.

YIELD in Google Sheets

All of the above examples work exactly the same in Google Sheets as in Excel.

YIELD Function in Google Sheets

Additional Notes

Use the YIELD Function to calculate the yield of a bond or similar security.

#NUM! Error occurs if the settlement date is greater or equal than the maturity date; or the values of the rate, redemption, frequency, or [basis] arguments are not valid numbers (i.e. rate < 0; or pr ≤ 0; or redemption ≤ 0; or frequency is any value other than 1, 2 or 4; or [basis] value is other than 0, 1, 2, 3, or 4)

#VALUE! Error occurs if the dates of the settlement or the maturity arguments are not valid Excel dates.

It is recommended that the settlement and maturity dates should be entered in the YIELD function as references to cells containing the dates or dates returned from formulas.

The result of the YIELD Function may appear as a decimal. Change the cell Number Formatting to Percentage to display the function result as a percentage:
percentage formatting excel shortcut

Return to the List of all Functions in Excel