In this article, you will learn about the YIELD function, the formula syntax and usage of the function in Microsoft Excel.
YIELD Function in Excel
The YIELD function in Excel returns the yield on a security that pays periodic interest.
Syntax
=YIELD (sd, md, rate, pr, redemption, frequency, [basis])
Arguments
- sd – Settlement date of the security.
- md – Maturity date of the security.
- rate – Annual coupon rate.
- pr – Security’s price per $100 face value.
- redemption – Redemption value per $100 face value.
- frequency – Coupon payments per year (annual = 1, semiannual = 2; quarterly = 4).
- basis – [optional] Day count basis (see below, default =0).
Usage Notes and Possible Errors
- As we know Excel stores dates as sequential series of numbers, i.e., January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 as it is 39,448 days after January 1, 1900.
- The settlement date is the date a buyer purchases a coupon, such as a bond.
- The maturity date is the date when a coupon expires.
- Settlement, maturity, frequency, and basis are truncated to integers.
- When the settlement or maturity is not a valid date, YIELD returns the #VALUE! error value.
- When rate < 0, YIELD returns the #NUM! error value.
- If pr ≤ 0 or if redemption ≤ 0, YIELD returns the #NUM! error value.
- When frequency is of any number other than 1, 2, or 4, YIELD returns the #NUM! error value.
- When basis < 0 or if basis > 4, YIELD returns the #NUM! error value.
- If settlement ≥ maturity, YIELD returns the #NUM! error value.
- When there is one coupon period or less until redemption, YIELD is calculated as follows:
where:
- A = number of days from the beginning of the coupon period to the settlement date (accrued days).
- DSR = number of days from the settlement date to the redemption date.
- E = number of days in the coupon period.
How to use the yield function in Excel?
Using this function in a WS is simple; all you need to do is enter the function as a formula of the cell in the formula bar.
Take a look at the given example.
Enter the data in the respective columns and enter the yield formula.
Formula: =YIELD(A2,B2,C2,D2,E2,F2,G2)
Here, A2 refers to the cell name or the cell address. You will get the result in the Yield column.
