YIELD ( settlement , maturity , rate , pr , redemption , frequency [ , [ basis ] ] )
Description: Computes the yield on a security that pays periodic interest.
Mathematical Formula:
If 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.
If there is more than one coupon period until redemption, YIELD is calculated through some number of iterations. The resolution uses the Newton method, based on the formula used for the function PRICE. The yield is changed until the estimated price given the yield is close to price.
Arguments:
|
Name |
Type |
Description | ||||||||||||
|
settlement |
number |
The security's settlement date. | ||||||||||||
|
maturity |
number |
The security's maturity date. | ||||||||||||
|
rate |
number |
The security's interest rate. | ||||||||||||
|
pr |
number |
The security's price. | ||||||||||||
|
redemption |
number |
The security's redemption value per $100 face value. | ||||||||||||
|
frequency |
number |
the number of coupon payments per year. (For annual payments, frequency is 1; for semiannual payments, frequency is 2; for quarterly payments, frequency is 4.) frequency is truncated to an integer. | ||||||||||||
|
basis |
number |
The truncated integer type of day count basis to use, as follows:
|
Time information in the date arguments is ignored.
Return Type and Value: number – The yield on a security that pays periodic interest.
However, if
• settlement or maturity is out of range for the current date base value, #NUM! is returned.
• settlement ≥ maturity, #NUM! is returned.
• rate < 0, #NUM! is returned.
• pr or redemption ≤ 0, #NUM! is returned.
• frequency is any number other than 1, 2, or 4, #NUM! is returned.
• basis < 0 or basis > 4, #NUM! is returned.
[Example:
YIELD(DATE(2008,2,15),DATE(2016,11,15),0.0575,95.04287,100,2,0) results in 6.5000%
end example]