### YIELD Function Excel

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

The YIELD Function Calculates the bond yield.

To use the YIELD Excel Worksheet Function, select a cell and type: (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. 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 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 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.

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