YIELDMAT Function
It calculates the interest paid at the maturity of security to return its annual yield.
What is the YIELDMAT Function?
The YIELDMAT function, commonly used in Microsoft Excel, calculates 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, you can find the internal rate of return with the IRR function. When using the IRR function, the most common scenario would be calculating the internal rate of return for an investment or various projects.
Key Takeaways
- The YIELDMAT function calculates interest-paid or matured security to determine its annual yield.
- A couple of similar functions that calculate the returns of a project are the YIELD function and the IRR function.
- The settlement and maturity dates must be entered as serial numbers.
- The basis component of the function, an optional input, must be entered as numerical values from one to four.
- The maturity date is an important component of the function because it includes the date at which the security is paid in full.
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, you can notice that 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.
Reviewed & edited by Parul Gupta | LinkedIn
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?