YIELD
The YIELD function calculates the yield of a security that pays periodic interest, such as bonds or treasury bills. It is commonly used in financial analysis to determine the rate of return on investments.
Syntax 🔗
=YIELD(Settlement
, Maturity
, Rate
, Pr
, Redemption
, [Frequency]
, [Basis]
)
Settlement | The settlement date of the security. |
Maturity | The maturity date of the security. |
Rate | The annual coupon rate of the security. |
Pr | The price per $100 face value. |
Redemption | The redemption value at maturity. |
Frequency (Optional) | The number of coupon payments per year. Defaults to 1 (annual) if omitted. |
Basis (Optional) | The day-count basis for the calculation. Defaults to 0 if omitted. |
About YIELD 🔗
When seeking insight into the potential return on a security investment, turn to Excel's YIELD function. This versatile tool plays a crucial role in financial assessments, offering a means to gauge the yield on investments like bonds or treasury bills based on specific parameters provided by the user. By computing the yield, individuals gain valuable information for decision-making processes in investment scenarios, determining the profitability and risk associated with the security being considered. YIELD empowers users with a robust mechanism to analyze and compare investment opportunities, facilitating informed financial choices.
Examples 🔗
Suppose you have a bond with a maturity date of August 31, 2026, an annual coupon rate of 4%, priced at $95 per $100 face value, with a redemption value of $100, and paying interest semi-annually. You want to calculate the yield on July 15, 2022. The YIELD formula would be: =YIELD("07/15/2022", "08/31/2026", 0.04, 95, 100, 2)
Consider a treasury bill with a settlement date of June 1, 2022, maturity on December 31, 2022, a 3% annual discount rate, a purchase price of $95 per $100 face value, and a redemption value of $100. To determine the yield, use the formula: =YIELD("06/01/2022", "12/31/2022", 0.03, 95, 100)
Notes 🔗
Ensure that the dates provided as arguments are valid Excel date values or references to valid date values within cells. The frequency of coupon payments and the day-count basis should align with the specific terms of the security being evaluated. Adjust the function parameters according to the bond or security details to obtain accurate yield calculations.
Questions 🔗
The YIELD function calculates the yield by iterating through approximations of the yield until it reaches an accuracy level within 0.000001 (0.0001%). It employs an iterative process considering the settlement date, maturity date, coupon rate, price, redemption value, frequency of coupon payments, and day-count basis to determine the yield.
Can the YIELD function handle securities with irregular coupon payment schedules?The YIELD function assumes a regular coupon payment schedule for calculations. It may not provide accurate results for securities with irregular payment dates. For precise calculations, ensure that the security's terms align with the parameters specified in the function.
Can you adjust the frequency of coupon payments for the YIELD function?Yes, you can modify the frequency of coupon payments using the optional Frequency
argument. If your security's coupon payments are semi-annual, quarterly, or have a different schedule, enter the corresponding frequency value in the formula to reflect the payment pattern accurately.
The YIELD function serves as a valuable tool for evaluating investment opportunities by calculating the yield of securities. By determining the rate of return, individuals can assess the profitability and risk associated with investments, enabling informed decision-making in financial matters.
Related functions 🔗
ACCRINT
COUPDAYS
COUPDAYSNC
COUPNCD
COUPNUM
COUPPCD
MDURATION
YIELDDISC
YIELDMAT
ACCRINT