How to Use the YIELDMAT Function in Excel
Today we will explore the YIELDMAT function, a valuable tool in Microsoft Excel and Google Sheets for calculating the annual yield of a security that pays interest at maturity. In this discussion, we’ll break down its syntax, explore practical applications through examples, and demonstrate how to employ this function effectively.
Understanding the YIELDMAT Function
The YIELDMAT function is designed to compute the annual yield of securities that pay interest when they mature. Here’s the syntax for this function:
=YIELDMAT(settlement, maturity, issue, rate, pr, basis)- Settlement: The date when the security settlement occurs.
- Maturity: The date when the security matures.
- Issue: The date when the security was issued.
- Rate: The annual coupon rate of the security.
- Pr: The price of the security per $100 face value.
- Basis: The day count basis to be used in the calculation.
Practical Applications of YIELDMAT
Let’s examine some scenarios where the YIELDMAT function can be effectively applied:
| Task | Description |
|---|---|
| Calculating yield of a security | Determine the yield on a security given its settlement, maturity, issue dates, annual coupon rate, price, and day count basis. |
| Comparing yields | Evaluate and compare the yields of different securities using the YIELDMAT function by adjusting respective parameters. |
Using YIELDMAT in Excel
In Microsoft Excel, the YIELDMAT function is utilized as follows:
=YIELDMAT("1/1/2022", "1/1/2025", "1/1/2021", 0.05, 98, 0)This formula computes the yield of a security with a settlement date of January 1, 2022, a maturity date of January 1, 2025, an issue date of January 1, 2021, a 5% annual coupon rate, a price of $98, and using a 0 day count basis.
Using YIELDMAT in Google Sheets
The functionality of YIELDMAT in Google Sheets mirrors that of Excel:
=YIELDMAT("1/1/2022", "1/1/2025", "1/1/2021", 0.05, 98, 0)This formula will also calculate the yield for a security with identical parameters as used in the Excel example.
By leveraging the YIELDMAT function, financial analysis regarding the annual yields of maturity-paying securities becomes streamlined and efficient in both Excel and Google Sheets.
More information: https://support.microsoft.com/en-us/office/yieldmat-function-ba7d1809-0d33-4bcb-96c7-6c56ec62ef6f