Excel Yield Function

What is Yield Function in Excel?

The Yield Function in Excel is a built-in financial function that is used to determine the yield on a security or bond that pays interest on a periodic basis. The yield function calculates bond yield by using the settlement value, maturity, and rate of the bond, as well as the price and redemption of the bond.

Syntax

Compulsory Parameters:

• Settlement: The date on which the coupon is purchased by the buyer or the date on which bond is purchased or the settlement date of the security.
• Maturity: The maturity date of security or the date on which the purchased coupon expires.
• Rate: Rate is the annual coupon rate of security.
• Pr: Pr represents the security price per $100 stated value.
• Redemption: Redemption is the redemption value of security per $100 stated value.
• Frequency: Frequency means a number of coupons paid per year, i.e., 1 for an annual payment and 2 for semiannual and 4 for quarterly payment.

Optional Parameter:

The optional parameter is always appearing in [] in an excel yield formula. Here the Basis is an optional argument, so it comes as [basis].

• [Basis]: Basis is an optional integer parameter that specifies the day count basisDay Count BasisDay count convention is a system used in the finance field wherein number of days are used as a basis to determine the interest component in investments such as bonds, mortgages, swaps and forward rate agreements.read more used by the security.

The possible values for [basis] are as follows:

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

How to use the Yield function in excel?

Example #1

Bond Yield calculation for quarterly payment.        

Let’s consider the settlement date as 17^th May 2018, and the maturity date is 17^th May 2020 for the purchased coupon. The rate of interest per annum is 5%, price is 101, redemption is 100, and payment terms or frequency is quarterly, then yield will be 4.475%.

Example #2

Bond Yield calculation in Excel for Semi-annually payment.

Here the settlement date is 17^th May 2018, and the maturity date is 17^th May 2020. The rate of interest, price, and redemption values are 5%, 101, and 100. For semiannually, payment frequency will be 2.

Then output yield will be 4.472% [considered basis as 0].

Example #3

Bond YieldBond YieldThe bond yield formula evaluates the returns from investment in a given bond. It is calculated as the percentage of the annual coupon payment to the bond price. The annual coupon payment is depicted by multiplying the bond's face value with the coupon rate.read more calculation in Excel for a yearly payment.

For yearly payment, let’s consider the settlement dateSettlement DateThe settlement date is the date on which the cash and assets that have been exchanged or traded are settled by netting out a process that happened a few days ago. Commonly for shares, it is two business days after the trade.read more is 17^th May 2018 and the maturity date is 17^th May 2020. The rate of interest, price, and redemption values is 5%, 101, and 100. For semiannually, payment frequency will be 1.

Then output yield will be 4.466% considered basis as 0.

Things to Remember

Below are the error details which can be come across in the Bond Yield excel function due to type mismatch:

#NUM!: There may be two possibilities for this error in the bond yield in Excel.

1. If the settlement date in yield function is greater than or equal to the maturity date, then #NUM! Error occurs.
2. Invalid numbers are given to rate, pr, and redemption, frequency, or [basis] parameters.
   1. If rate < 0, then yield in Excel returns the #NUM! Error.
   2. If pr <=0 and redemption <= 0 then yield excel function returns the #NUM! Error.
   3. If the given frequency is not 1,2 or 4, then yield excel function returns the #NUM! Error.
   4. If basis < 0 or if basis > 4 then yield excel function returns the #NUM! Error.

#VALUE!:

1. If any of the given parameters are non-numbers.
2. Dates are not provided in proper date format.

Microsoft excels stores the date sequence from date 1^st January 1900 as number 1 and  43237 days for date 17^th January 2018.

Yield Function in Excel Video

Recommended Articles

This has been a guide to the YIELD Function in Excel. Here we discuss the YIELD Formula and how to use it in Excel along with practical examples and downloadable excel templates. You can also learn more about excel from the following articles –

• Calculate Yield to Maturity
• Coupon vs. Yield
• Excel Convert Function
• Calculate the Bond’s Coupon Rate