The Excel YIELD function is a Financial formula that calculates and returns the yield on a security that pays a periodic interest. A common use case for the YIELD function is calculating bond yields. In this guide, we’re going to show you how to use the Excel YIELD function, and also go over some tips and error handling methods.
Supported versions
- All Excel versions
Excel YIELD Function Syntax
Arguments
settlement | The security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. |
maturity | The security's maturity date. The maturity date is the date when the security expires. |
rate | The security's interest rate at date of issue. |
pr | The security's price per $100 face value. |
redemption | The security's redemption value per $100 face value. |
frequency |
The number of coupon payments per year. For annual payments,
|
[basis] |
Optional. The type of day count basis to be used.
|
Example
The following example shows how to calculate the yield on a bond purchased on August 8, 2019, with maturity date of February 2nd 2024. The annual rate of interest is 5.0% on the price per $100 face value is $101, and the redemption value is $100. The payments will be made based on the US (NASD) 30/360 day count.
Tips
- Microsoft recommends using the DATE or other similar functions that can return a date serial number.
- Excel keeps date and time values as numbers. Excel assumes that Jan 1st, 1900 is 1, and every subsequent date value is based on this. While whole numbers represent days, decimals represent time values. For example; 1/1/2018 is equal to 43101, and 12:00 is equal to 5.
- Please see Day count convention article in Wikipedia for more information about the [basis] types.
- Settlement, maturity, and basis are truncated to integers.
- Other price-related functions:
- PRICE returns the price per $100 face value of a security that pays periodic interest.
- PRICEDISC returns the price per $100 face value of a discounted security.
- PRICEMAT returns the returns the price per $100 face value of a security that pays interest at maturity.
- DURATION returns the Macauley duration for an assumed par value of $100.
Common Issues
- If settlement or maturity are not valid dates, the Excel YIELD function returns the #VALUE! error.
- If rate < 0, YIELD returns the #NUM! error value.
- The YIELD returns the #NUM! error value if pr ≤ 0 or if redemption ≤ 0.
- If [basis] < 0 or if [basis] > 4, YIELD returns the #NUM! error value.
- If settlement ≥ maturity, YIELD returns the #NUM! error value.