YIELDMAT Function
An Excel Financial Function that is used for calculating the interest paid at the maturity of security to return its annual yield.
What is the YIELDMAT Function?
The YIELDMAT function is an Excel Financial Function that is used for calculating the interest paid at the maturity of security to return its annual yield.
Similar to the YIELDDISC function, the YIELDMAT finds the annual yield of a security, but with the addition of interest payments.
Commonly used as a financial computation, one can determine the security's or bond's yield paid at an interest.
However, if there is a negative number in the function, the YIELDMAT function cannot compute a value. Similarly, non-numerical variables cannot be calculated to determine a matured security's annual yield.
If interested in exploring the various Excel functions, try similar ones to the YIELDMAT function. Firstly, the YIELD function will also return the annual yield of a security.
Secondly, the IRR function can be used to find the internal rate of return. The most common scenario for using the IRR function is calculating the internal rate of return for an investment or various projects.
Key Takeaways
- The YIELDMAT function is an Excel Financial Function that is used to calculate the annual yield of a security that matures at maturity.
- Users provide arguments, including the settlement date, maturity date, issue date, rate, yield, redemption value, and basis. The system returns the security's annual yield.
- Potential errors that users might encounter when using the YIELDMAT function, such as providing invalid input values or referencing cells with incorrect data, and how to address them effectively.
- YIELDMAT function finds applications in bond valuation, fixed-income analysis, and portfolio management, where calculating the yield of security at maturity is important for assessing investment risk and return.
YIELDMAT Function Formula
The formula to follow for calculating the annual yield of a matured security is as follows:
YIELDMAT(settlement, maturity, issue, rate, pr, [basis]).
Breaking the formula down, let's discuss the importance of each component, starting with the settlement.
The settlement date is the date after which the security was purchased or issued to the buyer. The settlement is also a crucial aspect of the function as it considers the opening date of the deposit.
Next is the maturity date, which is also an important and required aspect of the function. The maturity (MAT) date determines when the security or bond expires and is fully paid off by the buyer.
Similar to the settlement date, the issue component is another required function aspect. The issue date is when the buyer purchased the matured security.
The rate, which helps differentiate the calculation between a discounted security and a matured security, uses the interest rate of the purchased security.
Lastly, of all the required aspects, is the pr – the security's face value price.
NOTE
You can include the basic component to calculate the count for the days the security is used.
YIELDMAT Function Example
Let's see an example of a matured security paid at a 5% rate.
In this example, the YIELDMAT function is utilized, with further description in the top right-hand corner, to calculate the annual yield at maturity. The annual yield of the matured security returns 0.049993057 or about 5%.
Another example is a ten-year bond with a higher interest rate of 7.5%.
The structure remains the same, with the same formatting for the settlement, maturity, and issue dates. In this second example, the YIELDMAT function returns an annual yield of 0.074984378, or about 7.5%.
Suppose you receive a #NUM! Error, consider one of the following may be inputted incorrectly:
- The settlement date occurs after the maturity date.
- The interest rate is less than zero.
- The Pr is less than zero.
Free Resources
To continue learning and advancing your career, check out these additional helpful WSO resources:
or Want to Sign up with your social account?