The `YIELD` function in Excel is used to calculate the yield of a security that pays periodic interest, such as a bond. This function is particularly useful for investors assessing the return on a bond investment. Here’s how you can use the `YIELD` function in Excel:
Syntax
YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])
Parameters
- settlement: The settlement date of the security. This is the date after the issue date when the security is traded to the buyer.
- maturity: The maturity date of the security, which is the date when the security expires.
- rate: The annual coupon rate of the security.
- pr: The price of the security per $100 face value.
- redemption: The redemption value of the security per $100 face value.
- frequency: The number of coupon payments per year. Typically, this is 1 for annual, 2 for semiannual, or 4 for quarterly payments.
- [basis]: The day count basis to use. This is optional and can be:
- 0 or omitted: US (NASD) 30/360
- 1: Actual/actual
- 2: Actual/360
- 3: Actual/365
- 4: European 30/360
Example
Let’s assume you have a bond with the following details:
- Settlement date: January 1, 2022
- Maturity date: January 1, 2032
- Annual coupon rate: 5%
- Price: $95
- Redemption value: $100
- Frequency: 2 (semiannual payment)
- Basis: 0 (US (NASD) 30/360)
The formula to calculate the yield would be:
=YIELD(DATE(2022, 1, 1), DATE(2032, 1, 1), 0.05, 95, 100, 2, 0)
When you enter this formula into Excel, it will return the yield of the bond based on the given parameters.
Key Points
- Make sure all date values are entered using the `DATE` function to avoid errors.
- The settlement and maturity dates should be actual dates and must be entered in Excel as date serial numbers.
- Ensure that the frequency and basis are entered correctly based on the specific bond you are analyzing.
- `YIELD` function assumes periodic interest payments and uses a day count convention to compute the yield.
Using this function helps investors determine the expected annual return of a bond given its current market price and other features.
Unlock Your Potential
Excel
Access
Power BI
