YIELDMAT Function in Excel

In this article, you will learn about the YIELDMAT function, the formula syntax and usage of the function in Microsoft Excel.

YIELDMAT Function in Excel

The YIELDMAT function in Excel returns the annual yield of a security that pays interest at maturity.

Syntax

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

Arguments:

  • Settlement    Required. – The security’s settlement date.
  • Maturity    – The security’s maturity date. The maturity date is the date when the security expires.
  • Issue    – The security’s issue date is expressed as a serial date number.
  • Rate    – The security’s interest rate at the date of issue.
  • Pr – The security’s price per $100 face value.
  • Basis  – The type of day count basis to use (Optional).

Usage Notes and Possible Errors

  • As we know Excel stores dates as sequential series of numbers, i.e., January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 as it is 39,448 days after January 1, 1900.
  • The settlement date is the date a buyer purchases a coupon.
  • The maturity date is the date when a coupon expires
  • Settlement, maturity, issue, and basis are truncated to integers.
  • In case of settlement, maturity, or issue date is not a valid date, YIELDMAT returns the #VALUE! error value.
  • When rate < 0 or if pr ≤ 0, YIELDMAT returns the #NUM! error value.
  • If basis < 0 or if basis > 4, YIELDMAT returns the #NUM! error value.
  • When settlement ≥ maturity, YIELDMAT returns the #NUM! error value.

How to use the YIELDMAT function in Excel?

Using this function in a Worksheet is simple; all you need to do is enter the function as a formula of the cell in the formula bar.

Take a look at the given example

How to use the YIELDMAT function in Excel?

Enter the data in the respective columns and enter the YIELDMAT formula.

Formula: =YIELD(A2,B2,C2,D2,E2,F2,G2)

Here, A2 refers to the cell name or the cell address. You will get the result in the Yield column.