What is the YIELDMAT Function?
The YIELDMAT function is categorized under FINANCIAL functions. It will return the annual yield of a security that pays an interest at maturity.
In financial analysis, YIELDMAT can be useful in calculating the yield on a bond that pays an interest at maturity. The annual yield is the total increase in value in one year.
=YIELDMAT(settlement, maturity, issue, rate, pr, [basis])
The YIELDMAT function uses the following arguments:
- Settlement (required argument) – It is the settlement date of the security. The date should be after the issue date when the security is traded to the buyer.
The settlement date is the date a buyer purchases a coupon, such as a bond. The maturity date is the date when the coupon expires. For example, suppose a 30-year bond is issued on January 1, 2009, and is purchased by a buyer six months later. The issue date would be January 1, 2009, the settlement date would be July 1, 2009, and the maturity date would be January 1, 2039, which is 30 years after the issue date of January 1, 2009.
- Maturity (required argument) – It is the maturity date of the security or when the security expires.
- Issue (required argument) – It is the issue date of the security. It is expressed as a serial date number.
- Rate (required argument) – It is the interest rate of the security at the date of issue.
- Pr (required argument) – It is the security’s price per $100 face value.
- Basis (optional argument) – It is the type of count day basis to use:
|Basis||Day Count basis|
|0 or omitted||US(NASD) 30/360|
How to use the YIELDMAT Function in Excel?
As a worksheet function, YIELDMAT can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let us consider an example:
Let us calculate the annual yield for a security using the following details:
- Settlement date: January 1, 2017
- Maturity date: June 30, 2018
- Issue date: July 1, 2015
- Interest rate: 6.5%
- Security price: 101
- Count basis: Actual/365
The formula used here is:
We get the results below:
Note that in the above example:
- The yield of security so calculated is formatted as percentage.
- As recommended, the settlement, maturity, and issue dates were entered as references to cells that contain dates.
Few notes about the YIELDMAT Function:
- #NUM! error – Occurs if either:
- The issue date is greater than or equal to settlement date.
- The settlement date is greater than or equal to maturity date.
- Invalid numbers are provided for the rate, pr or [basis] arguments, i.e., if either: rate < 0; pr ≤ 0; or [basis] is any number other than 0, 1, 2, 3 or 4).
- #VALUE! error – Occurs if either:
- Any of the given arguments are non-numeric.
- The given settlement, maturity or issue dates are not valid Excel dates.
- MS Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.
- Settlement, maturity, issue, and basis are truncated to integers.
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources: