How to use YIELDMAT Function in Excel

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

Syntax:= YIELDMAT (sd, md, id, rate, pr, [basis])

The YIELDMAT function syntax has the following arguments:

  • Settlement    Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
  • Maturity    Required. The security’s maturity date. The maturity date is the date when the security expires.
  • Issue    Required. The security’s issue date, expressed as a serial date number.
  • Rate    Required. The security’s interest rate at date of issue.
  • Pr    Required. The security’s price per $100 face value.
  • Basis    Optional. The type of day count basis to use.
Basis Day count basis
0 or omitted US (NASD) 30/360
1 Actual/actual
2 Actual/360
3 Actual/365
4 European 30/360

Example: Let’s look at some Excel YIELDMAT function examples and explore how to use the YIELDMAT function as a worksheet function in Microsoft Excel:

In the following spreadsheet, the Excel Yieldmat function is used to calculate the annual yield for a security purchased on 01-Feb-2020, with issue date 01-Nov-2019 and maturity date 01-Oct-2020. The interest rate at date of issue is 5.75% and the security has a price of $99.125 per $100 face value. The US (NASD) 30/360 day count basis is used:

YIELDMAT Function - How to use YIELDMAT Function in Excel

Syntax:  =YIELDMAT(B1,B2,B3,B4,B5,B6)

Result: 7.02%

YIELDMAT Function in Excel - How to use YIELDMAT Function in Excel

Note:

  1. #NUM! error – Occurs if either:
    1. The issue date is greater than or equal to the settlement date.
    2. The settlement date is greater than or equal to the maturity date.
    3. 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).
  2. #VALUE! error – Occurs if either:
    1. Any of the given arguments are non-numeric.
    2. The given settlement, maturity, or issue dates are not valid Excel dates.
  3. 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.
  4. Settlement, maturity, issue, and basis are truncated to integers.

Add a Comment

Your email address will not be published. Required fields are marked *