YIELDMAT
The YIELDMAT function calculates the annual yield of a security that pays interest at maturity. This function is commonly used in financial analysis to determine the yield of a security based on its purchase price, par value, and maturity date.
Syntax
=YIELDMAT(Settlement, Maturity, Issue, Rate, Pr, [Basis]) Arguments
| Argument | Required | Description |
|---|---|---|
| Settlement | Yes | The security's settlement date. |
| Maturity | Yes | The security's maturity date. |
| Issue | Yes | The security's issue date. |
| Rate | Yes | Annual interest rate of the security. |
| Pr | Yes | The security's price per $100 face value. |
| Basis | No | The day-count basis to use. Defaults to 0 if omitted. |
About
When you're dealing with securities that pay interest at maturity and need to determine their annual yield accurately, look no further than the YIELDMAT function in Excel. This function serves as a valuable tool in financial analysis, offering insights into the effective yield of securities based on crucial parameters such as settlement date, maturity date, issue date, interest rate, and price per $100 face value of the security. YIELDMAT caters to the needs of investors and analysts aiming to gauge the return on investment for securities with specific maturity characteristics and pricing details. By utilizing YIELDMAT, you get a comprehensive perspective on the annualized yield of securities, aiding you in making informed decisions regarding investment opportunities in the financial markets.
Examples
Let's say you have purchased a security with a par value of $1,000, an annual interest rate of 4%, and it pays interest at maturity. The security was issued on January 1, 2022, you settled the transaction on May 1, 2022, and its maturity date is December 31, 2023. The price you paid for the security is $950. To calculate the annual yield, use the following YIELDMAT formula: =YIELDMAT("05/01/2022", "12/31/2023", "01/01/2022", 0.04, 950)
Consider a scenario where you hold a security with a face value of $500, an annual interest rate of 5%, and it pays interest at maturity. The security's issue date is August 15, 2021, the settlement date is January 20, 2022, and its maturity is set for October 31, 2024. You acquired the security for $520. To determine the annual yield in this case, apply the YIELDMAT formula: =YIELDMAT("01/20/2022", "10/31/2024", "08/15/2021", 0.05, 520)
Consider a scenario where you hold a security with a face value of $500, an annual interest rate of 5%, and it pays interest at maturity. The security's issue date is August 15, 2021, the settlement date is January 20, 2022, and its maturity is set for October 31, 2024. You acquired the security for $520. To determine the annual yield in this case, apply the YIELDMAT formula: =YIELDMAT("01/20/2022", "10/31/2024", "08/15/2021", 0.05, 520)
Tips & notes
Ensure that the dates provided to the YIELDMAT function are in the correct Excel date format or reference valid date values stored in cells. Adjust the function parameters according to the specifics of the security you are analyzing, such as the interest rate, settlement, maturity, and issue dates.
Common questions
How does the YIELDMAT function calculate the annual yield of a security?
The YIELDMAT function calculates the annual yield of a security using a sophisticated algorithm that considers parameters like the settlement date, maturity date, issue date, interest rate, and purchase price. By factoring in these inputs, YIELDMAT delivers an accurate estimate of the security's annual yield.
Can the YIELDMAT function handle securities with irregular interest payment schedules?
Yes, the YIELDMAT function is capable of handling securities that pay interest at maturity, even if they have irregular interest payment schedules. It focuses on determining the annual yield based on the specific dates and rates of the security, providing a comprehensive view of the investment's return.
Is it possible to customize the day-count basis when using the YIELDMAT function?
Absolutely! The YIELDMAT function allows users to customize the day-count basis according to their preference. You can specify a particular day-count basis using the optional Basis argument, tailoring the calculation to suit specific requirements beyond the default basis option.