Menu
EXCEL YIELD FUNCTION
Excel YIELD Function Introduction
The YIELD Function is classified as a financial function. It will compute the yield on a security that pays a fixed amount of interest on a regular basis. The function is commonly used to compute the bond yield. As financial analysts, we frequently compute the yield on a bond to determine the annual income. Yield differs from rate of return in that the latter represents the gain actually realised, whilst the former is the projected return.
Description of Excel YIELD Function
Syntax :
=YIELD (settlement, maturity, rate, pr, redemption, frequency, [basis])
Parameters :
1. Settlement (necessary argument) – It is the security’s settlement date. It is the date after the issuance date when the security is exchanged to the buyer.
2. Maturity (mandatory argument) – This is the security’s maturity date. It is the date on which the security will expire.
3. Rate (obligatory argument) – This is the yearly coupon rate.
4. Pr (mandatory argument) – This is the security’s price per $100 face value.
5. Redemption (mandatory argument) – This is the redemption amount divided by the face value of $100.
The YIELD function should be given with the settlement and maturity dates as either:
• references to cells holding dates; or
• dates returned from formulae.
Step By Step Guide of YIELD Function
Example :
• Settlement date: January 1, 2017
• Maturity date: June 30, 2019
• Interest rate: 10%
• FV price per $100: $101
• The redemption value is $100.
• Payment schedule: quarterly
1. We utilised the US (NASD) 30/360 day system as our foundation.
2. The date parameters were entered as references to cells holding dates, as advised by Microsoft.
Key Points
1. #NUM! error – Occurs when:
• The supplied settlement date is greater than or equal to the maturity date.
• We supplied incorrect values for the rate, pr, redemption, frequency, and [base] parameters. That is, we specified rate 0; price 0; redemption 0; frequency is any number other than 1, 2, or 4; or [base] is any number other than 0, 1, 2, 3, or 4.
2. #VALUE! error – Occurs when any of the given parameters is non-numeric.
• The given settlement and maturity dates are not genuine.
• The settlement and maturity dates provided are not valid dates.
3. The output of the Excel RATE function appears to be 0 or as a percentage but with no decimal places. The formatting of the cell holding the function is frequently the source of the problem. If this is the case, you may resolve the issue by formatting the cell to display a percentage with decimal places.
4. The settlement date is the day on which a buyer buys a coupon, such as a bond. The maturity date of a coupon is the day on which it expires. A 30-year bond, for example, is issued on January 1, 2010 and acquired six months later by a buyer. The issue date is January 1, 2010, the settlement date is July 1, 2010, and the maturity date is January 1, 2040, which is 30 years following the issue date after the January 1, 2010 issue date.
Related Functions
HLOOKUP Function
HLOOKUP Function
Explore NowVLOOKUP Function
VLOOKUP Function
Explore NowINDEX Function
INDEX Function
Explore NowMATCH Function
MATCH Function
Explore NowDATE Function
DATE Function
Explore NowIF Function
IF Function
Explore NowOR Function
OR Function
Explore NowAND Function
AND Function
Explore NowRANK Function
RANK Function
Explore NowSUMIF Function
SUMIF Function
Explore NowXIRR Function
XIRR Function
Explore NowWORKDAYS Function
WORKDAYS Function
Explore Now
