YIELD Function in Excel: Explained
In this article, you will learn how to use the YIELD formula in Excel.
What is the YIELD formula in Excel?
The YIELD function is a financial function that calculates the yield of a security, such as a bond, based on its price, coupon rate, maturity date, and other relevant parameters.
Uses of YIELD formula in Excel
The YIELD function in Excel is primarily used in financial analysis and investment valuation, specifically for analyzing and pricing bonds. Here are some common scenarios where the YIELD function can be helpful:
- Bond Valuation: The YIELD function can be used to calculate the yield of a bond based on its market price, coupon rate, maturity date, and other relevant parameters. This information is important for valuing bonds and making investment decisions.
- Yield Curve Analysis: The YIELD function can be used in conjunction with other financial functions to analyze yield curves. By varying the maturity dates and coupon rates, you can plot the yield curve and assess the relationship between bond yields and maturity periods.
- Bond Pricing: The YIELD function can be used to determine the fair value or market price of a bond. By inputting the desired yield and other bond parameters, you can calculate the price at which the bond should be traded in the market.
How to use the YIELD formula in Excel
Syntax:
Where,
settlement: The settlement date of the security, which is the date on which it is purchased.
maturity: The maturity date of the security, which is the date when the principal amount is repaid.
rate: The annual coupon rate of the security.
pr: The price per $100 face value of the security.
redemption: The redemption value or face value of the security at maturity.
frequency: The number of coupon payments per year (1 for annually, 2 for semi-annually, etc.).
basis: [Optional] The day count basis to use for calculation. If omitted, it defaults to 0 (U.S. (NASD) 30/360 basis).
The YIELD function returns the annual yield of the security as a percentage. It represents the rate of return an investor can expect to receive by holding the security until maturity, considering the purchase price and the periodic coupon payments.
Note: The YIELD function assumes that the coupon payments are made on time and that the bond is held until maturity.
Example Case Study: Bond Yield Comparison
Suppose you need to compare two bonds with different coupon rates, prices, and maturities and determine which one provides higher yield. YIELD function could be used as shown below in the example:
Step 1: Gather and record the relevant information for the bonds you wish to compare. In this case, Bonds A and B.
Step 2: Use the Yield Formula with the required input parameters to calculate the Yield of both the Bonds as shown in the image below.
By using the YIELD function in Excel, one can easily calculate and compare the yields of different bonds, allowing one to make informed investment decisions based on their relative returns.
