The YIELD function calculates a security’s yield that pays interest periodically, such as a bond. The function is one of the financial functions and was first introduced in the Excel 2007 version.
YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])
The function has the following arguments:
settlement – Required. After the issuance date, it is the date on which the bond is traded to the buyer.
maturity – Required. The date on which the bond will expire.
rate – Required. The coupon rate (annual) for the bond.
pr – Required. The price of the bond on which it was purchased.
redemption – Required. The redemption value of the bond on the expiry.
Frequency – Required. The coupon payment period.
[basis] – Optional. For calculation purpose, the day count basis to be used.
|Basis||Day count basis|
|0 or omitted||US (NASD) 30/360|
Suppose, you purchased a bond for $94 on June 05, 2019, it has the annual coupon rate of 8% with quarterly payments. On June 22, 2022, the maturity date of the bond, it redeems at $100.
|#VALUE!||If supplied dates are not valid|
|#NUM!||If rate < 0|
|If pr ≤ 0 or if redemption ≤ 0|
|If frequency is any number other than 1, 2, or 4|
|If basis < 0 or if basis > 4|
|If settlement ≥ maturity|