How to use YIELDMAT function in Excel?

The YIELDMAT function in Excel is used to calculate the annual yield of a security that pays interest at maturity. This function is particularly useful for evaluating bonds or other financial instruments that do not pay regular coupon interest but instead (or additionally) repay at maturity.

Here’s how you can use the YIELDMAT function:

Syntax

YIELDMAT(settlement, maturity, issue, rate, pr, [basis])

Arguments

settlement: This is the date on which you purchase the security. It must be noted as a valid date.
maturity: This is the date when the security will expire, and the principal is to be repaid. It must also be a valid date.
issue: This is the date when the security was initially issued. Again, it must be noted as a valid date.
rate: This represents the annual coupon rate of the security.
pr: This stands for the price per $100 face value of the security.
[basis]: This is an optional argument. It represents the type of day count basis to use. Excel provides several different bases:

0 or omitted: US (NASD) 30/360
1: Actual/actual
2: Actual/360
3: Actual/365
4: European 30/360

Example Usage

Suppose you have a security with the following details:

Settlement Date: April 15, 2023
Maturity Date: April 15, 2025
Issue Date: April 15, 2021
Annual Rate: 6%
Price: $95 per $100 face value
Basis: US (NASD) 30/360

You can calculate the yield using the YIELDMAT function as follows:

=YIELDMAT(DATE(2023, 4, 15), DATE(2025, 4, 15), DATE(2021, 4, 15), 0.06, 95, 0)

This will return the annual yield on the security.

Important Notes

Date inputs should be entered using the `DATE` function or as serial numbers to avoid potential errors with date interpretation.
Ensure that the settlement date is after the issue date and before the maturity date; otherwise, the function will return an error.

By understanding these components and inputs, you can effectively use the YIELDMAT function to perform yield calculations for a variety of financial securities that mature at a certain date.

facebooklinkedin