Excel YIELD Function
YIELD is an Excel function that returns the yield to maturity of a bond given its coupon rate, current price, principal amount and coupon payment frequency per year.
In the context of debt securities, yield is the return that a debt-holder earns by investing in a security at its current price. There are two common measures of yield: current yield and yield to maturity.
Current yield equals the annual interest payment divided by the current market price of the security.
Yield to maturity is the internal rate of return of a security which means it is the rate an investor will earn by purchasing the security at its current price and receiving all future cash flows, such as coupon payments till maturity and the maturity value.
Yield to maturity can be calculated by solving the following equation for YLD using hit-and-trial:
$$ \text{Price}=\text{REDEMPTION}\ \times\frac{\text{RATE}}{\text{FREQUENCY}}\times\frac{\text{1}-{(\text{1}+\frac{\text{YLD}}{\text{FREQUENCY}})}^{-\text{n}}}{\text{YLD}/\text{FREQUENCY}}+\frac{\text{REDEMPTION}}{{(\text{1}+\frac{\text{YLD}}{\text{FREQUENCY}})}^\text{n}} $$
Syntax
The formula’s syntax is:
YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis]
Settlement refers to the settlement date i.e. the reference date for pricing, maturity is the maturity date i.e. the date on which the security-holder receives principal back, Pr stands for the current market price of the security; redemption is the value received by the bond-holder at the expiry of the bond representing the repayment of principal; frequency refers to number of periodic interest payments per year and [basis] is an optional argument specifying the day-counting basis to be used.
Example
The following example shows how to enter the required values in YIELD function:
Note that we entered price (pr) and redemption value (redemption) per $100 regardless of actual face value of the bond.
by Obaidullah Jan, ACA, CFA and last modified on